# SQLAlchemy and psycopg

SQLAlchemy is the library through which Connect reads and writes every record, and psycopg 3 is the driver that carries those queries to PostgreSQL. Connect's workspace kernel hooks into SQLAlchemy so every query on a scoped table is filtered by workspace before it reaches the database. PostgreSQL's own row-level security then checks again.

- **Status:** Reference
- **Audience:** both, developer
- **Last verified:** 2026-11-02
- **Canonical:** https://connectbyjbrh.com/docs/technology/sqlalchemy/

## What it is

SQLAlchemy is a Python SQL toolkit and object-relational mapper, created by Mike Bayer and first released in 2006. Version 2.0, released in 2023, unified the query style around `select()` and made typed, explicit sessions the norm.

psycopg is the most widely used PostgreSQL adapter for Python. Version 3 is a rewrite with native async support, server-side binding and a binary build (`psycopg[binary]`) that bundles its C library, so the host needs no compiler.

**Category** — ORM and SQL toolkit; database driver
**Versions in Connect** — `sqlalchemy>=2.0`; `psycopg[binary]>=3.2`
**Connection URL form** — `postgresql+psycopg://…` (the value lives in the host's environment)
**Official sites** — [sqlalchemy.org](https://www.sqlalchemy.org/), [psycopg.org](https://www.psycopg.org/)

## Where Connect uses it

| Place in Connect | What it does there |
|---|---|
| `backend/app/models.py` | The table definitions, rooted at `workspaces` |
| `backend/app/db.py` | Engines and sessions: the application role and the admin role |
| `backend/app/workspace_kernel.py` | The ORM hook that filters every query on a scoped table by the current workspace |
| `backend/app/migrate.py` | Additive schema changes run at every boot |
| `tools/pg_harden.py` | Applies and verifies the row-level security policies, separately from the app |

## How it connects

1. A handler opens a session inside a workspace context
2. The workspace kernel adds the workspace filter to each query on a scoped table
3. SQLAlchemy compiles the SQL, and psycopg sends it with bound parameters
4. PostgreSQL applies the forced row-level security policy for the role
5. Rows come back as model objects, or as plain rows for reports

## Benefits and limits

What it gives Connect:

- The kernel makes isolation the default rather than a filter each query must remember. This is the second of three isolation layers ([multi-tenancy](/docs/technology/multi-tenancy/)).
- Bound parameters throughout, so values never become SQL text.
- One model definition serves the schema, the migrations' checks and the queries.

Where it stops, and what Connect does about it:

- An ORM makes N+1 queries easy to write. Connect measures statement counts per screen rather than timing, see [query cost](/docs/technology/query-cost/).
- Reflection-based migrations miss some index shapes. The migration guards are written to be idempotent ([schema migrations](/docs/technology/migrations/)).
- psycopg is LGPL-3.0. Connect uses it unmodified, as a library, which that licence permits.

## Security and privacy

- The application connects with a role that cannot weaken its own policies. Policies come from a separate hardening step.
- A table with a `workspace_id` that is missing from the scoped list is scoped by nothing. That is why the list is a checked artefact.
- The database password is never on these pages or in the code. See [secret management](/docs/technology/secret-management/).

## Licence, cost and versions

| Question | Answer |
|---|---|
| Licence | SQLAlchemy: MIT. psycopg: LGPL-3.0 |
| Cost | None |
| Version | `sqlalchemy>=2.0`, `psycopg[binary]>=3.2` (floors) |
| Compatibility | PostgreSQL 16 on the host; Python 3.12. SQLite is used only for limited local experiments, and process locks fall back when it is |

## Troubleshooting

**A page gets slower as a customer grows** — Count statements for the screen. A loop issuing one query per row is the usual cause.
**`permission denied for table` after adding a table** — The new table is not covered by the hardening step's grants or policies yet.
**A query returns another workspace's rows in a test** — It was run without the workspace context, or as the admin role. Production's row-level security would still refuse it.

## Official references

- [SQLAlchemy 2.0 documentation](https://docs.sqlalchemy.org/en/20/)
- [psycopg 3 documentation](https://www.psycopg.org/psycopg3/docs/)
- [PostgreSQL row security policies](https://www.postgresql.org/docs/16/ddl-rowsecurity.html)

## Questions

### Does every query go through the ORM?

Nearly all do. Reports and hardening scripts sometimes use Core SQL or raw statements. These still run as a role subject to row-level security, so the database's layer applies whichever route is used.

### Why psycopg 3 rather than psycopg2?

It is the maintained line, supports async natively, and its binary wheel installs without build tools. The requirements file records that it once went missing and a fresh host could not reach its database. It is now declared.

## Related

- [PostgreSQL](https://connectbyjbrh.com/docs/technology/postgresql/)
- [Row-level security](https://connectbyjbrh.com/docs/technology/row-level-security/)
- [Schema migrations](https://connectbyjbrh.com/docs/technology/migrations/)
- [Query cost](https://connectbyjbrh.com/docs/technology/query-cost/)

## What this page is based on

- `requirements.txt`
- `backend/app/db.py`
- `backend/app/workspace_kernel.py`
- AGENTS.md §4
- https://docs.sqlalchemy.org/en/20/
