# 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
      --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

```shell
# 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:

```diff
 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:

```shell
# 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:

```shell
# 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

```shell
# 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:

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

This generates:
```sql
ALTER TABLE users RENAME COLUMN username TO user_name;
```

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

```sql
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:

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

You can also use the block comment style:

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

This generates:
```sql
ALTER TABLE user_accounts RENAME TO users;
```

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

```sql
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:

```sql
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:

```sql
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:

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

You can rename multiple indexes:

```sql
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:

```sql
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

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

### Using Inline Configuration

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

### Combining Multiple Configurations

```shell
$ 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. |