PostgreSQL — Patterns and Commands

Connection (Node.js)

const { Pool } = require('pg');
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  // or
  host: process.env.DB_HOST,
  database: process.env.DB_NAME,
  user: process.env.DB_USER,
  password: process.env.DB_PASS,
  port: 5432,
});

// Query
const { rows } = await pool.query('SELECT * FROM users WHERE id = $1', [id]);

Common Patterns

Upsert (INSERT or UPDATE)

INSERT INTO settings (key, value)
VALUES ('token', 'xxx')
ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value;

Pagination

SELECT * FROM orders
ORDER BY created_at DESC
LIMIT $1 OFFSET $2;
-- OFFSET = (page - 1) * limit

JSON Field

-- Save
UPDATE users SET meta = $1::jsonb WHERE id = $2;
-- Read a field
SELECT meta->>'name' FROM users WHERE id = $1;

Transaction

const client = await pool.connect();
try {
  await client.query('BEGIN');
  await client.query('UPDATE ...', [...]);
  await client.query('INSERT INTO ...', [...]);
  await client.query('COMMIT');
} catch (e) {
  await client.query('ROLLBACK');
  throw e;
} finally {
  client.release();
}

Migrations (without ORM)

// migrations/001_add_column.sql
ALTER TABLE users ADD COLUMN IF NOT EXISTS phone VARCHAR(20);
CREATE INDEX IF NOT EXISTS idx_users_phone ON users(phone);

psql -U user -d dbname -f migrations/001_add_column.sql

psql Commands

psql -U fuji -d fujidb      # connect
\dt                                    # list tables
\d tablename                           # table structure
\q                                     # exit

# Quick query without entering psql
psql -U fuji -d fujidb -c "SELECT COUNT(*) FROM users;"

Backup and Restore

# Backup
pg_dump -U fuji fujidb > backup_$(date +%Y%m%d).sql

# Restore
psql -U fuji_user fuji_routes < backup.sql

Indexes — When to Add

-- On fields used in WHERE and JOIN conditions
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status) WHERE status != 'done';

-- Check slow queries
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123;

Errors and Solutions