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.