SQL

SQL (Structured Query Language) is the standard language used for interacting with and managing relational databases. It is essential for creating, retrieving, updating, and deleting data in databases. SQL has its origins in the 1960s and remains the predominant language for relational database management systems.

Key Concepts

• Tables: Structures that store data in rows and columns.
• Columns: Fields within a table that hold specific types of data (e.g., text, numbers, dates).
• Rows: Records within a table that represent individual data entries.
• Queries: Statements that retrieve data from a database based on specified criteria.
• Joins: Combine data from multiple tables based on common fields.
• Aggregates: Functions that perform calculations on a dataset (e.g., SUM, AVG, COUNT).

Setup PostgreSQL

To run PostgreSQL using Docker:

docker run --name postgres -p 5432:5432 -e POSTGRES_PASSWORD=my_password -d postgres

Connect to the PostgreSQL database using psql:

docker exec -it postgres psql -U postgres

Create a new database:

CREATE DATABASE mydb;

Use \l to List databases:

postgres=# \l
                                                    List of databases
   Name    |  Owner   | Encoding | Locale Provider |  Collate   |   Ctype 
-----------+----------+----------+-----------------+------------+------------
 mydb      | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 
 postgres  | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 

Use \c to switch to the new database, c for connect:

\c mydb

Basic SQL

Create a New Table

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100)
);

Use \dt to list tables:

mydb=# \dt
           List of relations
 Schema |   Name    | Type  |  Owner
--------+-----------+-------+----------
 public | customers | table | postgres

Use \d to describe the table:

mydb=# \d customers
                                    Table "public.customers"
 Column |          Type          | Collation | Nullable |                Default
--------+------------------------+-----------+----------+---------------------------------------
 id     | integer                |           | not null | nextval('customers_id_seq'::regclass)
 name   | character varying(50)  |           |          |
 email  | character varying(100) |           |          |
Indexes:
    "customers_pkey" PRIMARY KEY, btree (id)

Inserting Data

Insert a single record:

INSERT INTO customers (name, email) 
VALUES ('Alice', 'alice@example.com');

Batch insert records:

INSERT INTO customers (name, email) VALUES
('David Lee', 'davidlee@example.com'),
('Emily Brown', 'emilybrown@example.com');

Retrieving Data

Retrieve all records:

mydb=# SELECT * from customers;
 id |    name     |         email
----+-------------+------------------------
  1 | Alice       | alice@example.com
  2 | David Lee   | davidlee@example.com
  3 | Emily Brown | emilybrown@example.com
(3 rows)

Retrieve a specific record:

mydb=# SELECT * from customers WHERE id = 3;
 id |    name     |         email
----+-------------+------------------------
  3 | Emily Brown | emilybrown@example.com
(1 row)

Retrieve a limited number of records in descending order:

mydb=# SELECT * from customers ORDER BY name DESC LIMIT 2;
 id |    name     |         email
----+-------------+------------------------
  3 | Emily Brown | emilybrown@example.com
  2 | David Lee   | davidlee@example.com
(2 rows)

Updating Data

Update a specific record:

UPDATE customers
SET email = 'new_email@example.com'
WHERE id = 1;

Deleting Data

Delete a specific record:

DELETE FROM customers WHERE id = 2;

Note: In real-world applications, it's advisable to avoid using DELETE whenever possible. Reasons include:

1. Irreversibility: Permanently deleting important data can lead to significant challenges in recovery.
2. Data Integrity: Deleting data without consideration can result in inconsistencies and orphaned records (e.g., deleting a customer without addressing related orders).
3. Audit Trails: Many organizations require a record of data changes for compliance and auditing purposes.

Instead of deleting records, consider implementing "soft deletes" by adding a deleted_at column or a status field to indicate deleted records.

Joining Tables

First, create a new table for orders:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(id),
    order_date DATE,
    total_amount DECIMAL(10, 2)
);

Insert some records into the orders table:

INSERT INTO orders (customer_id, order_date, total_amount) VALUES
(1, '2024-10-01', 100.00),
(1, '2024-10-02', 50.50),
(3, '2024-10-03', 150.25);

To retrieve data from multiple tables, perform an inner join:

SELECT customers.name, orders.total_amount
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id
WHERE orders.order_date BETWEEN '2024-01-01' AND '2024-12-31';

This query returns:

    name        | total_amount
----------------+--------------
  Emily Brown   |       150.25
  Alice         |        50.50
  Alice         |       100.00

Aggregation

In previous example we got two records for Alice, we need to add them together, that can be achieved by the SUM function:

SELECT customers.name, SUM(orders.total_amount) AS total
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id
WHERE orders.order_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY customers.id;

This query will produce:

    name        |   total
----------------+---------
  Alice         |   150.50
  Emily Brown   |   150.25

Besides SUM, there are other aggregate functions like AVG, MIN, MAX, and COUNT to calculate different statistics.

Connecting to PostgreSQL from JavaScript

Install the postgres package:

bun add postgres

Create index.ts with following content:

import postgres from 'postgres'

const sql = postgres({username: 'postgres', password: 'my_password'})

console.log(await sql`select * from customers`)

Running the script with:

bun index.ts

You should see an output similar to this:

[
  {
    "id": 1,
    "name": "Alice",
    "email": "new_email@example.com"
  },
  {
    "id": 3,
    "name": "Emily Brown",
    "email": "emilybrown@example.com"
  }
]

That's it, read the documentaion for more.