dust_db_postgres #

PostgreSQL runtime for Database code generated by Dust.

This package implements Dust's driver-independent Connection, Executor, and Row contracts over package:postgres. It is an adapter: Dust validates your SQL against your migrations at build time and generates the row mapping, and this executes it.

Database is beta. It uses raw SQL with build-time validation; it is not an ORM or a query builder.

Installation #

dart pub add dust_dart dust_db_postgres

PostgreSQL is reached over a socket, so this runs on native Dart and Flutter targets, not on the web.

Quick start #

Write migrations in ./migrations:

-- migrations/0001_create_orders.sql
CREATE TABLE orders (
  id        BIGSERIAL PRIMARY KEY,
  item      TEXT NOT NULL,
  quantity  INTEGER NOT NULL CHECK (quantity > 0),
  placed_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Declare the database and its queries:

import 'package:dust_dart/db.dart';
import 'package:dust_db_postgres/dust_db_postgres.dart';

part 'app_database.g.dart';

@SqlxDatabase(type: SqlxDatabaseType.postgres, migrations: './migrations')
abstract class AppDatabase implements DatabaseClient {
  factory AppDatabase.connect(String url, {PgConnectOptions? options}) =
      _$AppDatabase.connect;

  @override
  Connection get connection;
}

@Derive([FromRow()])
@Sqlx(renameAll: SqlxRename.snakeCase)
final class Order {
  const Order({required this.id, required this.item, required this.quantity});

  final int id;
  final String item;
  final int quantity;
}

@SqlxDao()
abstract final class OrdersRepo {
  const factory OrdersRepo(Executor db) = _$OrdersRepo;

  @Query(r'SELECT id, item, quantity FROM orders WHERE id = ANY($1) ORDER BY id')
  Future<Result<List<Order>, SqlxError>> byIds(List<int> ids);
}

Generate and validate:

DUST_DATABASE_URL='postgres://user:pw@localhost:5432/app?sslmode=disable' dust db build

Then use it:

final database = AppDatabase.connect(url);
await database.migrate();

final orders = await OrdersRepo(database.connection).byIds(<int>[1, 2]);
switch (orders) {
  case Ok(:final value):
    print(value.map((order) => order.item));
  case Err(:final error):
    print('lookup failed: $error');
}

Connecting #

The URL is postgres://user:password@host:port/database. ?sslmode= is read from it, as every other PostgreSQL tool reads it:

libpq's prefer and allow are rejected rather than guessed: they mean "try TLS, fall back to plaintext", and this driver has no such mode.

Explicit PgConnectOptions win over the URL:

AppDatabase.connect(
  url,
  options: const PgConnectOptions(
    sslMode: PgSslMode.verifyFull,
    connectTimeout: Duration(seconds: 5),
    applicationName: 'orders-api',
  ),
);

Migrations #

migrate() applies any migration the database has not run, in name order, inside one transaction and under a PostgreSQL advisory lock — several servers can start against one database at once, and the lock is what stops them racing to apply the same migration.

Applied migrations are recorded in __dust_schema_migrations, the same table the SQLite runtime uses.

What differs from SQLite #

The query text does not. $1 is what you write on either dialect: PostgreSQL reads it natively, and dust_db_sqlite3 rewrites it to ? at bind time.

ExecResult.lastInsertId is always null here: PostgreSQL has no counterpart to SQLite's last_insert_rowid().

Unchecked SQL #

Migrations, EXPLAIN, one-off administrative work — the cases build-time validation cannot reach:

await database.unsafe.execute('VACUUM', const []);

unsafe is on the database facade and not on Executor, so a request handler holding an executor cannot reach it. Each use warns; a dust:allow-unsafe-sql comment on the call or the line above silences one.

Testing #

dart test runs everything that needs no server. The integration suite is skipped unless DUST_DATABASE_URL names a database it may write to:

DUST_DATABASE_URL='postgres://user:pw@localhost:5432/dust_test?sslmode=disable' dart test

There is no in-memory PostgreSQL, which is also why dust db build needs a server and CI validates from the committed query cache instead.

Documentation #

• Examples — one file per question, from opening a pool to jsonb and timestamptz
• Database usage guide
• Design notes

License #

MIT. See LICENSE.