Skip to content

Postgres 2 SQLite database link

SQLite FDW

Postgres foreign data wrapper for SQLite database

Introduction

This is part 3 of #fdw foreign data wrappers series of posts. Initial part 1 was about connectivity to MySQL database. Next part 2 was related to accessing Oracle database. In this post we will access SQLite database from within Postgres.

sqlite_fdw

There is no binaries available on official GitHub repository for this extension. You have to manually compile and install it. Procedure is quite simple. Also it requires only single sqlite3 libs package to be installed as a runtime dependency.

When done with installation you will be able to setup it already within postgres database. Process is standard: execute CREATE EXTENSION command from within your postgres database. Aftewards create foreign server via CREATE SERVER command which specifies path to the target sqlite database file.

Docker image

Similarly to MySQL and Oracle I've created a postgres image with sqlite_fdw extension installed. It's available on docker hub. Because of single library runtime dependency it is only +1 Mb in size comparing to the smallest official postgres alpine image. Section below describes how to use the image.

Demo

SQLite is a serverless database which consists of a single file. For working example we need to spin up container from the mentioned above image and mount sqlite database file to it as a mount target. Thus we will get local sqlite database with write access granted to a postgres container with sqlite_fdw extension installed.

Inside sqlite database we will create a single table and populate it with 3 rows. From postgres instance we will select, insert and delete rows from this table. After that we will select from this table again via sqlite client locally to confirm that the data has been changed accordingly.

Note: If you use docker then just replace podman with docker in all commands below.

Let's create sqlite database test.db. 

$ sqlite3 test.db
SQLite version 3.32.3 2020-06-18 14:00:33
Enter ".help" for usage hints.
Within the database create table t and populate it with 3 rows. 
sqlite> create table t(id int primary key);
sqlite> insert into t values (1), (2), (3);
sqlite> .headers on
sqlite> select * from t;
id
1
2
3

Create postgres database with sqlite_fdw extension available from our chumaky/postgres_sqlite_fdw image. 

$ podman run -d --name sqlite_test -u $(id -u):$(id -g) \
             -v $(pwd)/test.db:/tmp/test.db -p 5432:5432 \
             -e POSTGRES_PASSWORD=postgres chumaky/postgres_sqlite_fdw

$ podman ps
CONTAINER ID  IMAGE                                         COMMAND   CREATED        STATUS            PORTS                   NAMES
57a3d931b446  docker.io/chumaky/postgres_sqlite_fdw:latest  postgres  3 seconds ago  Up 3 seconds ago  0.0.0.0:5432->5432/tcp  sqlite_test
By default container is triggered as a root user. Container will update local sqlite database file test.db. We want to make sure that it will update it with privileges of a current user. For this purpose we specify to start container as a current user -u $(id -u):$(id -g). We also mount local test.db file as /tmp/test.db file within the container. Finally container is named as sqlite_test.

In the same time within container postgres itself is owned by separately created postgres account. In order to allow postgres account from within container to change externally created file in a host system we grant write access to others via o+w key. 

$ chmod o+w test.db
$ ls -l test.db
-rw-r--rw-. 1 toleg toleg 12288 жов 22 22:15 test.db

Start within our container named sqlite_test interactive psql shell connected to default postgres database as default postgres superuser account. 

$ podman exec -it sqlite_test psql postgres postgres
psql (13.0)
Type "help" for help.

postgres=#

Create foreign server to access sqlite database. Also create t foreign table object which will be pointed to the same named table in the sqlite database. We specify options (key 'true') clause to tell sqlite_fdw extension that foreign table has PK/UK constraint defined. This enables delete and update operations on a table. 

=> create schema sqlite_fdw;
CREATE SCHEMA
=> create extension sqlite_fdw schema sqlite_fdw;
CREATE EXTENSION
=> create server sqlite foreign data wrapper sqlite_fdw options (database '/tmp/test.db');
CREATE SERVER
=> create foreign table t(id int options (key 'true')) server sqlite;
CREATE FOREIGN TABLE

Select data from the foreign table. 

=> select * from t;
 id
----
  1
  2
  3
(3 rows)

DML operations also work 

=> insert into t values (4);
INSERT 0 1
=> delete from t where id = 1;
DELETE 1

=> select * from t;
 id
----
  2
  3
  4
(3 rows)

Check that local database file on a host system has changed accordingly. 

sqlite> select * from t;
id
2
3
4

For quicker setup of the mentioned layout there is a postgres_sqlite_compose.sh script available.

Summary

sqlite_fdw extension allows SELECT and DML operations to be triggered from postgres and applied to sqlite database. For quickier setup there is a chumaky/postgres_sqlite_fdw docker image is available on docker hub. It contains sqlite_fdw extension pre-installed as well as sqlite3 client utility.

Comments