PostgreSQL

Connecting

Set type to DBType.Postgres and supply a standard libpq connection string. pg is a dependency of the ORM, so nothing extra needs installing.

import { DBType, type DBConfig } from "stabilize-orm";

const dbConfig: DBConfig = {
  type: DBType.Postgres,
  connectionString:
    process.env.DATABASE_URL || "postgresql://user:pass@localhost:5432/mydb",
  retryAttempts: 3,
  retryDelay: 1000,
};

export default dbConfig;

The CLI scaffolds the same file with --type postgres:

Unlike the SQL Server pool, the pg pool is built in the DBClient constructor, so new Stabilize(config) is the whole of the setup. There is no separate connect step in your code: a transaction borrows a connection from that pool and returns it when the callback settles.

What the Dialect Changes

Statements are written once against the builder and then rendered for the target dialect. Nothing is emulated at runtime — the SQL that reaches a Postgres server is native to it:

Writes Return the Row

create() sends one statement and reads the inserted row out of it. The generated key arrives with the row, so nothing has to be asked for separately:

-- await userRepo.create({ email: "ada@example.com", name: "Ada" })
INSERT INTO users (email, name) VALUES ($1, $2) RETURNING *

bulkCreate() builds the same shape for a whole batch: the placeholders are numbered across every row and a single RETURNING * covers all of them.

INSERT INTO users (email, name) VALUES ($1, $2), ($3, $4) RETURNING *

This is the branch Postgres alone takes. The MySQL and SQLite paths emit no RETURNING, so there the generated key is read off the connection (LAST_INSERT_ID() or last_insert_rowid()) and the row is fetched again by id — three statements where Postgres needs one. Because RETURNING hands back raw column values, encrypted columns are decoded on the way out exactly as a read decodes them.

Upsert with ON CONFLICT

upsert(entity, keys) turns the key list into the conflict target and updates every other column from EXCLUDED. Each value is bound once, on the VALUES line — the update list refers to the proposed row rather than binding the payload a second time.

-- await userRepo.upsert({ email: "ada@example.com", name: "Ada" }, ["email"])
INSERT INTO users (email, name) VALUES ($1, $2)
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name
RETURNING *

The conflict target has to be backed by a unique index or constraint. PostgreSQL rejects the statement outright when nothing matches it — there is no unique or exclusion constraint matching the ON CONFLICT specification — so mark the key column unique: true on the model and let autoMigrate create the index, or create it in the migration yourself.

Row Limiting

Postgres spells row limiting the ordinary way, so limit() and offset() are emitted as written. One shared detail is worth knowing: the builder renders a single clause for all three LIMIT dialects, and MySQL and SQLite reject a bare OFFSET, so an offset with no limit is padded with a sentinel limit even here.

-- repo.find().limit(10).offset(20)
SELECT * FROM users
LIMIT 10 OFFSET 20

-- repo.find().offset(20) -- no limit set
SELECT * FROM users
LIMIT 9223372036854775807 OFFSET 20

-- repo.find().paginate(2, 25) composes the two: offset = (page - 1) * pageSize
SELECT * FROM users
LIMIT 25 OFFSET 25

A STRING id Becomes a UUID

The declared type of an id column decides the primary key that is emitted. An integer id is generated by the database; a STRING or UUID id is supplied by the caller. What a STRING id becomes differs between dialects, and Postgres is the odd one out:

-- id: DataTypes.STRING -- the caller supplies it
Postgres      id UUID PRIMARY KEY
MySQL         id VARCHAR(255) PRIMARY KEY
SQLite        id TEXT PRIMARY KEY

-- id: DataTypes.UUID
Postgres      id UUID PRIMARY KEY

const User = defineModel({
  tableName: "users",
  columns: {
    // UUID PRIMARY KEY on Postgres, TEXT PRIMARY KEY on SQLite.
    id: { type: DataTypes.STRING, required: true, unique: true },
    email: { type: DataTypes.STRING, required: true },
  },
});

// Accepted everywhere:
await userRepo.create({ id: "550e8400-e29b-41d4-a716-446655440000", email: "a@b.c" });

// Accepted on SQLite and MySQL; on Postgres the server rejects it:
// await userRepo.create({ id: "user-1", email: "a@b.c" });

Type Mapping

Every DataTypes member maps to a Postgres type. Note JSONB for JSON, BYTEA for blobs, and DECIMAL(10,2) as the default — a bare Postgres DECIMAL stores whatever it is handed, so the constrained form is emitted instead. Declare precision and scale to size it yourself. STRING is TEXT here, with no width to declare: Postgres treats TEXT and VARCHAR(n) identically, so a length is enforced by the ORM rather than written into the DDL.

A declared length never reaches the DDL: STRING is TEXT here whatever length you ask for, because Postgres treats TEXT and VARCHAR(n) as the same type. The limit is enforced by the ORM on write instead, so length: 50 still rejects a 60-character value. BOOLEAN is a real type on Postgres, which is why the generated toggle is SET col = NOT col.

Parameter Binding

The library writes every statement with ? placeholders and rewrites them for the target driver. Postgres gets $1, $2, … in the order they appear, so user code never has to number anything — whereRaw("name = ?", value) and havingRaw are written the same way on every dialect.

The rewrite is textual, and it replaces every ? in the statement rather than only the ones the builder placed. A literal question mark inside a raw fragment — in a string, or in Postgres' own ? jsonb key-exists operator — is rewritten too, which shifts the numbering of every parameter after it. Keep ? out of raw fragments; bind the value instead.

The pg driver serialises a plain object or array parameter to JSON on the way out, so a JSON column accepts the object itself — no JSON.stringify in your code. That is a Postgres-only property; MySQL and SQL Server have the ORM encode the value instead.

const Event = defineModel({
  tableName: "events",
  columns: {
    id: { type: DataTypes.INTEGER, required: true, unique: true },
    // JSONB on Postgres; the object is serialised by the driver.
    payload: { type: DataTypes.JSON },
  },
});

await eventRepo.create({ payload: { type: "signup", plan: "pro" } });

Transactions

A transaction borrows a connection from the pool, sends BEGIN, and commits or rolls back on that same connection before releasing it — an unrelated connection would commit on its own. The ORM does that wiring for you, so the callback looks the same as on every other dialect.

await orm.transaction(async (txClient) => {
  const userRepo = orm.getRepository(User);
  const profileRepo = orm.getRepository(Profile);

  const user = await userRepo.create({ email: "ada@example.com" }, {}, txClient);
  await profileRepo.create({ userId: user.id }, {}, txClient);
});

Pass the txClient to every call inside the callback. A repository call that falls back to the ORM client takes a different connection and runs outside the transaction.

Limitations

One thing that is not a limitation here: whereILike emits ILIKE, and Postgres is the only dialect that understands it. On MySQL and SQLite the same call produces a syntax error, so a query written here does not travel to the other dialects unmodified.