6 Migraciones SQL

En este post haremos los pasos para crear una tabla movies en la base de datos greenlight.

Para hacer esto simplemente podriamos usar la herramienta psql y correr la declaracion CREATE TABLE contra nuestra DB. Pero en su lugar vamos a explorar como usar SQL MIGRATIONS para crear la tabla.

Aprenderás acerca de:

• Los principios generales detrás de las migraciones SQL y por qué son útiles.
• Cómo utilizar la herramienta de migrate de línea de comandos para gestionar de manera programática los cambios en el esquema de tu base de datos.”

6.1 Vision general de las SQL Migrations

En caso de que no estés familiarizado con la idea de las migraciones SQL, a un nivel muy alto, el concepto funciona de la siguiente manera:

1. Para cada cambio que desees realizar en el esquema de tu base de datos (como crear una tabla, agregar una columna o eliminar un índice no utilizado), creas un par de archivos de migración. Un archivo es la migración ‘up’, que contiene las declaraciones SQL necesarias para implementar el cambio, y el otro es una migración ‘down’, que contiene las declaraciones SQL para revertir (o deshacer) el cambio.

2. Cada par de archivos de migración se numeran secuencialmente, generalmente como 0001, 0002, 0003… o con una marca de tiempo de Unix, para indicar el orden en que se deben aplicar las migraciones a una base de datos.

3. Utilizas alguna herramienta o script para ejecutar o deshacer las declaraciones SQL en los archivos de migración secuenciales en tu base de datos. La herramienta lleva un seguimiento de las migraciones que ya se han aplicado, de manera que solo se ejecuten las declaraciones SQL necesarias.

Utilizar migraciones para gestionar el esquema de tu base de datos, en lugar de ejecutar manualmente las declaraciones SQL, tiene algunas ventajas:

1. El esquema de la base de datos (junto con su evolución y cambios) está completamente descrito por los archivos de migración SQL ‘up’ y ‘down’. Y debido a que estos son simplemente archivos regulares que contienen algunas declaraciones SQL, pueden incluirse y rastrearse junto con el resto de tu código en un sistema de control de versiones.

2. Es posible replicar el esquema de la base de datos actual de manera precisa en otra máquina ejecutando las migraciones ‘up’ necesarias. Esto es de gran ayuda cuando necesitas gestionar y sincronizar esquemas de bases de datos en diferentes entornos (desarrollo, pruebas, producción, etc.).

3. Es posible deshacer cambios en el esquema de la base de datos si es necesario aplicando las migraciones ‘down’ apropiadas.

6.1 Instalando la herramienta de migraciones

Para gestionar las migraciones SQL en este proyecto, vamos a utilizar la herramienta de línea de comandos “migrate” (que está escrita en Go).

Instrucciones detalladas de instalación para diferentes sistemas operativos se pueden encontrar aquí, pero en macOS deberías poder instalarlo con el siguiente comando:

$ brew install golang-migrate

Y en Linux y Windows, el método más sencillo es descargar un binario precompilado y moverlo a una ubicación en tu ruta del sistema. Por ejemplo, en Linux:

$ cd /tmp
$ curl -L https://github.com/golang-migrate/migrate/releases/download/v4.16.2/migrate.linux-amd64.tar.gz | tar xvz
$ mv migrate ~/go/bin/

Antes de continuar, asegúrate de que esté disponible y funcione en tu máquina probando ejecutar el binario de migrate con la bandera -version. Debería mostrar el número de versión actual similar a esto:

$ migrate -version
4.16.2

6.2 Trabajando con migraciones SQL

Lo primero que necesitamos hacer es generar un par de archivos de migración utilizando el comando migrate create. Si estás siguiendo los pasos, adelante y ejecuta el siguiente comando en tu terminal:

