gitsqlite
A Git clean/smudge/diff filter for storing SQLite databases in plain text SQL, enabling meaningful diffs and merges.
Why
Binary SQLite databases are opaque to Git – you can’t easily see changes or resolve conflicts.
gitsqlite automatically converts between .sqlite and SQL text on checkout and commit or diff, letting you version SQLite data just like source code.
There are several benefits over using sqlite3 .dump directly:
- byte-by-byte equal across windows/linux/mac
- Consistent float rounding (deterministic dumps).
- Strip SQLite’s internal/system tables from dumps.
- Temp-file I/O for robustness (vs fragile pipes).
- handles broken pipes with Git Gui Clients
- easier to deploy and maintain in an organization - eg: winget for windows
- Optional: logging for diagnostics
Quick Start
-
Install gitsqlite (see Installation for all options):
# Windows
# curl -L -o gitsqlite.exe https://github.com/danielsiegl/gitsqlite/releases/latest/download/gitsqlite-windows-amd64.exe
winget install danielsiegl.gitsqlite
# Linux/macOS
curl -L -o gitsqlite https://github.com/danielsiegl/gitsqlite/releases/latest/download/gitsqlite-linux-amd64
chmod +x gitsqlite && sudo mv gitsqlite /usr/local/bin/
-
Ensure SQLite 3 is installed (required dependency):
(Not required when installed via winget.)
# Windows
winget install SQLite.SQLite
# Linux (Ubuntu/Debian)
sudo apt install sqlite3
-
Configure Git filters:
echo '*.db filter=gitsqlite' >> .gitattributes
# echo '*.db diff=gitsqlite' >> .gitattributes
git config filter.gitsqlite.clean "gitsqlite clean"
git config filter.gitsqlite.smudge "gitsqlite smudge"
# git config diff.gitsqlite.textconv "gitsqlite diff"
-
Start versioning SQLite files:
git add mydb.db
git commit -m "Add database in SQL format"
Git will automatically convert SQLite files to SQL text for storage and back to binary when checked out.
Quick Start: Schema/Data Separation
For cleaner diffs that only show data changes, use the schema/data separation feature:
-
Configure Git filters for data-only mode:
echo '*.db filter=gitsqlite-data' >> .gitattributes
git config filter.gitsqlite-data.clean "gitsqlite -data-only -schema clean"
git config filter.gitsqlite-data.smudge "gitsqlite -schema smudge"
-
Add schema file to Git:
git add .gitsqliteschema
git commit -m "Add database schema"
-
Version your database:
git add mydb.db
git commit -m "Add database data"
Git will now store only data changes in the database file, while schema is managed separately. This results in much cleaner diffs that only show INSERT operations.
Quick Start Git Diff
To enable SQL-based diffs for SQLite databases in Git, add the following to your repository's .gitattributes and configure your Git diff driver: (It doesn't matter if it is stored as binary or via smudge/clean.)
.gitattributes
*.db diff=gitsqlite
.git/config
[diff "gitsqlite"]
textconv = gitsqlite diff
This will use gitsqlite diff to convert SQLite databases to SQL for diffing in Git.
Sample Repo: https://github.com/danielsiegl/gitsqliteDiffFilterDemo
⚠️ Important Notice: Database Merging
Merging SQLite databases is complex and risky for many applications. While gitsqlite enables text-based diffs and basic merging, domain-specific databases often require specialized tools for safe merging.
When NOT to rely on automatic merging:
- Sparx Enterprise Architect (.qeax) databases - Use LieberLieber LemonTree for proper model merging
- Application-specific databases with complex schemas, constraints, or business logic
- Databases with foreign key relationships where merge conflicts could break referential integrity
- Production databases where data corruption could have serious consequences
Recommended workflow:
- Use gitsqlite for visibility - See what changed in your database commits
- Use specialized tools for merging - Domain experts tools understand your data structure
- Manual conflict resolution - Review and resolve conflicts using appropriate tools
- Test thoroughly - Validate database integrity after any merge operation
gitsqlite is excellent for tracking changes and simple scenarios, but consider it a foundation tool rather than a complete solution for complex database merging.
Installation
-
Windows (PowerShell):
# AMD64 (Intel/AMD 64-bit)
winget install danielsiegl.gitsqlite
# curl -L -o gitsqlite.exe https://github.com/danielsiegl/gitsqlite/releases/latest/download/gitsqlite-windows-amd64.exe
# ARM64 (Windows on ARM)
# curl -L -o gitsqlite.exe https://github.com/danielsiegl/gitsqlite/releases/latest/download/gitsqlite-windows-arm64.exe
# Move to a directory in your PATH, e.g.:
# Move-Item gitsqlite.exe C:\Windows\System32\
-
Linux:
# AMD64 (Intel/AMD 64-bit) - using curl
curl -L -o gitsqlite https://github.com/danielsiegl/gitsqlite/releases/latest/download/gitsqlite-linux-amd64
# ARM64 (ARM servers) - using curl
# curl -L -o gitsqlite https://github.com/danielsiegl/gitsqlite/releases/latest/download/gitsqlite-linux-arm64
# Alternative with wget
wget -O gitsqlite https://github.com/danielsiegl/gitsqlite/releases/latest/download/gitsqlite-linux-amd64
chmod +x gitsqlite
sudo mv gitsqlite /usr/local/bin/
-
macOS:
# Apple Silicon (M1/M2/M3) - using curl
# curl -L -o gitsqlite https://github.com/danielsiegl/gitsqlite/releases/latest/download/gitsqlite-macos-arm64
# Alternative with wget
wget -O gitsqlite https://github.com/danielsiegl/gitsqlite/releases/latest/download/gitsqlite-macos-amd64
chmod +x gitsqlite
sudo mv gitsqlite /usr/local/bin/
-
From Source (Go):
go install github.com/danielsiegl/gitsqlite@latest
Requirements:
sqlite3 CLI available in PATH (or specify with -sqlite flag)- Go ≥ 1.21 (only needed to build from source)
Usage
gitsqlite operates as a Git clean/smudge/diff filter, automatically converting between binary SQLite databases and SQL text format. The diff operation now takes a database filename as input (not stdin) and streams the SQL dump to stdout for comparison or inspection.
Basic syntax:
gitsqlite clean # Convert SQLite binary → SQL text (for Git storage)
gitsqlite smudge # Convert SQL text → SQLite binary (for checkout)
gitsqlite diff database.db # Stream SQL dump from SQLite binary (for diff/comparison)
Manual conversion:
gitsqlite clean < database.db > database.sql
gitsqlite smudge < database.sql > database.db
gitsqlite diff database.db > database.sql # No filtering, direct dump for diff/comparison
See CLI Parameters for all available options.
CLI Parameters
Operations
clean - Convert binary SQLite database to SQL dump (reads from stdin, writes to stdout, filtering optimized for cross platform)smudge - Convert SQL dump to binary SQLite database (reads from stdin, writes to stdout)diff - Stream SQL dump from binary SQLite database (reads from file, writes to stdout; no filtering)
Options
-sqlite - Path to SQLite executable (default: "sqlite3")
gitsqlite -sqlite /usr/local/bin/sqlite3 clean < database.db
-float-precision - Set the number of digits for rounding float values in SQL output (default: 9). Ensures deterministic dumps and consistent diffs across platforms.
gitsqlite -float-precision 8 clean < database.db > database.sql
-log - Enable logging to file in current directory
gitsqlite -log clean < database.db > database.sql
-log-dir - Log to specified directory instead of current directory
gitsqlite -log-dir ./logs clean < database.db > database.sql
-version - Show version information
gitsqlite -version
-help - Show help information
gitsqlite -help
Schema/Data Separation Options
-data-only - For clean/diff: output only data (INSERT statements), no schema
gitsqlite -data-only clean < database.db > data.sql
gitsqlite -data-only diff database.db > data.sql
-schema - Use .gitsqliteschema file for schema/data separation (works with all operations)
gitsqlite -schema clean < database.db > data.sql
gitsqlite -schema smudge < data.sql > database.db
gitsqlite -schema diff database.db > data.sql
-schema-file - Use specified file for schema/data separation (works with all operations)
gitsqlite -schema-file schema.sql clean < database.db > data.sql
gitsqlite -schema-file schema.sql smudge < data.sql > database.db
gitsqlite -schema-file schema.sql diff database.db > data.sql
Examples
Quick Start Example
- Create a sample SQLite database:
sqlite3 sample.db "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT); INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com'), ('Jane Smith', 'jane@example.com');"
- Convert to SQL text:
gitsqlite clean < sample.db > sample.sql
- Stream SQL for diff/comparison:
gitsqlite diff sample.db > sample.sql
- Show differences between two databases using diff:
gitsqlite diff old.db > old.sql
gitsqlite diff new.db > new.sql
diff -u old.sql new.sql
- View the SQL output:
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT);
INSERT INTO users VALUES(1,'John Doe','john@example.com');
INSERT INTO users VALUES(2,'Jane Smith','jane@example.com');
COMMIT;
- Convert back to database:
gitsqlite smudge < sample.sql > restored.db
- Verify the restoration:
sqlite3 restored.db "SELECT * FROM users;"
Schema/Data Separation Workflow
The schema/data separation feature allows you to store database schema and data separately for cleaner Git workflows and easier diff viewing.
- Separate schema and data during clean:
# Extract data-only (INSERT statements) and save schema to separate file
gitsqlite -data-only -schema clean < database.db > data.sql
- View the separated files:
# Schema file contains CREATE TABLE statements
cat .gitsqliteschema
# PRAGMA foreign_keys=OFF;
# BEGIN TRANSACTION;
# CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT);
# COMMIT;
# Data file contains INSERT statements
cat data.sql
# PRAGMA foreign_keys=OFF;
# BEGIN TRANSACTION;
# INSERT INTO users VALUES(1,'John Doe','john@example.com');
# INSERT INTO users VALUES(2,'Jane Smith','jane@example.com');
# COMMIT;
- Restore database from separated files:
# Combine schema and data back into database
gitsqlite -schema smudge < data.sql > restored.db
- Benefit: Cleaner diffs that show only data changes:
# After modifying data, diff will only show INSERT/UPDATE/DELETE changes
gitsqlite -data-only clean < modified.db > modified_data.sql
diff data.sql modified_data.sql
Advanced Usage Examples
With custom SQLite path:
# Linux/macOS
gitsqlite -sqlite /usr/local/bin/sqlite3 clean < database.db
# Windows
gitsqlite -sqlite "C:\sqlite\sqlite3.exe" clean < database.db
Round-trip Testing
Test data integrity with a complete round-trip:
# Create test → SQL → Database → SQL (should be identical)
gitsqlite smudge < sample.sql | gitsqlite clean > roundtrip.sql
diff sample.sql roundtrip.sql
Manual Testing Commands
# Test with logging for debugging
gitsqlite -log clean < test.db > test.sql
gitsqlite -log smudge < test.sql > test-restored.db
# Verify round-trip integrity
gitsqlite clean < test.db | gitsqlite smudge > restored.db
sqlite3 restored.db "SELECT COUNT(*) FROM sqlite_master;"
Logging
gitsqlite provides comprehensive logging to help monitor performance and troubleshoot issues during clean and smudge operations.
Quick Start
Enable basic logging:
gitsqlite -log clean < database.db > output.sql
📖 For comprehensive logging documentation, see log.md
Known Issues / Limitations
sqlite_sequence table content can change outside of your edits.- Large databases may be slow to convert.
- Temporary files are written to the system temp directory.
Uninstall
To remove the filter globally:
git config --global --unset-all filter.gitsqlite.clean
git config --global --unset-all filter.gitsqlite.smudge
Remove the .gitattributes entry from your repos.
Contributing
Pull requests and issues are welcome.
Versioning & Changelog
We follow Semantic Versioning.
Changes are documented in Releases.
Credits
Forked from quarnster/gitsqlite with improvements:
- Updated build and installation instructions
- Made to handle more scenarios
- Fixed cross-platform compatibility issues
- Added test scripts and example docs
- Line ending differences between OSes should not cause diff noise.
- Tries to detect Sqlite
Troubleshooting
Common Issues
"sqlite3 not found" Error
- Windows: Use
winget install sqlite or download from sqlite.org
- Linux: Use
sudo apt-get install sqlite3 or equivalent for your distribution
- macOS: Use
brew install sqlite3 or use the system-provided version
- Manual: Specify path with
-sqlite /path/to/sqlite3
Empty Output from Clean Operation
- Verify SQLite file is valid:
file yourfile.db
- Check file permissions and accessibility
- Enable logging with
-log flag to see detailed error messages
Smudge Operation Creates Invalid Database
- Ensure input is valid SQL (test with
sqlite3 :memory: < input.sql)
- Check for unsupported SQLite extensions or pragmas
- Verify SQL dump was created by gitsqlite or compatible tool
Permission Errors
- Check file permissions on database files
- Ensure write access to output directory when using
-log-dir
- On Windows, avoid paths with special characters or spaces
Performance Issues
- Large databases (>100MB) may take significant time to process
- Use SSD storage for better performance with large files
- Monitor log files to identify bottlenecks in clean/smudge operations
Debugging Tips
-
Enable logging to see detailed operation progress:
gitsqlite -log clean < problem.db > output.sql
-
Test SQLite accessibility separately:
sqlite3 -version
sqlite3 test.db ".tables"
-
Verify round-trip integrity on smaller test files first:
# Create minimal test case
sqlite3 test.db "CREATE TABLE t(x); INSERT INTO t VALUES(1);"
gitsqlite clean < test.db | gitsqlite smudge > restored.db
-
Check Git filter status:
git config --list | grep filter.gitsqlite
cat .gitattributes | grep gitsqlite
License
BSD-2-Clause © Fredrik Ehnbom
BSD-2-Clause © Daniel Siegl