# Postgres

Lightweight in-memory Postgres implementation speaking the real wire protocol.

| Key | Value |
|-----|-------|
| Port | 5432 |
| Protocol | Wire protocol (TCP) |
| Size | ~80 KB |
| Startup | < 200ms |

## Default Connection

```
postgresql://parlel:parlel@localhost:5432/parlel
```

| Parameter | Value |
|-----------|-------|
| User | `parlel` |
| Password | `parlel` |
| Database | `parlel` |

## Supported SQL

### DDL

```sql
-- Create table
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(255),
  age INTEGER DEFAULT 0,
  active BOOLEAN DEFAULT true,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create view
CREATE VIEW active_users AS SELECT * FROM users WHERE active = true;

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

-- Create sequence
CREATE SEQUENCE order_id_seq START 1000;

-- Create function (accepted, not executed)
CREATE FUNCTION update_timestamp() RETURNS TRIGGER AS $$ BEGIN ... END; $$ LANGUAGE plpgsql;
```

### DML — INSERT

```sql
INSERT INTO users (email, name) VALUES ('alice@test.com', 'Alice');
INSERT INTO users (email, name, age) VALUES ('bob@test.com', 'Bob', 30);

-- RETURNING
INSERT INTO users (email, name) VALUES ('charlie@test.com', 'Charlie') RETURNING *;
INSERT INTO users (email, name) VALUES ('dave@test.com', 'Dave') RETURNING id;
```

### DML — SELECT

```sql
SELECT * FROM users;
SELECT name, email FROM users;
SELECT * FROM users WHERE id = 1;
SELECT * FROM users WHERE email = 'alice@test.com';
SELECT * FROM users WHERE age > 25;
SELECT * FROM users WHERE name LIKE 'A%';
SELECT * FROM users WHERE active = true;

-- Aggregates
SELECT COUNT(*) FROM users;
SELECT COUNT(*) as total FROM users WHERE active = true;

-- Sorting
SELECT * FROM users ORDER BY name ASC;
SELECT * FROM users ORDER BY created_at DESC;

-- Limit
SELECT * FROM users LIMIT 10;
SELECT * FROM users LIMIT 10 OFFSET 5;

-- IN
SELECT * FROM users WHERE id IN (1, 2, 3);

-- BETWEEN
SELECT * FROM users WHERE age BETWEEN 20 AND 30;

-- JOINs
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id;

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

-- Subqueries
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);
```

### DML — UPDATE

```sql
UPDATE users SET name = 'Alice Updated' WHERE id = 1;
UPDATE users SET age = age + 1 WHERE active = true;
UPDATE users SET name = 'New Name', email = 'new@test.com' WHERE id = 5;
```

### DML — DELETE

```sql
DELETE FROM users WHERE id = 1;
DELETE FROM users WHERE active = false;
DELETE FROM orders WHERE user_id = 1;
```

### Sequences

```sql
SELECT NEXTVAL('order_id_seq');
SELECT CURRVAL('order_id_seq');
SELECT SETVAL('order_id_seq', 2000);
```

### System Queries

```sql
SELECT 1;

-- Information schema
SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';
SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'users';
```

### Transactions

```sql
BEGIN;
INSERT INTO users (email, name) VALUES ('tx@test.com', 'TX User');
COMMIT;

BEGIN;
INSERT INTO users (email, name) VALUES ('rollback@test.com', 'Rollback');
ROLLBACK;
```

## Usage Examples

### Via Test Context

```typescript
const ctx = await createTestContext();

// Create schema
await ctx.postgres.execute(`
  CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email TEXT UNIQUE NOT NULL,
    name TEXT
  )
`);

// Insert
await ctx.postgres.execute(`
  INSERT INTO users (email, name) VALUES ('alice@test.com', 'Alice')
`);

// Query
const users = await ctx.postgres.query("SELECT * FROM users");
console.log(users); // [{ id: 1, email: 'alice@test.com', name: 'Alice' }]

// Cleanup (drops all tables)
await ctx.postgres.cleanup();
```

### Via SDK

```typescript
const instance = await startParallel({ services: [{ name: "postgres" }] });
const port = instance.services.get("postgres").port;

import pg from "pg";
const pool = new pg.Pool({
  host: "localhost",
  port,
  user: "parlel",
  password: "parlel",
  database: "parlel",
});

await pool.query("CREATE TABLE items (id SERIAL PRIMARY KEY, name TEXT)");
await pool.query("INSERT INTO items (name) VALUES ($1)", ["Widget"]);
const { rows } = await pool.query("SELECT * FROM items");
```

### Via CLI + psql

```bash
parlel up postgres     # starts on the default port 5432
PGPASSWORD=parlel psql -h localhost -p 5432 -U parlel -d parlel

parlel=# CREATE TABLE test (id SERIAL PRIMARY KEY, val TEXT);
parlel=# INSERT INTO test (val) VALUES ('hello');
parlel=# SELECT * FROM test;
parlel down postgres
```

### Via MCP (Parlel Sandbox)

