PostgreSQL

Use PostgreSQL for production‑grade features (JSONB, window functions, robust constraints). Configure via URL or discrete fields.

Install

Add the driver package:

dependencies:
  ormed: ^0.1.0
  ormed_postgres: ^0.1.0

Configure (ormed.yaml)

URL

driver:
  type: postgres
  options:
    url: postgres://postgres:postgres@localhost:6543/orm_test
    sslmode: disable # disable | require | verify-full
    schema: public
    session:
      search_path: public
    init:
      - SET application_name = 'ormed'

Fields

driver:
  type: postgres
  options:
    host: localhost
    port: 5432
    database: orm_test
    username: postgres
    password: postgres
    sslmode: disable # disable | require | verify-full
    schema: public

Use in code (DataSource)

import 'package:ormed/ormed.dart';
import 'package:ormed_postgres/ormed_postgres.dart';

void main() async {
  // 1. Register the driver
  ensurePostgresDriverRegistration();

  // 2. Load configuration (usually from ormed.yaml)
  final config = loadOrmConfig();

  // 3. Create DataSource
  final ds = DataSource.fromConfig(config);

  // 4. Initialize (connects to DB)
  await ds.init();

  // Use the datasource...
}

Type support

Most PostgreSQL column types map to plain Dart types (int, double, String, bool, DateTime). For richer Postgres-specific types, ormed_postgres ships codecs and value types you can use directly in model fields.

Common Postgres-specific mappings

PostgreSQL type	Dart type	Notes
uuid	UuidValue	Exact UUID value (from package:uuid).
numeric / decimal	Decimal	Exact decimal math (from package:decimal).
bytea	Uint8List	Binary payloads.
interval	Interval	Months/days/microseconds.
tsvector	TsVector	Full‑text search document.
tsquery	TsQuery	Full‑text search query.
int4range / int8range	IntRange	Integer ranges.
daterange	DateRange	Date ranges.
tsrange / tstzrange	DateTimeRange	Timestamp ranges.
inet	PgInet	IP host address wrapper.
cidr	PgCidr	Network address wrapper.
macaddr / macaddr8	PgMacAddress	MAC address wrapper.
bit / varbit	PgBitString	Bit strings (0/1).
money	PgMoney	Stored as integer cents (avoid locale-dependent formatting).
time	Time	Time of day (no date).
timetz	PgTimeTz	Time of day + UTC offset.
pg_lsn	LSN	Log sequence number.
pg_snapshot / txid_snapshot	PgSnapshot	Transaction snapshot (xmin, xmax, xip).
point / line / lseg / box / path / polygon / circle	Point, Line, LineSegment, Box, Path, Polygon, Circle	Geometric types (re-exported by ormed_postgres).
json / jsonb	Map<String, Object?> / List<Object?>	Encoded/decoded as JSON.

Values

final uuid = UuidValue.fromString('00000000-0000-0000-0000-000000000000');
final amount = Decimal.parse('12.34');
final money = const PgMoney.fromCents(1234);
final bits = PgBitString.parse('10101010');
final timeTz = PgTimeTz(
  time: Time(1, 2, 3),
  offset: const Duration(hours: 4),
);
final lsn = LSN.fromString('0/10');
final snapshot = const PgSnapshot(xmin: 1, xmax: 10, xip: [2, 3]);
final location = const Point(1.5, 2.5);
final payload = Uint8List.fromList([1, 2, 3, 4]);

Migrations

Use package:ormed_postgres/migrations.dart when you want Postgres-specific schema helpers.

class CreateHostsTable extends Migration {
  const CreateHostsTable();

  @override
  void up(SchemaBuilder schema) {
    schema.create('hosts', (table) {
      table.inet('ip');
      table.cidr('subnet');
      table.macaddr('mac');
      table.bit('flags', length: 8);
      table.varbit('tag_bits');
      table.money('balance');
      table.timetz('local_time');
      table.pgLsn('lsn');
      table.pgSnapshot('snapshot');
      table.point('location');
      table.binary('payload');
    });
  }

