SQL Databases

What is SQL?

  • SQL, Structured Query Language is a language used to organize and manipulate data in a database
  • Originally developed by IBM in the 70's
    • Quickly became the most popular database language
    SELECT id, email
FROM users
WHERE first_name = 'Teppo';

Relational Database Management Systems

  • In relational databases, values are stored in tables
    • Each table has rows and columns
    • Data is displayed in a two-dimensional matrix
  • Values in a table are related to each other
    • Values can also be related to values in other tables
  • A relational database management system (RDBMS) is a program that executes queries to relational databases

Database management systems compared

https://db-engines.com/en/ranking

PostgreSQL

  • PostgreSQL (pronounced postgres-QL) is a free and open-source, cross-platform relational database management system
  • Emphasizes extensibility and SQL compliance
  • Fully ACID-compliant (atomicity, consistency, isolation and durability)
  • Used in conjunction with an interface like pgAdmin (graphical) or psql (command-line)
  • Once installed, can be accessed with the SQL Shell (see instructions here)

Extra: Running Postgres in Docker

  • Instead of running a PostgreSQL database locally, you can run it Dockerized
  • Using the official Postgres Docker image , let's create a locally running Postgres instance
    docker run --name my-postgres --env POSTGRES_PASSWORD=pgpass 
--env POSTGRES_USER=pguser -p 5432:5432 -d postgres:15.2
  • You can choose the values for POSTGRES_PASSWORD and POSTGRES_USER freely.

psql

  • psql (see Documentation) is a terminal-based interface for PostgreSQL and comes bundled with it
    • With psql, you can type in SQL queries interactively and see the results
    • Input from file, command line arguments, scripting and automation...

With Docker:

  • You can use psql directly from the Docker container running the database
    docker exec -it my-postgres psql -U pguser

Without Docker:

  • Just open the SQL shell from Start menu.

psql commands

  • If you have connected to the server as user pguser, you are by default connected to pguser database.
  • This is a database for user information. Do not use it for storing program data!
  • All databases can be listed using the list command: \l
  • PostgreSQL uses a default database named postgres
  • Users can connect to a different database with the connect command \c:
    \c <database-name>
  • Exit psql with the command exit

Exercise 1. psql basics

  1. If using Docker, start a local instance of Postgres in Docker.
  2. Connect to the server using psql.
  3. Use command \l to see what databases there are already created on the server.
  4. Create a new database called sqlpractice.
  5. Connect to the newly created database.

pgAdmin

  • pgAdmin is an administration and development platform for PostgreSQL
  • Cross-platform, features a web interface
  • A control panel frontend for your PostgreSQL database
  • A graphical alternative to psql
  • Completely separate from PostgreSQL
  • One of many database management applications

Extra: Running pgAdmin in Docker

  • Using the official pgAdmin image, we'll run pgAdmin alongside Postgres
    docker run --name my-pgadmin -p 5050:80 
--env PGADMIN_DEFAULT_EMAIL=<your-email-address> 
--env PGADMIN_DEFAULT_PASSWORD=<your-password> 
-d dpage/pgadmin4
  • Both PostgreSQL and pgAdmin are now running in our local Docker. To connect pgAdmin to PostgreSQL, we need the IP address used inside Docker.
  • Using Docker's inspect, we'll get Docker's internal IP for my-postgres -container. Since the command produces quite a lot of information, we pipe the result to grep (Linux) or findstr (Windows) to see only the rows that contain the word IPAddress

Linux

docker inspect <container-name> | grep IPAddress

Windows

docker inspect <container-name> | findstr IPAddress
  • In the example output, the IP Address is 172.17.0.2:

Logging into pgAdmin

With Docker:

  1. With pgAdmin running, navigate your web browser to http://localhost:5050

Without Docker:

  1. Run pgAdmin from the Start menu.
  1. Use the username and password you provided to log in.

Connecting PgAdmin to our DB

  1. Now we have all that we need for a connection. In pgAdmin, select
    Object > Register > Server.
    • If the Object menu is greyed out, click on Servers.
  2. In the General tab, give the server a name that identifies the connection.

  1. In the Connection tab, enter:

    1. Host name/address
    • the internal Docker address
    1. Port, Username, and Password
    • the values defined when running the PostgreSQL container

  2. Then click Save. You should see all the databases available on this server.

Exercise 2: pgAdmin basics

  1. If using Docker, start a local instance of pgAdmin in Docker
  2. Following lecture instructions, connect the pgAdmin to your already running PostgreSQL server.
  3. Verify that you can see the database created in the previous assignment.

Using PostgreSQL

Creating a database

With pgAdmin:

  1. Right-click Servers > my-postgres > Databases
  2. Select Create > Database...
  3. Insert a name for the database and hit Save

With psql:

  1. Enter command (remember ;!!!)
    CREATE DATABASE <database-name>;
  2. After creating a new database, you still need to connect to it with \c

Querying

With pgAdmin:

  1. Right-click sqlpractice > Query tool...
  2. Insert a query into the Query Editor and hit Execute (F5)

With psql:

  1. List all available databases with \l
  2. Connect to the created database with \c <database-name>
  3. List all tables in the database with \dt
  4. Type a query and press enter

