Data Table PostgreSQL Overview

The data-table-postgres package connects the data-table query toolkit to PostgreSQL via the pg driver. PostgreSQL is the recommended database for production Remix applications that need scalability, advanced features, or multi-server deployments.

When to Use PostgreSQL

Use Case	PostgreSQL Fits?
Production web applications	Yes --- designed for concurrent access, replication, and high availability.
Multi-server deployments	Yes --- networked connections and advisory locks for safe migrations.
Advanced queries (CTEs, window functions, JSONB)	Yes --- PostgreSQL has the richest SQL feature set.
Local development	Yes, but SQLite is simpler if you want zero setup.
Embedded / edge / serverless	Maybe --- requires a running PostgreSQL server or a hosted service.

Quick Setup

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

// Create a connection pool
let pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,
})

// Create the adapter and database
let adapter = createPostgresDatabaseAdapter(pool)
let db = createDatabase(adapter)

// Define a table
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(),
  },
})

// Query
let all = await db.findMany(users)

What is a connection pool?

A connection pool maintains a set of open database connections that are reused across requests. This avoids the overhead of opening a new TCP connection for every query. Always use a Pool in production.

Key Advantages Over SQLite

Feature	PostgreSQL	SQLite
Concurrent writes	Yes	Single writer only
Network access	Yes	In-process only
RETURNING clause	Yes	Yes
Advisory locks	Yes --- safe multi-process migrations	No
ILIKE	Native	Emulated via COLLATE NOCASE
UUID columns	Native gen_random_uuid()	Text-based
JSONB	Native with indexing	JSON stored as text

Connection Pooling

Always configure the pool for your workload:

let pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,                      // Maximum connections in the pool
  idleTimeoutMillis: 30000,     // Close idle connections after 30s
  connectionTimeoutMillis: 2000, // Fail if a connection takes over 2s
})

Installation

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

Related

• Data Table PostgreSQL Tutorial --- Step-by-step setup, table creation, and querying.
• Data Table PostgreSQL Reference --- Full API reference.
• Data Table Overview --- The core query toolkit.
• Data Table SQLite --- SQLite adapter for development and small apps.
• Data Table MySQL --- MySQL adapter.