# Run without installing
npx pg-micro

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

npm install pg-micro

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

# 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

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

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();

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();

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();

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();

-- 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'
);

# 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();

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)

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

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

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