Structural Parity Between Tables in Separate SQLite Databases: Schema Synchronization Challenges

Identifying Missing Columns and Generating Schema Alterations

The core challenge revolves around maintaining structural parity between two tables (table) residing in separate SQLite databases (master and slave). These tables start with identical schemas but evolve independently, requiring periodic synchronization of column additions. The process involves three critical phases:

1. Detecting column discrepancies using PRAGMA_TABLE_INFO
2. Generating ALTER TABLE statements for missing columns
3. Executing schema modifications without external programming languages

SQLite’s PRAGMA_TABLE_INFO function provides metadata about columns in a specific table, including data types and constraints. When applied to both databases via the PRAGMA_TABLE_INFO('table', 'database') syntax, it enables comparison of column sets. The original approach uses Common Table Expressions (CTEs) to create temporary tables (master.columns_to_add and slave.columns_to_add) storing missing column names. However, this only addresses the detection phase – not the execution of schema changes.

A critical limitation emerges here: SQLite lacks native support for dynamic SQL execution. While you can generate ALTER TABLE statements through SELECT queries, you cannot directly execute these generated strings within standard SQLite without extensions or external tools. This creates a gap between identifying missing columns and applying structural changes.

Constraints in Dynamic DDL Execution and Cross-Database Operations

Three fundamental limitations create roadblocks in achieving pure SQL-based synchronization:

1. Static Nature of SQLite DDL Statements
SQL Data Definition Language (DDL) commands like ALTER TABLE require explicit column definitions at parse time. You cannot parameterize column names or dynamically construct DDL statements that execute within the same session without interpreter assistance. This forces reliance on external tools to execute generated SQL strings.

2. Database Attachment Requirements
To compare schemas across databases, both must be attached to the same SQLite session using ATTACH DATABASE. The original example assumes separate database connections, but true cross-database operations require a single session with both databases attached. Without this, PRAGMA_TABLE_INFO cannot reference external databases directly.

3. Transactional Schema Modifications
SQLite allows schema changes within transactions, but altering multiple tables across attached databases introduces locking complexities. Each ALTER TABLE statement implicitly commits the current transaction, making batch operations atomic only at the statement level. This complicates rollback scenarios if synchronization fails mid-process.

Implementing Schema Synchronization with SQLite Tools and Extensions

Phase 1: Attaching Databases and Detecting Column Differences

Step 1: Attach Both Databases to a Single Session
Begin by attaching both databases to a common SQLite session. This enables cross-database queries without switching connections:

ATTACH 'slave.db' AS slave;  
ATTACH 'master.db' AS master;  

Step 2: Identify Missing Columns Using Temporary Tables
Create temporary tables to store column names missing in each database. Use EXCEPT set operations for cleaner syntax compared to NOT IN subqueries:

CREATE TEMP TABLE master_missing_columns AS  
SELECT name FROM slave.table_info  
EXCEPT  
SELECT name FROM master.table_info;  

CREATE TEMP TABLE slave_missing_columns AS  
SELECT name FROM master.table_info  
EXCEPT  
SELECT name FROM slave.table_info;  

Replace table_info with actual PRAGMA_TABLE_INFO calls wrapped in a CTE:

WITH master.table_info(name) AS (  
  SELECT name FROM PRAGMA_TABLE_INFO('table', 'master')  
),  
slave.table_info(name) AS (  
  SELECT name FROM PRAGMA_TABLE_INFO('table', 'slave')  
)  

Phase 2: Generating ALTER TABLE Statements

Step 3: Construct DDL Statements for Missing Columns
Use string concatenation to generate ALTER TABLE commands. For columns missing in master:

SELECT 'ALTER TABLE master.table ADD COLUMN ' || name || ' TEXT;'  
FROM master_missing_columns;  

Repeat for slave_missing_columns. Store these statements in a script file using the SQLite CLI’s .once directive:

