# Database Setup Cheatsheet
[TOC]

## Creating a database on the command line:

1) Open up your client:

pgcli

2) Decide on a good name. I'm going to use '**superbase**' 😎
2) inside your pgcli/psql shell run this command:

CREATE DATABASE superbase;

3) Grant all priviledges on the database to your preferred user

GRANT ALL PRIVILEGES ON DATABASE superbase TO jessie;

## Adding your database url to your config.env

Put the `config.env` file in the root and **make sure it’s in your gitignore**

Follow this pattern:


DB_URL = postgres://jessie:password@localhost:5432/superbase

+ **no semicolon at the end**
+ **change the username, password AND database to your own**

## Filling your database with stuff (i.e. tables)

1. Write some nice SQL to create tables and insert some data. Put this in a file called `db_build.sql ` (ideally in `src/database`)
2. You now have 2 options.

#### Proper way:
a) Write your `db_connection.js` file. You will need this to write the build script, and in the rest of your app.

b) Write a `db_build.js` file that runs the SQL. You will need this anyway for writing tests so you might as well do it in setup.

c) Run the build file in your terminal (NOT inside psql/pgcl - just regular terminal):

node src/database/db_build.js

^or whatever the path is to your file from where you are.

d) Rectify any errors (it usually tells you where the error was)

#### Quick hacky way:
a) Connect to your database

pgcli superbase 

(or if you are already in pgcli/psql)

\c superbase

b) Copy paste all that SQL into the shell.

c) Rectify any errors (it usually tells you where the error was)

### Whichever route you do:

3. Check it worked!

SELECT * FROM hobbies;

or `\d` will give you a list of tables in the database you are connected to

## Setting up a separate TEST Database

You can use your local database as a test database, if you want. But it will get wiped every time you run your tests.

If you want to add a special test DB, it’s a lot like setting up a normal database.

1. Follow the first section in this sheet - [Creating a database on the command line](#Creating-a-database-on-the-command-line) - but give the database a different name, like **superbaseTest** 😎
2. Add the new url to your config.env - so you have TWO!

TEST_DB_URL = postgres://jessie:password@localhost:5432/superbaseTest

3. Adapt your `db_connection.js` file to use the right database url in the right situation (test vs running local). [This workshop](https://github.com/foundersandcoders/ws-database-testing/) has a reminder!

4. You’re ready to rock and roll with tests. Checkout [this workshop](https://github.com/foundersandcoders/ws-database-testing/) if you cannot remember how to write a database test!

## Setting up a HEROKU Database

[This workshop](https://github.com/foundersandcoders/db-morning-challenge) has great instructions on how to set up a database on heroku. Only one person per team needs to add this.

When building the tables _inside_ the database, you may need to temporarily adapt the `db_connection.js` file to use the URL generated by heroku, before running `node src/database/db_build.js`. This url can be found easily in the dashboard > settings > config vars.

You only need to run this build once! Make sure you’re working with your local database or test url after you’ve done this.

Ask a mentor if you’re stuck!

## Getting Travis to pass your database tests!

If you want to use travis, you need configure it to build it’s own databases so it can access them in the tests! You should use put a `.travis.yml` file into the root of your project, set up like this.

services:
  - postgresql
  
env:
  - DB=postgres
  - DB_URL=postgres://admin:password@localhost:5432/superbase
  - TEST_DB_URL=postgres://admin:password@localhost:5432/superbaseTest

language: node_js
node_js :
 - "node"
after_success: npm run coverage

before_script:
  - psql -c "CREATE DATABASE superbase;" -U postgres
  - psql -c "CREATE DATABASE superbaseTest;" -U postgres
  - psql -c "CREATE USER admin WITH PASSWORD 'password';" -U postgres
  - psql -c "ALTER DATABASE superbase OWNER TO admin;" -U postgres
  - psql -c "ALTER DATABASE superbaseTest OWNER to admin;" -U postgres

**Some checks when copy pasting the above!**
+ This file sets separate DB_URL and TEST_DB_URL environment variables. Check you’re using those _same variable names_ in your db_connection and config.env. If you’ve called your variables different things (or only have one) e.g. you used DB_TEST_URL instead of TEST_DB_URL - amend the yml file so they're the same.
* Remove the line `after_success: npm run coverage` if you don’t have a ‘coverage’ script in your package.json

## Useful PSQL Resources
- [TutorialsPoint](https://www.tutorialspoint.com/sql/index.htm)
- probably the easiest to navigate
- [PostgreSQL Tutorial](http://www.postgresqltutorial.com/)
- also easy to navigate and the nicest to look at
- [PostgreSQL Documentaion Version-11](https://www.postgresql.org/docs/11/sql.html)
- The actual documentaion, great if you want more detail and up-to-date info
- but a bit hard to look through.
- You probably want Section "7.Queries" but other sections are also useful
- Section "8.Data Types" should be useful for writing your db_build.sql file

- [PostgreSQL Exercises](https://pgexercises.com/gettingstarted.html)
- Great place to practice SQL queries
- Similar excercises to the query workshops but with more thorough explanation of answers and helpful schema diagrams