PostgreSQL & Neon Database

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Insert single row and return generated ID
INSERT INTO users (name, email)
VALUES ('Dara S.', 'dara@example.com')
RETURNING id, created_at;

-- Insert multiple rows in one query
INSERT INTO users (name, email)
VALUES 
    ('Lina K.', 'lina@example.com'),
    ('Bona T.', 'bona@example.com');

-- Select specific columns (avoid SELECT *)
SELECT id, name, email
FROM users
WHERE email = 'dara@example.com';

-- Ordered query with LIMIT
SELECT id, name
FROM users
ORDER BY id DESC
LIMIT 10;

-- Safe UPDATE with specific WHERE target
UPDATE users
SET name = 'Dara Sam'
WHERE id = 1;

-- Safe DELETE with target ID
DELETE FROM users
WHERE id = 1;

-- Create table with JSONB column
CREATE TABLE user_settings (
    user_id BIGINT PRIMARY KEY,
    metadata JSONB NOT NULL DEFAULT '{}'
);

-- Extract as JSON object (->) vs TEXT (->>)
SELECT 
    metadata->'theme' as theme_json,
    metadata->>'theme' as theme_text
FROM user_settings
WHERE metadata->>'newsletter' = 'true';

-- PostgreSQL 13+ built-in function
SELECT gen_random_uuid();
-- Output: c9a646d3-9c61-4cd7-bf53-73d8f7634f19

-- Creating table with UUID primary key
CREATE TABLE projects (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE posts (
    id BIGSERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    -- Cascading foreign key definition
    user_id BIGINT NOT NULL 
        REFERENCES users(id) 
        ON DELETE CASCADE
);

-- Single column index
CREATE INDEX idx_users_email 
ON users(email);

-- Composite index (multi-column)
CREATE INDEX idx_tasks_project_status 
ON tasks(project_id, status);

BEGIN;

-- 1. Deduct from Alice
UPDATE accounts 
SET balance = balance - 100 
WHERE id = 'alice-123';

-- 2. Add to Bob
UPDATE accounts 
SET balance = balance + 100 
WHERE id = 'bob-456';

-- Commit changes permanently
COMMIT;

-- Or abort on any failure:
-- ROLLBACK;

-- Safe: Adding nullable column or column with DEFAULT
ALTER TABLE users 
ADD COLUMN avatar_url TEXT;

-- Safe: Adding column with default (PG 11+)
ALTER TABLE users 
ADD COLUMN role TEXT NOT NULL DEFAULT 'member';

-- 1. Create dedicated application user
CREATE USER app_user WITH PASSWORD 'StrongSecretPassword123!';

-- 2. Grant connection rights
GRANT CONNECT ON DATABASE myapp TO app_user;

-- 3. Grant table DML permissions only
GRANT SELECT, INSERT, UPDATE, DELETE 
ON ALL TABLES IN SCHEMA public TO app_user;

// FATAL VULNERABILITY!
const query = `SELECT * FROM users WHERE email = '${userEmail}'`;
// If user inputs: ' OR 1=1; DROP TABLE users; --
// Your entire database is destroyed!

// 100% SECURE: Engine treats input strictly as data
await sql`SELECT * FROM users WHERE email = ${userEmail}`;
// Or in psql / pg:
// SELECT * FROM users WHERE email = $1;

-- Page 5000:
SELECT id, title, created_at
FROM tasks
ORDER BY id ASC
LIMIT 20 OFFSET 100000;

-- Next page starts immediately after last seen ID:
SELECT id, title, created_at
FROM tasks
WHERE id > 100020
ORDER BY id ASC
LIMIT 20;

INSERT INTO tasks (project_id, assigned_to, title, status)
VALUES (
    'a1b2c3d4-0000-0000-0000-000000000001',
    'b2c3d4e5-0000-0000-0000-000000000002',
    'Implement Neon connection pooling',
    'in_progress'
)
RETURNING id, title, status;

SELECT 
    tasks.id,
    tasks.title,
    tasks.status,
    users.name AS assignee_name,
    COUNT(comments.id) AS comment_count
FROM tasks
LEFT JOIN users ON users.id = tasks.assigned_to
LEFT JOIN comments ON comments.task_id = tasks.id
WHERE tasks.project_id = $1
GROUP BY tasks.id, users.name;

UPDATE tasks
SET 
    status = 'done',
    updated_at = NOW()
WHERE id = 'c3d4e5f6-0000-0000-0000-000000000003'
RETURNING id, status, updated_at;

-- Deleting project automatically deletes
-- all linked tasks and comments via CASCADE!
DELETE FROM projects
WHERE id = 'a1b2c3d4-0000-0000-0000-000000000001';

CREATE DATABASE myapp;
DROP DATABASE myapp;

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

CREATE INDEX idx_name ON users(name);
ALTER TABLE users ADD COLUMN bio TEXT;

-- CRUD
INSERT INTO users (name, email) VALUES ('A', 'a@b.com') RETURNING id;
SELECT id, name FROM users WHERE id = $1;
UPDATE users SET name = 'B' WHERE id = $1;
DELETE FROM users WHERE id = $1;

-- Joins
SELECT u.name, p.title FROM users u JOIN posts p ON p.user_id = u.id;

-- Transactions
BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
UPDATE accounts SET balance = balance + 10 WHERE id = 2;
COMMIT;