Database
Migrations
Software maintenance is a hard task, and you’ll probably need to patch your database to add, modify or remove some fields. The way to handle that with Pop is to use migrations.
You can create new migrations using fizz, a custom language describing the database changes in the most database-agnostic way; or use SQL statements if you prefer.
Writing Migrations
soda commands are embedded into the buffalo command, behind the pop namespace. So every time you want to use a command from soda, just execute buffalo pop instead.
Fizz Migrations
The soda command will generate SQL migrations (both the up and down) files for you.
$ soda generate fizz name_of_migration
Running this command will generate the empty following files:
./migrations/20220706213354_name_of_migration.up.fizz
./migrations/20220706213354_name_of_migration.down.fizz
The generated files are fizz files. Pop uses Fizz to generate migrations that are both easy to work with and work across multiple types of databases.
Further info about this command can be found by using the --help flag:
$ soda g migration --help
Generates Up/Down migrations for your database using fizz.
Usage:
soda generate fizz [name] [flags]
Aliases:
fizz, migration
Flags:
-h, --help help for fizz
Global Flags:
-c, --config string The configuration file you would like to use.
-d, --debug Use debug/verbose mode
-e, --env string The environment you want to run migrations against. Will use $GO_ENV if set. (default "development")
-p, --path string Path to the migrations folder (default "./migrations")
id that serves as the primary key, as well as created_at and updated_at datetime columns, so there is no need to create your own. These are the default, but you can override them if you want.
SQL Migrations
If you don’t want to use Fizz, or you have a complicated query you want to execute, you can use SQL.
To generate a new empty migration, use the following command:
$ soda generate sql name_of_migration
Running this command will generate the following files:
./migrations/20220706213354_name_of_migration.up.sql
./migrations/20220706213354_name_of_migration.down.sql
Further info about this command can be found by using the --help flag:
$ soda g sql --help
Generates Up/Down migrations for your database using SQL.
Usage:
soda generate sql [name] [flags]
Flags:
-h, --help help for sql
Global Flags:
-c, --config string The configuration file you would like to use.
-d, --debug Use debug/verbose mode
-e, --env string The environment you want to run migrations against. Will use $GO_ENV if set. (default "development")
-p, --path string Path to the migrations folder (default "./migrations")
Running Migrations
soda commands are embedded into the buffalo command, behind the pop namespace. So every time you want to use a command from soda, just execute buffalo pop instead.
Apply Migrations
Once migrations have been created they can be run with either of the following commands:
$ soda migrate
$ soda migrate up
Both commands are identical, one is shorter to type! Migrations will be run in sequential order.
Rollback a Migration
If you want to rollback the last applied migration, use the following command:
$ soda migrate down
More information about the migration command be found by running:
$ soda migrate --help
Runs migrations against your database.
Usage:
soda migrate [flags]
soda migrate [command]
Aliases:
migrate, m
Available Commands:
down Apply one or more of the 'down' migrations.
status Displays the status of all migrations.
up Apply one or more of the 'up' migrations.
Flags:
-h, --help help for migrate
Global Flags:
-c, --config string The configuration file you would like to use.
-d, --debug Use debug/verbose mode
-e, --env string The environment you want to run migrations against. Will use $GO_ENV if set. (default "development")
-p, --path string Path to the migrations folder (default "./migrations")
Use "soda migrate [command] --help" for more information about a command.
Targeting a Database
Since Pop v4.4.0, migrations can target a specific database, using a suffix. This allows to use commands specific to a dialect, only for a given database.
For instance, if you want to support both PostgreSQL and MySQL, you can create two migrations:
my-migration.mysql.up.sqlandmy-migration.mysql.down.sqlwill be used when migrating a MySQL database.my-migration.postgres.up.sqlandmy-migration.postgres.down.sqlwill be used when migrating a PostgreSQL database.
If no version for the dialect can be found, Pop will fallback to the non-suffixed version, if it exists.
Custom Migrations Table
By default, the applied migrations are tracked in the table schema_migration. This table is created by pop if it doesn’t exist.
In some cases, though, you may want to use a different name for this table. Since pop v4.5.0, you can customize the name of this table using the migration_table_name option. The example below will use migrations as the table name:
development:
dialect: "postgres"
url: "your_db_development"
options:
migration_table_name: migrations
Migrations Once Deployed
When you build your app, the migrations are stored inside your binary. Your binary has a hidden migrate command baked in that performs the migrations, just like it does when you use buffalo pop migrate:
$ ./myapp migrate
DEBU[2018-01-12T06:14:20Z] select count(*) as row_count from (SELECT schema_migration.* FROM schema_migration AS schema_migration WHERE version = ?) a $1=20171213171622
DEBU[2018-01-12T06:14:20Z] select count(*) as row_count from (SELECT schema_migration.* FROM schema_migration AS schema_migration WHERE version = ?) a $1=20171213172104
DEBU[2018-01-12T06:14:20Z] select count(*) as row_count from (SELECT schema_migration.* FROM schema_migration AS schema_migration WHERE version = ?) a $1=20171213172249
DEBU[2018-01-12T06:14:20Z] select count(*) as row_count from (SELECT schema_migration.* FROM schema_migration AS schema_migration WHERE version = ?) a $1=20171213173148
DEBU[2018-01-12T06:14:20Z] select count(*) as row_count from (SELECT schema_migration.* FROM schema_migration AS schema_migration WHERE version = ?) a $1=20171219070903
DEBU[2018-01-12T06:14:20Z] select count(*) as row_count from (SELECT schema_migration.* FROM schema_migration AS schema_migration WHERE version = ?) a $1=20171219071524
0.0010 seconds