pg-micro: PostgreSQL-compatible Embeddable Database

0.0.5 verified Thu Apr 23 auth: no javascript

pg-micro is an experimental, in-process reimplementation of PostgreSQL, currently at version 0.0.5. It is built as an experimental fork of Turso, which is a full, from-scratch rewrite of SQLite in Rust. Unlike other approaches that try to compile PostgreSQL to WebAssembly or translate PostgreSQL syntax to SQLite, pg-micro directly parses the PostgreSQL language using `libpg_query` (the same parser PostgreSQL itself uses) and compiles it to SQLite bytecode for execution on Turso's engine. This approach aims to provide a fast, embeddable, single-file database that natively understands PostgreSQL syntax, targeting ephemeral, low-touch, short-lived, and small database use cases. Its key differentiators include 100% PostgreSQL syntax fidelity, direct compilation to SQLite bytecode, and a standard SQLite-compatible storage format, allowing for dual access via PostgreSQL and SQLite syntax. Release cadence is currently irregular due to its experimental nature and close ties to Turso's development cycle.

Common errors

error Error: Database is closed ↓

cause Attempting to perform operations on a `pg-micro` Database instance after it has been explicitly closed with `db.close()`, or if the connection failed to establish initially.

fix

Ensure all database operations complete before calling db.close(). Use await for asynchronous operations. Re-establish a new connection if further operations are needed after closing.

error Syntax error near '...' / Unexpected token ↓

cause Executing PostgreSQL SQL that contains syntax not yet fully supported by `pg-micro`'s translation layer or is incompatible with the underlying SQLite bytecode compilation. While it parses 100% PostgreSQL, some constructs might not translate directly.

fix

Simplify the SQL query, or consult the pg-micro GitHub repository and issues for known limitations regarding specific PostgreSQL features or syntax. Test queries incrementally to identify the problematic part.

error TypeError: Cannot read properties of undefined (reading 'prepare') ↓

cause This typically occurs if the `db` object (the `Database` instance) was not successfully initialized or is `null`/`undefined` when `db.prepare()` or `db.exec()` is called. This might happen if `connect()` failed or was not awaited.

fix

Ensure await connect(...) completes successfully and that the returned Database instance is valid before attempting to call its methods. Add error handling around the connect call.

Warnings

breaking As `pg-micro` is an experimental project (version 0.0.5), its API is subject to frequent and significant breaking changes. Relying on specific internal behaviors or undocumented features is highly discouraged as they may change without major version bumps. ↓

fix Refer to the latest GitHub repository and any available changelogs for the most up-to-date API and usage patterns before upgrading. Pin exact versions in production.

gotcha `pg-micro` is built on a Rust-based SQLite-compatible storage engine (Turso). While it parses 100% PostgreSQL syntax, some advanced or niche PostgreSQL features (e.g., specific extensions, complex stored procedures, certain non-standard functions) might not yet be fully supported or have different performance characteristics compared to a native PostgreSQL server. ↓

fix Thoroughly test complex PostgreSQL queries and features in `pg-micro` to confirm compatibility and expected behavior. Check the project's documentation or issue tracker for known limitations.

gotcha The project is explicitly an "experimental fork of Turso" with a stated guideline to "Minimize changes to the Turso core." This means its development path is heavily influenced by Turso's evolution, which is also under active development. Stability and long-term maintenance are tied to Turso's ecosystem. ↓

fix Monitor both `pg-micro` and `Turso` repositories for significant updates, architectural changes, or potential shifts in project direction. Consider the long-term viability in relation to Turso's status.

Install

npm install pg-micro

yarn add pg-micro

pnpm add pg-micro

Imports

connect
wrong
```
const { connect } = require('pg-micro');
```
correct
```
import { connect } from 'pg-micro';
```
The primary entry point for establishing a database connection is `connect`.
Database
wrong
```
import Database from 'pg-micro';
```
correct
```
import { Database } from 'pg-micro';
```
The `Database` class is a named export, not a default export.
Row
```
import { type Row } from 'pg-micro';
```
Import types using `import type` for clarity and to ensure they are stripped from compiled JavaScript.

Quickstart

Demonstrates connecting to an in-memory pg-micro database, creating a table, inserting data with prepared statements, and querying results.

import { connect } from 'pg-micro';

async function runExample() {
  // Connect to an in-memory database for a quick test
  // For a file-backed database, pass a path like 'myapp.db'
  const db = await connect(':memory:');

  try {
    // Execute DDL statement to create a table
    await db.exec(
      "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT, email TEXT)"
    );
    console.log('Table "users" created.');

    // Insert data into the table using prepared statements
    const insertStmt = db.prepare(
      "INSERT INTO users (name, email) VALUES ($1, $2)"
    );
    await insertStmt.run('Alice', 'alice@example.com');
    await insertStmt.run('Bob', 'bob@example.com');
    console.log('Data inserted into "users" table.');

    // Query data using a prepared statement and fetch all rows
    const rows = await db.prepare("SELECT * FROM users").all();
    console.log('Fetched rows:', rows);
    // Expected output: 
    // [
    //   { id: 1, name: 'Alice', email: 'alice@example.com' },
    //   { id: 2, name: 'Bob', email: 'bob@example.com' }
    // ]

  } catch (error) {
    console.error('Database operation failed:', error);
  } finally {
    // Always close the database connection when done
    await db.close();
    console.log('Database connection closed.');
  }
}

runExample();