pgmicro-postgres-sqlite

pgmicro

Skill by 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. 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)

# Run without installing
npx pg-micro

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

JavaScript/TypeScript SDK

npm install pg-micro

From source (Rust)

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

CLI usage

# 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

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

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

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

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

-- 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

# 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

// 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)

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

# 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

-- 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
• Turso (upstream engine)
• libpg_query — the PostgreSQL parser extraction library
• pg_query Rust crate
• npm: pg-micro