Can sqlite dump database schema in a fixed order?

Question

I am using SQLiteStudio 3.0.7 to dump the database schema regularly for versioning purposes and it seems to change the order of the table definitions at random. This wreaks havoc on the diffs as even a small change like adding one or two additional columns seems to result in wild diffs all over the place. A side by side comparision can result in a whole table being list out separately when it is just the line difference that should show.

Can SQLiteStudio be configured to do that correctly., or can sqlite itself do the same schema export in a fixed formatted manner?

• phil294: Some discussion about also here, but not really useful

Answer 1

SQLiteStudio sorts the database objects by type (table, index, trigger, view), but inside each group, sorts them randomly. (If you consider this a bug, report it.)

The sqlite3 shell dumps tables in the order in which they are stored in the sqlite_master table, which is typically the same order in which they were (re)created.

• vfclists: It is not a bug, but I will request it as a feature

Answer 2

To get a consistently sorted schema dump output, you might want to manually process the output yourself. I made a small helper script for this purpose:

#!/bin/bash
db=the_database.db
for table in $(sqlite3 $db "select name from sqlite_master where type = 'table' order by name"); do
 sqlite3 $db ".schema $table" | grep 'CREATE TABLE' | grep -v 'sqlite_sequence('
 sqlite3 $db ".schema $table" | grep -v 'CREATE TABLE' | sort
done

Does what I want; no guarantee for correctness though. The order of the columns of each respective table however can still vary.

Answer 3

Save it as whatever.sh and run: sh whatever.sh

#!/bin/bash
db="db.sqlite"
tables=("table1" "table2" "table3")
output_file="dump.sql"
# Clear or create the result.sql file
echo "" > $output_file
# Output the schema and data for the specified tables
for table in "${tables[@]}"; do
 # Output the schema for the current table
 echo "/* Schema for table $table */" >> $output_file
 sqlite3 $db ".schema $table" >> $output_file
 # Output the data for the current table as INSERT statements
 echo "/* Data for table $table */" >> $output_file
 sqlite3 $db ".mode insert $table" ".headers on" "SELECT * FROM $table;" >> $output_file
done