We use a similar technique (querying sqlite_schema and table_info) to generate an Entity Relationship Diagram using Graphviz, for our documentation. This particular sequence is important to avoid breaking foreign keys. In short we create a new table, copy the data from the old table into the new table, drop the old table, and rename the new table. If there are new or changed columns, we follow the 12 step procedure in the SQLite documentation. To detect changes to existing tables we use PRAGMA table_info, which returns a list of the table’s columns: sqlite> pragma table_info(Node) Node_node_id|CREATE UNIQUE INDEX Node_node_id on Node(node_id) Similarly for indexes: sqlite> select name, sql from sqlite_schema where type = "index" The above query gives us the CREATE TABLE sql, which we can execute to create the new tables: sqlite> select name, sql from sqlite_schema where type = "table" New_tables = set(pristine_tables.keys()) - set(tables.keys()) removed_tables = set(tables.keys()) - set(pristine_tables.keys()) When our application starts up, our migrator creates a new in-memory database and executes the schema to create a “pristine” or “desired” version of the database: We define our database schema in a single file -let’s call it schema.sql- with normal CREATE TABLE and CREATE INDEX statements. We’ve been using this since 2019 and it works well, though I must admit our database is small (a dozen tables, ~40MB) and it doesn’t change that often (65 changes to our schema in those 3 years, according to git log). This is important for CI where switching between branches can cause the schema to change regularly. We can downgrade too, by dropping tables or columns (if we don’t mind losing data).The auto-migration code gives us some guarantees that the resulting database schema will match our SQL file.This brings some of the benefits of schemaless databases to SQL, particularly around speed of development / ease of experimentation.We (usually) don’t need to write the database migration SQL manually.This is superior to explicit database migration scripts: If we add tables, columns, or indexes, our application will create them automatically the next time it starts up. We define the schema in a single file with SQL CREATE TABLE statements. We have included both, open-source tools and advanced, professional software (offering trial versions).By David Rõthlisberger and William Manley.Īt my company we use a small SQLite database. We have selected database documenters that, we believe, are truly noteworthy.
0 Comments
Leave a Reply. |