A command-line program for generating SQLite schema diffs.
Note
This project is not associated with the sqlitediff package on PyPI, catering towards forensic analysis of SQLite databases. If you're interested in 5f0ne's source code, check out his repository!
$ sqlitediff examples/user_group_1.sql examples/user_group_2.sql
PRAGMA foreign_keys = off;
BEGIN TRANSACTION;
-- Modified Objects --
-- Previous table schema for user:
-- CREATE TABLE user (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE sqlitediff_temp (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
INSERT INTO sqlitediff_temp (id, name) SELECT id, name FROM user;
DROP TABLE user;
ALTER TABLE sqlitediff_temp RENAME TO user;
-- Restoring references to user:
CREATE INDEX ix_user_name ON user (name);
-- Previous view schema for user_group_kawaii:
-- CREATE VIEW user_group_kawaii (id) AS
-- SELECT user_id FROM user_group WHERE group_id = 1;
DROP VIEW IF EXISTS user_group_kawaii;
CREATE VIEW user_group_kawaii (id) AS
SELECT user_id FROM user_group WHERE group_id = 2;
-- Previous trigger schema for add_user_to_kawaii_group:
-- CREATE TRIGGER add_user_to_kawaii_group
-- INSERT ON user
-- BEGIN
-- INSERT INTO user_group (user_id, group_id) VALUES (new.id, 1);
-- END;
DROP TRIGGER IF EXISTS add_user_to_kawaii_group;
CREATE TRIGGER add_user_to_kawaii_group
INSERT ON user
BEGIN
INSERT INTO user_group (user_id, group_id) VALUES (new.id, 2);
END;
-- New Objects --
ALTER TABLE "group" ADD COLUMN description TEXT NOT NULL DEFAULT '';
CREATE INDEX ix_group_user ON user_group (group_id, user_id);
CREATE VIEW user_group_all (id) AS
SELECT user_id FROM user_group WHERE group_id = 1;
CREATE TRIGGER add_user_to_all_group
INSERT ON user
BEGIN
INSERT INTO user_group (user_id, group_id) VALUES (new.id, 1);
END;
-- Please verify foreign keys before committing!
-- The following pragma should return 0 rows:
PRAGMA foreign_key_check;
COMMIT;
sqlitediff uses the sqlite_schema
table to read your database structure
and compare differences between tables, indices, views, and triggers.
It can parse DDL for tables to determine new, modified, or deleted columns
and tries to produce ALTER TABLE statements where supported by SQLite.
Additionally, recommendations will be provided if sqlitediff detects
potential issues with the output script such as table/column renames.
Assuming you have Python 3.8+ and Git, you can install this application with:
pip install git+https://github.com/thegamecracks/sqlitediff@v0.1.7
After installation, the command-line interface can be used with sqlitediff
or python -m sqlitediff
. It can compare SQLite database files directly
or take .sql scripts which are executed in-memory before comparison.
Run sqlitediff --help
for more information.
Warning
Do not run sqlitediff's output on a production database un-modified without first verifying that the script works on a copy. Some modifications by themselves can cause constraint violations or data loss due to ambiguity in how the changes should be applied or the order in which they are executed. In the worst-case scenario, you can use the output as a reference to write your own migration script.
This project is written under the MIT license.