pgsqlite

🐘+🪶=<span style="color: red; font-size: 1.5em">♥</span>

PostgreSQL protocol for SQLite databases. Turn any SQLite database into a PostgreSQL server that your existing tools and applications can connect to.

⚠️ WARNING: Experimental Project This is an experimental project and is not yet ready for production use. It is under active development and may contain bugs, incomplete features, or breaking changes.

Why pgsqlite?

pgsqlite lets you use PostgreSQL tools and libraries with SQLite databases. This is perfect for:

🚀 Rapid Testing & CI/CD: Run integration tests without spinning up PostgreSQL. Just copy your SQLite file and go.
🌿 Feature Branch Deployments: Each branch gets its own database. Just copy the SQLite file - no complex database provisioning.
🤖 AI Agent Development: Multiple agents can work on isolated sandbox environments with zero setup. Clone the database instantly.
💻 Local Development: Use your favorite PostgreSQL tools (psql, pgAdmin, DataGrip) with lightweight SQLite storage.
🔧 Migration Path: Prototype with SQLite, seamlessly move to PostgreSQL later without changing application code.

Quick Start

Installation

Option 1: Download Pre-built Binaries (Recommended)

Visit the GitHub Releases page to download the latest pre-built binary for your platform:

# Example for Linux x64:
wget https://github.com/erans/pgsqlite/releases/latest/download/pgsqlite-linux-x64.tar.gz
tar -xzf pgsqlite-linux-x64.tar.gz
chmod +x pgsqlite
./pgsqlite

Option 2: Build from Source

# Clone and build from source
git clone https://github.com/erans/pgsqlite
cd pgsqlite
cargo build --release
./target/release/pgsqlite

Basic Usage

Start pgsqlite with a SQLite database:

# Use an existing SQLite database
pgsqlite --database ./my-database.db

# Or start with an in-memory database for testing
pgsqlite --in-memory

Connect with any PostgreSQL client:

# Using psql
psql -h localhost -p 5432 -d my-database

# Using connection string
psql "postgresql://localhost:5432/my-database"

Use it just like PostgreSQL:

-- Create tables with PostgreSQL syntax
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Insert data
INSERT INTO users (email) VALUES ('user@example.com');

-- Query with PostgreSQL functions
SELECT * FROM users WHERE created_at > NOW() - INTERVAL '7 days';

Usage Examples

For Testing Environments

# Copy your template database for each test run
cp template.db test-1.db
pgsqlite --database test-1.db --port 5433 &

# Run your tests against it
npm test -- --database-url postgresql://localhost:5433/test-1

# Cleanup is just removing the file
rm test-1.db

For Feature Branch Deployments

# Each branch gets its own database copy
cp main.db feature-branch-123.db
pgsqlite --database feature-branch-123.db --port 5433

Connect from Your Application

Python (psycopg2):

import psycopg2
conn = psycopg2.connect(
    host="localhost",
    port=5432,
    database="myapp"
)

Node.js (pg):

const { Client } = require('pg')
const client = new Client({
  host: 'localhost',
  port: 5432,
  database: 'myapp'
})

Any PostgreSQL-compatible ORM: Works with SQLAlchemy, Django ORM, ActiveRecord, Prisma, etc.

Configuration

Essential Options

# Basic options
pgsqlite \
  --database <path>     # SQLite database file (default: sqlite.db)
  --port <port>         # PostgreSQL port (default: 5432)
  --in-memory           # Use in-memory database

# Security
pgsqlite \
  --ssl                 # Enable SSL/TLS encryption
  --ssl-cert <path>     # Custom SSL certificate
  --ssl-key <path>      # Custom SSL key

# Performance
pgsqlite \
  --journal-mode WAL    # Enable WAL mode for better concurrency

# Connection Pooling (for concurrent workloads)
PGSQLITE_USE_POOLING=true pgsqlite \
  --database <path>     # Enable read/write connection separation

For all configuration options, see the Configuration Reference.

Features

PostgreSQL Compatibility

✅ Wire Protocol: Full PostgreSQL v3 protocol implementation
✅ Clients: Works with psql, pgAdmin, DBeaver, and all PostgreSQL drivers
✅ SQL Syntax: Most PostgreSQL queries work without modification
✅ Data Types: 40+ PostgreSQL types including SERIAL, JSON, UUID, arrays (stored as JSON)
✅ Transactions: Full ACID compliance via SQLite

Notable Features