$ migrate create -seq -ext=.sql -dir=./migrations create_movies_table
/home/nahueldev23/Projects/greenlight/migrations/000001_create_movies_table.up.sql
/home/nahueldev23/Projects/greenlight/migrations/000001_create_movies_table.down.sql

En este comando:

• La bandera -seq indica que queremos utilizar numeración secuencial como 0001, 0002, … para los archivos de migración (en lugar de un sello de tiempo de Unix, que es el valor predeterminado).
• La bandera -ext indica que queremos dar a los archivos de migración la extensión .sql.
• La bandera -dir indica que queremos almacenar los archivos de migración en el directorio ./migrations (que se creará automáticamente si aún no existe).
• El nombre create_movies_table es una etiqueta descriptiva que damos a los archivos de migración para indicar su contenido.

Si miras en tu directorio de migraciones, ahora deberías ver un par de nuevos archivos de migración ‘up’ y ‘down’, algo así:

./migrations/
├── 000001_create_movies_table.down.sql
└── 000001_create_movies_table.up.sql

En este momento, estos dos archivos nuevos están completamente vacíos. Editemos el archivo de migración ‘up’ para incluir la declaración CREATE TABLE necesaria para nuestra tabla de películas, de la siguiente manera:

CREATE TABLE IF NOT EXISTS movies (
    id bigserial PRIMARY KEY,
    created_at timestamp(0) with time zone NOT NULL DEFAULT NOW(),
    title text NOT NULL,
    year integer NOT NULL,
    runtime integer NOT NULL,
    genres text[] NOT NULL,
    version integer NOT NULL DEFAULT 1
);

Observa cómo los campos y tipos en esta tabla son análogos a los campos y tipos en la estructura Movie que creamos anteriormente. Esto es importante porque significa que podremos mapear fácilmente los datos en cada fila de nuestra tabla de películas a una única estructura Movie en nuestro código en Go.

Si no estás familiarizado con los diferentes tipos de datos de PostgreSQL en la declaración SQL anterior, la documentación oficial proporciona una visión general completa. Pero lo más importante que hay que señalar es que:

• La columna id tiene el tipo bigserial, que es un entero de 64 bits autoincremental que comienza en 1. Esto será la clave primaria de la tabla.
• La columna genres tiene el tipo text[], que es un array de cero o más valores de texto. Es importante destacar que los arrays en PostgreSQL son consultables e indexables por sí mismos, algo que demostraremos más adelante.
• Es posible que recuerdes de “Let’s Go” que trabajar con valores NULL en Go puede ser incómodo, y siempre que sea posible, lo más fácil es simplemente establecer restricciones NOT NULL en cada columna de la tabla junto con valores DEFAULT apropiados, como hemos hecho anteriormente.
• Para almacenar cadenas, estamos utilizando el tipo text, en lugar de los tipos alternativos varchar o varchar(n). Si estás interesado, este excelente artículo explica por qué text es generalmente el mejor tipo de carácter para usar en PostgreSQL.

Muy bien, continuemos con la migración ‘down’ y agreguemos las declaraciones SQL necesarias para revertir la migración ‘up’ que acabamos de escribir.

DROP TABLE IF EXISTS movies;

El comando DROP TABLE en PostgreSQL siempre elimina cualquier índice y restricciones que existan para la tabla de destino, por lo que esta única declaración es suficiente para revertir la migración ‘up’. ¡Genial, ya tenemos nuestro primer par de archivos de migración listos para usar!

Mientras estamos en ello, creemos también un segundo par de archivos de migración que contengan restricciones CHECK para hacer cumplir algunas de nuestras reglas de negocio a nivel de base de datos. Específicamente, queremos asegurarnos de que el valor de tiempo de ejecución (runtime) siempre sea mayor que cero, el valor del año esté entre 1888 y el año actual, y que el array de géneros siempre contenga entre 1 y 5 elementos.

Nuevamente, si estás siguiendo estos pasos, ejecuta el siguiente comando para crear un segundo par de archivos de migración:

