Labels

Database Migrations

Db Creation/Db Migration is really MUST-have for any projects (even pet) which use DB.

This allow reduce deploy time complexity, and simplify development.

Even simple creation new DB on App deploy or startup (not so complex as in well known Joomla, WordPress) significantly. Like, you loaded some open-source app from Git-Hub, and you wanted to try out, did you want to manually create DB by hand, apart you expect that package tool (maven, npm, )

Even you use NoSQL  (Not-Only SQL :) ) DB - you would faced need it migration. Yes, schema-less DB  creates Columns/Documents/Tables automatically as you go, but you would need some schema refactoring someday. Like, renaming columns, moving existing data from one Table/Column to another Table/Column, changing data type, or format, cleaning data, changing indexes etc.

Create new DB

This is simple, you should ship create db script with your sources, like: 
\db\createdb.sql

You can maintain this script by yourself or use some tool or DB UI to export all dev DB into one big-fat createdb.sql script. 

Then you teach your package manager to call this script on first app deployment.
Alternatively, application can run this script on start, like when no DB Connection is specified in properties.

Migrate existing DB

What it someone already deployed your application? What you package manager should do if DB with some data created by older version of your application, but newer version require different DB Schema? Answer - or clear user data 😁 or migrate existing DB (by hand or by tool).

Migration Strategies

  1. Require developer to write all migrations manually (like in SQL). A tool just look on directory and apply each SQL migration file in directory one-by-one. Also a tool write some info to DB about its action (like to not apply some migration twice).
    • POS: 
      • You have full control of any aspect of DB migration. Only your sql scripts will be applied on DB. Full transparency
      • Tool is simple 
    • CONS: 
      • Some trivial thing like Create Table - you have to write manually each time (but you can just copy-paste from other tool which do its best).
  2. Keep some Full Db-Schema in some file/file. And developers edit it. A Tool compare live DB with Full Db-Schema file and generates migration sql for it. 
    • POS: 
      • You don't write "dump" Create Table, Add Column commands manually.
      • Schema is always compared.
    • CONS: 
      • A tool is very complex and error prone, it may generate a lot of SQL noise which really  hard to read.
      • May not support DB specifics
      • Developers forget about SQL, its bad.
      • A tool do something "behind my back"
      • Developers forget about SQL, its bad. 

For #1 - migrations distinct scripts by two types: versioned or repeatable, expects them on different folders:
  • Versioned migrations have a unique version and are applied exactly once 
    • Like: Create Table, Alter Table.
  • Repeatable migrations do not have a version. Instead they are (re-)applied every time their checksum changes. 
    • Like: Storied Functions, Storied Procedures.
Source structure can be like:
\db\V1_Initial.sql
\db\V1.1_FixSomeBugs.sql
\db\R_MyView.sql
\db\R_MySecondView.sql
\db\R_MyStoriedProcedure.sql



Note: In structure above we don't need  \db\createdb.sql as on first deploy we tool can achive a same with execution each script one-by-one.
Warning: Please use versioning names carefully, as tool will execute them in according file name sorting order, and most of tools will run V10.sql before! V2.sql,V2.sql. Using dates also works in sort-wise formats 2017-04-01.sql and its may be relive for those hum not ready for app version names.

For #2 - Auto migration script generation works in 20%-60% of cases. For complex cases - like "Make all  Login names lover-case" - developer should write script manually, just as in #1 !!! There is NO tool for reading your minds 😄. So, expect that some big-branded IDE/Tool will show you "Please enter SQL" popup dialog.

Also #1 and partially #2 can be included inside your application and on App start. This is very handy feature especially for pet-projects which often lack of CI or any scripting.

As conclusion, I really suggest to use #1 tools. Moreover, tools #1 are so simple, that you can develop simple implementation by yourself (see Automating of SQL Diff-scripts).

Existing Tools

Enterprise system on which I working on was written a long before, and we developed and improved such tool with a time. But now we have a lot of Db-Migration Tools.

Some good examples:
Many implementation are included into Frameworks/ORMs/Tools sets:
  • mybatis - Java
  • Laravel - PHP 
  • Entity Framework - .Net
  • a lot of others



Labels:

Comments

Post a Comment