# Moving your database

Copy a PostgreSQL or MySQL database, such as the one of a Vapor application, into the database of an environment with brewless db:import.


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.

```bash
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.

| Source | Destination | How | When it stops halfway |
| --- | --- | --- | --- |
| PostgreSQL | Serverless SQL Database, or Managed Database for PostgreSQL | `pg_dump` into `psql`, in one transaction | the destination is as it was |
| MySQL or MariaDB | Managed Database for MySQL | `mariadb-dump` into `mariadb` | what was copied stays |
| MySQL or MariaDB | Serverless SQL Database, or Managed Database for PostgreSQL | converted with `pgloader` | what was copied stays |
| PostgreSQL | Managed Database for MySQL | refused | |

**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.

| Laravel variable | Source: how you give it | Example at Vapor |
| --- | --- | --- |
| `DB_CONNECTION` | the scheme of the argument (`postgres://`, `mysql://`), `SOURCE_DB_CONNECTION` or `--from-env` | `mysql` |
| `DB_HOST` | the argument, `SOURCE_DB_HOST` or `--from-env` | `shop-db.abc123.eu-west-1.rds.amazonaws.com` |
| `DB_PORT` | the argument, `SOURCE_DB_PORT` or `--from-env`; left out it is 5432 or 3306 | `3306` |
| `DB_DATABASE` | the path of the argument, `SOURCE_DB_DATABASE` or `--from-env` | `vapor` |
| `DB_USERNAME` | the user of the argument, `SOURCE_DB_USERNAME` or `--from-env` | `vapor` |
| `DB_PASSWORD` | `SOURCE_DB_PASSWORD`, `--from-env` or asked | |
| `DB_SSLMODE` | `?sslmode=` in the argument, `SOURCE_DB_SSLMODE` or `--from-env`: `require` (the default), `verify-full` or `disable` | `require` |

`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`:

```bash
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

```bash
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](/applications/moving-files). Then deploy, and move your domain (see [Moving your domain to Brewless](/getting-started/moving-your-domain)). 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.