$ migrate create -seq -ext=.sql -dir=./migrations add_movies_check_constraints
/home/nahueldev23/Projects/greenlight/migrations/000002_add_movies_check_constraints.up.sql
/home/nahueldev23/Projects/greenlight/migrations/000002_add_movies_check_constraints.down.sql

Y luego agrega las siguientes declaraciones SQL para agregar y eliminar las restricciones CHECK, respectivamente:

ALTER TABLE movies ADD CONSTRAINT movies_runtime_check CHECK (runtime >= 0);
ALTER TABLE movies ADD CONSTRAINT movies_year_check CHECK (year BETWEEN 1888 AND date_part('year', now()));
ALTER TABLE movies ADD CONSTRAINT genres_length_check CHECK (array_length(genres, 1) BETWEEN 1 AND 5);

ALTER TABLE movies DROP CONSTRAINT IF EXISTS movies_runtime_check;
ALTER TABLE movies DROP CONSTRAINT IF EXISTS movies_year_check;
ALTER TABLE movies DROP CONSTRAINT IF EXISTS genres_length_check;

Cuando insertamos o actualizamos datos en nuestra tabla de películas, si alguna de estas verificaciones falla, nuestro controlador de base de datos devolverá un error similar a este:

pq: new row for relation "movies" violates check constraint "movies_year_check"

Nota: Es perfectamente aceptable que un solo archivo de migración contenga múltiples declaraciones SQL, como vemos en los dos archivos anteriores. De hecho, podríamos haber incluido las restricciones CHECK junto con la declaración CREATE TABLE en el primer par de archivos de migración, pero con el propósito de este post, tenerlas en un segundo archivo de migración separado nos ayuda a ilustrar cómo funciona la herramienta de migración.

6.2 Ejecutando migraciones

Si estás siguiendo los pasos, adelante y utiliza el siguiente comando para ejecutar las migraciones, pasando el DSN de la base de datos desde tu variable de entorno. Si todo está configurado correctamente, deberías ver una salida que confirma que las migraciones se han ejecutado con éxito. Similar a esto:

$ migrate -path=./migrations -database=$GREENLIGHT_DB_DSN up
1/u create_movies_table (38.19761ms)
2/u add_movies_check_constraints (63.284269ms)

Importante: Si estás utilizando PostgreSQL v15, es posible que recibas el error “error: pq: permission denied for schema public…” al ejecutar este comando. Si esto ocurre, es porque la versión 15 revoca el permiso CREATE de todos los usuarios excepto del propietario de la base de datos. Consulta esta publicación en StackOverflow para obtener más información.Para solucionarlo, establece el propietario de la base de datos como el usuario “greenlight”:

$ sudo -u postgres psql
psql (15.4 (Ubuntu 15.4-1.pgdg22.04+1))
Type "help" for help.
postgres=# ALTER DATABASE greenlight OWNER TO greenlight;
postgres=# GRANT CREATE ON DATABASE greenlight TO greenlight;

En este punto, vale la pena abrir una conexión a tu base de datos y listar las tablas con el comando meta \dt:

