POSTGRESQL

Installation

Ubuntu includes PostgreSQL by default. No download needed.

sudo apt install postgresql

Check status

sudo systemctl status postgresql

Stop the PostgreSQL service

sudo systemctl stop

Start the PostgreSQL service

sudo systemctl start postgresql

Disable the PostgreSQL service

sudo systemctl disable postgresql

Enable the PostgreSQL service

sudo systemctl enable postgresql

Open the PostgreSQL command-line tool with the default super user

sudo -u postgres psql

Create user

CREATE USER john WITH
    SUPERUSER                --Provide admin rights
    CREATEDB                 --Allow user to create new databases
    CREATEROLE               --Allow user to create, alter and drop roles
    CONNECTION LIMIT -1;     --Unlimited concurrent connections (default, can be skipped)

ALTER USER john WITH PASSWORD 'iloveyou'; --Add passwort to user

CREATE DATABASE test;        --Create a new database
\q                           --Quit the psql terminal (no semicolon needed here, since it's a psql meta-command, not sql)

Essential PostgreSQL CLI Commands

Connect to postgreSQL locally with the cli (terminal)

psql -U coolzero91 -h localhost -d test

List all databases with owners

\l

Connect to a database (names with hypen must be in quotes)

\c "<database>"

List all roles and permissions (regardless of current database)

\du

List all schemas

\dn

List all tables

\dt

Essential Commands For Object Management

Delete a table

DROP TABLE <name>;

DROP TABLE <name> CASCADE; -- Delete table with all dependant objects

Delete a database (connect to a different database before deleting)

DROP DATABASE <name>;

Delete a user (take care of reassignement if necessary, see below)

DROP USER <name>;

User that own tables or databases cannot be dropped, unless ownership is transferred

REASSIGN OWNED BY user_name TO new_owner;

Quit the PostgreSQL interactive terminal

\q

Queries

Create a table

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  position VARCHAR(50),
  salary DECIMAL(10, 2),
  verified BOOLEAN        DEFAULT FALSE,
  created_at TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
);

Insert data into the employees table

INSERT INTO employees (name, position, salary) VALUES
('Alice Smith', 'Developer', 75000.00),
('Bob Johnson', 'Manager', 85000.00),
('Carol White', 'Analyst', 65000.00),
('David Black', 'Designer', 70000.00);

Show all entries from the employees table

SELECT * FROM employees;

Various Queries

INSERT INTO users (name, age, email) VALUES ('John', 20, 'john@example.com');
-- Inserts a new row into the users table with the specified values.

SELECT * FROM users;
-- Selects all rows from the users table.

SELECT * FROM users WHERE age >= 18;
-- Selects all rows from the users table where the age column is greater than or equal to 18.

SELECT * FROM users WHERE email = 'john@example.com' LIMIT 1;
-- Selects the first row from the users table where the email column matches 'john@example.com'.

UPDATE users SET is_adult = true WHERE age >= 18;
-- Updates the is_adult column of all rows in the users table where the age column is greater than or equal to 18.

UPDATE account
SET balance = 100
WHERE id = 1 AND name = 'Bob' AND balance = 50;

DELETE FROM users WHERE is_adult = false;
-- Deletes all rows from the users table where the is_adult column is false.

SELECT COUNT(*) FROM users WHERE age >= 18;
-- Returns the number of rows in the users table where the age column is greater than or equal to 18.

Relations

CREATE TABLE clients (
    id          SERIAL PRIMARY KEY,
    name        TEXT,
    email       TEXT
);

CREATE TABLE pets (
    id          SERIAL PRIMARY KEY,
    name        TEXT,
    species     TEXT,
    client_id   INT,
    FOREIGN KEY (client_id) REFERENCES clients(id)
);

- First, a client needs to be created
INSERT INTO clients
    (name, email)
VALUES
    ('Alice', 'alice@example.org')
;

-- Then, a pet can be created, because it needs the client ID to exist
INSERT INTO pets
    (name, species, client_id)
VALUES
    ('Veera', 'cat', 1)
;

Sequelize

Connection

import Sequelize from "sequelize";

// Option 1: Passing parameters separately
const sequelize = new Sequelize('database', 'username', 'password', {
  host: 'localhost',
  dialect: /* one of 'mysql' | 'mariadb' | 'postgres' | 'mssql' */
});

// Option 2: Passing a connection URI
const sequelize = new Sequelize('postgres://user:pass@example.com:5432/dbname');