Connection Pooling: Optional read/write connection separation for improved concurrent performance (enabled via PGSQLITE_USE_POOLING=true)
Query Optimization System: Advanced optimization infrastructure with context merging, lazy schema loading, pattern recognition, and integrated optimization management
PostgreSQL Functions: Comprehensive function support including:
- String Functions: split_part(), string_agg(), translate(), ascii(), chr(), repeat(), reverse(), left(), right(), lpad(), rpad()
- Math Functions: trunc(), round(), ceil(), floor(), sign(), abs(), mod(), power(), sqrt(), exp(), ln(), log(), trigonometric functions, random()
Array Types: Full support for PostgreSQL arrays (e.g., INTEGER[], TEXT[][]) with ARRAY literal syntax, ALL operator, and unnest() WITH ORDINALITY
JSON Support: Complete JSON and JSONB implementation with operators (->, ->>, @>, <@, #>, #>>, ?, ?|, ?&) and functions (json_agg, json_object_agg, row_to_json, json_populate_record, json_to_record, jsonb_insert, jsonb_delete, jsonb_pretty, etc.)
Full-Text Search: Complete PostgreSQL FTS implementation with tsvector/tsquery types, @@ operator, to_tsvector(), to_tsquery(), plainto_tsquery() functions using SQLite FTS5 backend
ENUM Types: CREATE TYPE status AS ENUM ('active', 'pending', 'archived')
RETURNING Clauses: INSERT INTO users (email) VALUES ('test@example.com') RETURNING id
CTEs: WITH and WITH RECURSIVE queries
Generated Columns: SERIAL and BIGSERIAL auto-increment columns
VARCHAR/CHAR Constraints: Length validation for VARCHAR(n) and CHAR(n) with proper padding
NUMERIC/DECIMAL Constraints: Precision and scale validation for NUMERIC(p,s) and DECIMAL(p,s)
CREATE INDEX with Operator Classes: Support for PostgreSQL operator classes like varchar_pattern_ops, text_pattern_ops (mapped to SQLite COLLATE BINARY for pattern matching optimization)
psql Compatibility: Enhanced psql support with \d, \dt, and \d tablename commands fully working

Security Features

Advanced SQL Injection Protection: AST-based SQL parsing with fallback pattern detection
- Detects tautology attacks (e.g., 1=1, 'a'='a')
- Prevents dangerous function execution (exec, xp_cmdshell)
- Blocks suspicious UNION operations with sensitive tables
- Limits multi-statement execution
Security Audit Logging: Comprehensive audit trail with configurable severity filtering
Rate Limiting: Built-in DoS protection with circuit breaker pattern
Input Validation: Protocol-level validation for all client inputs
Memory Safety: Rust's ownership system prevents buffer overflows and memory corruption

Limitations

❌ Stored procedures and custom functions
❌ PostgreSQL-specific system functions (pg_*)
❌ Some advanced data types (ranges, geometric types)
⚠️ Some advanced array features (array assignment operations, advanced indexing)
❌ Multiple concurrent writers (SQLite allows only one writer at a time, mitigated by connection pooling for reads)

For detailed compatibility information, see Type Mapping Documentation.

Performance Considerations

pgsqlite acts as a translation layer between PostgreSQL protocol and SQLite, providing full PostgreSQL compatibility with measurable overhead:

Real-World Performance (2025-09-20)

Driver Performance Comparison (100 operations each): | Driver | SELECT | INSERT | UPDATE | DELETE | Best For | |--------|--------|--------|--------|--------|----------| | psycopg3-binary | 0.452ms | 0.976ms | 0.219ms | 0.176ms | Read-heavy workloads | | psycopg3-text | 0.925ms | 1.067ms | 0.304ms | 0.271ms | Balanced usage | | psycopg2 | 2.939ms | 0.214ms | 0.089ms | 0.063ms | Write-heavy workloads |

Overhead vs Pure SQLite (200 operations):

Pure SQLite: 44.4ms (0.22ms per operation) - Maximum speed
pgsqlite: ~16 seconds (~80ms per operation) - ~360x overhead
Trade-off: Raw performance vs full PostgreSQL compatibility + ORM support

When pgsqlite is the Right Choice

Web applications: 80ms database operations feel instant to users
ORM integration: Django, SQLAlchemy, Rails, Ecto work seamlessly
Development/testing: Full PostgreSQL feature compatibility
API endpoints: Database time typically 10-20% of total request time

Performance Optimizations

Protocol choice: Binary mode (psycopg3-binary) is 3.1% faster than text mode
Batch operations: Multi-row INSERT provides dramatic improvements:
- 10-row batches: ~11x faster than single-row INSERTs
- 100-row batches: ~51x faster
- 1000-row batches: ~76x faster
Connection architecture: Connection-per-session provides excellent isolation
Ultra-fast path: Optimized execution for simple SELECT queries

For applications requiring microsecond-level performance, use pure SQLite. For PostgreSQL compatibility with acceptable overhead, pgsqlite is ideal.

Connection Pooling

For concurrent read-heavy workloads, enable connection pooling to improve performance:

Pgsqlite

Install / Use

README