postlite: A command to use SQLite3 as PostgreSQL

Introduction

Have you ever wondered if there was something as easy to handle as SQLite3 but accessible over a network like PostgreSQL? I bet you have. Well, here it is.

postlite

https://github.com/benbjohnson/postlite

postlite is the tool that meets this need. With postlite, you can treat database files created with SQLite3 as if they were PostgreSQL.

The mechanism is quite simple: it uses a wrapper for the PostgreSQL communication protocol on top of go-sqlite3 (which I develop) and simulates the PostgreSQL schema using virtual tables.

Installation

postlite uses go-sqlite3's vtable. Therefore, you must follow the procedure described in postlite's README.md instead of a standard go install.

go install -tags vtable ./cmd/postlite

Let's Try It Out

First, let's create a suitable SQLite3 database file.

input.sql

BEGIN TRANSACTION;
CREATE TABLE foo (id integer not null primary key, name text);
INSERT INTO foo VALUES(1,'foo');
INSERT INTO foo VALUES(2,'bar');
INSERT INTO foo VALUES(3,'baz'); COMMIT; 

Write some appropriate DDL and create the database file.

$ sqlite3 foo.db < input.sql

Start postlite by specifying the directory where the database file is located.

$ postlite --data-dir ./data

Next, connect using the psql command.

$ psql -h localhost foo.db
psql (14.0, server 13.0.0)
Type "help" for help.

foo.db=> select * from foo;
 id | name
----+------
 1  | foo
 2  | bar
 3  | baz
(3 rows)

It's that easy.

Summary

I have introduced postlite, a command that turns SQLite3 into PostgreSQL. I haven't used it extensively, so I'm not sure about its full capabilities, but if it works properly, it seems like it could be useful for things like testing.