PostgreSQL Driver

Full PostgreSQL migration support including native enums, arrays, and advanced index types

Capabilities

Type Mappings

Auto-Increment

PostgreSQL uses SERIAL and BIGSERIAL types for auto-increment:

const User = model("users", {
  id: int().primaryKey().autoIncrement(),
});

Generates:

CREATE TABLE "users" (
  "id" serial PRIMARY KEY
);

UUID Generation

const User = model("users", {
  id: uuid().primaryKey().default("gen_random_uuid()"),
});

Generates:

CREATE TABLE "users" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid()
);

Native Enums

PostgreSQL supports native enum types:

const User = model("users", {
  status: enumScalar(["active", "inactive", "pending"]),
});

Generates:

CREATE TYPE "users_status_enum" AS ENUM ('active', 'inactive', 'pending');

CREATE TABLE "users" (
  "status" "users_status_enum" NOT NULL
);

Enum Operations

Add value:

ALTER TYPE "users_status_enum" ADD VALUE 'suspended';

Remove value: Requires enum recreation (handled automatically).

Native Arrays

PostgreSQL supports native array types:

const User = model("users", {
  tags: string().array(),
  scores: int().array(),
});

Generates:

CREATE TABLE "users" (
  "tags" text[] NOT NULL,
  "scores" integer[] NOT NULL
);

Index Types

The driver generates btree (default), hash, GIN, and GiST indexes, as well as partial indexes with WHERE clauses. See Index Options for declaring them on models.

Advisory Locks

The PostgreSQL driver uses advisory locks to prevent concurrent migrations:

SELECT pg_advisory_lock(123456789);
-- ... run migrations ...
SELECT pg_advisory_unlock(123456789);

This ensures only one migration process runs at a time, even across multiple application instances.

Limitations

Next Steps