When Postgres runs inside a Parlel sandbox, drive it through the sandbox's MCP
endpoint with `parlel_execute`. Pass raw SQL as `command` (multiple statements
may be separated by `;`):

```json
{
  "jsonrpc": "2.0", "id": 1, "method": "tools/call",
  "params": {
    "name": "parlel_execute",
    "arguments": {
      "service": "postgres",
      "command": "CREATE TABLE t (id int, name text); INSERT INTO t VALUES (1, 'Ada'); SELECT * FROM t;"
    }
  }
}
```

Each statement returns `{ statement, tag, fields, rows, error }`.

## State Inspection

```typescript
const state = await ctx.dump("postgres");
// {
//   tableCount: 2,
//   tables: {
//     users: {
//       columns: [
//         { name: "id", type: "SERIAL", nullable: false, primaryKey: true },
//         { name: "email", type: "VARCHAR(255)", nullable: false },
//         { name: "name", type: "VARCHAR(255)", nullable: true }
//       ],
//       rowCount: 3,
//       rows: [
//         { id: 1, email: "alice@test.com", name: "Alice" },
//         { id: 2, email: "bob@test.com", name: "Bob" },
//         { id: 3, email: "charlie@test.com", name: "Charlie" }
//       ],
//       sample: [/* first 5 rows */]
//     },
//     orders: { ... }
//   },
//   sequences: { order_id_seq: { current: 1003, increment: 1 } }
// }
```

## Chaos Testing

```typescript
import { PRESET_RULES } from "@parlel/parlel-pool/agent";

// Postgres returns errors
ctx.chaos.configure(PRESET_RULES.postgresDown);
ctx.chaos.activate();

// Slow writes
ctx.chaos.configure(PRESET_RULES.postgresSlowWrites);

// SELECT returns empty
ctx.chaos.configure(PRESET_RULES.postgresEmptySelects);

// Custom: break specific queries
ctx.chaos.configure({
  name: "break_user_queries",
  service: "postgres",
  command: "SELECT",
  pattern: "users",
  action: "empty_result",
});
```

## Seed File

```sql
-- schema.sql
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(255),
  role VARCHAR(50) DEFAULT 'user'
);

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id),
  title TEXT NOT NULL,
  body TEXT,
  published BOOLEAN DEFAULT false,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO users (email, name, role) VALUES ('admin@test.com', 'Admin', 'admin');
INSERT INTO users (email, name) VALUES ('alice@test.com', 'Alice');
INSERT INTO users (email, name) VALUES ('bob@test.com', 'Bob');

INSERT INTO posts (user_id, title, body, published) VALUES (1, 'First Post', 'Hello world', true);
INSERT INTO posts (user_id, title, body) VALUES (2, 'Draft', 'Work in progress');
```

```typescript
await ctx.seed.executeFile("postgres", "./schema.sql", 5432);
```

## Surface coverage

This emulator faithfully replicates the API surface most application code and agents exercise. Anything below the supported lines is either an intentional design choice for a fast, zero-cost local emulator (✓ By design) or a candidate for a future release (⟳ Roadmap) — never a silent inaccuracy.

Legend: ✅ fully supported · ◐ accepted (stored, not strictly enforced) · ✓ by design · ⟳ on the roadmap.

| Feature | Status |
|---------|--------|
| CRUD (`INSERT`/`SELECT`/`UPDATE`/`DELETE`, `RETURNING`) | ✅ Supported |
| `WHERE` with numeric-aware `=` `<>` `<` `>` `<=` `>=`, `AND`/`OR` | ✅ Supported |
| `IN (...)`, `IN (subquery)`, `NOT IN`, `BETWEEN`, `LIKE`/`ILIKE`, `IS [NOT] NULL` | ✅ Supported |
| `ORDER BY` (numeric + text, `ASC`/`DESC`, multi-key), `LIMIT`, `OFFSET`, `DISTINCT` | ✅ Supported |
| Aggregates `COUNT`/`SUM`/`AVG`/`MIN`/`MAX` with `GROUP BY` and `HAVING` | ✅ Supported |
| `JOIN` (inner, `ON a.x = b.y`) | ✅ Supported |
| Set ops `UNION` / `INTERSECT` / `EXCEPT` | ✅ Supported |
| CTEs (`WITH name AS (...) SELECT ...`, multiple CTEs) | ✅ Supported |
| Transactions (`BEGIN`/`COMMIT`/`ROLLBACK`) | ✅ Supported |
| Sequences (`nextval`/`currval`/`setval`, `SERIAL`) | ✅ Supported |
| Views | ◐ Stored; queryable as snapshots |
| Indexes | ◐ Accepted (no physical effect — results are always correct) |
| Window functions (`OVER (...)`) | ⟳ Roadmap — **returns an explicit `0A000` error, never wrong rows** |
| Stored procedures / triggers | ⟳ Roadmap — DDL accepted; bodies not executed |
| JSON/JSONB operators (`->`, `->>`, `@>`) | ⟳ Roadmap |
| Array types / full-text search (`tsvector`) | ⟳ Roadmap |