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 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

List information (e.g. Types) of a certain table

\d <name>

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

Datatypes

Data Type	Alias	Size	Use Case	TypeScript Type	Notes
PRIMARY KEYS
SERIAL	SERIAL4	4 bytes	Auto-incrementing IDs	number	Simple, sequential, predictable
BIGSERIAL	SERIAL8	8 bytes	Auto-incrementing (large scale)	number	Max 9.2 quintillion
UUID	-	16 bytes	Distributed systems, security	string	Non-sequential, globally unique
TEXT & STRINGS
TEXT	-	Variable	Any length text	string	No length limit, recommended
CHARACTER VARYING(n)	VARCHAR(n)	Variable	Limited length text	string	Use only if you need length constraint
CHARACTER(n)	CHAR(n)	Fixed	Fixed-length codes	string	Rarely needed, space-padded
NUMBERS
INTEGER	INT, INT4	4 bytes	Whole numbers	number	-2B to +2B
BIGINT	INT8	8 bytes	Large whole numbers	number or bigint	Use for large counts
SMALLINT	INT2	2 bytes	Small whole numbers	number	-32K to +32K
NUMERIC(p,s)	DECIMAL(p,s)	Variable	Exact decimals (money)	string or Decimal	NUMERIC(10,2) for currency
REAL	FLOAT4	4 bytes	Floating point	number	6 decimal precision
DOUBLE PRECISION	FLOAT8, FLOAT	8 bytes	Floating point (scientific)	number	15 decimal precision
BOOLEAN
BOOLEAN	BOOL	1 byte	True/false	boolean	Stores true, false, null
DATES & TIME
DATE	-	4 bytes	Date only	string or Date	2024-01-15
TIME	-	8 bytes	Time only	string	14:30:00
TIMESTAMP	-	8 bytes	Date + time (no timezone)	string or Date	Avoid, use TIMESTAMPTZ
TIMESTAMP WITH TIME ZONE	TIMESTAMPTZ	8 bytes	Date + time + timezone	string or Date	Recommended for all timestamps
JSON
JSON	-	Variable	JSON data	any or typed	Stores exact text
JSONB	-	Variable	JSON data (binary)	any or typed	Recommended, indexable, faster
ARRAYS
TEXT[]	-	Variable	Array of text	string[]	['tag1', 'tag2']
INTEGER[]	INT[]	Variable	Array of integers	number[]	[1, 2, 3]
SPECIALIZED
BYTEA	-	Variable	Binary data	Buffer	For files/images (usually use S3 instead)
INET	-	7-19 bytes	IP addresses	string	Auto validates IP format
CIDR	-	7-19 bytes	Network addresses	string	For IP ranges
ENUM	-	4 bytes	Custom type	union	CREATE TYPE mood AS ENUM (...)

Example configuration for CRUD apps

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT UNIQUE NOT NULL,
  name TEXT NOT NULL,
  age INTEGER,
  is_active BOOLEAN DEFAULT true,
  metadata JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE products (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  price NUMERIC(10,2) NOT NULL,  -- Exact for money
  tags TEXT[],
  stock INTEGER DEFAULT 0,
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

Queries

Select a database

USE <name>

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
);

-- Modern alternative to SERIAL
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
-- Strict: PostgreSQL always controls the ID, you can't insert manually

id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
-- Flexible: you can override and insert your own ID if needed

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;

Delete all tables of the current database

DROP SCHEMA public CASCADE;
CREATE SCHEMA public;

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.

SELECT 
    c.name AS client, -- display clients.name under the name "client"
    p.name AS pet,
    a.date AS appointment
FROM clients c -- c is an optional alias for clients
JOIN pets p ON c.id = p.client_id -- JOIN is short for INNER JOIN
JOIN appointments a ON p.id = a.pet_id;
-- Variants: JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN

Alter Tables

ALTER TABLE employees
ALTER COLUMN job_id TYPE INT; -- UNIQUE cannot be set under TYPE

ALTER TABLE employees
ADD UNIQUE (job_id);
-- or provide own name for constraint
ADD CONSTRAINT employees_job_id_unique UNIQUE (job_id);

Relations

One-To-One

CREATE TABLE persons (
    id          SERIAL PRIMARY KEY,
    name        TEXT,
    birthdate   DATE
);

CREATE TABLE passports (
    id              SERIAL PRIMARY KEY,
    passport_number TEXT,
    expiry_date     DATE,
    person_id       INT UNIQUE,  -- UNIQUE enforces the one to one relationship
    FOREIGN KEY (person_id) REFERENCES persons(id)
);

-- First, a person needs to be created
INSERT INTO persons
    (name, birthdate)
VALUES
    ('Bob', '1990-05-15')
;

-- Then, a passport can be created for that person
INSERT INTO passports
    (passport_number, expiry_date, person_id)
VALUES
    ('AB123456', '2030-01-01', 1)
;

One-To-Many

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)
;

Many-To-Many

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

CREATE TABLE courses (
    id          SERIAL PRIMARY KEY,
    title       TEXT,
    credits     INT
);

-- A junction table is needed to link both tables
CREATE TABLE enrollments (
    student_id  INT,
    course_id   INT,
    enrolled_at DATE,
    PRIMARY KEY (student_id, course_id),  -- prevents duplicate enrollments
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

-- Alternative to PRIMARY KEY (student_id, course_id)
id SERIAL PRIMARY KEY,
student_id INT,
course_id INT,
UNIQUE (student_id, course_id)


INSERT INTO students
    (name, email)
VALUES
    ('Carol', 'carol@example.org'),
    ('Dave',  'dave@example.org')
;

INSERT INTO courses
    (title, credits)
VALUES
    ('Mathematics', 5),
    ('History',     3)
;

-- Carol enrolls in both courses, Dave enrolls in Mathematics
INSERT INTO enrollments
    (student_id, course_id, enrolled_at)
VALUES
    (1, 1, '2024-01-15'),
    (1, 2, '2024-01-15'),
    (2, 1, '2024-01-16')
;

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');

Relations

Mnemonic: The foreign key always ends up in the table of the class that calls .belongsTo()!

// Relationships in a separate associations.ts file
// Important: This file must be imported AFTER all
// models have been initialized, but BEFORE db.sync() is called.

// One-to-One: Captain <-> Ship
// The foreign key (captainId) resides in the Ships table.
Captain.hasOne(Ship, { foreignKey: "captainId" })
Ship.belongsTo(Captain , { foreignKey: "captainId" })

// One-to-Many: Ship <-> Sailor
// The foreign key (shipId) resides in the Sailors table.
Ship.hasMany(Sailor , { foreignKey: "shipId" })
Sailor.belongsTo(Ship , { foreignKey: "shipId" })

// Many-to-Many: Sailor <-> Certification
// Sequelize automatically creates an intermediate table 'SailorCertifications'
Sailor.belongsToMany(Certification, {
  through: 'SailorCertifications',
  foreignKey: 'sailorId',
  otherKey: 'certificationId'
})

Certification.belongsToMany(Sailor, {
  through: 'SailorCertifications',
  foreignKey: 'certificationId',
  otherKey: 'sailorId'
})