PostgreSQL Introduction

-- Common types
CREATE TABLE users (
  id SERIAL PRIMARY KEY,           -- Auto-incrementing integer
  uuid UUID DEFAULT gen_random_uuid(), -- UUID
  email VARCHAR(255) NOT NULL,     -- Variable-length string
  name TEXT,                       -- Unlimited text
  age INTEGER,                     -- Integer
  balance DECIMAL(10, 2),          -- Precise decimal
  is_active BOOLEAN DEFAULT true,  -- Boolean
  metadata JSONB,                  -- JSON (binary, indexed)
  tags TEXT[],                     -- Array
  created_at TIMESTAMPTZ DEFAULT NOW(), -- Timestamp with timezone
  updated_at TIMESTAMPTZ
);

-- One-to-Many: User has many posts
CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  content TEXT,
  user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Many-to-Many: Posts have many tags
CREATE TABLE tags (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE post_tags (
  post_id INTEGER REFERENCES posts(id) ON DELETE CASCADE,
  tag_id INTEGER REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (post_id, tag_id)
);

-- Speed up queries
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_posts_created_at ON posts(created_at DESC);

-- Unique index
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- Partial index (only active users)
CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;

-- GIN index for JSONB
CREATE INDEX idx_users_metadata ON users USING GIN(metadata);

-- Select with JOIN
SELECT
  p.id,
  p.title,
  u.name as author_name,
  u.email as author_email
FROM posts p
JOIN users u ON p.user_id = u.id
WHERE p.created_at > NOW() - INTERVAL '7 days'
ORDER BY p.created_at DESC
LIMIT 10;

-- Aggregate
SELECT
  u.id,
  u.name,
  COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
GROUP BY u.id
HAVING COUNT(p.id) > 5;

-- Insert returning
INSERT INTO users (email, name)
VALUES ('user@example.com', 'John Doe')
RETURNING id, email, created_at;

-- Upsert (insert or update)
INSERT INTO users (email, name)
VALUES ('user@example.com', 'John Doe')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name, updated_at = NOW()
RETURNING *;

npm install pg

import pg from 'pg';

const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
});

// Query
export async function getUsers() {
  const { rows } = await pool.query('SELECT * FROM users');
  return rows;
}

// Parameterized query (safe from SQL injection)
export async function getUserById(id) {
  const { rows } = await pool.query(
    'SELECT * FROM users WHERE id = $1',
    [id]
  );
  return rows[0];
}

// Insert
export async function createUser(email, name) {
  const { rows } = await pool.query(
    'INSERT INTO users (email, name) VALUES ($1, $2) RETURNING *',
    [email, name]
  );
  return rows[0];
}

// Transaction
export async function transferFunds(fromId, toId, amount) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');

    await client.query(
      'UPDATE accounts SET balance = balance - $1 WHERE id = $2',
      [amount, fromId]
    );

    await client.query(
      'UPDATE accounts SET balance = balance + $1 WHERE id = $2',
      [amount, toId]
    );

    await client.query('COMMIT');
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
}