Database Management

[TheRefraction]

Database Management

The following explain how the database is deployed and managed as we cannot work directly onto a remote database (this costs money...).

When starting the MySQL service, the database is created or opened if the volume mysql_data has not been unmounted/destroyed. Then migrations are applied accordingly ; each applied is tracked under the flyway_schema_history table (do not delete it if you don't know what you're doing). Each change you make that is not put into a migration, is solely local. Keep that in mind when unmounting volumes.

Migrations

Migrations are defined as the movement of data contained in a given database (for instance, schema objects, procedures, triggers, ...) from the latter to a new or updated database. In Docker, the Flyway service has already been configured and takes care of that. Migrations are applied when using the manage script with the migrate parameter.

File structure

Each migration should be placed under db/migrations. Those migrations are committed to Git as they should be.
Writing migrations is pretty straightforward: just create a .sql file containing the exact SQL statements needed to evolve the database from one version to the next.

This part is very important as if you do not follow the rules, Flyway will fail to register and apply migrations.

Versioned migrations

Each is applied exactly once, in version order. They should follow the following naming convention: V<version>__<description>.sql. Please note that this name is case-sensitive. Also, there should be exactly two underscores as the separator. The version string is composed of numbers with underscores (for example 1, 1_2 or 2_0_1). The description string is pretty explicit although any space should be replaced by an underscore.

A simple example:

-- V1__create_users_table.sql
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_username ON users(username);

Repeatable migrations

Used for views and procedures. They are applied every time the file changes. They follow the following naming convention R__<description>.sql.

-- R__active_users_view.sql
CREATE OR REPLACE VIEW active_users AS
SELECT id, username, last_login 
FROM users 
WHERE last_login > NOW() - INTERVAL '30 days';

-- R__search_function.sql
CREATE OR REPLACE FUNCTION search_products(search_term TEXT)
RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
    RETURN QUERY 
    SELECT id, name 
    FROM products 
    WHERE name ILIKE '%' || search_term || '%';
END;
$$ LANGUAGE plpgsql;

Best practices

The following should also be applied to prevent having any kind of issue.

Immutable migrations

Warning

Never, ever modify a versioned migration script that has already been committed to version control, and thus applied to any shared environment.

Flyway tracks a checksum of each applied migration. Therefore if you modify a script after it's been applied, Flyway will fail to deploy any subsequent migration and return a checksum mismatch.

Therefore if you need to edit a migration script (for a typo for instance) or undo it, and it has already been committed, you'll need to write another migration that counteracts the first one.

Idempotency

Warning

Write migrations that can be safely run multiple times without causing errors or duplicate data.

During development, migrations might need to run multiple times for any reason (testing, disaster recovery, ...). Whence why each SQL migration should idempotent.

For instance, let's consider the following example:

-- V1__create_settings_table.sql
CREATE TABLE settings (  
    key VARCHAR(100) PRIMARY KEY,
    value TEXT
);

INSERT INTO settings VALUES ('app_name', 'MyApp'); 

First it may fail creating the table settings if it already exists. Then, the insertion can duplicate data on re-run.
Finally, here is a better version:

-- V1__create_settings_table.sql
CREATE TABLE IF NOT EXISTS settings (
    key VARCHAR(100) PRIMARY KEY,
    value TEXT
);

INSERT INTO settings (key, value) 
VALUES ('app_name', 'MyApp')
ON DUPLICATE KEY UPDATE value = VALUES(value);

Atomic migrations

Warning

Each migration should represent exactly one logical change to the database schema.

This is important for many reasons, one of which is better "debugging" and code review. Also it is easier to rollback if a migration fails: only one element will be lost.

However, there might multiple changes that are linked (for example, creating a trigger and its trigger function). In this case, it is completely fine to only write one migration.

Handle data migrations

Warning

Always separate schema changes from data migrations

File presentation

Warning

Always include a header with the name of the file as well as a description. Do include rollback logic in comments as well as comments when not trivial.

-- V1__create_settings_table.sql
-- Create the settings table

-- ROLLBACK: DROP TABLE settings;
CREATE TABLE IF NOT EXISTS settings (
    key VARCHAR(100) PRIMARY KEY,
    value TEXT
);

Testing migrations

In order to avoid having to troubleshoot your migration because Flyway failed, you should do the following to ensure data integrity:

1. Open PHPMyAdmin (or the MySQL console if you prefer)
2. Duplicate your current database and give it a memorable name like exquisite_potassium
3. Run your migration script in the SQL console

It is easier to manage such a database than having to unmount and rebuild the containers every time, because the migration screwed up...

Troubleshooting

Flyway failed to apply the migration

This might be because of a SQL syntax error, or non-idempotency issue (trying to add a column that already exists). In this case, even though the script is transactional, MySQL will implicitely commit any changes to the database until the syntax error. This is why it's important not to have a huge migration, as it will be difficult to rollback.

You have several options:

• Either you drop the volume, and start the containers again (but you will lose all your local data)
• Or you use PHPMyAdmin's SQL console to rollback any changes you made (you should run your SQL commands in the opposite order of the ones in your migration).

Afterwards make sure to fix properly your migration, run the manage script with the repair option, and then migrate again!

Migrations have failed validation

If you get that kind of message:

ERROR: Validate failed: Migrations have failed validation
Detected failed migration to version 1.0.0 (primary tables).
Please remove any half-completed changes then run repair to fix the schema history.

You need to run the manage script with the repair option. Then you might run your migration.

Backups

Running the manage script with backup as the parameter will dump the current database under db/backups as a SQL file that can be imported later in PHPMyAdmin. Those files can be committed with no problem.