How to port PostgreSQL db to SQLite db

Convert postrgresql to sqlite database

SQLite and PostgreSQL. If you want to convert PostgreSQL db from, for example, your server to SQLite then you will need to follow some rules.

First of all you need to make a backup file of your current PostgreSQL database.

Via CLI it will look like

pg_dump --clean --create --inserts postgresDbName > backupName.sql

Or via TablePlus for GUI lovers

About keys and even more — here

Now you can open backupName.sql in(for example) Notepad++ and we need to change it because of difference between postgresql and sqlite(briefly — they have different structures, different commands and different data types :-)))

You will see something like this

CREATE TABLE…;
CREATE ROLE …;
ALTER ROLE …’;
SELECT…;
SET…;
REVOKE ALL ON DATABASE tem1 FROM postgresDbName;
GRANT…;
INSERT INTO…;

Remove all lines with mentions about OWNER, SCHEMA, CREATE VIEW, FUNCTIONS etc. SQLite does not understand it.

You need to remove all these strings and remain only commands for tables creating CREATE TABLE and INSERT INTO … VALUE …

Now add as a first line of this file BEGIN; and as a last one — END; If your TablePlus will be unhappy during transactions — additionally add command END TRANSACTION; before BEGIN;

Now your structure is(double minus means comment line in SQL)

— END TRANSACTION;
BEGIN;
CREATE TABLE…;
DELETE TABLE..; — clean db else it will accumulate and duplicate your data
INSERT INTO table_name VALUES (par1, par2…); — here you fill up your rows
END;

One more trick — PostgreSQL and SQLite store ‘true’ and ’false’ in different ways. And now we need to replace all mentions of (‘true’/‘false’) with (‘t’/‘f’). But don’t touch ‘true’ and ‘false’ inside command CREATE TABLE. Also check that you did not replace all mentions ‘true’ and ‘false’ inside jsons if you pass json as a TEXT in some column(as parameter for INSERT INTO command).

It was:

INSERT INTO table_name VALUES (1, ‘any string’, true, false, {id: ‘true’});

It became:

INSERT INTO table_name VALUES (1, ‘any string’, ‘t’, ‘f’, {id: ‘true’});

And a last one(huray!) — check all data types inside commands CREATE TABLE. Very likely there you’ll find something like BIGINT, SERIAL or even MONEY datatypes from PostgreSQL inheritance. Replace them with SQLite datatypes:

TEXT
NUMERIC
INTEGER
REAL
BLOB

If you are not sure about your data consistency — check all fields for symbol ‘\’ (back slash). For example, if you have product name column and it contains backslash data then conversion will be interrupted on it.

If you don’t have sqlite on your computer — install it and add paths to environment variables

That’s all regarding preparation. Now you just need to create db file and fill it up via your backupName.sql file:

sqlite3 targetdb.db
sqlite> .read backupName.sql

Or via TablePlus:

That’s it! Cheers

Just now you replicated your postgresql database to sqlite database. If something goes wrong — leave a comment