sqlite3def

Usage:
  sqlite3def [OPTION]... FILENAME --export
  sqlite3def [OPTION]... FILENAME --apply < desired.sql
  sqlite3def [OPTION]... FILENAME --dry-run < desired.sql
  sqlite3def [OPTION]... current.sql < desired.sql

Application Options:
  -f, --file=FILENAME         Read desired SQL from the file, rather than stdin (default: -)
      --dry-run               Don't run DDLs but just show them
      --check                 Like --dry-run, but exit with code 2 when DDL would be applied (useful as a CI gate to detect schema drift)
      --apply                 Apply DDLs to the database (default, but will require this flag in future versions)
      --export                Just dump the current schema to stdout
      --enable-drop           Enable destructive changes such as DROP for TABLE, SCHEMA, ROLE, USER, FUNCTION, PROCEDURE, TRIGGER, VIEW, INDEX, SEQUENCE, TYPE
      --config=PATH           YAML configuration file (can be specified multiple times)
      --config-inline=YAML    YAML configuration as inline string (can be specified multiple times)
      --help                  Show this help
      --version               Show version information

Synopsis

# Create SQLite database and tables
$ sqlite3 mydb.db "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);"

# Export current schema
$ sqlite3def mydb.db --export
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT
);

# Save it to edit
$ sqlite3def mydb.db --export > schema.sql

Update schema.sql as follows:

 CREATE TABLE users (
     id INTEGER PRIMARY KEY,
-    name TEXT
+    name TEXT,
+    email TEXT NOT NULL
 );
+
+CREATE INDEX idx_users_email ON users(email);

And then run:

# Preview migration plan (dry run)
$ sqlite3def mydb.db --dry-run < schema.sql
-- dry run --
BEGIN;
ALTER TABLE users ADD COLUMN email TEXT NOT NULL;
CREATE INDEX idx_users_email ON users(email);
COMMIT;

# Apply DDLs
$ sqlite3def mydb.db --apply < schema.sql
-- Apply --
BEGIN;
ALTER TABLE users ADD COLUMN email TEXT NOT NULL;
CREATE INDEX idx_users_email ON users(email);
COMMIT;

# Operations are idempotent - safe to run multiple times
$ sqlite3def mydb.db --apply < schema.sql
-- Nothing is modified --

# By default, DROP operations are skipped (safe mode)
# To enable DROP TABLE, DROP COLUMN, etc., use --enable-drop
$ sqlite3def mydb.db --apply --enable-drop < schema.sql
-- Apply --
BEGIN;
DROP TABLE old_users;
COMMIT;

# Use config file to filter tables
$ cat > config.yml <<EOF
target_tables: |
  users
  posts_\d+
skip_tables: |
  sqlite_.*
  temp_.*
EOF
$ sqlite3def mydb.db --apply --config=config.yml < schema.sql

# Use inline YAML configuration
$ sqlite3def mydb.db --apply --config-inline="skip_tables: backup_.*" < schema.sql

# Multiple configs (later values override earlier ones)
$ sqlite3def mydb.db --apply --config=config.yml --config-inline="target_tables: users" < schema.sql

Offline Mode (File-to-File Comparison)

sqlite3def can compare two schema files without connecting to a database. This is useful for CI/CD pipelines, schema validation, and generating migration scripts.

How It Works

When the filename argument ends with .sql, sqlite3def operates in offline mode:

# Normal mode: connects to database file
$ sqlite3def mydb.db --apply < schema.sql

# Offline mode: compares two files (no database connection)
$ sqlite3def current.sql < desired.sql

In offline mode:

• No database connection is established
• The tool compares two SQL files (current vs desired)
• DDL statements are generated to show what would change
• Changes are always shown in dry-run mode (not applied to any database)

Basic Usage

# Compare two schema files
$ sqlite3def current_schema.sql < desired_schema.sql
-- dry run --
BEGIN;
ALTER TABLE `users` ADD COLUMN `email` text NOT NULL;
CREATE INDEX idx_users_email ON users(email);
COMMIT;

# Using --file flag instead of stdin
$ sqlite3def --file desired_schema.sql current_schema.sql

