单车变摩托 | PG + SQLite = 嵌入式PG

PG + SQLite = 嵌入式PG ?

SQLite是嵌入式数据库, 支持内存模式, 也支持磁盘模式.

如果把SQLite变成(非嵌入式)数据库服务会怎么样?

并且给它加上PostgreSQL的SQL解析器.

PGSQLite这个开源项目就能满足以上需求, 它实际上做了一层转发和语法转换层.

PGSQLite启动后是一个数据库服务, 后端实际上是SQLite, 用户与PGSQLite通过PG的协议和语法进行交互.

试一试PGSQLite

1、安装部署PGSQLite

依赖rust

curl --proto '=https' --tlsv1.2 -sSf https://sh.rustup.rs | sh  

. "$HOME/.cargo/env"

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

2、使用PGSQLite

cd pgsqlite  

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

# Or start with an in-memory database for testing  
./target/release/pgsqlite --in-memory  

3、连接PGSQLite

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

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

4、使用PG语法

-- 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 ('[email protected]');  

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

需要注意的是, PG的函数、语法、类型比SQLite更丰富, 如果用到了SQLite不支持的函数、类型, 会报错.

postgres=> insert into tbl select generate_series(1,100000), md5(random()::text), clock_timestamp();  
ERROR:  Query execution failed: SQLite error: near "AS": syntax error in insert into tbl select generate_series(1,100000), md5(random(CAST( AS TEXT))), clock_timestamp(); at offset 67  

其他用法

# 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  



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

PGSQLite确实非常轻量, 比安装部署PG使用简单多了. 但是似乎不太可能用在生产? 至少我还没有想到吧.

你有什么想法?

什么场景中会用到PGSQLite呢?

作者提到的愿景倒是挺好的: 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.

但是, 前提是绝大部分PG的常用功能、类型、语法都要覆盖, 那才会有意义.