# Using a copy of the dev database for local testing

Postgres can serialize the entire database (more or less) to a sql file using `pg_dump`.
This file can then be loaded up into a local copy of the database for testing.

## Quickstart

1. Copy dev.db

2. createdb shadowlms_development

3. psql shadowlms_development

4. run a bunch of roles

    ```
    create role shadowadmin; 
    create role shadow_lms;
    create role readonly;
    create role rdsadmin;
    ```
    
5. load the database (dev.db) with psql shadowlms_development < dev.db

## Basic case

This is an example using your local development databaes (useful for backups)

1. Dump a database to sql

    This creates the sql backup file named `dev.psql`

    `pg_dump shadowlms_development > dev.psql`

2. Create a new database for testing

    `createdb shadowlms_scratch` 

3. Load the sql dump file into the scratch database

    ```
    psql shadowlms_scratch < dev.psql
    ```

4. Change ther rails `database.yml` file to use the new database

    > NOTE: don't commit this change.

    ```yml
    development:
        adapter: postgresql
        database: shadowlms_scratch
    ```

    You will need to reload your rails server and rails console for them to use the new database.

    You can check if `rails console` is using the correct database by running ` ActiveRecord::Base.connection.current_database`

## Dumping / Loading app-dev

> NOTE: It probably isn't a good idea to dump app-dev frequently...

1. Start the vpn

2. Get the postgres connection string from the app-dev connection

    ```sh
    k8s # inside k8s
    k8s apps:config --list shadowlms_development # or whatever pod
    ```

    copy the value of the key called `DATABASE_URL`. It should be something like `postgres://<longhash>@<long_host>:5432/shadowlms_<something>`

3. Dump the database with pg_dump 

    `pg_dump <your-database-url> > app_dev.psql`

4. Load the database as above

    I ran into issues like *no such role "shadow_lms". For each of those, create the users in the psql command prompt. 
    It should be done something like this: 

    ```sql
    psql <your-local-database>
    create role shadowadmin; 
    create role shadow_lms;
    create role readonly;
    create role rdsadmin;
    ```

    remember to edit your database.yml file.

5. Run migrations

## Useful shell commands

I use these functions in my bash config to easily keep different databases on different branches.

```sh
# get the current database name
# usage: ,db_current 
# output: shadowlms_scratch
function ,db_current {
  cat ./config/database.yml | grep database: | head -n 1 | awk '{split($0,a," "); print a[2]}'   
}

# create a stash of a database 
# usage: ,db_stash `,db_current` my_stash
function ,db_stash {
  dbname=$1
  stashname=$2
  echo $dbname to $stashname

  pg_dump $dbname > ~/.local/$stashname.db
}

# load a stashed database from the command above
# usage: ,db_load `,db_current` my_stash
# note: this drops the prior database
function ,db_load {
  dbname=$1
  stashname=$2
  echo $dbname from $stashname
  dropdb $dbname
  createdb $dbname
  psql -U saahil.claypool $dbname < ~/.local/$stashname.db
  vim ./config/database.yml
}

```