pgmicro-postgres-sqlite

$27

# 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