Skip to main content

​
Overview

data-table-postgres provides a PostgreSQL-specific adapter for data-table. It supports the full data-table API with PostgreSQL-optimized features including RETURNING clauses, savepoints, upserts, and transactional DDL. Key Features:
  • Full RETURNING clause support for inserts, updates, and deletes
  • Savepoint support for nested transactions
  • Native upsert (ON CONFLICT) support
  • Transactional DDL for safe migrations
  • Advisory lock support for migration coordination
  • Connection pooling support

​
Installation

npm i remix
npm i pg

​
API Reference

​
createPostgresDatabaseAdapter

Creates a PostgreSQL database adapter.
​
client
PostgresDatabasePool
required
PostgreSQL client or pool instance from the pg package.
​
options.capabilities
AdapterCapabilityOverrides
Optional capability overrides.
​
options.capabilities.returning
boolean
Enable/disable RETURNING clause support. Defaults to true.
​
options.capabilities.savepoints
boolean
Enable/disable savepoint support. Defaults to true.
​
options.capabilities.upsert
boolean
Enable/disable upsert support. Defaults to true.
​
options.capabilities.transactionalDdl
boolean
Enable/disable transactional DDL. Defaults to true.
​
options.capabilities.migrationLock
boolean
Enable/disable advisory lock for migrations. Defaults to true.
​
adapter
PostgresDatabaseAdapter
A PostgreSQL database adapter instance.
import { Pool } from 'pg'
import { createDatabase } from 'remix/data-table'
import { createPostgresDatabaseAdapter } from 'remix/data-table-postgres'

let pool = new Pool({
  connectionString: process.env.DATABASE_URL,
})

let adapter = createPostgresDatabaseAdapter(pool)
let db = createDatabase(adapter)

​
PostgresDatabaseAdapter

PostgreSQL adapter class implementing the DatabaseAdapter interface. 
class PostgresDatabaseAdapter implements DatabaseAdapter {
  dialect: 'postgres'
  capabilities: AdapterCapabilities
  
  compileSql(
    operation: DataManipulationOperation | DataMigrationOperation
  ): SqlStatement[]
  
  execute(
    request: DataManipulationRequest
  ): Promise<DataManipulationResult>
  
  migrate(
    request: DataMigrationRequest
  ): Promise<DataMigrationResult>
  
  hasTable(
    table: TableRef,
    transaction?: TransactionToken
  ): Promise<boolean>
  
  hasColumn(
    table: TableRef,
    column: string,
    transaction?: TransactionToken
  ): Promise<boolean>
  
  beginTransaction(
    options?: TransactionOptions
  ): Promise<TransactionToken>
  
  commitTransaction(
    token: TransactionToken
  ): Promise<void>
  
  rollbackTransaction(
    token: TransactionToken
  ): Promise<void>
  
  createSavepoint(
    token: TransactionToken,
    name: string
  ): Promise<void>
  
  rollbackToSavepoint(
    token: TransactionToken,
    name: string
  ): Promise<void>
  
  releaseSavepoint(
    token: TransactionToken,
    name: string
  ): Promise<void>
}

​
Type Definitions

​
PostgresDatabaseClient

Minimal PostgreSQL client contract. 
type PostgresDatabaseClient = {
  query(text: string, values?: unknown[]): Promise<PostgresQueryResult>
}

​
PostgresDatabasePool

PostgreSQL pool-like client with optional connection pooling. 
type PostgresDatabasePool = PostgresDatabaseClient & {
  connect?: () => Promise<PostgresTransactionClient>
}

​
PostgresTransactionClient

PostgreSQL transaction client with optional connection release. 
type PostgresTransactionClient = PostgresDatabaseClient & {
  release?: () => void
}

​
PostgresQueryResult

Result shape returned by PostgreSQL client query() calls. 
type PostgresQueryResult = {
  rows: unknown[]
  rowCount: number | null
}

​
PostgresDatabaseAdapterOptions

PostgreSQL adapter configuration. 
type PostgresDatabaseAdapterOptions = {
  capabilities?: AdapterCapabilityOverrides
}

