pg2sqlite-rs

A PostgreSQL 16 DDL to SQLite3 DDL schema converter written in Rust.

Built with Claude Code.

Why pg2sqlite-rs?

• 🔄 Offline conversion — no database connections required, pure text-to-text
• 📦 Single binary — no runtime dependencies
• 🎯 Accurate mapping — comprehensive type, constraint, and expression conversion
• ⚠️ Lossy conversion warnings — clearly reports what information is lost
• 🦀 Memory safe — written in Rust

Features

• ✅ PostgreSQL 16 DDL parsing via sqlparser
• ✅ Comprehensive type mapping (INTEGER, TEXT, REAL, NUMERIC, BLOB)
• ✅ Constraint conversion (PK, UNIQUE, FK, CHECK)
• ✅ SERIAL/BIGSERIAL → INTEGER PRIMARY KEY AUTOINCREMENT
• ✅ ALTER TABLE constraint merging into CREATE TABLE
• ✅ Foreign key support with PRAGMA foreign_keys
• ✅ Topological sort for FK dependency ordering
• ✅ Schema filtering (public, custom, or all schemas)
• ✅ Strict mode — fail on lossy conversions
• ✅ Golden test suite for regression testing

Installation

Cargo (crates.io)

cargo install pg2sqlite

Homebrew (macOS/Linux)

brew install hiromaily/tap/pg2sqlite

From Source

Requires Rust 1.93+ (install from rustup.rs):

git clone https://github.com/hiromaily/pg2sqlite-rs.git
cd pg2sqlite-rs
make install
# The binary will be installed as `pg2sqlite`

Build Only

make build    # Debug build
make release  # Release build

Usage

Basic usage

# Convert and print to stdout
pg2sqlite -i schema.sql

# Convert and write to file
pg2sqlite -i schema.sql -o sqlite_schema.sql

# Filter by schema
pg2sqlite -i schema.sql -s myschema

# Include all schemas
pg2sqlite -i schema.sql --include-all-schemas

Foreign key support

# Enable PRAGMA foreign_keys and include FK constraints
pg2sqlite -i schema.sql --enable-foreign-keys

Strict mode

# Fail on lossy conversions instead of emitting warnings
pg2sqlite -i schema.sql --strict

Warning output

# Emit warnings to stderr
pg2sqlite -i schema.sql --emit-warnings stderr

# Emit warnings to a file
pg2sqlite -i schema.sql --emit-warnings warnings.log

Options

-i, --input <PATH>              PostgreSQL DDL input file
-o, --output <PATH>             SQLite DDL output file (default: stdout)
-s, --schema <NAME>             Filter by schema (default: "public")
    --include-all-schemas       Include all schemas
    --enable-foreign-keys       Emit PRAGMA and FK constraints
    --strict                    Fail on lossy conversions
    --emit-warnings <PATH>      Warning destination (file path or "stderr")
-h, --help                      Print help
-V, --version                   Print version

Conversion Examples

Basic table

-- Input (PostgreSQL)
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email VARCHAR(255) UNIQUE,
    active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT now()
);

-- Output (SQLite)
CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE,
  active INTEGER DEFAULT 1,
  created_at TEXT DEFAULT (CURRENT_TIMESTAMP)
);

SERIAL types

-- Input (PostgreSQL)
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    total NUMERIC(10, 2) NOT NULL
);

-- Output (SQLite)
CREATE TABLE orders (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  total NUMERIC NOT NULL
);

Foreign keys

-- Input (PostgreSQL)
CREATE TABLE posts (
    id INTEGER PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE
);

-- Output (SQLite) with --enable-foreign-keys
PRAGMA foreign_keys = ON;

CREATE TABLE users (
  ...
);

CREATE TABLE posts (
  id INTEGER PRIMARY KEY,
  user_id INTEGER REFERENCES users(id) ON DELETE CASCADE
);

Tables are topologically sorted so that referenced tables appear before referencing tables.

Type Mapping

Default Expression Mapping

Warning Codes

pg2sqlite emits warnings when conversion is lossy:

Architecture

pg2sqlite-rs/
├── core/                 # Library crate (pg2sqlite-core)
│   └── src/
│       ├── lib.rs        # Public API: convert_pg_ddl_to_sqlite()
│       ├── pg/           # PostgreSQL DDL parsing
│       ├── ir/           # Internal representation
│       ├── transform/    # PG IR → SQLite IR transformation
│       ├── sqlite/       # SQLite DDL rendering
│       └── diagnostics/  # Warning/error system
├── cli/                  # Binary crate (pg2sqlite)
│   └── src/main.rs       # CLI entry point
├── tests/                # Integration & golden tests
│   ├── fixtures/         # PostgreSQL DDL input files
│   └── golden/           # Expected SQLite DDL output files
└── docs/                 # Specifications

Pipeline

PostgreSQL DDL
  → Parse (sqlparser)
  → Normalize (schema filter, identifiers)
  → Plan (merge ALTERs, resolve SERIAL/sequences)
  → Transform (types, expressions, constraints, indexes)
  → Order (topological sort for FK dependencies)
  → Render (SQLite DDL text)
  → Report (warnings)

Library Usage

pg2sqlite-core can be used as a library:

use pg2sqlite_core::{convert_pg_ddl_to_sqlite, ConvertOptions};

let pg_ddl = r#"
    CREATE TABLE users (
        id SERIAL PRIMARY KEY,
        name TEXT NOT NULL
    );
"#;

let opts = ConvertOptions::default();
let result = convert_pg_ddl_to_sqlite(pg_ddl, &opts).unwrap();

println!("{}", result.sqlite_sql);
for w in &result.warnings {
    eprintln!("warning: {}", w);
}

Development

Commands

make ci              # Run all CI checks (fmt-check + lint + test)
make fmt             # Format code
make lint            # Run clippy
make test            # Run all tests
make test-verbose    # Tests with output
make test-one TEST=name  # Run specific test
make watch           # Watch and test on change

Adding Golden Tests

1. Add a PostgreSQL DDL file to tests/fixtures/
2. Add the expected SQLite output to tests/golden/
3. Run make test to verify

Verifying Output

You can verify the generated SQLite DDL by pasting it into SQLite Online and confirming it executes without errors.

Exit Codes

• 0: Success
• 1: Conversion error or strict mode violation

Contributing

Contributions are welcome! Please feel free to submit issues or pull requests.

License

MIT

Acknowledgments

• sqlparser-rs — PostgreSQL SQL parser