Development course SQL databases in node

1.

DEVELOPMENT COURSE
SQL DATABASES IN NODE.JS
CONFIDENTIAL
1

2.

Agenda of the lecture
• Important Aspects of Relational Databases;
• Create Relationships among Tables;
• SQL COMMANDS;
- DDL - Data Definition Language
- DML - Data Manipulation Language
- DCL - Data Control Language
- TCL - Transaction Control Language
- Transaction Isolation Levels
• Native node - postgresql driver
- Connection
- Queries
- Pool, TCL
CONFIDENTIAL
1

3.

DATABASES
CONFIDENTIAL
1

4.

Important Aspects of Relational Databases
CONFIDENTIAL
1

5.

Create Relationships among Tables
One-to-Many (1:N)
One-to-One (1:1)
PK
PK
FK
PK
Many-to-Many (N:М)
FK
PK
PK - Primary Key
FK - Foreign Key
CONFIDENTIAL
PK
FK
FK
1

6.

SQL Commands
CONFIDENTIAL
1

7.

DDL- Data Definition Language
Data Definition Language is used to define the database structure or
schema. DDL is also used to specify additional properties of the data.
CREATE (DATABASE, TABLE, VIEW, TRIGGER, PROCEDURE) : to
create objects in database
ALTER (DATABASE, TABLE, VIEW, TRIGGER, PROCEDURE) : alters the
structure of database
DROP (DATABASE, TABLE, VIEW, TRIGGER, PROCEDURE) : delete
objects from database
RENAME (DATABASE, TABLE, VIEW, TRIGGER, PROCEDURE) :
rename an objects
TRUNCATE TABLE : The result of this operation quickly removes all data
from a table, typically bypassing a number of integrity enforcing mechanisms.
CONFIDENTIAL
1

8.

DML-Data Manipulation Language
DML statements are used for managing data within schema objects.
DML are of two types:
Procedural DMLs : require a user to specify what data are needed and how
to get those data.
Declarative DMLs : (also referred as Non-procedural DMLs) : require a user
to specify what data are needed without specifying how to get those data.
Declarative DMLs are usually easier to learn and use than procedural DMLs.
However, since a user does not have to specify how to get the data, the
database system has to figure out an efficient means of accessing data.
SELECT: retrieve data from the database
INSERT: insert data into a table
UPDATE: update existing data within a table
DELETE: deletes all records from a table, space for the records remain
CONFIDENTIAL
1

9.

DDL AND SIMPLE DML
EXAMPLES
CONFIDENTIAL
1

10.

1
2
3
4
CONFIDENTIAL
1

11.

5
6
7
8
9
10
CONFIDENTIAL
1

12.

11
12
13
14
15
CONFIDENTIAL
1

13.

DML QUERY EXAMPLES
CONFIDENTIAL
1

14.

2
1
3
4
CONFIDENTIAL
1

15.

5
6
7
8
9
10
CONFIDENTIAL
1

16.

11
12
13
14
CONFIDENTIAL
1

17.

DML QUERY WITH INDEX
EXAMPLES
CONFIDENTIAL
1

18.

1
2
3
4
5
6
7
8
CONFIDENTIAL
1

19.

SQL Joins
CONFIDENTIAL
1

20.

DML QUERY JOINS
EXAMPLES
CONFIDENTIAL
1

21.

3
1
2
4
5
6
CONFIDENTIAL
1

22.

7
8
9
CONFIDENTIAL
1

23.

10
11
CONFIDENTIAL
1

24.

DCL- Data Control Language
A Data Control Language is a syntax similar to a computer programming language used to control access to
data stored in a database (Authorization). In particular, it is a component of Structured Query Language
(SQL).
Examples of DCL commands :
GRANT: allow specified users to perform specified tasks.
REVOKE: cancel previously granted or denied permissions.
The operations for which privileges may be granted to or revoked from a user or role apply to both the Data
definition language (DDL) and the Data manipulation language (DML), and may include CONNECT, SELECT,
INSERT, UPDATE, DELETE, EXECUTE and USAGE.
CONFIDENTIAL
1

25.

TCL- Transaction Control Language
Transaction Control Language commands are used to manage transactions in the database. These are used
to manage the changes made by DML-statements. It also allows statements to be grouped together into
logical transactions.
Examples of TCL commands:
COMMIT: Commit command is used to permanently save any transaction
into the database.
ROLLBACK: This command restores the database to last committed state.
It is also used with savepoint command to jump to a savepoint
in a transaction.
SAVEPOINT: Savepoint command is used to temporarily save a transaction so
that you can rollback to that point whenever necessary.
CONFIDENTIAL
1

26.

Transaction isolation levels vs read
phenomena
CONFIDENTIAL
1

27.

Lost Update
Transaction 1
Non-Repeatable-Read
Transaction 2
Transaction 1
UPDATE tbl1
SET f2=f2+20 WHERE f1=1;
Transaction 2
SELECT f2 FROM tbl1
WHERE f1=1;
UPDATE tbl1
SET f2=f2+25 WHERE f1=1;
UPDATE tbl1 SET f2=f2+1
WHERE f1=1;
COMMIT;
Dirty Read
Transaction 1
SELECT f2 FROM tbl1
WHERE f1=1;
Transaction 2
SELECT f2 FROM tbl1
WHERE f1=1;
f2
-------------100
(1 row)
Phantom Reads
Transaction 1
Transaction 2
SELECT SUM(f2) FROM tbl1;
UPDATE tbl1 SET f2=200
WHERE f1=1;
INSERT INTO tbl1(f1, f2)
VALUES (15, 20);
SELECT f2 FROM tbl1
WHERE f1=1;
f2
-------------200
(1 row)
COMMIT;
SELECT SUM(f2) FROM tbl1;
ROLLBACK
CONFIDENTIAL
1

28.

Serialization Anomalies
Transaction 1
Transaction 2
SELECT SUM(value) FROM
mytab WHERE class = 1;
-----------30
(1 row)
SELECT SUM(value) FROM
mytab WHERE class = 2;
-----------30
(1 row)
INSERT INTO mytab (value, class)
VALUES (30, 2)
INSERT INTO mytab (value,
class)
VALUES (300, 1)
COMMIT;
COMMIT;
CONFIDENTIAL
XA - Transaction
1

29.

TCL DEMO EXAMPLES
CONFIDENTIAL
1

30.

1
2
3
4
5
CONFIDENTIAL
1

31.

Native node - postgresql driver (Connection)
Link Nodejs driver api:
https://node-postgres.com
Free Database(
20MB data, 5concurrent connections
):
https://www.elephantsql.com/
CONFIDENTIAL
1

32.

Native node - postgresql driver (Queries)
CONFIDENTIAL
1

33.

Native node - postgresql driver (Pool, TCL)
CONFIDENTIAL
1