Convert SQL dump to SQLite

Working from a .sql file

A .sql file is only text containing statements, so what you get out depends entirely on which statements are in it. Schema and data both have to be present for the result to be a database rather than an empty shell.

• →Four things are used: CREATE TABLE, ALTER TABLE ... PRIMARY KEY, INSERT and COPY. Views, triggers, stored procedures, indexes and GRANT statements are read past rather than converted.
• →A file with INSERTs but no CREATE TABLE still produces its rows, with a warning that the columns are unnamed - the file never said what they were called.
• →A file with neither comes back with "No CREATE TABLE or INSERT statements were found", which usually means the schema lives in a second file.
• →psql meta-commands - the lines beginning with a backslash, like \connect - are client directives rather than SQL, and are skipped before parsing.

What the .sqlite file looks like inside

The output here is a real database rather than a script, so the interesting question is not how to load it but what SQLite did with the types on the way in. Its storage classes are looser than the declarations the file ends up carrying.

• →Names are carried over exactly as they were, with none of the sanitising the script targets apply: a table called Sales Report keeps its space and a column called Order ID keeps its own, so your queries have to quote them.
• →Underneath the declared types sit only five storage classes, and NUMERIC affinity - what a decimal column gets - is the loosest of them: it normalises inserted text to whatever a plain INTEGER or REAL column would hold. The precision and scale in the schema are for a human reading it, not a guarantee SQLite enforces on the bytes; the Writing notes below cover what that means for money specifically.
• →Declared type and stored type are different things here: a BOOLEAN column holds the integers 1 and 0, and a DATE column holds ISO text, because those are the storage classes SQLite actually has. Every client reads them back the same way.

Reading SQL dump

• →Parsed with a real SQL parser, not regular expressions, so a value containing a closing parenthesis or a semicolon cannot split a row in half.
• →CREATE TABLE types, primary keys, NOT NULL and AUTO_INCREMENT are all read from the dump.
• →The source dialect is detected from the dump itself — MySQL, PostgreSQL and SQL Server dumps all parse.

Writing SQLite

• →Columns get real declared types rather than everything being TEXT.
• →Primary keys and NOT NULL constraints are preserved.
• →A money column is declared NUMERIC(10,2), but that declaration does not stop SQLite’s own numeric affinity from converting the inserted value into a float once it lands, so 19.99 can come back with a rounding tail. Pick the DuckDB target instead when the exact decimal has to survive.

Convert SQL dump to something else

• MySQL
• PostgreSQL
• Oracle
• SQL Server
• DuckDB
• CSV
• Excel

Convert something else to SQLite

• CSV
• Excel
• DBF
• JSON
• XML
• Parquet
• DuckDB
• MS Access
• PostgreSQL backup