Pgmicro Postgres Sqlite

作者 reason-machines2384a003145a無授權條款83 個星標收錄於 2026年10月8日更新於 2026年10月8日儲存庫3 個月前更新

Use pgmicro — an in-process PostgreSQL reimplementation backed by SQLite-compatible storage, embeddable as a library or CLI

僅含說明Software DevelopmentData & Analytics
AI 產生的概覽

介紹 pgmicro 的使用方式，這是一套以 SQLite 相容儲存為後端的可嵌入行程內 PostgreSQL 實作。

功能
說明如何以 CLI、JavaScript/TypeScript SDK 或 PostgreSQL 線協定伺服器模式安裝與使用 pgmicro。涵蓋連線至記憶體或檔案資料庫、執行 DDL 與參數化查詢、交易，以及 TypeScript 型別。也記錄支援的 PostgreSQL 語法、系統目錄內省與疑難排解。
適用情境
適用於在應用程式內嵌入 PostgreSQL 相容資料庫而不需執行伺服器，或需要讓單一檔案、可被 SQLite 讀取的資料庫接受 PostgreSQL SQL 的情境。也適合將 psql 或 ORM 連線到 pgmicro 的伺服器模式。
執行需求
使用 CLI 或 JavaScript/TypeScript SDK 需要 Node.js 與 npm；從原始碼建置需要 Rust 工具鏈以及 C 編譯器與 cmake。未提及憑證；僅在安裝套件與伺服器模式連線時需要網路存取。

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)

bash
# Run without installingnpx pg-micro
# Install globallynpm install -g pg-micropg-micro myapp.db

JavaScript/TypeScript SDK

bash
npm install pg-micro

From source (Rust)

bash
git clone https://github.com/glommer/pgmicrocd pgmicrocargo build --release./target/release/pgmicro

CLI usage

bash
# In-memory database (ephemeral)pgmicro
# File-backed databasepgmicro myapp.db
# PostgreSQL wire protocol serverpgmicro 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

typescript
import { connect } from "pg-micro";
// In-memory databaseconst db = await connect(":memory:");
// File-backed databaseconst db = await connect("./myapp.db");
// DDLawait db.exec(`  CREATE TABLE users (    id SERIAL PRIMARY KEY,    name TEXT NOT NULL,    email TEXT UNIQUE,    created_at TEXT DEFAULT CURRENT_TIMESTAMP  )`);
// Insertawait db.exec(`  INSERT INTO users (name, email) VALUES ('Alice', '[email protected]')`);
// Prepared statement — fetch all rowsconst stmt = await db.prepare("SELECT * FROM users WHERE name = ?");const rows = await stmt.all("Alice");console.log(rows);// [{ id: 1, name: 'Alice', email: '[email protected]', created_at: '...' }]
// Fetch single rowconst row = await stmt.get("Alice");
// Execute with bound parametersawait db.exec("INSERT INTO users (name, email) VALUES (?, ?)", ["Bob", "[email protected]"]);
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 parametersconst 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 filterconst 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 transactionawait 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
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', '[email protected]')");
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

sql
-- SERIAL / auto-incrementCREATE TABLE items (id SERIAL PRIMARY KEY, name TEXT);
-- Dollar-quoted stringsCREATE FUNCTION hello() RETURNS TEXT AS $$  SELECT 'hello world';$$ LANGUAGE SQL;
-- Cast syntaxSELECT '42'::int;SELECT NOW()::text;
-- JSON operators (where implemented)SELECT data->>'key' FROM records;
-- Standard PG typesCREATE TABLE typed (  n   INT,  f   FLOAT8,  t   TEXT,  b   BOOLEAN,  ts  TIMESTAMP,  j   JSON);
-- PostgreSQL-style constraintsCREATE TABLE orders (  id    SERIAL PRIMARY KEY,  total NUMERIC NOT NULL CHECK (total >= 0),  state TEXT DEFAULT 'pending');

Server mode with psql / ORMs

bash
# Start serverpgmicro myapp.db --server 127.0.0.1:5432
# Connect with psqlpsql -h 127.0.0.1 -p 5432 -U turso -d main
# Connect with libpq connection string (Node.js pg driver)# DATABASE_URL=postgresql://[email protected]:5432/main
typescript
// Using node-postgres (pg) against pgmicro serverimport { 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)

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

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

Schema introspection via pg catalog

sql
-- List all user tablesSELECT tablename FROM pg_tables WHERE schemaname = 'public';
-- List columns for a tableSELECT column_name, data_typeFROM information_schema.columnsWHERE 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

來源與署名

來源：reason-machines/trending-skills位於skills/pgmicro-postgres-sqlite提交2384a00

授權條款： 無授權條款

內容歸原作者所有。SourceWeft 從公開儲存庫中收錄這些內容。

檢舉或申請下架

更多來自 reason-machines/trending-skills 的技能

Yourvpndead Vpn Detection

reason-machines

介紹一款 Android 應用程式，用來偵測 VPN 用戶端並探測本機 SOCKS5 與 Clash API 連接埠是否外洩。

Security833 個月前更新

X Mentor Skill Nuwa

reason-machines

提供 X（Twitter）內容策略指引：推文與長推撰寫、鉤子評論、成長策略與帳號診斷。

Marketing & Sales833 個月前更新

Wildworld Dataset

reason-machines

介紹 WildWorld 動作條件 ARPG 世界建模資料集，並提供預期的載入、篩選與基準評測程式碼。

Data & Analytics833 個月前更新

Understand Anything Knowledge Graph

reason-machines

為程式碼庫建立可互動的知識圖譜，並支援視覺化探索、搜尋與提問。

Software Development833 個月前更新

Webphysics Avbd Engine

reason-machines

指導將實驗性 WebGPU AVBD 剛體與軟體物理引擎整合到瀏覽器專案中。

Software Development833 個月前更新

Ui Ux Pro Max Skill

reason-machines

依產品描述產生 UI/UX 設計系統，包含頁面結構、風格、配色、字體搭配與檢查清單。

Design & Creative833 個月前更新

更多Software Development技能

Playground

anthropics

精選

建立自成一體的互動式 HTML 遊樂場，包含控制項、即時預覽與可複製的提示詞輸出。

Software Development37K今天更新

M5 Onboard

anthropics

精選

透過 USB 偵測 M5Stack ESP32 開發板，燒錄 UIFlow 2.0 韌體並安裝 MicroPython 應用程式套件。

Software Development37K今天更新

Cardputer Buddy

anthropics

精選

指導迭代 Cardputer-Adv 的 MicroPython 應用程式套件：新增應用程式、透過 USB 序列埠推送檔案、查看日誌並執行 REPL 指令。

Software Development37K今天更新

Web Design Guidelines

vercel-labs

精選

依據從遠端來源取得的 Web Interface Guidelines 審查 UI 程式碼，並以 file:line 形式回報問題。

Software Development32K5 週前更新

Vercel React Native Skills

vercel-labs

精選

React Native 與 Expo 最佳實務規則，涵蓋清單效能、動畫、導覽、UI 模式、狀態與 monorepo 設定。

Software Development32K5 週前更新

Vercel React Best Practices

vercel-labs

精選

Vercel 的 React 與 Next.js 效能指南，以 70 條規則的形式用於撰寫、審查與重構程式碼。

Software Development32K5 週前更新