pgsqlite - Load SQLite databases into PostgresSQL
Written by Nikos Vaggalis   
Monday, 17 October 2022

pgsqlite is a pure python module and command line tool that makes it simple to import a SQLite database into Postgres, saving a ton of time and hassle in the process.

sqlite

SQLite is amazing. In "In Praise Of SQLite" I describe why that is so. One of those reasons was extensibility:

in a sense extensibility is SQLite's biggest power; it adds unforeseen utility and value for its users, and we can witness many imaginative uses for it, provided by the community.

I then gave a list of such extensions.

But there's the other way too; not extending SQLite itself but extending third party products by means of SQLite. One such plugin is pgsqlite by Adam Fletcher of bit.io which allows for importing SQLite databases into Postgres seamlessly.

On paper such a migration might seem like a simple task, after all it's just structured data out and structured data in. It wasn't so in reality. Adam Fletcher found many obstacles in the way :

 

  • Typing Differences
    First, SQLite and Postgres use different types.
  • Identifier Quoting
    Mismatches in some cases between how PostgresSQL and Sqlite quote their objects.
  • Forgiving SQL Parsing
    SQLite is more lax with syntax errors in some SQL statements, while Postgres is not.

 

As such he had to make some tradeoffs like deciding what to do about NULLs on a per-type basis, recognizing that you may then need to modify the actual data in the database as you move it from SQLite to Postgres.

In the end, the tool was ready, simply invokable as :

pgsqlite. py [-h] -f SQLITE_FILENAME -p POSTGRES_CONNECT_URL [-d DEBUG] [--drop_tables DROP_TABLES] [--drop_everything DROP_EVERYTHING] [--drop_tables_after_import DROP_TABLES_AFTER_IMPORT]

Example : Import into the bit.io database adam/AMEND, with DEBUG-level logging.

python pgsqlite. py -f . . /example_dbs/Chinook_Sqlite.sqlite -p postgresql://adam:<password>@db.bit.io/adam/AMEND --debug true

That's it! Your data is owned by PostgresSQL now.

 

More Information

pgsqlite: A pure Python module to import SQLite databases into Postgres

Related Articles

Use Rust To Reduce The Size Of Your SQLite Database

In Praise Of SQLite

 

To be informed about new articles on I Programmer, sign up for our weekly newsletter, subscribe to the RSS feed and follow us on Twitter, Facebook or Linkedin.

Banner


TestSprite Announces End-to-End QA Tool
14/11/2024

TestSprite has announced an early access beta program for its end-to-end QA tool, along with $1.5 million pre-seed funding aimed at accelerating product development, expanding the team, and scaling op [ ... ]



JetBrains Improves Kubernetes Support In IDE Upgrades
12/11/2024

JetBrains has improved its IDEs with features to suggest the logical structure of code, to streamline the debugging experience for Kubernetes applications, and provide comprehensive cluster-wide Kuber [ ... ]


More News

espbook

 

Comments




or email your comment to: comments@i-programmer.info

Last Updated ( Monday, 17 October 2022 )