Backend - Create Database & Seed

[Jcloghesy]

• [ ] create database
• [ ] create tables
• [ ] Link tables
• [ ] seed database

[Jcloghesy]

3.0 DATABASE SETUP/WORK (Assuming pg is installed)

3.1 WORKSPACE SETUP: Start Postgres Server & PostgreSQL Command Line Shell

• [ ] 3.1.1 Postgres Server START-> startpostgres

  In WSL2, start PostgreSQL service prior to running PSQL to start the PostgreSQL server enter command into WSL2 command line interface:

• [ ] 3.1.2 PostgreSQL Command line Shell START-> psql

  psql is the PostgreSQL command line shell it allows us to interact with the database from our terminal (should see labber=#) as this is the default user (& password fyi)

3.2 CREATE DATABASE: Create DB

• [ ] 3.2.1 Create [name] Database CREATE DATABASE [name];
• [ ] 3.2.2 Test by Connecting to Database (in psql (labber=# )

  \c [name]
  ('you are now connected to database "[name]" as user 'labber")

3.3 CREATE TABLES & COLUMNS USING SCHEMA: "\i "

• [ ] 3.3.1 Run scheme file to Create Tables and Columns, Joins

lightbnb=# \i migrations/01_schema.sql psql: migrations/01_schema.sql:5: NOTICE: table "users" does not exist, skipping DROP TABLE CREATE TABLE….

• [ ] 3.3.2 Test Query to See if Tables Created (\dt)

Ensure: Tables created / exist in database Enter \dt at the psql prompt.

• You should run it at least once per table.
• To run a schema file against a database, make sure you are in the correct directory first and that you have a psql session initiated in the directory.
• For example, here is how you would run the command from your project's root directory:

[Jcloghesy]

TODO

Split database schema into separate files, one per table See db folder for pre-populated examples

• [ ] (table definitions) and
• [ ] seeds (inserts)

Warnings & Tips

• Do not edit the layout.css file directly, it is auto-generated by layout.scss.

• Split routes into their own resource-based file names, as demonstrated with users.js and widgets.js.

• Use helper functions to run your SQL queries and clean up any data coming back from the database.

• See db/queries for pre-populated examples.

• Use the npm run db:reset command each time there is a change to the database schema or seeds.

• It runs through each of the files, in order, and executes them against the database.

• Note: you will lose all newly created (test) data each time this is run, since the schema files will tend to DROP the tables and recreate them.