data-table-postgres

The data-table-postgres package provides a PostgreSQL adapter for the data-table query toolkit. It uses the pg driver.

Installation

npm install remix pg
npm install -D @types/pg

Or install the standalone package:

npm install @remix-run/data-table-postgres pg
npm install -D @types/pg

Import

import { createPostgresDatabaseAdapter } from 'remix/data-table-postgres'

Or from the standalone package:

import { createPostgresDatabaseAdapter } from '@remix-run/data-table-postgres'

---

API

createPostgresDatabaseAdapter(client, options?)

Creates a DatabaseAdapter for use with createDatabase().

import { Pool } from 'pg'
import { createPostgresDatabaseAdapter } from 'remix/data-table-postgres'
import { createDatabase } from 'remix/data-table'

let pool = new Pool({ connectionString: process.env.DATABASE_URL })
let adapter = createPostgresDatabaseAdapter(pool)
let db = createDatabase(adapter)

Parameters

Parameter	Type	Description
client	Pool | PoolClient (from pg)	A pg connection pool or an individual pool client.
options	PostgresDatabaseAdapterOptions	Optional adapter configuration.

PostgresDatabaseAdapterOptions

Option	Type	Description
capabilities	AdapterCapabilityOverrides	Override detected adapter capabilities.

Capabilities

The PostgreSQL adapter reports the following capabilities by default:

Capability	Default	Description
returning	true	PostgreSQL supports RETURNING clauses.
savepoints	true	PostgreSQL supports savepoints for nested transactions.
upsert	true	PostgreSQL supports ON CONFLICT upsert.
transactionalDdl	true	PostgreSQL supports DDL inside transactions.
migrationLock	true	PostgreSQL supports advisory locks for migration safety.

---

Connection Pooling

Always use a Pool in production. The pool manages connections automatically and handles cleanup.

import { Pool } from 'pg'

let pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,              // Maximum pool size
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
})

You can also pass an individual PoolClient for use within a manually managed connection:

let client = await pool.connect()
try {
  let adapter = createPostgresDatabaseAdapter(client)
  let db = createDatabase(adapter)
  // ... use db
} finally {
  client.release()
}

---

PostgreSQL-Specific Features

• Full RETURNING support: All write operations (create, update, delete) can return the affected rows.
• ILIKE: Native case-insensitive pattern matching is fully supported.
• Advisory locks: The migration runner uses PostgreSQL advisory locks to prevent concurrent migrations in multi-process deployments.
• UUID columns: Native UUID type is supported. Use c.uuid() for UUID primary keys.
• JSON/JSONB: The c.json() column type maps to PostgreSQL's JSON types.
• Enum types: The c.enum() column builder maps to PostgreSQL CHECK constraints by default.

---

Example

import { Pool } from 'pg'
import { createPostgresDatabaseAdapter } from 'remix/data-table-postgres'
import { createDatabase, table, column as c } from 'remix/data-table'

let pool = new Pool({ connectionString: process.env.DATABASE_URL })
let adapter = createPostgresDatabaseAdapter(pool)
let db = createDatabase(adapter)

let users = table({
  name: 'users',
  columns: {
    id: c.uuid().primaryKey().defaultSql('gen_random_uuid()'),
    email: c.varchar(255).notNull().unique('users_email_uq'),
    name: c.text().notNull(),
    createdAt: c.timestamp({ withTimezone: true }).notNull().defaultNow(),
    updatedAt: c.timestamp({ withTimezone: true }).notNull().defaultNow(),
  },
  timestamps: true,
})

let user = await db.create(users, {
  email: 'alice@example.com',
  name: 'Alice',
}, { returnRow: true })

---

Related

• data-table --- The core query toolkit.
• data-table-sqlite --- SQLite adapter.
• data-table-mysql --- MySQL adapter.
• Database Guide --- Practical database patterns.