Proposal automated migrations mechanism

[eznix86]

This introduce BREAKING CHANGE to migration system.

Migration Mechanism

Say, you want to build a model,

const columMapping: ColumnMapping<Animal> = {
  id: { type: columnTypes.INTEGER },
  name: { type: columnTypes.TEXT }
}

when you run migrations.migrate() it will automatically try to see if there is a migration inside of migrations, if it doesn't it will infer the a JSON and a SQL (here a create table from the mapping, and migrate it to the database, then it will write the JSON to a migrations table with a timestamp as key.

Next time you update the Animal Table,

const columMapping: ColumnMapping<Animal> = {
  id: { type: columnTypes.INTEGER },
  name: { type: columnTypes.TEXT },
+  color: { type: columnTypes.TEXT },
}

When running, migrations.migrate() and it will perform a look up in the table, taking the latest migration, take the JSON, and compare the keys difference, if a new key was added inside the JSON, it will create an alter table and migrate it right away.

File based migration

Similarly instead of writing to a database, it will write to a folder, and read from it. It is developer friendly compared to the database only.

Hybrid

We can do both, where `migrations.migrate(diff=true) is introduced to print the difference to console + write to file if there is a diff + on migration, it will run the latest migration JSON to the database.

Update:

instead of JSON we can also look at the table info to see the difference https://www.sqlite.org/pragma.html#pragma_table_info

Example:

PRAGMA table_info([cities]);