  @override
  void down(SchemaBuilder schema) {
    schema.drop('hosts', ifExists: true, cascade: true);
  }
}

Options

Option	Type	Default	Description
url	String?	—	Full connection string. Aliases: uri, dsn. When set, it becomes the primary source for host/port/database/user/password and may also read sslmode, timezone, and application_name from the URL query parameters.
host	String	localhost	Database host (ignored when url/uri/dsn is set).
port	int	5432	Database port (ignored when url/uri/dsn is set).
database	String	connection name or postgres	Database name (ignored when url/uri/dsn is set).
username	String	postgres	Database username (ignored when url/uri/dsn is set).
password	String?	—	Database password.
sslmode	String	disable	TLS mode: disable, require, verify-full.
connectTimeout	int? (ms)	driver default	Socket connect timeout in milliseconds.
statementTimeout	int? (ms)	driver default	Server-side query timeout in milliseconds.
timezone	String	UTC	Session timezone.
applicationName	String?	—	Tags sessions for monitoring (application_name in URL query is also supported).
schema	String	public	Default schema used when qualifying tables for migrations/queries.
session	Map<String, Object?>	{}	Session settings applied via SET key = value.
init	List<String>	[]	SQL statements executed after connecting.

Notes

• Supports RETURNING, JSON/JSONB operators, window functions, and schema introspection.
• Migrations and seeds are configured separately in ormed.yaml. See Getting Started → Configuration.
• For plan/previews/schema internals, see Drivers → Internals.

Extensions

Postgres extensions are exposed through Ormed’s driver‑extension hooks. You register extension handlers per data source and then call them via selectExtension, whereExtension, orderByExtension, etc. For background on the extension system, see Driver Internals → Driver extensions.

Mini tutorial

Define a driver extension (this example adds a case-insensitive match using ILIKE):

class PostgresCaseInsensitiveExtensions extends DriverExtension {
  const PostgresCaseInsensitiveExtensions();

  @override
  List<DriverExtensionHandler> get handlers => const [
        DriverExtensionHandler(
          kind: DriverExtensionKind.where,
          key: 'ci_equals',
          compile: _compilePostgresCaseInsensitive,
        ),
      ];
}

DriverExtensionFragment _compilePostgresCaseInsensitive(
  DriverExtensionContext context,
  Object? payload,
) {
  final data = payload as Map<String, Object?>;
  final column = context.grammar.wrapIdentifier(data['column'] as String);
  final placeholder = context.grammar.parameterPlaceholder();
  return DriverExtensionFragment(
    sql: '${context.tableIdentifier}.$column ILIKE $placeholder',
    bindings: [data['value']],
  );
}

Register the extension and call it from the query builder:

Future<List<Map<String, Object?>>> searchDocumentsPostgres(
  DataSource dataSource,
  String query,
) {
  return dataSource.context
      .table('documents')
      .whereExtension('ci_equals', {'column': 'title', 'value': query})
      .rows();
}

Future<DataSource> createPostgresExtensionDataSource() async {
  final dataSource = DataSource(
    DataSourceOptions(
      name: 'docs-postgres-extensions',
      driver: PostgresDriverAdapter.fromUrl(
        'postgresql://postgres:postgres@localhost:5432/app',
      ),
      entities: generatedOrmModelDefinitions,
      driverExtensions: const [PostgresCaseInsensitiveExtensions()],
    ),
  );
  await dataSource.init();
  return dataSource;
}

If the extension requires Postgres DDL, apply it in a migration before using the handlers:

final schema = SchemaBuilder()
  ..raw('CREATE EXTENSION IF NOT EXISTS pg_trgm');

Reference implementation (not published)

Example extensions live in the repo under packages/ormed_postgres_extensions (PostGIS, pgvector, pg_trgm, hstore, ltree, citext, uuid‑ossp, pgcrypto). These are reference implementations, not published packages:

• PostGIS integration test
• pgvector integration test