Home

Awesome

SQLite Foreign Data Wrapper for PostgreSQL

This is a foreign data wrapper (FDW) to connect PostgreSQL to SQLite database file. This FDW works with PostgreSQL 13, 14, 15, 16, 17 and confirmed with SQLite 3.46.0.

<img src="https://upload.wikimedia.org/wikipedia/commons/2/29/Postgresql_elephant.svg" align="center" height="100" alt="PostgreSQL"/> + <img src="https://upload.wikimedia.org/wikipedia/commons/3/38/SQLite370.svg" align="center" height="100" alt="SQLite"/>

Contents

  1. Features
  2. Supported platforms
  3. Installation
  4. Usage
  5. Functions
  6. Identifier case handling
  7. Generated columns
  8. Character set handling
  9. Examples
  10. Limitations
  11. Tests
  12. Contributing
  13. Useful links

Features

Common features

Pushdowning

Notes about pushdowning

Notes about features

Also see Limitations

Supported platforms

sqlite_fdw was developed on Linux and should run on any reasonably POSIX-compliant system.

Installation

Package installation

For some Linux distributives internal packages with sqlite_fdw are avalilable.

Source installation

Prerequisites:

1. Install SQLite & Postgres Development Libraries

For Debian or Ubuntu: apt-get install libsqlite3-dev apt-get install postgresql-server-dev-XX, where XX matches your postgres version, i.e. apt-get install postgresql-server-dev-15

You can also download SQLite source code and build SQLite with FTS5 for full-text search.

2. Build and install sqlite_fdw

Add a directory of pg_config to PATH and build and install sqlite_fdw.

make USE_PGXS=1
make install USE_PGXS=1

If you want to build sqlite_fdw in a source tree of PostgreSQL, use

make
make install

Usage

CREATE SERVER options

sqlite_fdw accepts the following options via the CREATE SERVER command:

CREATE USER MAPPING options

There is no user or password conceptions in SQLite, hence sqlite_fdw no need any CREATE USER MAPPING command. About access model and possible data modifications problems see about connection to SQLite database file and access control.

CREATE FOREIGN TABLE options

sqlite_fdw accepts the following table-level options via the CREATE FOREIGN TABLE command:

sqlite_fdw accepts the following column-level options via the CREATE FOREIGN TABLE command:

Datatypes

This table represents sqlite_fdw behaviour if in PostgreSQL foreign table column some affinity of SQLite data is detected. Some details about data values support see in limitations.

SQLite NULL affinity always can be transparent converted for a nullable column in PostgreSQL.

SQLite data processing dependend on affinity

PostgreSQLINTREALBLOBTEXTTEXT but <br>emptynearest<br>affinity
boolV∅Ti∅INT
bit(n)V<br>(n<=64)∅∅∅∅INT
bytea∅∅✔V?BLOB
char(n)??T✔VTEXT
dateVVTV+NULL?
float4V+✔∅iNULLREAL
float8V+✔∅iNULLREAL
int2✔-?∅∅NULLINT
int4✔-?∅∅NULLINT
int8✔?∅∅NULLINT
json??TV+?TEXT
macaddr✔-∅V<br>(Len=6b)V+?INT
macaddr8✔∅V<br>(Len=8b)V+?INT
name??TiNULLTEXT
numericVVTiNULLREAL
text??T✔VTEXT
timeVVTV+NULL?
timestampVVTV+NULL?
timestamp + tzVVTV+NULL?
uuid∅∅V+<br>(Len=16b)V+∅TEXT, BLOB
varchar(n)??T✔VTEXT
varbit(n)V<br>(n<=64)∅∅∅∅INT

IMPORT FOREIGN SCHEMA options

sqlite_fdw supports IMPORT FOREIGN SCHEMA (PostgreSQL 9.5+) and accepts following options via the IMPORT FOREIGN SCHEMA command:

Datatype translation rules for IMPORT FOREIGN SCHEMA

SQLitePostgreSQL
intbigint
chartext
clobtext
texttext
blobbytea
realdouble precision
floadouble precision
doubdouble precision
datetimetimestamp
timetime
datedate
uuiduuid
macaddrmacaddr
macaddr8macaddr8

TRUNCATE support

sqlite_fdw implements the foreign data wrapper TRUNCATE API, available from PostgreSQL 14.

As SQLite does not provide a TRUNCATE command, it is simulated with a simple unqualified DELETE operation.

Actually, TRUNCATE ... CASCADE can be simulated if we create child table of SQLite with foreign keys and ON DELETE CASCADE, and then executing TRUNCATE (which will be deparsed to DELETE).

Following restrictions apply:

Connection to SQLite database file and access control

In OS sqlite_fdw works as executed code with permissions of user of PostgreSQL server. Usually it is postgres OS user.

Data read access

For succesfully connection to SQLite database file you must have at least existed and correct SQLite file readable for OS user of PostgreSQL server process. This means all directories by path to the file must be also readable (listable) for OS user of PostgreSQL server process. There are no other conditions for PostreSQL database superuser to read all of SQLite data if there are also sqlite_fdw extension in the database and FOREIGN SERVER for SQLite database file.

