# Tutorial: SQL Data Filtering

This tutorial demonstrates end-to-end data filtering with OPA around a concrete question: **whose salaries can a Director see?**

You will write an authorization policy, use OPA's partial evaluation to derive a SQL `WHERE` clause, and apply that filter to a real database query.

## Prerequisites

*   [OPA installed](/docs/#1-download-opa)
*   [sqlite3](https://sqlite.org/index.html) (pre-installed on macOS and most Linux distributions)
*   `curl` and `jq`

## Steps

### 1\. Create and populate the database

This tutorial works with the following dataset:

| name | department | role | salary |
| --- | --- | --- | --- |
| Alice | engineering | director | 130000 |
| Bob | engineering | engineer | 90000 |
| Carol | engineering | engineer | 85000 |
| Dave | marketing | director | 120000 |
| Eve | marketing | manager | 95000 |

Save the following SQL to a file named `employees.sql`:

employees.sql

```
CREATE TABLE employees (name TEXT, department TEXT, role TEXT, salary INTEGER);INSERT INTO employees VALUES ('Alice', 'engineering', 'director', 130000);INSERT INTO employees VALUES ('Bob',   'engineering', 'engineer',  90000);INSERT INTO employees VALUES ('Carol', 'engineering', 'engineer',  85000);INSERT INTO employees VALUES ('Dave',  'marketing',   'director', 120000);INSERT INTO employees VALUES ('Eve',   'marketing',   'manager',   95000);
```

Then create the database by loading that file:

```
sqlite3 company.db < employees.sql
```

### 2\. Write the policy

The rule is: Directors may see the salaries of employees in their own department.

`input.employees` is declared as _unknown_ — it represents database rows that OPA has not seen yet. `input.user` is _known_ at query time and its values will be substituted during partial evaluation.

Save the following Rego code to a file named `policy.rego`:

policy.rego

```
# METADATA# scope: package# compile:#   unknowns: [input.employees]package filtersinclude if {    input.user.role == "director"    input.employees.department == input.user.department}
```

### 3\. Start OPA

```
opa run --server policy.rego
```

OPA is now listening on `http://localhost:8181`.

### 4\. Ask OPA for a SQL filter

In another terminal, call the compile endpoint with the logged-in user as input. Alice is a Director in Engineering:

```
curl -s -X POST http://localhost:8181/v1/compile/filters/include \  -H "Content-Type: application/json" \  -H "Accept: application/vnd.opa.sql.sqlite+json" \  -d '{"input": {"user": {"name": "alice", "role": "director", "department": "engineering"}}}'
```

OPA partially evaluates the policy:

*   `input.user.role == "director"` — both sides are known; the condition is true, so it is consumed.
*   `input.employees.department == input.user.department` — the left hand side is unknown; the known right hand side (`"engineering"`) is substituted, yielding the SQL condition.

The response:

```
{  "result": {    "query": "WHERE employees.department = 'engineering'"  }}
```

### 5\. Query the database

Extract the filter and use it in a SQL query:

```
FILTER=$(curl -s -X POST http://localhost:8181/v1/compile/filters/include \  -H "Content-Type: application/json" \  -H "Accept: application/vnd.opa.sql.sqlite+json" \  -d '{"input": {"user": {"name": "alice", "role": "director", "department": "engineering"}}}' \  | jq -r '.result.query')sqlite3 company.db "SELECT name, salary FROM employees $FILTER;"
```

Output — Alice sees all Engineering salaries:

| name | salary |
| --- | --- |
| Alice | 130000 |
| Bob | 90000 |
| Carol | 85000 |

Dave is a Director in Marketing, so he gets a different filter from the same policy:

```
FILTER=$(curl -s -X POST http://localhost:8181/v1/compile/filters/include \  -H "Content-Type: application/json" \  -H "Accept: application/vnd.opa.sql.sqlite+json" \  -d '{"input": {"user": {"name": "dave", "role": "director", "department": "marketing"}}}' \  | jq -r '.result.query')sqlite3 company.db "SELECT name, salary FROM employees $FILTER;"
```

Output — Dave sees all Marketing salaries:

| name | salary |
| --- | --- |
| Dave | 120000 |
| Eve | 95000 |

### 6\. Non-Directors are denied

Bob is an Engineer, not a Director. The `input.user.role == "director"` condition is known and false, so no rule body can ever be satisfied — the policy unconditionally denies:

```
curl -s -X POST http://localhost:8181/v1/compile/filters/include \  -H "Content-Type: application/json" \  -H "Accept: application/vnd.opa.sql.sqlite+json" \  -d '{"input": {"user": {"name": "bob", "role": "engineer", "department": "engineering"}}}'
```

Response — the `query` key is absent:

```
{}
```

An absent `query` means unconditional deny. The application should return zero rows without issuing a database query.

Ensure safe defaults

OPA returns the filter — it does not enforce it. The application is responsible to use it as intended.

In this example, if the user is not a Director, no rule body can be satisfied and OPA returns an unconditional deny — represented as a missing `query` key in the result — meaning the application should safely return zero rows.

## What partial evaluation did

OPA evaluated the policy with `input.user` fully known. The expressions that involved only known values (`input.user.role == "director"`) were fully evaluated and consumed — they do not appear in the output. Only expressions involving the unknown `input.employees` survived as residual conditions, which OPA then translated into SQL.

The application never needs to know _how_ the policy decides which salaries are visible. It sends user context and receives a SQL filter (or a deny) to act on.

## Handling unconditional results

| OPA response | Meaning | Application action |
| --- | --- | --- |
| `{ "query": "WHERE ..." }` | Conditional allow | Append filter to SQL query |
| `{ "query": "" }` | Unconditional allow | Run query with no `WHERE` |
| `{}` | Unconditional deny | Return zero rows, skip query |

## Clean up

Stop the OPA server with `Ctrl+C` in the terminal where it is running, then remove the files created during this tutorial:

```
rm employees.sql policy.rego company.db
```

## Next steps

*   [Evaluating a Data Filter Policy](/docs/filtering/partial-evaluation) — a step-by-step walkthrough of partial evaluation
*   [Writing valid Data Filtering Policies](/docs/filtering/fragment) — which Rego constructs are supported as filter conditions
*   [Language SDKs](/ecosystem#languages) — in a production setup, using a language SDK is recommended over raw `curl` calls. The ecosystem page lists SDKs for Go, Java, Python, JavaScript, and more, all of which provide typed clients for the compile API used in this tutorial.