Back to Details

pgmicro-postgres-sqlite

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

pgmicro

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.
由ara.so提供的技能 — 2026年度每日技能合集。
pgmicro是一款基于SQLite兼容存储引擎实现的进程内PostgreSQL重实现版本。它使用真实的PostgreSQL解析器(
libpg_query
)解析PostgreSQL SQL，直接编译为SQLite VDBE字节码，由Turso执行。最终得到的是一款快速、可嵌入、单文件（或内存型）的PostgreSQL兼容数据库，无需启动服务进程。

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.)
  • 完整的PostgreSQL SQL语法支持（通过真实的PG解析器实现）
  • SQLite兼容的
    .db
    文件格式（可被任意SQLite工具读取）
  • JavaScript/TypeScript SDK（基于WASM，可在Node.js和浏览器中运行）
  • PostgreSQL网络协议服务模式（可使用
    psql
    、ORM等工具连接）
  • 方言切换：可使用PG或SQLite语法访问同一个数据库
  • PostgreSQL系统目录虚拟表支持（
    pg_class
    、
    pg_attribute
    、
    pg_type
    等）

Installation

安装

CLI (Node.js)

CLI (Node.js)

bash
undefined
bash
undefined

Run without installing

Run without installing

npx pg-micro
npx pg-micro

Install globally

Install globally

npm install -g pg-micro pg-micro myapp.db
undefined
npm install -g pg-micro pg-micro myapp.db
undefined

JavaScript/TypeScript SDK

JavaScript/TypeScript SDK

bash
npm install pg-micro
bash
npm install pg-micro

From source (Rust)

源码安装（Rust）

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

CLI usage

CLI使用方法

bash
undefined
bash
undefined

In-memory database (ephemeral)

In-memory database (ephemeral)

pgmicro
pgmicro

File-backed database

File-backed database

pgmicro myapp.db
pgmicro myapp.db

PostgreSQL wire protocol server

PostgreSQL wire protocol server

pgmicro myapp.db --server 127.0.0.1:5432
pgmicro myapp.db --server 127.0.0.1:5432

In-memory server (useful for testing)

In-memory server (useful for testing)

pgmicro :memory: --server 127.0.0.1:5432
undefined
pgmicro :memory: --server 127.0.0.1:5432
undefined

CLI meta-commands

CLI元命令

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

JavaScript/TypeScript SDK

JavaScript/TypeScript SDK

Basic usage

基础用法

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

参数化查询

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

事务

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

配合TypeScript类型使用

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

支持的PostgreSQL特性

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

使用psql/ORM连接服务模式

bash
undefined
bash
undefined

Start server

Start server

pgmicro myapp.db --server 127.0.0.1:5432
pgmicro myapp.db --server 127.0.0.1:5432

Connect with psql

Connect with psql

psql -h 127.0.0.1 -p 5432 -U turso -d main
psql -h 127.0.0.1 -p 5432 -U turso -d main

Connect with libpq connection string (Node.js pg driver)

Connect with libpq connection string (Node.js pg driver)

DATABASE_URL=postgresql://turso@127.0.0.1:5432/main

DATABASE_URL=postgresql://turso@127.0.0.1:5432/main


```typescript
// 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();

```typescript
// 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.
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)
  • 输出的
    .db
    文件是标准SQLite数据库——可以使用SQLite数据库浏览器、
    sqlite3
    CLI工具或者任意SQLite库打开。
  • PostgreSQL系统目录表（
    pg_class
    、
    pg_attribute
    、
    pg_type
    、
    pg_namespace
    ）以虚拟表形式暴露，因此
    psql
    的元命令如
    \dt
    和
    \d
    可以正常工作。

Common patterns

常见使用场景

Per-request ephemeral databases (AI agents, sandboxes)

按请求隔离的临时数据库（AI Agent、沙箱场景）

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

直接查看SQLite文件

bash
undefined
bash
undefined

pgmicro writes standard SQLite — use sqlite3 CLI to inspect

pgmicro writes standard SQLite — use sqlite3 CLI to inspect

sqlite3 myapp.db ".tables" sqlite3 myapp.db "SELECT * FROM users" sqlite3 myapp.db ".schema users"
undefined
sqlite3 myapp.db ".tables" sqlite3 myapp.db "SELECT * FROM users" sqlite3 myapp.db ".schema users"
undefined

Schema introspection via pg catalog

通过PG目录进行结构自省

sql
-- 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';
sql
-- 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.
SERIAL
列不自增 确保你没有显式向
id
列插入
NULL
——插入时不指定该列名，pgmicro会自动赋值。
psql
元命令（
\dt
、
\d
）无返回内容 确认你是在
public
schema（默认schema）下创建的表。PostgreSQL目录虚拟表是基于实际的结构元数据生成的。
文件数据库数据不持久化 请传入真实的文件路径，而非
:memory:
。确认进程对目标目录有写入权限。
客户端拒绝连接网络协议服务 服务支持PostgreSQL网络协议的子集，部分高级客户端特性（SSL、SCRAM认证、扩展查询协议的边缘场景）可能尚未实现。尽可能使用简单查询模式。
PostgreSQL语法不支持 pgmicro还处于实验阶段——并非所有PostgreSQL特性都完成了翻译转换。可查看转换层（
parser_pg/
）了解当前已支持的内容。常见的未支持功能：包含复杂PL/pgSQL的存储过程、窗口函数、CTE（可能部分支持）、
COPY
命令。
源码编译报错 确保你安装了最新的稳定版Rust工具链（
rustup update stable
），并且系统中存在
libpg_query
的原生依赖（C编译器、
cmake
）。

Resources

相关资源