PostgreSQL support (Alpha)

Thanks to contributions from the community, drift currently has alpha support for postgres with the drift_postgres package. Without having to change your query code, drift can generate Postgres-compatible SQL for most queries, allowing you to use your drift databases with a Postgres database server.

Setup

Begin by adding both drift and drift_postgres to your pubspec:

dependencies:
  drift: ^2.13.0
  drift_postgres: ^0.1.0

dev_dependencies:
  drift_dev: ^2.13.0
  build_runner: ^2.4.6

Defining a database with Postgres is no different than defining it for sqlite3 - the pages on Dart and SQL explain how to define tables picked up by drift.

Perhaps this example database is helpful as a starting point:

import 'package:drift/drift.dart';
import 'package:drift_postgres/postgres.dart';
import 'package:postgres/postgres_v3_experimental.dart';

part 'postgres.g.dart';

class Users extends Table {
  UuidColumn get id => customType(PgTypes.uuid).withDefault(genRandomUuid())();
  TextColumn get name => text()();
  DateTimeColumn get birthDate => dateTime().nullable()();
}

@DriftDatabase(tables: [Users])
class MyDatabase extends _$MyDatabase {
  MyDatabase(super.e);

  @override
  int get schemaVersion => 1;
}

void main() async {
  final pgDatabase = PgDatabase(
    endpoint: PgEndpoint(
      host: 'localhost',
      database: 'postgres',
      username: 'postgres',
      password: 'postgres',
    ),
  );

  final driftDatabase = MyDatabase(pgDatabase);

  // Insert a new user
  await driftDatabase.users.insertOne(UsersCompanion.insert(name: 'Simon'));

  // Print all of them
  print(await driftDatabase.users.all().get());

  await driftDatabase.close();
}

After starting a database server, for example by running docker run -p 5432:5432 -e POSTGRES_PASSWORD=postgres postgres, you can run the example to see drift talking to Postgres.

API extensions

The postgres library provides a few custom types enabling you to use postgres-specific types when writing queries in drift. For instance, the PgTypes.uuid type used in the example maps to a native UUID column type in Postgres. The gen_random_uuid() function in postgres is also exposed.

PostgreSQL provides a much larger set of functions, of which currently only a few are exported in the drift_postgres package. You can call others with a FunctionCallExpression - if you do, contributions extending drift_postgres are always welcome!

Migrations

In sqlite3, the current schema version is stored in the database file. To support drift's migration API being built ontop of this mechanism in Postgres as well, drift creates a __schema table storing the current schema version.

This migration mechanism works for simple deployments, but is unsuitable for large database setups with many application servers connecting to postgres. For those, an existing migration management tool is a more reliable alternative. If you chose to manage migrations with another tool, you can disable migrations in postgres by passing enableMigrations: false to the PgDatabase constructor.

Current state

Drift's support for Postgres is still in development, and the integration tests we have for Postgres are less extensive than the tests for sqlite3 databases. Also, some parts of the core APIs (like the datetime expressions API) are direct wrappers around SQL functions only available in sqlite3 and won't work in Postgres. However, you can already create tables (or use an existing schema) and most queries should work already.

If you're running into problems or bugs with the postgres database, please let us know by creating an issue or a discussion.