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
.dbfile 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.dbJavaScript/TypeScript SDK
npm install pg-microFrom source (Rust)
git clone https://github.com/glommer/pgmicro
cd pgmicro
cargo build --release
./target/release/pgmicroCLI 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:5432CLI meta-commands
\? Show help
\q Quit
\dt List tables
\d <table> Describe table schemaJavaScript/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
.dboutput file is a standard SQLite database — open it with DB Browser for SQLite, thesqlite3CLI, or any SQLite library. - PostgreSQL system catalog tables (
pg_class,pg_attribute,pg_type,pg_namespace) are exposed as virtual tables sopsqlmeta-commands like\dtand\dwork 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