​
Capabilities

The PostgreSQL adapter has the following default capabilities:
CapabilityDefaultDescription
returningtrueSupport for RETURNING clauses in INSERT/UPDATE/DELETE
savepointstrueSupport for nested transactions via savepoints
upserttrueSupport for ON CONFLICT upsert operations
transactionalDdltrueDDL statements can be run in transactions
migrationLocktrueAdvisory locks for migration coordination

​
Usage Examples

​
Basic Setup

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

// Define tables
let users = table({
  name: 'users',
  columns: {
    id: c.uuid().primaryKey(),
    email: c.varchar(255).notNull().unique(),
    name: c.varchar(255).notNull(),
    created_at: c.timestamp().defaultNow(),
  },
})

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

// Use the database
let user = await db.create(users, {
  id: crypto.randomUUID(),
  email: '[email protected]',
  name: 'Ada Lovelace',
})

​
Using RETURNING Clauses

PostgreSQL’s RETURNING support allows you to get inserted/updated rows without additional queries. 
// Insert with RETURNING
let createdUser = await db.create(
  users,
  {
    id: crypto.randomUUID(),
    email: '[email protected]',
    name: 'Grace Hopper',
  },
  { returnRow: true }
)

// Bulk insert with RETURNING
let insertedUsers = await db.createMany(
  users,
  [
    { id: crypto.randomUUID(), email: '[email protected]', name: 'Alan Turing' },
    { id: crypto.randomUUID(), email: '[email protected]', name: 'John von Neumann' },
  ],
  { returnRows: true }
)

// Update with RETURNING
let result = await db
  .query(users)
  .where({ email: '[email protected]' })
  .update({ name: 'Ada King' }, { returning: '*' })

​
Transactions and Savepoints

PostgreSQL supports nested transactions via savepoints. 
// Simple transaction
await db.transaction(async (tx) => {
  await tx.create(users, { id: crypto.randomUUID(), email: '[email protected]' })
  await tx.update(users, userId, { name: 'Updated Name' })
})

// Nested transactions with savepoints
await db.transaction(async (tx) => {
  await tx.create(users, { id: crypto.randomUUID(), email: '[email protected]' })
  
  // Inner transaction uses a savepoint
  await tx.transaction(async (inner) => {
    await inner.create(users, { id: crypto.randomUUID(), email: '[email protected]' })
    // If this throws, only the inner transaction is rolled back
  })
})

​
Upserts

PostgreSQL’s ON CONFLICT is exposed via the upsert method. 
// Upsert by primary key (implicit conflict target)
await db.query(users).upsert(
  {
    id: userId,
    email: '[email protected]',
    name: 'Updated Name',
  },
  {
    update: { name: 'Updated Name' },
  }
)

// Upsert with explicit conflict target
await db.query(users).upsert(
  {
    email: '[email protected]',
    name: 'New Name',
  },
  {
    conflictTarget: ['email'],
    update: { name: 'New Name' },
  }
)

// Upsert with RETURNING
let result = await db.query(users).upsert(
  { id: userId, email: '[email protected]' },
  {
    update: { email: '[email protected]' },
    returning: '*',
  }
)

​
Transaction Options

PostgreSQL supports transaction isolation levels and read-only mode. 
import type { TransactionOptions } from 'remix/data-table'

// Read-only transaction
await db.transaction(async (tx) => {
  let users = await tx.findMany(users)
  return users
}, { readOnly: true })

// Custom isolation level
await db.transaction(async (tx) => {
  await tx.create(users, { id: crypto.randomUUID(), email: '[email protected]' })
}, { isolationLevel: 'serializable' })

​
Schema Support

PostgreSQL adapter fully supports schema-qualified table names. 
let usersTable = table({
  name: 'public.users',  // Schema-qualified name
  columns: {
    id: c.uuid(),
    email: c.varchar(255),
  },
})

// Alternatively, define schema separately in migrations
let orders = table({
  name: 'orders',
  columns: { id: c.integer() },
})

