pgmicro-postgres-sqlite

# pgmicro

> Skill by [ara.so](https://ara.so) — Daily 2026 Skills collection.

pgmicro is an in-process reimplementation of PostgreSQL backed by a SQLite-compatible storage engine. It parses PostgreSQL SQL using the real PostgreSQL parser (`libpg_query`) and compiles it directly to SQLite VDBE bytecode, executed by [Turso](https://github.com/tursodatabase/turso). The result is a fast, embeddable, single-file (or in-memory) database that speaks PostgreSQL — no server process required.

## Key capabilities

- Full PostgreSQL SQL syntax (via the actual PG parser)
- SQLite-compatible `.db` file format (readable by any SQLite tool)
- JavaScript/TypeScript SDK (WASM-based, runs in Node.js and browsers)
- PostgreSQL wire protocol server mode (connect with `psql`, ORMs, etc.)
- Dialect switching: access the same database with PG or SQLite syntax
- PostgreSQL system catalog virtual tables (`pg_class`, `pg_attribute`, `pg_type`, etc.)

## Installation

### CLI (Node.js)

```bash
# Run without installing
npx pg-micro

# Install globally
npm install -g pg-micro
pg-micro myapp.db
```

### JavaScript/TypeScript SDK

```bash
npm install pg-micro
```

### From source (Rust)

```bash
git clone https://github.com/glommer/pgmicro
cd pgmicro
cargo build --release
./target/release/pgmicro
```

## CLI usage

```bash
# In-memory database (ephemeral)
pgmicro

# File-backed database
pgmicro myapp.db

# PostgreSQL wire protocol server
pgmicro myapp.db --server 127.0.0.1:5432

# In-memory server (useful for testing)
pgmicro :memory: --server 127.0.0.1:5432
```

### CLI meta-commands

```
\?          Show help
\q          Quit
\dt         List tables
\d <table>  Describe table schema
```

## JavaScript/TypeScript SDK

### Basic usage

```typescript
import { connect } from "pg-micro";

// In-memory database
const db = await connect(":memory:");

// File-backed database
const db = await connect("./myapp.db");

// DDL
await db.exec(`
  CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
  )
`);

// Insert
await db.exec(`
  INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com')
`);

// Prepared statement — fetch all rows
const stmt = await db.prepare("SELECT * FROM users WHERE name = ?");
const rows = await stmt.all("Alice");
console.log(rows);
// [{ id: 1, name: 'Alice', email: 'alice@example.com', created_at: '...' }]

// Fetch single row
const row = await stmt.get("Alice");

// Execute with bound parameters
await db.exec("INSERT INTO users (name, email) VALUES (?, ?)", ["Bob", "bob@example.com"]);

await db.close();
```

### Parameterized queries

```typescript
import { connect } from "pg-micro";

const db = await connect(":memory:");

await db.exec(`
  CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    type TEXT NOT NULL,
    payload TEXT,
    ts TEXT DEFAULT CURRENT_TIMESTAMP
  )
`);

// Positional parameters
const insert = await db.prepare(
  "INSERT INTO events (type, payload) VALUES ($1, $2)"
);
await insert.run("user.signup", JSON.stringify({ userId: 42 }));
await insert.run("page.view", JSON.stringify({ path: "/home" }));

// Query with filter
const query = await db.prepare(
  "SELECT * FROM events WHERE type = $1 ORDER BY id DESC"
);
const signups = await query.all("user.signup");
console.log(signups);

await db.close();
```

### Transactions

```typescript
import { connect } from "pg-micro";

const db = await connect(":memory:");

await db.exec("CREATE TABLE accounts (id INT PRIMARY KEY, balance INT)");
await db.exec("INSERT INTO accounts VALUES (1, 1000), (2, 500)");

// Manual transaction
await db.exec("BEGIN");
try {
  await db.exec("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
  await db.exec("UPDATE accounts SET balance = balance + 100 WHERE id = 2");
  await db.exec("COMMIT");
} catch (err) {
  await db.exec("ROLLBACK");
  throw err;
}

const rows = await db.prepare("SELECT * FROM accounts").all();
console.log(rows); // [{ id: 1, balance: 900 }, { id: 2, balance: 600 }]

await db.close();
```

### Using with TypeScript types

```typescript
import { connect } from "pg-micro";

interface User {
  id: number;
  name: string;
  email: string;
  created_at: string;
}

const db = await connect(":memory:");

await db.exec(`
  CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
  )
`);

await db.exec("INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com')");

const stmt = db.prepare<User>("SELECT * FROM users");
const users: User[] = await stmt.all();
console.log(users[0].name); // 'Alice'

await db.close();
```

## PostgreSQL features supported