Editing data with pgAdmin

  • Tables of data in a database are found under Database > Schemas > Tables
  • Inspect and edit data in pgAdmin by right-clicking a table and selecting View/Edit Data

  • Individual values in the table can be directly modified by double clicking the value and then editing the value in the visual user interface
  • Save the changes by pressing the Save Data Changes button


Exercise 3: Preparing the database

Using either pgAdmin or psql,

  1. Insert the provided example query to the new database
  2. Verify that the query has created new tables to your database

Basic SQL queries

  • SELECT
  • INSERT
  • DELETE
  • UPDATE
  • CREATE & DROP

Querying data with SELECT

  • Syntax:
    SELECT column1, column2, column3 FROM table_name;
  • Examples:
    SELECT full_name, email FROM users;
SELECT full_name AS name, email FROM users;
SELECT * FROM users;

Filtering data with WHERE

  • Syntax:
    SELECT column1, column2 FROM table_name WHERE condition;
  • Text is captured in single quotes.
  • In a LIKE condition, % sign acts as a wildcard.
  • IS and IS NOT are also valid operators.
  • Example:
    SELECT full_name FROM users
WHERE full_name = 'Teppo Testaaja';

SELECT * FROM books WHERE name LIKE '%rr%';
SELECT * FROM books WHERE author IS NOT null;

Ordering data with ORDER BY

  • Syntax:
    SELECT column1 FROM table_name ORDER BY column1 ASC;
  • Examples:
    SELECT full_name FROM users
ORDER BY full_name ASC;

SELECT full_name FROM users
ORDER BY full_name DESC;

Combining data with JOIN

  • Also known as INNER JOIN
  • Corresponds to intersection from set theory

JOIN examples

SELECT
users.id, users.full_name, borrows.id,
borrows.user_id, borrows.due_date, borrows.returned_at
FROM users
JOIN borrows ON
users.id = borrows.user_id;

SELECT
U.full_name AS name,
B.due_date AS due_date,
B.returned_at AS returned_at
FROM users AS U
JOIN borrows AS B ON
U.id = B.user_id;

Combining with LEFT JOIN

  • Also known as LEFT OUTER JOIN
  • Example:
    SELECT
U.full_name AS name,
B.due_date AS due_date,
B.returned_at AS returned_at
FROM users AS U
LEFT JOIN borrows AS B ON
U.id = B.user_id;

Exercise 4: Querying the library

Using SQL queries, get

  1. all columns of loans that are borrowed before 2020-10-27
  2. all columns of loans that are returned
  3. columns user.full_name and borrows.borrowed_at of the user with an id of 1
  4. columns book.name, book.release_year and language.name of all books that are released after 1960

Inserting data with INSERT

  • Syntax
    INSERT INTO table_name (column1, column2, column3) VALUES (value1, value2, value3);
  • Example
    INSERT INTO users (full_name, email, created_at)
VALUES ('Pekka Poistuja', 'pekka.poistuja@buutti.com', NOW());
  • Since id is not provided, it will be automatically generated.

Updating data with UPDATE

  • Syntax
    UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
  • Notice: if a condition is not provided, all rows will be updated!
    • If updating only one row, it is usually best to use id.
  • Example
    UPDATE users SET email = 'taija.testaaja@gmail.com' WHERE id = 2;

Removing data with REMOVE

  • Syntax
    DELETE FROM table_name WHERE condition;
  • Again, if the condition is not provided, DELETE affects all rows
  • Before deleting, it is a good practice to execute an equivalent SELECT query to make sure that only the proper rows will be affected.
  • Example:
    SELECT * FROM users WHERE id = 5;
DELETE FROM users WHERE id = 5;

Exercise 5: Editing data

  1. Postpone the due date of a loan with an id of 1 by two days in the borrows table
  2. Add a couple of new books to the books table
  3. Delete one of the loans.

Initializing data with CREATE TABLE

  • Before data can be manipulated, a database and its tables need to be initialized.
  • Syntax
    CREATE TABLE table_name (
  column1 datatype,
  column2 datatype,
  ...
);
  • Example:
    CREATE TABLE "users" (
  "id" SERIAL PRIMARY KEY,
  "full_name" varchar NOT NULL,
  "email" varchar UNIQUE NOT NULL,
  "created_at" timestamp NOT NULL
);

Removing data with DROP

  • In order to remove tables or databases, we use a DROP statement
    DROP TABLE table_name;
DROP DATABASE database_name;
  • These statements do not ask for confirmation and there is no undo feature. Take care when using a drop statement.
  • I'm legally obliged to include this XKCD comic here.

NoSQL

  • In addition to SQL databases, there's NoSQL
  • There are many differing definitions, but...
    • most agree that NoSQL databases store data in a format other than tables
    • They can still store relational data - just differently
  • Four different database types:
    • Document databases
    • Key-value databases
    • Wide-column stores
    • Graph databases
  • Example database engines include MongoDB, Redis and Cassandra
  • For more info, see MongoDB: What is NoSQL?

Object-Relational Mappers

  • Object-Relational Mappers, or ORMs allow the developer to write code instead of SQL to perform CRUD operations on their database
  • An abstraction layer between code and database
    • Can make database logic easier to read and write
    • Prevents SQL injection by sanitizing input (see the comic before...)
    • ...but sometimes you'd rather just write the SQL queries themselves
  • Some popular ORMs:
    • Hibernate (Java)
    • EFCore (.NET)
    • Sequelize (Node.js)
    • TypeORM (TypeScript) (documentation)