// In migrations, you can reference schemas
import { createTable } from 'remix/data-table/migrations'

export let up = createTable({
  name: 'tenant_orders',
  schema: 'tenant_1',
  columns: {
    id: c.integer(),
  },
})

​
Connection Pooling

The adapter works seamlessly with pg’s connection pooling. 
import { Pool } from 'pg'

let pool = new Pool({
  host: 'localhost',
  database: 'mydb',
  user: 'user',
  password: 'password',
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
})

let db = createDatabase(createPostgresDatabaseAdapter(pool))

// Pool automatically manages connections
let users = await db.findMany(users)

// Transactions automatically acquire and release connections
await db.transaction(async (tx) => {
  await tx.create(users, { id: crypto.randomUUID(), email: '[email protected]' })
})

// Clean up pool when done
await pool.end()

​
PostgreSQL-Specific Features

​
Array Types

PostgreSQL arrays are supported through JSON column serialization or custom types. 
let posts = table({
  name: 'posts',
  columns: {
    id: c.integer(),
    tags: c.json(),  // Store arrays as JSON
  },
})

await db.create(posts, {
  id: 1,
  tags: ['typescript', 'postgres', 'remix'],
})

​
Generated Columns

PostgreSQL computed columns are supported. 
let products = table({
  name: 'products',
  columns: {
    id: c.integer(),
    price: c.decimal(10, 2),
    tax_rate: c.decimal(5, 4),
    // Generated column
    price_with_tax: c.decimal(10, 2).computed('price * (1 + tax_rate)', { stored: true }),
  },
})

​
Identity Columns

PostgreSQL identity columns provide better serial key support. 
let users = table({
  name: 'users',
  columns: {
    id: c.integer().identity({ start: 1000, increment: 1 }),
    email: c.varchar(255),
  },
})

​
Differences from Other Adapters

​
vs. MySQL Adapter

FeaturePostgreSQLMySQL
RETURNING✅ Yes❌ No
Savepoints✅ Yes✅ Yes
Transactional DDL✅ Yes❌ No
Migration LockAdvisory LockTable Lock
UpsertON CONFLICTON DUPLICATE KEY
IdentityIDENTITYAUTO_INCREMENT

​
vs. SQLite Adapter

FeaturePostgreSQLSQLite
RETURNING✅ Yes✅ Yes
Savepoints✅ Yes✅ Yes
Transactional DDL✅ Yes✅ Yes
Migration LockAdvisory Lock❌ No
Concurrent Access✅ Excellent⚠️ Limited
Connection Pooling✅ Yes❌ N/A

​
Best Practices

​
Use Connection Pooling

Always use a connection pool for production applications. 
// Good: Use a pool
let pool = new Pool({ connectionString: process.env.DATABASE_URL })
let db = createDatabase(createPostgresDatabaseAdapter(pool))

// Bad: Use a single client
import { Client } from 'pg'
let client = new Client({ connectionString: process.env.DATABASE_URL })
await client.connect()
let db = createDatabase(createPostgresDatabaseAdapter(client))

​
Handle Connection Errors

Monitor pool events for connection issues. 
pool.on('error', (err) => {
  console.error('Unexpected pool error', err)
})

pool.on('connect', () => {
  console.log('Client connected to pool')
})

​
Use Transactions for Multiple Writes

Wrap multiple related writes in transactions. 
// Good: Use transaction
await db.transaction(async (tx) => {
  let user = await tx.create(users, { ... }, { returnRow: true })
  await tx.create(profiles, { user_id: user.id, ... })
})

// Bad: Separate operations
let user = await db.create(users, { ... }, { returnRow: true })
await db.create(profiles, { user_id: user.id, ... })

​
Leverage RETURNING Clauses

Use returnRow and returnRows to avoid extra queries. 
// Good: Single query with RETURNING
let user = await db.create(users, { ... }, { returnRow: true })

// Bad: Insert then query
await db.create(users, { id: userId, ... })
let user = await db.find(users, userId)

Build docs developers (and LLMs) love

Get started for freeTalk to us