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

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

← 返回排行榜

pgmicro-postgres-sqlite

安装