orbiter_postgres 0.1.9
orbiter_postgres: ^0.1.9 copied to clipboard
PostgreSQL kit for orbiter: the pieces for using it together with slingshot_postgres (a per-request row-level-security transaction middleware and a prefix tsquery builder).
orbiter_postgres #
Multi-tenant PostgreSQL for Dart backends: one interceptor gives every request its own row-level-security transaction, so a query can only see its tenant's rows.
Row-level security lets PostgreSQL itself filter every query by tenant, but only if each request runs inside a
transaction with the tenant set, and that transaction always ends: committed before a success response, rolled
back on errors, and its connection returned to the pool even when the client disconnects mid-stream. Getting that
right by hand in every handler is where tenant data leaks come from. This package does it once, as an interceptor
for orbiter over the native pool of
slingshot_postgres: the tenant comes from your authentication, the
setting is bound as a parameter, and the handler just runs its queries.
Features #
rlsTransaction: an interceptor that opens one transaction per request, applies the tenant withset_config(..., true)as its first statement and hands the handler an executor pinned to it.- Deterministic end:
COMMITbefore a buffered response is written, so a failed commit never turns into a200already sent; rollback on4xx/5xx, on exceptions or on demand; streamed bodies finish their transaction after the last byte. - Fails closed: no tenant means no connection and no handler call; an exhausted pool answers
503withRetry-After; errors carry the tenant without leaking the server message. withTenantTransaction: the same transaction opened by a repository or use case, when a request has to call something slow outside it.- SQL-first queries:
query,queryFirstandscalaron any executor, with@nameplaceholders and inferred parameter types; the result set is always released. - Migrations and seeds:
MigratorandSeederapply numbered.sqlfiles once each, one transaction per file, with a checksum of every applied file and a lock against concurrent runs. prefixTsQuery: turns free text into a safe prefixtsqueryfor search-as-you-type over a GIN index.- Testable without a database:
rlsTransactionOnruns the same policy over anyUnitOfWorkfactory.
Install #
dart pub add orbiter_postgres
The framework and the connector come with it. Experimental (pre-1.0). The connector ships its native library
pre-built and needs libpq at run time; see its README for AOT and container builds.
The family #
| Package | Role |
|---|---|
orbiter_postgres |
This kit. |
orbiter |
HTTP backend framework, no database dependency. |
slingshot_postgres |
PostgreSQL driver over libpq, with a native connection pool. |
slingshot |
The contract every slingshot driver implements. |
rlsTransaction: one RLS transaction per request #
final handler = Pipeline()
.add(authenticate()) // yours: puts the principal in the context
.add(rlsTransaction(
pool: pool,
tenantResolver: (request) => request.context.getOrNull<Principal>()?.tenantId,
onUnresolved: (_) => Response.forbidden(),
))
.addHandler(router.handler);
// In a handler:
final rows = await request.rls.executor.execute('SELECT ...');
Per request the middleware borrows a connection through PostgresUnitOfWork,
runs SELECT set_config($name, $tenant, true) (both bound as parameters) as the
first statement of the transaction, hands the handler an executor pinned to that
transaction, and ends it deterministically. The connection always goes back to
the pool, from a cleanup hook if nothing else did.
What ends the transaction #
| Situation | Outcome |
|---|---|
| Buffered body, status below 400 | COMMIT before the response is written, so a failing commit becomes an error response, never a 200 already sent |
| Buffered body, status 400 or above | rollback (predicate shouldCommit, default commitOnSuccess) |
| Handler throws | rollback, then the error reaches the error boundary |
request.rls.setRollbackOnly() |
rollback, whatever the status |
Stream body |
decided after the handler, applied by the cleanup hook after the last byte, so the stream can keep reading through the executor |
No tenant (null or empty) |
onUnresolved; no connection is borrowed, no next() |
| Pool exhausted | 503 with Retry-After: 1 (onUnavailable) |
Errors and configuration #
Failures of the transaction itself are thrown as the sealed RlsException, so an errorHandler can switch
over it. Each carries the tenantId, and the original database error stays in cause / causeStackTrace;
toString does not repeat the server message, so mapping it to a response never leaks it by accident.
| Exception | When | What the client gets |
|---|---|---|
RlsContextException |
applying the tenant setting failed; already rolled back, connection returned | whatever the errorHandler answers |
RlsCommitException, afterResponse: false |
COMMIT failed before the response was written |
whatever the errorHandler answers |
RlsCommitException, afterResponse: true |
COMMIT of a streamed body failed after the last byte |
the response already sent; the failure only reaches onError |
The callbacks are typed: tenantResolver is a TenantResolver (FutureOr<String?> Function(Request), where
null or empty means no tenant) and shouldCommit a CommitPredicate (bool Function(Response),
commitOnSuccess by default). settingName (default app.tenant_id) must match the name the database policy
reads.
rlsTransactionOn is the same middleware over any UnitOfWork factory. It exists to exercise the policy in
tests without the native library or a database; production code uses rlsTransaction.
Rules this package cannot enforce #
- Streamed routes under RLS must be read-only. A
COMMITthat fails after the last byte reaches only the server-sideonError(RlsCommitExceptionwithafterResponse: true); the client already has its response. - Never capture the pool in a handler to run an autocommit statement on tenant data: the tenant setting does not exist there. Treat it as a review rule.
- The tenant comes from an authenticated source, never from the body, the query or an unauthenticated header.
- Routes without a tenant (health, login) go in another
Pipeline. - The database role must be neither superuser, nor table owner, nor
BYPASSRLS, and the table needsFORCE ROW LEVEL SECURITY; otherwise the policy does not apply. settingName(defaultapp.tenant_id) must match the name the policy reads withcurrent_setting(..., true). It is configuration on purpose.
Cost #
Each request adds a BEGIN, the set_config, a COMMIT and the pool's
session reset on return. The cost of this middleware has not been measured.
Tests #
test/rls_middleware_test.dart, test/tenant_transaction_test.dart and
test/prefix_ts_query_test.dart need no database. The integration tests (test/integration/postgres_live_test.dart,
rls_live_test.dart, prefix_ts_query_live_test.dart and
multi_worker_pool_live_test.dart) need PostgreSQL at
localhost:5432 (dbname=teste user=postgres password=123).
prefix_ts_query_live_test.dart creates no schema or role.
multi_worker_pool_live_test.dart runs a MultiWorkerServer whose workers each
open their own pool in the setup and close it in onShutdown, and counts the
connections in pg_stat_activity. It also pins down what happens when a worker
dies: the other workers and their pools are unaffected, but nothing closes the
dead worker's pool (onShutdown does not run), so its connections stay open
in the database until the process exits.
rls_live_test.dart uses a superuser because it creates schema rls_mw and
roles rls_mw_owner/rls_mw_app, so do not run two instances at once:
dart test
The integration tests skip silently without the library; confirm the skipped count is zero.
Stress suite (integrity under load) #
test/stress/ is tagged stress (dart_test.yaml). It asserts integrity invariants only and never emits time, throughput or latency: client and server share one event loop and the handlers sleep on purpose.
dart test -x stress # fast suite, skips the stress files
dart test -t stress -r compact
Parameters (environment): STRESS_REQUESTS (400), STRESS_USERS (32), STRESS_SEED (random, always printed; the same seed rebuilds the same intents). It uses its own schema and roles (rls_stress*), so it can run next to rls_live_test.dart.
It covers four pool configurations (1, 4, 2 without session reset, 2 with a 100 ms acquire timeout), four tenants with one hot tenant, client aborts at four moments, handlers that fail or answer 409, and a clean shutdown (server.close() then pool.close()) with requests in flight, plus a stream the client abandons before the first byte. Two more cases run withTenantTransaction directly under the same parameters (pool of 2, with and without session reset), with bodies that throw or set rollback-only after inserting, and check that no caller sees another tenant's rows or a stale local setting.
The suite SKIPS silently when the native library or Postgres is missing: check the printed executed=N skipped=M line (skipped must be 0).
withTenantTransaction: one RLS transaction opened by the repository #
rlsTransaction keeps the transaction open for the whole handler. When a use
case has to call something slow or external (a KMS, another service) outside
the transaction, open it where it is needed instead:
final appointment = await withTenantTransaction(pool, principal.tenantId,
(tx) async {
final rs = await tx.executor.execute(
r'SELECT ... FROM appointment WHERE id = $1',
params: [Param.int8(id)],
);
try {
return Appointment.fromRow(rs.row(0));
} finally {
rs.release();
}
}, localSettings: {'app.correlation_id': correlationId});
The function receives the same RlsTransaction a handler gets from
request.rls (executor, tenantId, setRollbackOnly()), and its value is
returned. The two modes are independent: an application can use either, or
both, under the same database policy.
| Situation | What happens |
|---|---|
tenant null or empty |
TenantRequiredException, before the pool is touched |
| pool exhausted | PoolExhaustedException propagates; map it (for example to 503 with Retry-After) |
| applying the settings fails | rollback, then RlsContextException |
| the function throws | rollback, and the error is rethrown |
tx.setRollbackOnly() |
rollback, and the value is still returned |
COMMIT fails |
rollback, then RlsCommitException with afterResponse: false |
The connection goes back to the pool on every exit. TenantRequiredException
is not an RlsException: there is no tenant and no database error behind it.
localSettings are other transaction-local settings applied in the same
first statement as the tenant, one set_config(name, value, true) each, every
name and value bound as a parameter:
SELECT set_config($1, $2, true), set_config($3, $4, true), .... Names must be
prefixed custom settings (app.correlation_id); an empty name, a name without
a dot or the tenant's own settingName throws ArgumentError before the pool
is touched. Without localSettings the statement is the one rlsTransaction
sends.
withTenantTransactionOn is the same function over any UnitOfWork factory,
for tests without the native library or a database. The rules above about the
database role, FORCE ROW LEVEL SECURITY and an authenticated tenant apply
here too.
SQL-first queries: query, queryFirst, scalar #
An extension on PostgresQueryExecutorContract, so it works the same on a pooled connection
(withPostgresConnection), inside withTransaction and on request.rls.executor / tx.executor:
final users = await exec.query(
'SELECT id, name FROM users WHERE org = @org AND active = @active',
User.fromRow, // User Function(PostgresResultRow row)
params: {'org': orgId, 'active': true},
);
final user = await exec.queryFirst(
'SELECT id, name FROM users WHERE id = @id', User.fromRow, params: {'id': id});
final total = await exec.scalar<int>('SELECT count(*) FROM users WHERE org = @org',
params: {'org': orgId});
The mapper gets the row itself (row['name'], row.getInt('id')); a fromJson constructor can take
row.toMap(). The result set is released when mapping ends, also when the mapper throws.
Placeholders. @name is rewritten to $1, $2, … once per SQL text and cached (256 texts per
isolate). A name used twice maps to the same $N. Nothing is rewritten inside string literals ('...',
E'...'), quoted identifiers, -- and /* */ comments or dollar-quoted bodies, nor in the operators
@>, <@, @@ and @-@. A missing value throws ArgumentError before the query is sent. SQL that
mixes $1 with @name is rejected. Write the absolute-value operator as @ x or abs(x): @x is a
placeholder.
Parameter types.
| Dart value | Sent as |
|---|---|
Param |
itself |
int |
int8 |
double |
float8 |
bool |
bool |
String |
text |
DateTime |
timestamptz |
Uint8List |
bytea |
Iterable |
an array: int8[], float8[], bool[] or text[] by element |
null |
NULL with no type; taken from context |
A String is text, so a uuid, jsonb, numeric, inet or enum column needs a cast in the SQL
(WHERE id = @id::uuid) or an explicit parameter ({'id': Param.uuid(id)}). A List or Set becomes a
one-dimensional array for WHERE id = ANY(@ids): its non-null elements must all be int, double, bool or
String, with nulls allowed among them; uuid strings make a text[], so write ANY(@ids::uuid[]) or pass
Param.uuidArray(ids). An empty list, or one with only nulls, goes untyped and takes its type from context. Mixed
element types throw ArgumentError. Maps are not inferred: pass Param.jsonb(jsonEncode(value)). Any other type
throws ArgumentError. A null takes its type
from where it is used (an INSERT column, a comparison, an operator); where nothing fixes it (@x IS NULL, a
polymorphic argument such as pg_typeof(@x)) the server answers "could not determine data type": cast it (@x::int8) or pass
Param.nullValue(PostgresOid.int8).
Keys in params that the SQL does not use are ignored, so one map can serve several queries. The exception is
SQL with no placeholder at all: non-empty params there throws, because it usually means $1 was written
where @name was meant.
For a PostgresRepository, namedStmt(sql, params) returns the positional (sql, params) record its
*SqlParams hooks expect.
Several queries in one round-trip. queryBatch runs independent @name queries, each with its own
values and mapper, and returns one list of mapped rows per query, in order:
final out = await exec.queryBatch([
SqlBatchQuery('SELECT id, name FROM users WHERE id = @id', User.fromRow, params: {'id': id}),
SqlBatchQuery('SELECT * FROM grants WHERE user_id = @id', Grant.fromRow, params: {'id': id}),
]);
final users = out[0].cast<User>();
final grants = out[1].cast<Grant>();
Each list comes back as a List<Object?>, so read it with .cast<T>(); out[1] as List<Grant> throws.
When the executor implements PostgresBatchExecutor (as slingshot_postgres's own does, from 0.1.14) the
statements reach the server in one round-trip, through the statement cache when it is on. A wrapping executor
that does not forward the capability runs them one after the other, with the same results. The queries must not
depend on each other; the first failure throws and the rest are not run.
Cost #
A hash lookup of the SQL text and one Param per value on each call. Measured on an Apple M4 against
PostgreSQL 16, the cached rewrite plus binding costs about 80 ns per call, around 0.03% of a single-row query
(~240 µs p50 over one connection). End to end, query<T> and a direct execute with the same SQL and mapper
stayed within the run's noise (p50 +1.3% over 10 interleaved repetitions), which that setup cannot resolve
below a few percent.
Migrations and seeds: Migrator, Seeder #
final applied = await Migrator(pool).run(await SqlScript.loadDirectory('migrations'));
await Seeder(pool).run(await SqlScript.loadDirectory('seeds'));
Files are named <version>_<description>.sql (001_init.sql, 2_users.sql, 10_index.sql) and run in
numeric order of the version, each at most once. loadDirectory reads the *.sql files directly inside the
directory; to ship the SQL inside an AOT binary, build the list yourself:
[SqlScript('001_init.sql', initSql), …].
- Whole file, one transaction. The file goes to the server in one call, so functions,
DO $$ … $$blocks and;inside literals work as written. It runs in a transaction together with its row in the bookkeeping table: if any statement fails, nothing of that file stays, the error is aMigrationExceptionnaming the file (with the server error ascause), and the run stops. Files applied before it in the same run stay. Do not putCOMMIT,ROLLBACKorENDin such a file: it ends the transaction early, and PostgreSQL then commits the rest of the file on its own. The run detects it and stops without recording the file, whose statements most likely took effect; check the database before running again. - Outside a transaction. A file whose first non-blank line is
-- orbiter:no-transaction(surrounding spaces ignored) runs withoutBEGIN, for statements PostgreSQL refuses inside one (CREATE INDEX CONCURRENTLY). If it fails halfway, its earlier statements may have taken effect; keep such files to a single statement. If the file succeeds but its bookkeeping row cannot be written, the error says so: the file would run again next time. - Applied files must not change. Each applied file is recorded with its CRC-32 (line endings normalised).
Before anything runs, every recorded file is checked against the one given: an edited file, or a recorded
version missing from the set, throws
MigrationException. Change the schema with a new file. - Concurrent runs. A PostgreSQL advisory lock keyed on the bookkeeping table serialises runs, so several instances starting at once apply each file once. The run releases the lock on every exit; if the process dies, the server releases it when the connection closes.
- Bookkeeping tables.
orbiter_migrationsandorbiter_seeds(version,name,checksum,applied_at), created when missing; passtable:to use another name, optionally schema-qualified. - Out of order. A new file with a version below the highest applied one (two branches merged) is applied.
- No
down. Undo a change with a new file.
Seeder follows the same rules with its own table, so seeds version independently of the schema. "Once" is per
file: write seed SQL that tolerates existing rows (ON CONFLICT DO NOTHING) when they may already be there.
prefixTsQuery: prefix search from free text #
Turns what a user types into a prefix tsquery, safe to bind as a parameter:
prefixTsQuery('jo sil'); // "'jo':* & 'sil':*"
prefixTsQuery("d'avila & !"); // "'d':* & 'avila':*"
prefixTsQuery(' &|! '); // null
With a generated tsvector column and a GIN index:
CREATE TABLE person (
id serial PRIMARY KEY,
name text NOT NULL,
doc tsvector GENERATED ALWAYS AS (to_tsvector('simple', name)) STORED
);
CREATE INDEX person_doc ON person USING gin (doc);
final q = prefixTsQuery(term);
if (q == null) return Response.badRequest(); // or run the query without the filter
final rows = await request.rls.executor.execute(
"SELECT id, name FROM person WHERE doc @@ to_tsquery('simple', \$1)",
params: [Param.text(q)],
);
Only runs of Unicode letters, combining marks and digits survive; every other
character (& | ! ( ) : * < - >, quotes, backslashes, spaces, punctuation) is
a separator. Each word becomes a quoted prefix lexeme, joined with &.
- Normalise the input the way the index was built. Case is folded by the
text search configuration, but accents are kept: if the
tsvectorcomes fromunaccent(...), apply the same removal to the term. nullmeans no word: skip the filter.to_tsqueryon an empty string matches nothing.- Always bind the result (
Param.text); never concatenate it into SQL.
Cost #
One regular expression pass over the input. Not measured.
Example: CRUD server #
example/main.dart wires BackendServer, Pipeline and Router to the native pool. It
expects a users table:
CREATE TABLE users (id serial PRIMARY KEY, name text NOT NULL, email text NOT NULL UNIQUE);
POSTGRES_HOST=localhost POSTGRES_DB=teste POSTGRES_USER=postgres POSTGRES_PASSWORD=123 \
dart run example/main.dart
Endpoints: GET /health, GET /api/v1/users (first 50), GET /api/v1/users/:id, POST /api/v1/users
(inside a transaction). Error responses carry generic messages; exception details and PostgreSQL server
messages are logged on the server only. The server shuts down on SIGINT and SIGTERM.
test/integration/postgres_live_test.dart runs against the same database and skips when it, or the native
library, is unavailable.
Benchmarks and research #
The project started as an MBA thesis in Software Engineering at the University of São Paulo (USP). The
cross-language benchmark suite, its results and the evaluated code are public at
tganzerli/dart_db_connector (see
benchmarks/RESULTS.md). The
cost of this kit's middleware has not been measured.
License #
MIT, see LICENSE.