Pgmicro Postgres Sqlite

reason-machines/trending-skills/skills/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周前更新
Pgmicro Postgres Sqlite · skills/pgmicro-postgres-sqlite 智能体技能 | SourceWeft