PSQL (PostgreSQL)

PostgreSQL - (psql)

• database management system
• can hold multiple databases
• commands
  • \l -> list all databases
  • \c name_of_database -> connect to the database
  • \d -> display the tables in the database
  • \q -> exit the psql

SQL Commands (cont.) - Databases and Tables

• Databases
  • CREATE DATABASE database_name;
  • DROP DATABASE database_name; - delete the database
• Tables
  • CREATE TABLE table_name ( column1 column_type, column2 column_type_2, ...); -> add a new table
  • ALTER -> modify an existing table
  • DROP -> delete a table
• Rows
  • INSERT INTO table_name ( column1, column2...) VALUES (value1, value2...); -> add a new record (row)
  • UPDATE - modify a record
  • DELETE - removes a record

pg package

• allows us to connect and communicate with the DB from the code
• client = connection information
  • client.connect() -> connect to the DB
  • client.query() -> send SQL to the DB
  • client.end() -> disconnect from the DB

Seed File - a file to create mock data in our database that will be used by devs and QA for testing

• Creating Tables
  • SERIAL -> starts at 1 and goes up by one every time a new record is created
  • UNIQUE -> must be different from every other row
  • NOT NULL -> value can not be null or empty
  • PRIMARY KEY
    • only one allowed per table
    • value can not be null
    • value must be unique from all other rows
• Dropping Tables
  • IF EXISTS -> checks if the table exists before dropping