Data Table PostgreSQL Tutorial

In this tutorial you will connect to a PostgreSQL database, create tables, insert data, and run queries using the data-table toolkit.

Prerequisites

You need a running PostgreSQL server. Set the connection string in an environment variable:

export DATABASE_URL="postgresql://user:password@localhost:5432/myapp"

Install the packages:

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

---

Step 1 --- Connect with a Pool

Always use a connection pool in production. The pool manages connections automatically:

// db.ts
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,
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
})

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

Graceful shutdown

Call pool.end() when your application shuts down to close all connections cleanly.

Step 2 --- Define Tables

PostgreSQL supports UUID primary keys with gen_random_uuid() and timestamps with timezone:

// db/tables.ts
import { table, column as c } from 'remix/data-table'

export 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(),
    role: c.enum(['admin', 'user']).notNull().default('user'),
    createdAt: c.timestamp({ withTimezone: true }).notNull().defaultNow(),
    updatedAt: c.timestamp({ withTimezone: true }).notNull().defaultNow(),
  },
  timestamps: true,
})

export let posts = table({
  name: 'posts',
  columns: {
    id: c.uuid().primaryKey().defaultSql('gen_random_uuid()'),
    title: c.text().notNull(),
    body: c.text().notNull(),
    published: c.boolean().notNull().default(false),
    userId: c.uuid().notNull().references('users', 'fk_posts_user').onDelete('cascade'),
    createdAt: c.timestamp({ withTimezone: true }).notNull().defaultNow(),
    updatedAt: c.timestamp({ withTimezone: true }).notNull().defaultNow(),
  },
  timestamps: true,
})

Step 3 --- Create Tables with a Migration

// migrations/001-initial.ts
import { createMigration, column as c } from 'remix/data-table/migrations'

export default createMigration({
  up(ctx) {
    ctx.createTable('users', {
      id: c.uuid().primaryKey().defaultSql('gen_random_uuid()'),
      email: c.varchar(255).notNull().unique('users_email_uq'),
      name: c.text().notNull(),
      role: c.enum(['admin', 'user']).notNull().default('user'),
      createdAt: c.timestamp({ withTimezone: true }).notNull().defaultNow(),
      updatedAt: c.timestamp({ withTimezone: true }).notNull().defaultNow(),
    })

    ctx.createTable('posts', {
      id: c.uuid().primaryKey().defaultSql('gen_random_uuid()'),
      title: c.text().notNull(),
      body: c.text().notNull(),
      published: c.boolean().notNull().default(false),
      userId: c.uuid().notNull().references('users', 'fk_posts_user').onDelete('cascade'),
      createdAt: c.timestamp({ withTimezone: true }).notNull().defaultNow(),
      updatedAt: c.timestamp({ withTimezone: true }).notNull().defaultNow(),
    })

    ctx.createIndex('posts', ['userId'], { name: 'idx_posts_user' })
  },

  down(ctx) {
    ctx.dropTable('posts')
    ctx.dropTable('users')
  },
})

Run migrations:

import { createMigrationRunner } from 'remix/data-table/migrations'
import { loadMigrations } from 'remix/data-table/migrations/node'

let migrations = await loadMigrations('./migrations')
let runner = createMigrationRunner(adapter)
await runner.up(migrations)

Advisory locks

The PostgreSQL migration runner uses advisory locks to prevent multiple servers from running migrations concurrently. This is safe for multi-process deployments.

Step 4 --- Insert and Query Data

import { db } from './db'
import { users, posts } from './db/tables'
import { hasMany } from 'remix/data-table'
import { ilike } from 'remix/data-table/operators'

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

// Create a post
await db.create(posts, {
  title: 'Hello World',
  body: 'My first post.',
  userId: user.id,
  published: true,
})

// Find user with posts
let userPosts = hasMany(users, posts, { foreignKey: 'userId' })

let alice = await db.findOne(users, {
  where: { email: 'alice@example.com' },
  with: { posts: userPosts },
})
// alice.posts is Post[]

// Case-insensitive search (native ILIKE)
let results = await db.findMany(users, {
  where: ilike('email', '%@EXAMPLE.COM'),
})

What You Learned

• Connect to PostgreSQL with a connection pool using the pg driver.
• Define tables with UUID primary keys and timezone-aware timestamps.
• Run migrations with advisory lock safety.
• Insert and query data with eager-loaded relations.
• Use native ilike for case-insensitive searches.

Related

• Data Table PostgreSQL Overview --- When to use PostgreSQL and key advantages.
• Data Table PostgreSQL Reference --- Full API reference.
• Data Table Tutorial --- Comprehensive tutorial covering all data-table features.