Data change access

Data modification access in sqlite_fdw drived by both operating system and PostgreSQL.

OS restrictions can disallow any SQLite data modifications. Hence any PostgreSQL FOREIGN SERVER or FOREIGN TABLE options or GRANTs can be absolutely not effective. In this case SQLite data modification operations allowed by PostgreSQL can cause error message from SQLite like attempt to write a readonly database with result code 8.

Full list of OS-leveled conditions of data modification access to SQLite database file

Full list of PostgreSQL-leveled conditions of data modification access to SQLite database file

Generally for sqlite_fdw access management FOREIGN SERVER owner can be like remote access manager for other FDWs.

Remote access manager can block any data modififcations in remote database for remote user of a FDW. In this case SQLite have no user or separate access conceptions, hence FOREIGN SERVER owner combines remote access manager role with internal PostgreSQL roles such as FOREIGN SERVER access management.

Functions

As well as the standard sqlite_fdw_handler() and sqlite_fdw_validator() functions, sqlite_fdw provides the following user-callable utility functions:

sqlite_fdw_version
--------------------
              20500

Identifier case handling

PostgreSQL folds identifiers to lower case by default, SQLite is case insensitive by default only for uppercase and lowercase ASCII base latin letters. It's important to be aware of potential issues with table and column names.

Following SQL isn't correct for SQLite: Error: duplicate column name: a, but is correct for PostgreSQL

	CREATE TABLE T (
	  "A" INTEGER,
	  "a" NUMERIC
	);

Following SQLs is correct for both SQLite and PostgreSQL because there is no column with names composed from ASCII base latin letters only.

	CREATE TABLE T_кир (
	  "А" INTEGER,
	  "а" NUMERIC
	);
	CREATE TABLE T_ελλ (
	  "Α" INTEGER,
	  "α" NUMERIC
	);
	CREATE TABLE T_dia (
	  "Ä" INTEGER,
	  "ä" NUMERIC
	);

For SQLite there is no difference between

	SELECT * FROM t;   -- №1
	SELECT * FROM T;   -- №2
	SELECT * FROM "t"; -- №3
	SELECT * FROM "T"; -- №4

For PostgreSQL the query with comment №4 is independend query to table T, not to table t as other queries. Please note this table name composed from ASCII base latin letters only. This is not applicable for other alphabet systems or mixed names. This is because toLower operation in PostgreSQL is Unicode operation but ASCII only operation in SQLite, hence other characters will not be changed.

	SELECT * FROM т;   -- №5
	SELECT * FROM Т;   -- №6
	SELECT * FROM "т"; -- №7
	SELECT * FROM "Т"; -- №8

In this case for PostgreSQL the query with comment №8 is independend query to table Т, not to table т as other queries. But for SQLite the queries with comments №6 and №8 belongs to table Т, and the queries with comments №5 and №7 belongs to table т.

If there is

	CREATE TABLE T (
	  A INTEGER,
	  b REAL
	);

in SQLite, both a and A , b and B columns will have the same real datasource in SQLite in follow foreign table:

	CREATE FOREIGN TABLE "SQLite test" (
	  "A" int4 NULL,
	  "B" float8 NULL,
	  "a" int8 NULL,
	  "b" numeric NULL
	)
	SERVER sqlite_server
	OPTIONS (table 'T');

Generated columns

SQLite provides support for generated columns. Behaviour of sqlite_fdw with this columns isn't yet described.

Note that while sqlite_fdw will INSERT or UPDATE the generated column value in SQLite, there is nothing to stop the value being modified within SQLite, and hence no guarantee that in subsequent SELECT operations the column will still contain the expected generated value. This limitation also applies to postgres_fdw.

For more details on generated columns see:

Character set handling

There is no character set metadata stored in SQLite, only PRAGMA encoding; with UTF-only values (UTF-8, UTF-16, UTF-16le, UTF-16be). SQLite text output function guarantees UTF-8 encoding.

When sqlite_fdw connects to a SQLite, all strings are interpreted acording the PostgreSQL database's server encoding. It's not a problem if your PostgreSQL database encoding belongs to Unicode family. Otherewise interpretation transformation problems can occur. Some unproper for PostgreSQL database encoding characters will be replaced to default 'no such character' character or there will error like character with byte sequence 0x** in encoding "UTF8" has no equivalent in encoding "**".

Character case functions such as upper, lower and other are not pushed down because they does not work with Unicode character in SQLite.

Sqlite_fdw tested with PostgreSQL database encodings EUC_JP, EUC_KR, ISO_8859_5, ISO_8859_6, ISO_8859_7, ISO_8859_8, LATIN1, LATIN2, LATIN3, LATIN4, LATIN5, LATIN6, LATIN7, LATIN8, LATIN9, LATIN9, LATIN10, WIN1250, WIN1251, WIN1252, WIN1253, WIN1254, WIN1255, WIN1256, WIN1257 and it's synomyms. Some other encodings also can be supported, but not tested.

Examples

