How to setup a dev db for symfony development where migration scripts are not used.

In my work, I develop a symfony application. As usual, migration scripts are abandoned and I have no easy way of setting up a development mysql database in my machine for local development.

But I found an easy solution to setup the db shema.

STEP 1 Import a db dump - initialise database

At the first step, you need to obtain a dump of a development db and import it using mysql. This needs to run every time you create a fresh db instance:

mysql -h 127.0.0.1 -u dbusr -pXXXX somedb < dump.sql

Each time you drop and recreate the db, import the dump it is a good idea to keep it upon your hard disk.

STEP 2 Update schema

Then I use symfony's shema:update and I post-process it (it can generate some unwanted sql):

php bin/console doctrine:schema:update --dump-sql | \
awk '/^ALTER TABLE/ {
    gsub(/,$/, "", $0);
    table=$3;
    sub(/^ALTER TABLE [^ ]+ /, "", $0);
    n=split($0, parts, /, */);
    for (i=1; i<=n; i++) {
        print "ALTER TABLE " table " " parts[i] ";"
    }
    next
}
{ print }
' | \
tee /dev/tty | mysql -h 127.0.0.1 -u dbusr -pXXXX somedb 

Let me explain this monstrocity I typed above a bit:

1 Generate schema update sql

php bin/console doctrine:schema:update --dump-sql

With this command I get the SQL from doctrine:schema:update using the parameter --dump-sql

2 Postprocess sql using awk

awk '/^ALTER TABLE/ {
    gsub(/,$/, "", $0);
    table=$3;
    sub(/^ALTER TABLE [^ ]+ /, "", $0);
    n=split($0, parts, /, */);
    for (i=1; i<=n; i++) {
        print "ALTER TABLE " table " " parts[i] ";"
    }
    next
}

Using awk I split any ALTER TABLE sql statement into smaller ones. I use awk because if an ALTER TABLE statement alters multiple colums it may fail.

I wanted each ALTER TABLE statement to add, alter or drop only one column at a time, increasing changes of the updates to successfully run upon db.

That allows me to fix any issues at my own time.

3 Print upon console

tee /dev/tty 

With this, I wanted to show what sql statements run upon db whilst I also pass it upon mysql command:

4 run sql statements

mysql --force -h 127.0.0.1 -u dbusr -pXXXX somedb 

Finally, using mysql I run the statements that are generated upon db. I use --force option because if ommited and an sql statement fails other will not run upon db.

What I wanted was to run any sql statement that updated the db schema.

Caveats

This method is subpar to the use of db migration scripts. But many cases where for various reasons db migrations are not maintained properly (lack of time or knowledge) this is the next best thing.

If upon your team db migrations used properly this method should be avoided and use the migration scripts to setup the db.

Also this method forces any sql statement to run upon db without some sort of controll or test. Some of them can be dangerous do irreversible damage to db schema. Furthermor a db setup or migrations also need need data migration as well that this method does not provide.

This approach is recommended for a local db setup during development and should be avoided for app deployment.

The good things about this method

This method though offers fixed steps to setup and update a development database at any given moment. Personally, I made 2 bash scripts:

One that nukes and creates a fresh db from a db dump (as mentioned upon step 1):

#!/usr/bin/env bash

mysql -h 127.0.0.1 -u dbusr -pXXXX -e "DROP DATABASE app_db"
mysql -h 127.0.0.1 -u dbusr -pXXXX somedb -e "CREATE DATABASE app_db"

# Init
mysql -h 127.0.0.1 -u dbusr -pXXXX somedb < dump.sql

And one that updates the schema (as mentioned upon step 2)

#!/usr/bin/env bash

php bin/console doctrine:schema:update --dump-sql | \
awk '/^ALTER TABLE/ {
    gsub(/,$/, "", $0);
    table=$3;
    sub(/^ALTER TABLE [^ ]+ /, "", $0);
    n=split($0, parts, /, */);
    for (i=1; i<=n; i++) {
        print "ALTER TABLE " table " " parts[i] ";"
    }
    next
}
{ print }
' | \
tee /dev/tty | mysql -h 127.0.0.1 -u dbusr -pXXXX somedb 

These can be adjusted for example if php app runs through a docker container named php:

#!/usr/bin/env bash

docker exec -u www-data php php bin/console doctrine:schema:update --dump-sql  | \
awk '/^ALTER TABLE/ {
    gsub(/,$/, "", $0);
    table=$3;
    sub(/^ALTER TABLE [^ ]+ /, "", $0);
    n=split($0, parts, /, */);
    for (i=1; i<=n; i++) {
        print "ALTER TABLE " table " " parts[i] ";"
    }
    next
}
{ print }
' | \
tee /dev/tty | mysql -h 127.0.0.1 -u dbusr -pXXXX somedb 

I hope this to be found usefull.