Connect by JBRH Open Connect

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 What this means
Audience
both, developer
Last verified
Product version
6.3.2

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, psycopg.org

Where Connect uses it#

Place in ConnectWhat it does there
backend/app/models.pyThe table definitions, rooted at workspaces
backend/app/db.pyEngines and sessions: the application role and the admin role
backend/app/workspace_kernel.pyThe ORM hook that filters every query on a scoped table by the current workspace
backend/app/migrate.pyAdditive schema changes run at every boot
tools/pg_harden.pyApplies 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).
  • 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.
  • Reflection-based migrations miss some index shapes. The migration guards are written to be idempotent (schema 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.

Licence, cost and versions#

QuestionAnswer
LicenceSQLAlchemy: MIT. psycopg: LGPL-3.0
CostNone
Versionsqlalchemy>=2.0, psycopg[binary]>=3.2 (floors)
CompatibilityPostgreSQL 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#

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.