CREATE USER Mary WITH superuser PASSWORD 'mypassword';

    CREATE USER Mary WITH PASSWORD 'MyPassword';

    CREATE DATABASE clues;

If the user 'Mary' doesn't exist yet, create her.

    CREATE USER Mary WITH PASSWORD 'MyPassword';
    CREATE DATABASE clues WITH OWNER Mary;

    CREATE TABLE drinks (
        id serial,
        name VARCHAR(50),
        ounces INTEGER,
        PRIMARY KEY (id)
     );

    CREATE TABLE menu (
        id serial,
        name VARCHAR(50),
        drink_id INTEGER,
        PRIMARY KEY(id),
        FOREIGN KEY (drink_id) REFERENCES drinks(id);

Use the describe people command:

\d people;

Use the describe users command:

\du;

Use the describe tables command

    \dt;  //describe tables
    \d <<tableName>> //describe just this table

Use the list database command:

    \list;

    SELECT CURRENT_USER;

    CREATE DATABASE <<databaseName>>;
    REVOKE CONNECT ON DATABASE <<databaseName>> FROM PUBLIC;
    GRANT CONNECT ON DATABASE <<databaseName>> TO <<UserName>>;

If the table has any dependencies (i.e. another table references it) you MUST delete the table that references it first.

    DROP TABLE <<tableName>>;

If the user has any references, like "GRANT CONNECT ON DATABASE ...", you must REVOKE CONNECT ON DATABASE first.

    DROP USER <<UserName>>;

    psql -u -W <<userName>> <<databaseName>>;

No! "" is not equivalent to NULL.

Within the table, or using constraint syntax:

    id serial PRIMARY KEY,

    id serial,
    PRIMARY KEY(id);

    INSERT INTO pets (name, age, owner_id) VALUES
    ('Fido', 4, 3),
    ('Rover', 2, 3),
    ('Spot', 1, 2);

    SELECT city, state, population_estimate_2018
    FROM cities
    WHERE state NOT IN ('New York', 'California', 'Texas');

    SELECT city, (population_estimate_2018/1000000 || ' million') as population
    FROM cities;

    ALTER TABLE <<table_name>> old_name TO new_name;

An inner join is the default join. It returns only those rows that have matching values in both tables.

It returns ALL rows from the LEFT table, whether there is a match on the RIGHT table. If there is no match on the RIGHT table, all the right table's columns will be NULL;

Similar to the LEFT, or LEFT OUTER JOIN, it returns all records from the RIGHT table, and the matched records from the left table. If no records match on the left table, those columns will have NULL values.

It returns ALL records. Only the records that match will have values in the columns. If the records did not match, that table will have NULL values in those columns.

A LEFT JOIN:

FULL OUTER JOIN

JOIN, or INNER JOIN

RIGHT JOIN

    CREATE INDEX idx_addresses_city_post_code ON table_name (city_id, postal_code);

A partial index uses a subset of a table defined by a conditional expression:

    CREATE INDEX index_name ON table_name WHERE (conditional_expression);  OR
    CREATE INDEX addresses_phone_index ON addresses (phone_number) WHERE (city_id = 2);

Indexes can help us optimize our queries for faster performance. Use them:

• When working with a large set of data
• When your table does NOT have frequent, large-batch update or insert operations
• When your table does NOT have many NULL values
• When columns are NOT frequently manipulated

B-tree

For every update operation (DELETE/UPDATE/INSERTIONS) the index must be updated.

1. Use EXPLAIN and compare the benchmark values against different queries to see which is more efficient.
2. Use EXPLAIN and ANALYZE to yield a total runtime in ms for your query. This also shows you Bitmap Heap Scan, Bitmap Index Scan, and Index Scan results.

A self join is when you join a table onto itself. Typically used to compare rows within the same table.

    SELECT select_list
        FROM table_name t1
        INNER JOIN table_name t2 ON join_predicate;

   const { Pool } = require("pg");

   const pool = new Pool({
       database: "recipe_box",
       password: "foo"
   });

   (async () => {
        const results = await pool.query("INSERT INTO foo VALUES ($1, $2, $3, $4)",
                                        [1, 'Hunter Valley', 't', 200.00]);
        console.log(results);
        pool.end();
   })();

The RDBMS is a software application that you run that your programs can connect to so that they can store, modify, and retrieve data.

Relational Data is data that is related in some way. For instance user data in a photo sharing application might have a relationship with the photo data and photo data and user data might both have a relationship with comment data.

There are three types of relationships you can have:

1. One to One
2. One to Many
3. Many to Many

A database is a collection of structured data stored in a Database "System" or "Server"

A database can have many tables, a table is made up of several Columns . Each Column is of a certain type.A table Schema describes the columns in a table. Tables also contain Rows which hold the actual data for the table.

A primary key is a single unique column in a database table.

A foreign key is an integer column in a table which holds the value of a matching primary key from another table. A foreign key constraint ensures that the id stored in the foreign key column is a valid primary key in the related table.

psql -U <database username;> -h <hostname> <database name>

• -U specifies psql user (defaults to the same as your unix username)
• -h does not have a default value. If you leave it out, psql connects through a unix socket instead of connecting to a hostname through the network
• <database name> defaults to the same as whatever <database username> is

You can set these two environment variables ( PGHOST and PGUSER in your shell to override the default behavior -U and -h

In your .bash_profile or .zshrc file export your localhost and username as follows:

    export PGHOST=localhost
    export PGUSER=<userName>

\du : describe user

    postgres-# \du
    List of roles
    Role name | Attributes                                                |  Member of
    ------------------------+------------------------------------------------------------+-----------
    postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
    my_user  |                                                            | {}

CREATE USER <database username> WITH PASSWORD <password> <attributes>

Attributes can be things like SUPERUSER or permissions like CREATEDB or any of the other attributes listed in the documentation

CREATE DATABASE <database name> WITH OWNER <database username>

\l : list command

    Name             | Owner                  | Encoding | Collate    | Ctype      | Access privileges
    -----------------+------------------------+----------+------------+------------+-----------------------
    aa_times         | aa_times               | UTF8     | en_US.utf8 | en_US.utf8 |
    postgres         | postgres               | UTF8     | en_US.utf8 | en_US.utf8 |
    project_manager  | project_management_app | UTF8     | en_US.utf8 | en_US.utf8 |
    recipe_box       | recipe_box_app         | UTF8     | en_US.utf8 | en_US.utf8 |
    template0        | postgres               | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgress +
                     |                        |          |            |            | postgress=CTC/postgres
    template1        | postgres               | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgress +
                     |                        |          |            |            | postgress=CTC/postgres

    (6 rows)

    CREATE TABLE <table name> (
        <column name> <data type>,
        <column name> <data type>,
        ...
        <column name> <data type>
    );

\dt : describe tables

    aa_times=# \dt
    public | people   | table | aa_times
    public | sections | table | aa_times
    public | stories  | table | aa_times

• SERIAL
• VARCHAR
• TEXT
• NUMERIC
• INTEGER
• BOOLEAN
• TIMESTAMP

Unique constraints ensure that the data contained in a column is unique among all the rows in the table.

    CREATE TABLE products (
        id integer UNIQUE,
        name text,
        price NUMERIC
    );

A not-null constraint simply specifies that a column must not assume the null value.

    CREATE TABLE products (
        id integer NOT NULL,
        name text NOT NULL,
        price NUMERIC
    );

    CREATE TABLE products (
        id SERIAL PRIMARY KEY,
        name text,
        price numeric
    );

    CREATE TABLE products (
        id SERIAL,
        name text,
        price numeric,
        PRIMARY KEY(id)
    );

A foreign key constraint specifies that the values in a column (or a group of columns) must match the values appearing in some row of another table.

Use the REFERENCES keyword:

To reference the products table from the orders table:

    CREATE TABLE products (
        id SERIAL PRIMARY KEY,
        name text,
        price numeric
    );

    CREATE TABLE orders (
        id SERIAL PRIMARY KEY,
        product_id integer REFERENCES products (product_id),
        quantity integer
    );

 
    CREATE TABLE ORDERS (
        id SERIAL,
        product_id integer,
        quantity integer,
        PRIMARY KEY (id),
        FOREIGN KEY (product_id) REFERENCES products(id)
    );

Both! Although, SELECT name, quantity FROM orders; is the convention to use uppercase SQL keywords

If your tables and/or rows are using capital letters, you MUST quote them in your query. Otherwise PostgresSQL will first transform the table name/table columns to lower case, then perform the query.

    SELECT population
    FROM countries
    WHERE name = 'France';

    UPDATE countries
    SET population = 1000
    WHERE name = "Vatican City";

If you only wanted to update one country with that population, you would have actually updated ALL countries with that population. Remember to use the WHERE clause to select the country you want to update.

    DELETE FROM planets
    WHERE name = 'Pluto';

    SELECT player_name
    FROM players
    JOIN teams ON teams.id = players.team_id
    WHERE team_name = 'Lakers'

    INSERT INTO nfl_players
    VALUES(DEFAULT, 'Joe Burrow', 'Bengals');

    INSERT INTO nfl_players (playerName, team)
    VALUES('Joe Burrow', 'Bengals');

    INSERT INTO nfl_players (playerName, team)
    VALUES
        ('Joe Burrow', 'Bengals'),
        ('Tom Brady', 'Buccaneers'),
        ('Drew Brees', 'Saints');

1. Check to make sure the table exists, and if it doesn't create the table.
2. Use your normal insert statements.

    CREATE TABLE IF NOT EXISTS bands (
        id SERIAL,
        name VARCHAR(50) NOT NULL,
        vocalist VARCHAR(50),
        PRIMARY KEY (id)
    );

    INSERT INTO bands
    VALUES (DEFAULT, 'The Beatles', 'John Lennon');

    INSERT INTO bands
    VALUES (DEFAULT, 'Queen', 'Freddie Mercury');

    INSERT INTO bands
    VALUES (DEFAULT, 'U2', 'Bono');

A process of minimizing redundancy from a relation or set of relations.

If a relation contains composite, or multi-valued attributes, it violates first normal form. A relation is in first normal form if every attribute in that relation is a single valued attribute.

1. What are the main entities in my application (nouns)
2. How are they related to one another?
3. Can I normalize any information?

A transaction. If any query within the transaction fails, the entire transaction may be rolled back.

By using pool.query("Begin"), then a try-catch block. If a query fails, throw an error. If all queries succeed, then commit. In the catch block, rollback.

    async function transferFunds(pool, account1, account2, amount) {
        const balanceQ = 'select balance from "AccountBalances" where account_id = $1';
        const updateBalanceQ = 'update "AccountBalances" set balance=$1 where account_id = $2';

        await pool.query("BEGIN");
        try {
                const balance1 = await pool.query(balanceQ, [account1]);
                if (balance1 < amount) {
                    throw ("Not enough funds");
                }
                const balance2 = await pool.query(balanceQ, [account2]);

                await pool.query(updateBalanceQ, [balance2 + amount, account2]);
                await pool.query(updateBalanceQ, [balance1 - amount, account1]);
                await pool.query("COMMIT;");
            } catch (e) {
                await pool.query("ROLLBACK;");
            }
        }

To improve performance.

If our table is running many inserts, deletes, and updates, they would have to wait for the Index to update after each update. Updating indexes locks the table, and the queries may be stalled momentarily.

Use EXPLAIN and EXPLAIN ANALYZE to determine your query performance. EXPLAIN will give you baseline numbers you can use to compare query to query, for trying to optimize your queries. You can use EXPLAIN ANALYZE for details on how the query is executing, and to obtain a measure in milleseconds for the query.

From the command line, install node-postgres:

    $npm install node-postgres

Then import your postgres library and define your database connection:

    const { Pool } = require('pg');

    const pool = new Pool({
        database: <dbName>,
        hostname: <hostName>
        username: <userName>
        password: <password>
    });

    const result = await pool.query("SELECT 1;");

    //first, create the query
    const loginQuery = 'SELECT * FROM users WHERE username = $1 AND password = $2;';

    //now write a function using your parameterized query, passing in the values to your query
    async function loginUser(username, password) {
        const results = await pool.query(loginQuery, [username, password]);
    }

node-postgres library exists primarily to protect our applications from SQL-injection attacks. One of the most common type of hacking attacks that we see on the web.

There are four steps to setup your project:

1. Create your database and databaseUser within postgreSQL RDBMS (psql):

    CREATE USER <databaseUser> WITH SUPERUSER PASSWORD '<password>';
    CREATE DATABASE <databaseName> WITH OWNER '<databaseUser>';
    REVOKE CONNECT ON DATABASE <databaseName> FROM PUBLIC;
    GRANT CONNECT ON DATABASE <databaseName> TO <databaseUser>

2. Install sequelize and sequelize-cli as well as postgress:

    npm install sequelize@^5 sequelize-cli@^5 pg;

3. Initialize your project with sequelize

    npx sequelize-cli init;

4. Now edit your confi/config.json file to add your database name, username, password, and dialect:

    "development": {
        "username": "sequilize_recipe_box_app",
        "password": "HfKfK79k",
        "database": "recipe_box_development",
        "host": "127.0.0.1",
        "dialect": "postgres",
        "seederStorage": "sequelize"
      },


You may also want a test and a production target in your config file with the same information, if you're using the same databases in those environments.

1. Generate the model with the following command:

    npx sequelize-cli model:generate --name <ModelName> --attributes <ModelName>:<type>,<ModelName>:<type>, ...

2. Update your model and your migration files with the validations and constraints.
3. Run your pending migrations

    npx sequelize-cli db:migrate;

4. Create your new seed file

    npx sequelize-cli seed:generate --name <descriptiveName>

5. Run your seed files

    npx sequelize-cli db:seed:all

1. Rollback one migration:

    npx sequelize-cli db:migrate:undo

2. Rollback all migrations:

    npx sequelize-cli db:migrate:undo:all

3. Rollback one seed:

    npx sequelize-cli db:seed:undo

4. Rollback all seeds

    npx sequelize-cli db:seed:undo:all

Because you want the validation to occur BEFORE you touch the database, you will want to put your validation in your Model:

    const Cat = sequelize.define('Cat', {
        firstName: {
            type: DataTypes.STRING,
            allowNull: false,
            validate: {
                notNull: {
                    msg: "firstName must not be null",
                },
                notEmpty: {
                    msg: "firstName must not be empty",
                }
            },
        }
    }

Using a try, catch block, you use sequelize.transaction passing in an async callback function

    async function main() {
        try {
            await sequelize.transaction(async (tx) => {
                const markovAccount = await BankAccount.findByPk(1, { transaction: tx } );
                const curieAccount = await BankAccount.findByPk(2, {transaction: tx} );

                curieAccount.balance += 5000;
                await curieAccount.save( { transaction: tx} );

                markovAccount.balance -= 5000;
                await markovAccount.save ({ transaction: tx} );
            });
        } catch (err) {
            console.log("Unable to complete transaction.  Rolled back.");
        }

        await sequelize.close();
    }

    main();

<Model>.create() OR <Model>.build() with <instance>.save()

<Model>.findOne(), <Model>.findAll(), or <Model>.findByPk()

First you would get the instance by using one of the <Model>.find*() methods, and now you can simply set the property on the instance and call .save() or use the .update() method.

First you find the instance by using one of the <Model>.find*() methods, and now you can use <instance>.destroy(), OR use the <Model>.destroy() with a where clause to destroy multiple rows.