Install the extension:

Once for a database you need, as PostgreSQL superuser.

	CREATE EXTENSION sqlite_fdw;

Create a foreign server with appropriate configuration:

Once for a foreign datasource you need, as PostgreSQL superuser. Please specify SQLite database path using database option.

	CREATE SERVER sqlite_server
	FOREIGN DATA WRAPPER sqlite_fdw
	OPTIONS (
          database '/path/to/database'
	);

Grant usage on foreign server to normal user in PostgreSQL:

Once for a normal user (non-superuser) in PostgreSQL, as PostgreSQL superuser. It is a good idea to use a superuser only where really necessary, so let's allow a normal user to use the foreign server (this is not required for the example to work, but it's secirity recomedation).

	GRANT USAGE ON FOREIGN SERVER sqlite_server TO pguser;

Where pguser is a sample user for works with foreign server (and foreign tables).

User mapping

There is no user or password conceptions in SQLite, hence sqlite_fdw no need any CREATE USER MAPPING command. About access problems see in CREATE USER MAPPING options.

Create foreign table

All CREATE FOREIGN TABLE SQL commands can be executed as a normal PostgreSQL user if there were correct GRANT USAGE ON FOREIGN SERVER. No need PostgreSQL supersuer for secirity reasons but also works with PostgreSQL supersuer.

Please specify table option if SQLite table name is different from foreign table name.

	CREATE FOREIGN TABLE t1 (
	  a integer,
	  b text
	)
	SERVER sqlite_server
	OPTIONS (
	  table 't1_sqlite'
	);

If you want to update tables, please add OPTIONS (key 'true') to a primary key or unique key like the following:

	CREATE FOREIGN TABLE t1(
	  a integer OPTIONS (key 'true'),
	  b text
	)
	SERVER sqlite_server
	OPTIONS (
	  table 't1_sqlite'
	);

If you need to convert INT SQLite column (epoch Unix Time) to be treated/visualized as TIMESTAMP in PostgreSQL, please add OPTIONS (column_type 'INT') when defining FOREIGN table at PostgreSQL like the following:

	CREATE FOREIGN TABLE t1(
	  a integer,
	  b text,
	  c timestamp without time zone OPTIONS (column_type 'INT')
	)
	SERVER sqlite_server
	OPTIONS (
	  table 't1_sqlite'
	);

As above, but with aliased column names:

	CREATE FOREIGN TABLE t1(
	  a integer,
	  b text OPTIONS (column_name 'test_id'),
	  c timestamp without time zone OPTIONS (column_type 'INT', column_name 'unixtime')
	)
	SERVER sqlite_server
	OPTIONS (
	  table 't1_sqlite'
	);

Import a SQLite database as schema to PostgreSQL:

	IMPORT FOREIGN SCHEMA someschema
	FROM SERVER sqlite_server
	INTO public;

Note: someschema has no particular meaning and can be set to an arbitrary value.

Access foreign table

For the table from previous examples

	SELECT * FROM t1;

Limitations

SQL commands

Mixed affinity support

SQLite text affinity values which is different for SQLite unique checks can be equal for PostgreSQL because sqlite_fdw unifyes semantics of values, not storage form. For example 1(integer), Y(text) and tRuE(text) SQLite values is different in SQLite but equal in PostgreSQL as true values of boolean column. This is also applicable for a data with text affinity in uuid, timestamp, double precision, float and numeric columns of foreign tables. Please be carefully if you want to use mixed affinity column as PostgreSQL foreign table primary key.

Arrays

Numbers (range and precision)

Boolean values

UUID values

bit and varbit support

MAC address support

Tests

Test directory have structure as following:

+---sql
|   +---13.15
|   |       filename1.sql
|   |       filename2.sql
|   |
|   +---14.12
|   |       filename1.sql
|   |       filename2.sql
|   |
.................
|   \---17.0
|          filename1.sql
|          filename2.sql
|
\---expected
|   +---13.15
|   |       filename1.out
|   |       filename2.out
|   |
|   +---14.12
|   |       filename1.out
|   |       filename2.out
|   |
.................
|   \---17.0
            filename1.out
            filename2.out

The test cases for each version are based on the test of corresponding version of PostgreSQL. You can execute test by test.sh directly. The version of PostgreSQL is detected automatically by $(VERSION) variable in Makefile. The corresponding sql and expected directory will be used to compare the result. For example, for Postgres 15.0, you can execute "test.sh" directly, and the sql/15.0 and expected/15.0 will be used to compare automatically.

Test data directory is /tmp/sqlite_fdw_test. If you have /tmp mounted as tmpfs the tests will be up to 800% faster.

Contributing

Opening issues and pull requests on GitHub are welcome. For pull request, please make sure these items below for testing:

Preferred code style see in PostgreSQL source codes. For example

type
funct_name (type arg ...)
{
	t1 var1 = value1;
	t2 var2 = value2;

	for (;;)
	{
	}
	if ()
	{
	}
}

Useful links

Source

General FDW Documentation

Other FDWs

License

Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies.

See the License file for full details.