# Database

If you have used the rails activerecord migration system, this database migration system will be very familiar to you. It is a simple way to keep track of changes to your database schema, and to easily make changes to it.

You can find the source code for these commands in the `migrate/src` directory.

## Setup (Docker)

### Connection

```
Host: localhost:5434
User: wavtool
Password: wavtool
Database: wavtool
```

### Setup

When running `yarn up` from the root folder, the database will automatically be created and migrated.

To get data into your database, you can run `yarn migrate clone-prod`. You can find more information about this command in the "Restoring database from prod" section below.

## Setup (OLD)

> Note: This setup is outdated because the new environment uses Docker for local development. The database should be set up automatically when running `yarn up` from the root folder.

### Connection

```
Host: localhost:5432
User: <your username>
Database: wavtool
```

### Setup

In order for wavtool to work locally, you need to have the database set up.

1. Install postgresql
2. Acquire a database dump from a fellow developer

   - This is a `.sql` file that contains the database dump

3. Run `pg_restore -U <local_user> -d wavtool database_dump.sql`

   - This creates a database called `wavtool` in your local postgresql instance

4. Check to make sure the database is there
5. Check the `.env` file in the `server` directory

   - Make sure the `ENVIROMENT` variable is set to `development`
   - Make sure the database settings are correct
   - ```
      # Example database settings
      DATABASE_USER=xun
      DATABASE_HOST=localhost
      DATABASE_NAME=wavtool
      DATABASE_PASSWORD=
      DATABASE_PORT=5432
      ENVIRONMENT=development
     ```

6. Refer to the main server/README.md on how to run the server

Run `yarn` in the `migrate` directory to install dependencies.

If you haven't yet created a database for local development, you should do so now using `psql` or your favorite PostgreSQL GUI. For example:

```
user@your-machine % psql
psql (14.8)
Type "help" for help.

user=# CREATE DATABASE wavtool_local_dev;
CREATE DATABASE
user=# \q
user@your-machine %
```

The `migrate` tool needs to know which PostgreSQL instance and database to run migrations against. This is configured with the standard PostgreSQL environment variables. The tool reads `migrate/.env` to populate these variables (Note: Values set in the environment will override values in `migrate/.env`).

Before running any of the commands below, populate `migrate/.env` like this:

```
# This assumes you are using the docker dev environment
PGHOST=localhost
PGDATABASE=wavtool
PGUSER=wavtool
PGPASSWORD=wavtool
PGPORT=5434
```

Documentation for all PostgreSQL connection environment variables is available here: https://www.postgresql.org/docs/current/libpq-envars.html

## Creating a migration

Run the following command in the `migrate` directory:

```bash
   yarn migrate create some_name_here
```

Every migration will be given a timestamped name, and will be placed in the `migrations` directory. The migration will be empty, with a --up and --down section. SQL following `--up` will be run when the migration is applied, and SQL following `--down` will be run when the migration is rolled back. The `--down` section is optional, but migrations without it cannot be rolled back.

**Best practices:**

- Migrations should not modify the data and should only modify the schema. If you need to modify the data, you should create a separate script to do so.
- If there is a conflict with a migration that was added to master after yours, you should rename your migration to a more current timestamp.

## Running migrations

Run the following command in the `migrate` directory:

```bash
  yarn migrate sync
```

This will find the earliest-applied migration in the database's `migrations` table that is inconsistent with the `migrations` directory. If it exists, this migration and all migrations applied after it are rolled back in the reverse order of application. Finally, any migrations in the `migrations` directory that haven't been applied are applied in order of the timestamps that appear in their names.

You can use `sync` to quickly update your local development database when switching branches.

## Rolling back migrations

To roll back a migration, simply delete the corresponding file in the `migrations` directory and run `sync` as described above. Any migrations applied after the deleted one will be rolled back and re-applied.

Note: Migrations are rolled back because the down section of the deleted migration is stored in the database.

## Restoring database from prod

Edit your `migrate/.env` file to include the following variables:

```
PRODUCTION_PGHOST=database-1.ctreuprwt6kf.us-east-1.rds.amazonaws.com
PRODUCTION_PGPASSWORD=...
```

Ask someone on the team for the production database password, and to add your IP address to the AWS security group that
restricts remote access to the production database.

Run the following command in the `migrate` directory to clone the production database:

```bash
  yarn migrate clone-prod
```

This script will cause your local PostgreSQL instance to connect to the production database and pull down a subset of the data necessary to make local development work.