Docs/Database

Database

Full PostgreSQL power with zero DevOps. Branching, migrations, and real-time built in.

Overview

Every Lubes project includes a dedicated PostgreSQL database. You get full SQL power with enterprise features like connection pooling, automatic backups, and database branching.

PostgreSQL 15+

Latest PostgreSQL with all extensions

Auto Backups

Point-in-time recovery up to 30 days

Connection Pooling

PgBouncer built-in for efficiency

Connecting to Your Database

Using the SDK

The easiest way to connect is through the SDK:

import { Lubes } from '@lubes/sdk'

const lubes = new Lubes({
  projectId: process.env.LUBES_PROJECT_ID!,
  apiKey: process.env.LUBES_API_KEY!,
})

const { db } = lubes

Direct Connection

For direct PostgreSQL connections (e.g., with Prisma, Drizzle, or psql):

# Connection string format
postgresql://[user]:[password]@[host]:[port]/[database]

# Example
postgresql://postgres.abc123:password@db.lubes.dev:5432/postgres

# With connection pooling (recommended for serverless)
postgresql://postgres.abc123:password@db.lubes.dev:6543/postgres?pgbouncer=true

Find your connection string in the dashboard settings.

Queries

Basic Queries

// SELECT query
const users = await db.query`
  SELECT * FROM users
  WHERE status = 'active'
  ORDER BY created_at DESC
  LIMIT 10
`

// INSERT query
const newUser = await db.query`
  INSERT INTO users (name, email, status)
  VALUES (${'John'}, ${'john@example.com'}, ${'active'})
  RETURNING *
`

// UPDATE query
await db.query`
  UPDATE users
  SET status = ${'inactive'}
  WHERE last_login < NOW() - INTERVAL '30 days'
`

// DELETE query
await db.query`
  DELETE FROM sessions
  WHERE expires_at < NOW()
`

Parameterized Queries

Always use parameterized queries to prevent SQL injection:

// Safe - parameters are escaped
const email = 'user@example.com'
const user = await db.queryOne`
  SELECT * FROM users WHERE email = ${email}
`

// Also safe - using query method with params
const users = await db.query(
  'SELECT * FROM users WHERE role = $1 AND status = $2',
  ['admin', 'active']
)

Transactions

// Automatic transaction management
await db.transaction(async (tx) => {
  // Deduct from sender
  await tx.query`
    UPDATE accounts
    SET balance = balance - ${amount}
    WHERE id = ${senderId}
  `

  // Add to receiver
  await tx.query`
    UPDATE accounts
    SET balance = balance + ${amount}
    WHERE id = ${receiverId}
  `

  // Create transfer record
  await tx.query`
    INSERT INTO transfers (sender_id, receiver_id, amount)
    VALUES (${senderId}, ${receiverId}, ${amount})
  `
})
// Transaction automatically commits on success, rolls back on error

Migrations

Lubes uses SQL migration files to manage your database schema. Migrations are version-controlled and can be rolled back if needed.

Creating a Migration

# Create a new migration
lubes db migrations new create_users_table

This creates a new migration file in migrations/:

migrations/00001_create_users_table.sql

-- migrate:up
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(255),
  status VARCHAR(50) DEFAULT 'active',
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_status ON users(status);

-- migrate:down
DROP TABLE IF EXISTS users;

Running Migrations

# Apply all pending migrations
lubes db migrations up

# Roll back the last migration
lubes db migrations down

# Check migration status
lubes db migrations status

Database Branching

Database branching creates instant, isolated copies of your database. Perfect for development, testing, and preview environments.

# Create a branch from production
lubes db branches create feature-branch

# List all branches
lubes db branches list

# Switch to a branch
lubes db branches use feature-branch

# Delete a branch
lubes db branches delete feature-branch

Note: Database branching is available on Pro plans and above. Branches are instant copies that share storage with the parent until modified.

Real-time Subscriptions

Subscribe to database changes in real-time using WebSockets. Build reactive applications that update instantly when data changes.

// Subscribe to all changes on a table
const subscription = db.subscribe('messages', (payload) => {
  console.log('Change:', payload.eventType, payload.new)
})

// Subscribe to specific events
db.subscribe('users', { event: 'INSERT' }, (payload) => {
  console.log('New user:', payload.new)
})

// Subscribe with filters
db.subscribe('orders', {
  event: 'UPDATE',
  filter: 'status=eq.completed'
}, (payload) => {
  console.log('Order completed:', payload.new)
})

// Unsubscribe when done
subscription.unsubscribe()

Event Types

Event	Description	Payload
INSERT	New row inserted	`new` - the inserted row
UPDATE	Row updated	`new`, `old` - before/after
DELETE	Row deleted	`old` - the deleted row
*	All events	Varies by event type