# Verify idempotency (compare identical schemas)
$ sqlite3def desired_schema.sql < desired_schema.sql
-- Nothing is modified --

Supported features

The following DDLs are generated by updating CREATE TABLE. Some can also be used in the input schema.sql file.

• Tables: CREATE TABLE, DROP TABLE, ALTER TABLE RENAME TO, CREATE VIRTUAL TABLE
• Columns: ADD COLUMN, DROP COLUMN, ALTER TABLE RENAME COLUMN
• Constraints: PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, AUTOINCREMENT
• Indexes: CREATE INDEX, DROP INDEX, ALTER TABLE RENAME INDEX
• Views: CREATE VIEW, DROP VIEW

Column, Table, and Index Renaming

Column Renaming

sqlite3def supports renaming columns using the -- @renamed from=old_name annotation:

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  user_name TEXT, -- @renamed from=username
  age INTEGER
);

This generates:

ALTER TABLE users RENAME COLUMN username TO user_name;

For columns with special characters or spaces, use double quotes:

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  column_with_underscore VARCHAR(50), -- @renamed from="column-with-dash"
  normal_column TEXT, -- @renamed from="special column"
);

Table Renaming

sqlite3def supports renaming tables using the -- @renamed from=old_name annotation on the CREATE TABLE line:

CREATE TABLE users ( -- @renamed from=user_accounts
  id INTEGER PRIMARY KEY,
  username TEXT,
  age INTEGER
);

You can also use the block comment style:

CREATE TABLE users /* @renamed from=user_accounts */ (
  id INTEGER PRIMARY KEY,
  username TEXT,
  age INTEGER
);

This generates:

ALTER TABLE user_accounts RENAME TO users;

For tables with special characters or spaces, use double quotes:

CREATE TABLE user_profiles ( -- @renamed from="user accounts"
  id INTEGER PRIMARY KEY,
  name TEXT
);

You can combine table renaming with column renaming and other schema changes:

CREATE TABLE accounts ( -- @renamed from=old_accounts
  id INTEGER PRIMARY KEY,
  username TEXT NOT NULL, -- @renamed from=user_name
  is_active BOOLEAN DEFAULT 1
);

Index Renaming

sqlite3def supports renaming indexes using the -- @renamed from=old_name or /* @renamed from=old_name */ annotation:

CREATE INDEX new_email_idx /* @renamed from=old_email_idx */ ON users (email);

Note: SQLite doesn't support direct index renaming. sqlite3def handles this by dropping the old index and creating the new one:

DROP INDEX old_email_idx;
CREATE INDEX new_email_idx ON users (email);

You can rename multiple indexes:

CREATE INDEX email_idx ON users (email); -- @renamed from=idx_email
CREATE INDEX username_idx ON users (username); -- @renamed from=idx_username

The rename annotation also works for unique indexes:

CREATE UNIQUE INDEX unique_email /* @renamed from=old_unique_email */ ON users (email);

Configuration

Configuration can be provided through YAML files (--config) or inline YAML strings (--config-inline). Multiple configurations can be specified and will be merged in order.

Using Configuration Files

$ sqlite3def mydb.db --apply --config config.yml < schema.sql

Using Inline Configuration

$ sqlite3def mydb.db --apply --config-inline 'enable_drop: true' < schema.sql

Combining Multiple Configurations

$ sqlite3def mydb.db --apply \
  --config base.yml \
  --config-inline 'skip_tables: [logs, temp_data]' \
  --config-inline 'enable_drop: true' \
  < schema.sql

Available Configuration Options

Field	Type	Description
enable_drop	boolean	Enable destructive changes (DROP statements). Equivalent to --enable-drop flag.
target_tables	string	Regular expression patterns (one per line) to specify which tables to manage. Only tables matching these patterns will be processed.
skip_tables	string	Regular expression patterns (one per line) to specify which tables to skip. Tables matching these patterns will be ignored.
skip_views	string	Regular expression patterns (one per line) to specify which views to skip.
legacy_ignore_quotes	boolean	Controls identifier quoting behavior. When true (default), all identifiers are quoted in output. When false, identifiers preserve their original quoting from the source SQL. Default is true but will change to false in the next major version.