$ psql $GREENLIGHT_DB_DSN
Password for user greenlight:
psql (15.4 (Ubuntu 15.4-1.pgdg22.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
greenlight=> \dt
List of relations
Schema |
Name
| Type |
Owner
--------+-------------------+-------+------------
public | movies
| table | greenlight
public | schema_migrations | table | greenlight
(2 rows)

Deberías ver que la tabla “movies” se ha creado, junto con una tabla “schema_migrations”, ambas de las cuales son propiedad del usuario “greenlight”.

La tabla “schema_migrations” se genera automáticamente con la herramienta “migrate” y se utiliza para llevar un seguimiento de qué migraciones se han aplicado. Echemos un vistazo rápido:

greenlight=> SELECT * FROM schema_migrations;
 version | dirty
---------+-------
       2 | f
(1 row)

La columna “version” indica que nuestros archivos de migración hasta (e incluyendo) el número 2 en la secuencia se han ejecutado en la base de datos. El valor de la columna “dirty” es false, lo que indica que los archivos de migración se ejecutaron limpiamente, sin errores, y las declaraciones SQL que contienen se aplicaron con éxito en su totalidad.

Si lo prefieres, también puedes ejecutar el comando meta \d en la tabla “movies” para ver la estructura de la tabla y confirmar que las restricciones CHECK se crearon correctamente. Algo así:

greenlight=> \d movies
Table "public.movies"
 Column | Type                             | Collation | Nullable | Default
--------+----------------------------------+-----------+----------+---------
 id     | bigint                           |           | not null | nextval('movies_id_seq'::regclass)
 created_at | timestamp(0) with time zone    |           | not null | now()
 title  | text                             |           | not null |
 year   | integer                          |           | not null |
 runtime | integer                          |           | not null |
 genres | text[]                           |           | not null |
 version | integer                          |           | not null | 1
Indexes:
    "movies_pkey" PRIMARY KEY, btree (id)
Check constraints:
    "genres_length_check" CHECK (array_length(genres, 1) >= 1 AND array_length(genres, 1) <= 5)
    "movies_runtime_check" CHECK (runtime >= 0)
    "movies_year_check" CHECK (year >= 1888 AND year::double precision <= date_part('year'::text, now()))

6.2 Información adicional

6.2 Migrar a una versión especifica

Como alternativa para consultar la tabla schema_migrations, si deseas ver en qué versión de migración se encuentra tu base de datos, puedes ejecutar el comando de versión (version command) de la herramienta de migración, de la siguiente manera:

$ migrate -path=./migrations -database=$EXAMPLE_DSN version
2

También puedes realizar una migración hacia arriba o hacia abajo a una versión específica utilizando el comando goto:

$ migrate -path=./migrations -database=$EXAMPLE_DSN goto 1

6.2 Ejecutando migraciones con down

Puedes utilizar el comando “down” para revertir un número específico de migraciones. Por ejemplo, para revertir la migración más reciente, ejecutarías:

$ migrate -path=./migrations -database=$EXAMPLE_DSN down 1

Personalmente, prefiero utilizar el comando “goto” para realizar retrocesos (ya que es más explícito sobre la versión objetivo) y reservar el uso del comando “down” para revertir todas las migraciones, de la siguiente manera:

$ migrate -path=./migrations -database=$EXAMPLE_DSN down

¿Estás seguro de que quieres aplicar todas las migraciones hacia atrás? [y/N]
y
Aplicando todas las migraciones hacia atrás
2/d create_bar_table (39.988791ms)
1/d create_foo_table (59.460276ms)

Otra variante de esto es el comando “drop”, que eliminará todas las tablas de la base de datos, incluida la tabla “schema_migrations”, pero la base de datos en sí permanecerá, junto con cualquier otra cosa que se haya creado, como secuencias y enumeraciones. Debido a esto, utilizar “drop” puede dejar tu base de datos en un estado desconocido y desordenado, y generalmente es mejor usar “down” si deseas revertir todo

6.2 Arreglando errores en migraciones SQL

Es importante hablar sobre qué sucede cuando cometes un error de sintaxis en tus archivos de migración SQL, porque el comportamiento de la herramienta “migrate” puede ser un poco confuso al principio. Cuando ejecutas una migración que contiene un error, se aplicarán todas las declaraciones SQL hasta la errónea y luego la herramienta “migrate” se cerrará con un mensaje que describe el error. Similar a esto:

$ migrate -path=./migrations -database=$EXAMPLE_DSN up
1/u create_foo_table (36.6328ms)
2/u create_bar_table (71.835442ms)
error: migration failed: syntax error at end of input in line 0: CREATE TABLE (details: pq: syntax error at end of input)

Si el archivo de migración que falló contenía múltiples declaraciones SQL, es posible que el archivo de migración se haya aplicado parcialmente antes de que se encontrara el error. A su vez, esto significa que la base de datos está en un estado desconocido según la herramienta “migrate”.

En consecuencia, el campo “version” en la tabla “schema_migrations” contendrá el número de la migración que falló y el campo “dirty” se establecerá en true. En este punto, si ejecutas otra migración (incluso una migración “down”), obtendrás un mensaje de error similar a este:

Dirty database version {X}. Fix and force version.

Lo que debes hacer es investigar el error original y determinar si el archivo de migración que falló se aplicó parcialmente. Si fue así, debes retroceder manualmente la migración parcialmente aplicada. Una vez hecho esto, también debes ‘forzar’ el número de versión en la tabla “schema_migrations” al valor correcto. Por ejemplo, para forzar el número de versión de la base de datos a 1, deberías usar el comando “force” de la siguiente manera:

$ migrate -path=./migrations -database=$EXAMPLE_DSN force 1

Una vez que forzas la versión, la base de datos se considera “limpia” y deberías poder ejecutar las migraciones nuevamente sin problemas.

6.2 Archivos de migración remotos

La herramienta de migración también admite la lectura de archivos de migración desde fuentes remotas, incluidos Amazon S3 y repositorios de GitHub. Por ejemplo:

$ migrate -source="s3://<bucket>/<path>" -database=$EXAMPLE_DSN up
$ migrate -source="github://owner/repo/path#ref" -database=$EXAMPLE_DSN up
$ migrate -source="github://user:personal-access-token@owner/repo/path#ref" -database=$EXAMPLE_DSN up

Más información sobre esta funcionalidad y una lista completa de los recursos remotos admitidos se puede encontrar aquí.

6.2 Corriendo las migraciones cuando la app inicia

Si lo deseas, también es posible utilizar el paquete Go golang-migrate/migrate (no la herramienta de línea de comandos) para ejecutar automáticamente las migraciones de tu base de datos al iniciar la aplicación.

No utilizaremos este enfoque en este post, así que si estás siguiendo, por favor no cambies tu código. Pero, en líneas generales, el patrón se ve así:

package main

import (
    "context"
    "database/sql"
    "flag"
    "fmt"
    "log"
    "net/http"
    "os"
    "time"

    "github.com/golang-migrate/migrate/v4"            // Nueva importación
    "github.com/golang-migrate/migrate/v4/database/postgres" // Nueva importación
    _ "github.com/golang-migrate/migrate/v4/source/file" // Nueva importación
    _ "github.com/lib/pq"
)

func main() {
    ...
    db, err := openDB(cfg)
    if err != nil {
        logger.Error(err.Error())
        os.Exit(1)
    }
    defer db.Close()
    logger.Info("database connection pool established")

    migrationDriver, err := postgres.WithInstance(db, &postgres.Config{})
    if err != nil {
        logger.Error(err.Error())
        os.Exit(1)
    }

    migrator, err := migrate.NewWithDatabaseInstance("file:///path/to/your/migrations", "postgres", migrationDriver)
    if err != nil {
        logger.Error(err.Error())
        os.Exit(1)
    }

    err = migrator.Up()
    if err != nil && err != migrate.ErrNoChange {
        logger.Error(err.Error())
        os.Exit(1)
    }

    logger.Info("database migrations applied")
    ...
}

Aunque esto funciona, y puede parecer inicialmente atractivo, acoplar estrechamente la ejecución de migraciones con el código fuente de tu aplicación puede ser potencialmente limitante y problemático a largo plazo.

El artículo Decoupling Database Migrations from Server Startup proporciona una buena discusión sobre esto, y recomiendo leerlo si este es un tema que te interesa. Está enfocado en Python, pero no dejes que eso te desanime, los mismos principios se aplican en aplicaciones Go también.