.once alter_master.sql  
SELECT 'ALTER TABLE master.table ADD COLUMN ' || name || ' TEXT;'  
FROM master_missing_columns;  

.once alter_slave.sql  
SELECT 'ALTER TABLE slave.table ADD COLUMN ' || name || ' TEXT;'  
FROM slave_missing_columns;  

Phase 3: Executing Schema Changes

Option A: SQLite CLI Redirection
Leverage the command-line shell’s ability to read generated SQL files:

sqlite3 combined.db ".read alter_master.sql"  
sqlite3 combined.db ".read alter_slave.sql"  

This method requires scripting outside pure SQL but uses SQLite’s built-in capabilities.

Option B: Eval Extension
Compile and load the eval extension (from SQLite’s misc directory) to execute dynamic SQL:

SELECT eval('ALTER TABLE master.table ADD COLUMN ' || name || ' TEXT;')  
FROM master_missing_columns;  

This bypasses file I/O but requires enabling loadable extensions.

Option C: Sqldiff Utility
Use the sqldiff tool with schema-only comparison:

sqldiff --schema master.db slave.db  

Parse the output to extract ALTER TABLE statements. While primarily for diffing, its output can be adapted for synchronization.

Transactional Integrity and Error Handling

Wrap schema changes in transactions to handle partial failures:

BEGIN TRANSACTION;  
-- Execute all ALTER TABLE statements  
COMMIT;  

However, note that each ALTER TABLE auto-commits in SQLite. To mitigate this, batch changes into a single transaction using a WITH RECURSIVE hack for statement generation, though this is complex and error-prone.

Edge Cases and Optimization

1. Column Ordering
   SQLite appends new columns to the end of the table. If applications depend on column order, consider rebuilding the entire table with CREATE TABLE new_table AS SELECT ..., but this is resource-intensive.

2. Data Type Consistency
   Ensure added columns use compatible data types. The original problem assumes all columns are TEXT, but if types differ, synchronization must account for type mapping.

3. Indexes and Triggers
   Adding columns doesn’t affect indexes or triggers, but subsequent data synchronization might. Always re-validate dependent objects post-schema change.

4. Concurrent Writes
   Attaching databases in a write-ahead logging (WAL) mode session allows concurrent reads but requires exclusive access during schema changes. Use PRAGMA locking_mode=EXCLUSIVE before altering schemas.

Final Implementation Script

-- Attach databases  
ATTACH 'slave.db' AS slave;  
ATTACH 'master.db' AS master;  

-- Detect missing columns  
CREATE TEMP TABLE master_missing AS  
SELECT s.name  
FROM (SELECT name FROM PRAGMA_TABLE_INFO('table', 'slave')) s  
LEFT JOIN (SELECT name FROM PRAGMA_TABLE_INFO('table', 'master')) m  
  ON s.name = m.name  
WHERE m.name IS NULL;  

CREATE TEMP TABLE slave_missing AS  
SELECT m.name  
FROM (SELECT name FROM PRAGMA_TABLE_INFO('table', 'master')) m  
LEFT JOIN (SELECT name FROM PRAGMA_TABLE_INFO('table', 'slave')) s  
  ON m.name = s.name  
WHERE s.name IS NULL;  

-- Generate and execute ALTER statements using eval  
SELECT eval('ALTER TABLE master.table ADD COLUMN ' || name || ' TEXT;')  
FROM master_missing;  

SELECT eval('ALTER TABLE slave.table ADD COLUMN ' || name || ' TEXT;')  
FROM slave_missing;  

-- Detach databases  
DETACH slave;  
DETACH master;  

This script assumes the eval extension is available. For CLI-based execution, replace the eval calls with .once and .read directives.

By systematically addressing schema detection, DDL generation, and execution mechanics, you achieve structural parity across databases using predominantly SQL-native constructs, supplemented minimally by SQLite’s CLI capabilities or extensions.