# Migrations and Seeds Instructor Notes

## Objectives
* Set up database connection using knex in a brand new express project
* Create migrations
* Create seeds

## How do you setup a database connection in nodejs
* Create empty npm project
* Install `knex` and `pg`
* Create `knexfile.js` and add configuration. Where would you get that configuration from?
* Create `db` folder
* Create `index.js` file. What is contained in this file? Where would you find an example of it?
* Add `knex` script to `package.json`
* Create database

## How do you create migrations
* Create a migrations for school-api. What is the command to create a migration

| instructors                    |
|--------------------------------|
| id serial                      |
| name varchar(255) not nullable |
| timestamps

| cohorts                        |
|--------------------------------|
| id serial                      |
| name varchar(255) not nullable |
| timestamps                     |

| students                                             |
|------------------------------------------------------|
| id serial                                            |
| name varchar(255) not nullable                       |
| cohort_id integer references cohorts.id not nullable |
| timestamps                                           |

| instructors_cohorts                                           |
|---------------------------------------------------------------|
| id serial                                                     |
| instructors_id integer references instructors.id not nullable |
| cohorts_id integer reference cohorts.id not nullable          |
| timestamps

* What is the command to run a migration?
* Would a migration run more than once?
* How do you rollback migrations?
* In what order do migrations run?

## How do you create seeds?
* Create seeds for `school-api`. What is the command to create a seed file

### instructors
| id | name            |
|----|-----------------|
| 1  | Ada Lovelace    |
| 2  | Alan Turing     |
| 3  | Grace Hopper    |
| 4  | Jon Von Neumann |

### cohorts

| id | name          |
|----|---------------|
| 1  | Tiger Sharks  |
| 2  | Velociraptors |
| 3  | Hawks         |
| 4  | Lemurs        |

### students

| id | name    | cohort_id |
|----|---------|-----------|
| 1  | Abe     | 2         |
| 2  | Bryan   | 2         |
| 3  | Claudia | 1         |
| 4  | Daniel  | 3         |
| 5  | Dustin  | 4         |
| 6  | Gavin   | 1         |
| 7  | Luda    | 4         |
| 8  | Mark    | 4         |
| 9  | Sunil   | 3         |
| 10 | Vika    | 3         |


### instructors_cohorts

| id | instructors_id | cohorts_id |
|----|----------------|------------|
| 1  | 1              | 4          |
| 2  | 2              | 1          |
| 3  | 3              | 3          |
| 4  | 4              | 2          |

* Reset the sequence for each table
```
.then(() => {
  // reset sequence
  return knex.raw(`SELECT setval('${TABLE_NAME}_id_seq', (SELECT MAX(id) FROM ${TABLE_NAME}));`)
})
```

* What happens when you run seeds more than once?
* How can you fix constraint errors?
  * Add on cascade delete
  * Create a seed file to delete in the right order
* How do you roll back seeds?
* In what order do seeds run? Does it matter?

## Closing questions
* Where do the migrations folder and seed folder get configured?