Moving your database

An application that comes from Laravel Vapor, or from any other host, brings its data along in a PostgreSQL or MySQL database. brewless db:import copies that database into the database of an environment: you say where the data is now and give a user that may read it; where it goes follows from the environment.

brewless db:import test mysql://brewless-import@shop-db.abc123.eu-west-1.rds.amazonaws.com/vapor

What does db:import do?#

It copies the tables of the source database, with their rows, indexes, foreign keys and sequences, into the database the environment's application signs in to: the one named by DB_CONNECTION, DB_HOST, DB_DATABASE and DB_USERNAME in its variables. That is the database Brewless set up for it, or one of your own whose DB_* values you pushed with brewless env:push. Nothing at the source is changed. It signs in to the destination as the application does, so the tables belong to the application's own user and later migrations can change them.

The destination has to be empty. Before it writes anything the copy counts the tables in the destination; when there are any, it stops and writes nothing. --replace removes what the destination holds first: for PostgreSQL to PostgreSQL in the same transaction as the copy, so a copy that fails leaves the old tables in place; otherwise just before the copy. On production the command asks you to type the name of the environment before it replaces anything.

The destination of a new environment is empty until its first deploy, whose release commands run your migrations. Copy before that deploy, or copy with --replace afterwards.

Which value goes where#

The names are those of Laravel's database connection in config/database.php. For the source you give them; the destination is the environment's own variables.

SOURCE_DATABASE_URL, or DB_URL in the file of --from-env, takes the place of the first five. When a value is given in more than one way, the first of these wins: the argument, then SOURCE_DB_*, then --from-env, and last the question in the terminal. The password is never part of the argument or an option, where it would end up in your shell's history, and the command never prints it. DB_* variables of your own shell are not used: they belong to the application you are working on.

Note

On Vapor the DB_* values are not in vapor env:pull: Vapor adds them to the environment when it deploys. The host, user and password are on the database's page in the Vapor dashboard. Better still, make a user for the move only, with vapor database:user <database> brewless-import, and remove it afterwards.

Reaching the source#

By default the copy runs in your own Scaleway project, so the source has to be reachable from the internet: a Vapor database that is publicly accessible, an RDS database with a public address, or a database at any other host that takes connections from outside. A source on a private network, such as a private Vapor database or any Aurora Serverless database at Vapor, is not reachable from there. Open a tunnel to it from your own machine, through the jumpbox of that network, and copy with --local from 127.0.0.1:

ssh -N -L 3307:shop-db.abc123.eu-west-1.rds.amazonaws.com:3306 ec2-user@<jumpbox> &
brewless db:import test mysql://vapor@127.0.0.1:3307/vapor?sslmode=disable --local

The user needs to be able to read every table. For MySQL triggers and routines it also needs to see their definition: the user that made them may, another one needs SHOW_ROUTINE (MySQL 8.0.20 and later).

From MySQL to PostgreSQL#

A Serverless SQL Database speaks PostgreSQL. A MySQL database from Vapor is converted on the way with pgloader, in a form a Laravel application on PostgreSQL expects:

• every table lands in the schema public;
• the primary key of a table is named <table>_pkey, and every other index keeps its MySQL name, so a later migration that drops one by name finds it; a unique index becomes a unique constraint, as Laravel makes it;
• tinyint(1) becomes boolean; datetime and timestamp become timestamp without time zone, and a date of zeroes becomes NULL; an enum or set becomes varchar; json stays json; an unsigned integer becomes the next larger integer, and an unsigned bigint stays bigint;
• auto-increments become sequences, set to continue after the highest key.

What it does not convert: views, triggers and stored routines. Make those again in a migration.

Warning

Your application has to work on PostgreSQL too. MySQL compares text without regard to case and PostgreSQL does not, so where('email', $email) can find nothing where it found a row before. Raw SQL, GROUP BY with columns that are not grouped and MySQL's JSON functions also behave differently. Copy into test first and try the application there.

From MySQL to MySQL#

A MySQL database copies into a Managed Database for MySQL that you named when you set the environment up. Views, triggers and routines come along, made under the destination's user instead of the one at the source.

A Managed Database keeps a binary log, and then MySQL only takes triggers and routines from a user with SUPER, which a managed database does not hand out. When the source has any, the copy checks this first and stops before it writes anything: set log_bin_trust_function_creators to 1 in the settings of the Managed Database at Scaleway, and copy again.

In your project, or from your machine#

By default the copy runs in your own Scaleway project. Brewless runs a serverless job there with pg_dump, mariadb-dump or pgloader: the data goes from your old provider straight to your database at Scaleway, never through Brewless and not through your machine, and you can close your laptop while it runs. Both passwords, the source's and that of the environment's own database, reach the job in one secret of your Secret Manager (<application>-<environment>-db-import, under the path /brewless); when the copy ends, however it ends, that version of the secret is deleted. Brewless itself keeps no password: the operation only notes which version of the secret it was. A copy may run for up to a day, and only one runs into an environment at a time. The destination has to be reachable from the internet too: a Managed Database that is only on a Private Network is copied into with --local.

With --local the copy runs on your machine, in Docker, with the same image and the same script as the job: the data passes through your machine, and the terminal has to stay open. The source can then be a tunnel on 127.0.0.1. Your machine needs Docker, and needs to reach the destination; the passwords stay on your machine. Brewless only notes in the audit trail that a copy started and how it ended.

Following a copy#

The command follows the copy as it goes, table by table. Stop following with Ctrl+C: a copy in your project goes on, and

brewless db:import-status test

shows the last copy into that environment's database, while it runs and after it ended. The copy is also on the environment's page in the console, among its operations, where it can be cancelled. Cancelling stops the job and deletes the passwords.

--dry-run copies nothing: it signs in to both databases, counts the tables and the size of the source and the tables in the destination. From PostgreSQL to PostgreSQL it also makes the source's tables in the destination and rolls them back, which shows whether they fit.

When a copy stops, the command says at which side and the last error the tools gave, without the values of rows; the whole log is in Cockpit, in your Scaleway project.

Moving a Vapor application#

1. Copy into test and try your application there: brewless db:import test ….
2. Copy into production before its first deploy, or with --replace after it.
3. Just before you move your domain, put the application at Vapor in maintenance mode with vapor down production, copy once more with --replace, and copy your files once more with storage:import. Then deploy, and move your domain (see Moving your domain to Brewless). No writes are lost in between.
4. Remove the user you made at Vapor for the move.

At Vapor, AWS bills the data that leaves it; --dry-run says how much there is first.