PostgreSQL

Port 5432 Relational data with ACID transactions, joins, and strict schemas.

When to Use

• User accounts, orders, billing
• Foreign keys, JOINs, transactions
• Reporting and analytics
• Schema migrations with constraints

Connection

DATABASE_URL=postgresql://myapp_user:PASSWORD@db.bizfylabs.com:5432/myapp_db

Setup (Node.js / Python)

npm install pg          # Node.js
pip install psycopg2-binary   # Python

// Node.js
import pg from 'pg';
const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL, max: 10 });
await pool.query('SELECT 1');

# Python
import os, psycopg2
conn = psycopg2.connect(os.environ["DATABASE_URL"])

Schema — Create Tables

CREATE TABLE users (
  id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email      TEXT NOT NULL UNIQUE,
  name       TEXT NOT NULL,
  status     TEXT NOT NULL DEFAULT 'active',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE orders (
  id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id    UUID NOT NULL REFERENCES users(id),
  total      NUMERIC(10,2) NOT NULL,
  status     TEXT NOT NULL DEFAULT 'pending',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_orders_user_id ON orders (user_id);

CRUD — Node.js

Always use parameterized queries ($1, $2).

Create

const { rows } = await pool.query(
  'INSERT INTO users (email, name) VALUES ($1, $2) RETURNING *',
  [email, name]
);
const newUser = rows[0];

// Bulk insert
await pool.query(
  'INSERT INTO users (email, name) SELECT unnest($1::text[]), unnest($2::text[])',
  [['a@b.com','c@d.com'], ['Alice','Carol']]
);

Read

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

// List with filter + pagination
const { rows: list } = await pool.query(
  'SELECT * FROM users WHERE status = $1 ORDER BY created_at DESC LIMIT $2 OFFSET $3',
  ['active', 20, 0]
);

// Join
const { rows: orders } = await pool.query(
  `SELECT o.*, u.email FROM orders o
   JOIN users u ON u.id = o.user_id WHERE o.user_id = $1`,
  [userId]
);

// Count
const { rows: [{ count }] } = await pool.query(
  'SELECT COUNT(*) FROM users WHERE status = $1', ['active']
);

Update

await pool.query(
  'UPDATE users SET name = $1, updated_at = NOW() WHERE id = $2',
  [newName, userId]
);

// Upsert
await pool.query(
  `INSERT INTO settings (user_id, key, value) VALUES ($1, $2, $3)
   ON CONFLICT (user_id, key) DO UPDATE SET value = EXCLUDED.value`,
  [userId, 'theme', 'dark']
);

Delete

await pool.query('DELETE FROM users WHERE id = $1', [userId]);

// Soft delete
await pool.query(
  "UPDATE users SET status = 'deleted', updated_at = NOW() WHERE id = $1",
  [userId]
);

CRUD — Python

cur = conn.cursor()

# Create
cur.execute('INSERT INTO users (email, name) VALUES (%s, %s) RETURNING id', (email, name))
user_id = cur.fetchone()[0]

# Read
cur.execute('SELECT * FROM users WHERE id = %s', (user_id,))
user = cur.fetchone()

# Update
cur.execute('UPDATE users SET name = %s WHERE id = %s', (new_name, user_id))

# Delete
cur.execute('DELETE FROM users WHERE id = %s', (user_id,))
conn.commit()

Transactions

const client = await pool.connect();
try {
  await client.query('BEGIN');
  await client.query('INSERT INTO orders (user_id, total) VALUES ($1, $2)', [userId, total]);
  await client.query('UPDATE users SET balance = balance - $1 WHERE id = $2', [total, userId]);
  await client.query('COMMIT');
} catch (e) {
  await client.query('ROLLBACK');
  throw e;
} finally {
  client.release();
}

Admin — Create Database & User

docker exec -it bizfy-postgres psql -U postgres -d bizfy

-- 1. Create database
CREATE DATABASE myapp_db;

-- 2. Create read/write user
CREATE USER myapp_user WITH PASSWORD 'strong-random-password';
GRANT CONNECT ON DATABASE myapp_db TO myapp_user;

-- 3. Grant schema permissions
\c myapp_db
GRANT USAGE ON SCHEMA public TO myapp_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO myapp_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO myapp_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO myapp_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES TO myapp_user;

Read-only User

CREATE USER myapp_readonly WITH PASSWORD 'strong-random-password';
GRANT CONNECT ON DATABASE myapp_db TO myapp_readonly;
\c myapp_db
GRANT USAGE ON SCHEMA public TO myapp_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO myapp_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO myapp_readonly;

Delete User / Database

-- Revoke and drop user
REVOKE ALL PRIVILEGES ON DATABASE myapp_db FROM myapp_user;
\c myapp_db
DROP OWNED BY myapp_user;
DROP USER myapp_user;

-- Drop database (destructive)
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'myapp_db';
DROP DATABASE myapp_db;

List Users & Tables

\l                          -- list databases
\du                         -- list users
\dt                         -- list tables
SELECT * FROM pg_roles;     -- all roles

Send to Developer

DATABASE_URL=postgresql://myapp_user:PASSWORD@db.bizfylabs.com:5432/myapp_db