```sql
-- SERIAL / auto-increment
CREATE TABLE items (id SERIAL PRIMARY KEY, name TEXT);

-- Dollar-quoted strings
CREATE FUNCTION hello() RETURNS TEXT AS $$
  SELECT 'hello world';
$$ LANGUAGE SQL;

-- Cast syntax
SELECT '42'::int;
SELECT NOW()::text;

-- JSON operators (where implemented)
SELECT data->>'key' FROM records;

-- Standard PG types
CREATE TABLE typed (
  n   INT,
  f   FLOAT8,
  t   TEXT,
  b   BOOLEAN,
  ts  TIMESTAMP,
  j   JSON
);

-- PostgreSQL-style constraints
CREATE TABLE orders (
  id    SERIAL PRIMARY KEY,
  total NUMERIC NOT NULL CHECK (total >= 0),
  state TEXT DEFAULT 'pending'
);
```

## Server mode with psql / ORMs

```bash
# Start server
pgmicro myapp.db --server 127.0.0.1:5432

# Connect with psql
psql -h 127.0.0.1 -p 5432 -U turso -d main

# Connect with libpq connection string (Node.js pg driver)
# DATABASE_URL=postgresql://turso@127.0.0.1:5432/main
```

```typescript
// Using node-postgres (pg) against pgmicro server
import { Client } from "pg";

const client = new Client({
  host: "127.0.0.1",
  port: 5432,
  user: "turso",
  database: "main",
});

await client.connect();
const res = await client.query("SELECT * FROM users");
console.log(res.rows);
await client.end();
```

## Architecture overview

```
PostgreSQL SQL → libpg_query (real PG parser) → PG parse tree
                                                      │
                                              Translator (parser_pg/)
                                                      │ Turso AST
                                              Turso Compiler
                                                      │ VDBE bytecode
                                              Bytecode Engine (vdbe/)
                                                      │
                                              SQLite B-tree storage (.db file)
```

- The `.db` output file is a **standard SQLite database** — open it with DB Browser for SQLite, the `sqlite3` CLI, or any SQLite library.
- PostgreSQL system catalog tables (`pg_class`, `pg_attribute`, `pg_type`, `pg_namespace`) are exposed as virtual tables so `psql` meta-commands like `\dt` and `\d` work correctly.

## Common patterns

### Per-request ephemeral databases (AI agents, sandboxes)

```typescript
import { connect } from "pg-micro";

async function runAgentSession(agentId: string, sql: string) {
  // Each session gets its own isolated in-memory DB — no cleanup needed
  const db = await connect(":memory:");

  await db.exec("CREATE TABLE scratch (key TEXT PRIMARY KEY, value TEXT)");

  // Agent writes intermediate results
  await db.exec(
    "INSERT INTO scratch VALUES ($1, $2)",
    [`agent-${agentId}`, sql]
  );

  const result = await db.prepare("SELECT * FROM scratch").all();
  await db.close();
  return result;
}
```

### Inspecting the SQLite file directly

```bash
# pgmicro writes standard SQLite — use sqlite3 CLI to inspect
sqlite3 myapp.db ".tables"
sqlite3 myapp.db "SELECT * FROM users"
sqlite3 myapp.db ".schema users"
```

### Schema introspection via pg catalog

```sql
-- List all user tables
SELECT tablename FROM pg_tables WHERE schemaname = 'public';

-- List columns for a table
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'users';
```

## Troubleshooting

**`SERIAL` column not auto-incrementing**
Ensure you are not explicitly inserting `NULL` into the `id` column — insert without the column name and pgmicro will auto-assign.

**`psql` meta-commands (`\dt`, `\d`) show nothing**
Make sure you created tables in the `public` schema (the default). The PostgreSQL catalog virtual tables are populated from actual schema metadata.

**File database not persisting**
Pass a real file path, not `:memory:`. Confirm the process has write permission to the target directory.

**Wire protocol server refused by client**
The server supports a subset of the PostgreSQL wire protocol. Some advanced client features (SSL, SCRAM auth, extended query protocol edge cases) may not be implemented yet. Use simple query mode when possible.

**Unsupported PostgreSQL syntax**
pgmicro is experimental — not all PostgreSQL features are translated. Check the translator layer (`parser_pg/`) for what is currently mapped. Common gaps: stored procedures with complex PL/pgSQL, window functions, CTEs (may be partial), `COPY` command.

**Build errors from source**
Ensure you have a recent stable Rust toolchain (`rustup update stable`) and that `libpg_query` native dependencies (C compiler, `cmake`) are available on your system.

## Resources

- [GitHub: glommer/pgmicro](https://github.com/glommer/pgmicro)
- [Turso (upstream engine)](https://github.com/tursodatabase/turso)
- [libpg_query](https://github.com/pganalyze/libpg_query) — the PostgreSQL parser extraction library
- [pg_query Rust crate](https://crates.io/crates/pg_query)
- [npm: pg-micro](https://www.npmjs.com/package/pg-micro)