PostgreSQL

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

Prerequisites

• Drivers Overview
• Configuration

What You’ll Learn

• How to configure Postgres in code-first and config-driven flows
• Which Postgres options and value types Ormed supports
• How env-based boot maps to runtime helper APIs

Step Outcome

By the end of this page, you should have:

• A working Postgres datasource config (URL or fields)
• Environment-driven config fallback rules understood
• Confidence with Postgres-specific value mappings used in models

Install

Add the driver package:

dependencies:
  ormed: ^0.2.0
  ormed_postgres: ^0.2.0

Configure

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

Prefer URL config in production deployments unless your platform manages secrets as discrete fields.

Use in code (DataSource)

The recommended path is generated config.dart + datasource.dart.

For direct code-first boot without ormed.yaml:

import 'package:your_app/src/database/datasource.dart';

Future<void> main() async {
  final ds = createDataSource(connection: 'default');
  await ds.init();
  await ds.dispose();
}

Code-first Helper APIs

Use model-registry extensions when you want explicit setup in code:

• registry.postgresDataSourceOptions(...)
• registry.postgresDataSource(...)
• registry.postgresDataSourceOptionsFromEnv(...)
• registry.postgresDataSourceFromEnv(...)

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.

Environment Variables

postgresDataSourceOptionsFromEnv(...) / postgresDataSourceFromEnv(...) recognize:

• DB_URL or DATABASE_URL
• DB_HOST, DB_PORT, DB_NAME, DB_USER, DB_PASSWORD
• DB_SSLMODE, DB_TIMEZONE, DB_APP_NAME

If DB_URL/DATABASE_URL is set, URL-based config is used first, then optional overrides like DB_SSLMODE and DB_TIMEZONE.

Quick Verification

dart run ormed_cli:ormed migrate --pretend

Then run a basic query through runtime bootstrap to confirm connectivity and session settings.

Notes

• Supports RETURNING, JSON/JSONB operators, window functions, and schema introspection.
• Migrations and seeds default to convention registries (lib/src/database/*) and can be overridden via ormed.yaml.
• 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

Read This Next

• Migrations Overview
• Guide: Multi-database