Database

PostgreSQL

PostgreSQL is a powerful, open source object-relational database system with over 30 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance.

Installing PSequel

PSequel, a PostgreSQL GUI tool, provides a clean and simple interface for you to perform common PostgreSQL tasks quickly.

Installing Postgres via Brew

Pre-Reqs

$ brew doctor
$ brew update

Installing

$ brew install postgresql

Start & stop PostgreSQL service

$ brew services start postgresql
$ brew services stop postgresql

PostgreSQL Commands

Database Commands

command	desc
createdb <database_name>	create database
psql <dtabase_name>	connect to the database / enter into the database
psql -U postgres -l	list databases
psql -U postgres -d <database_name>	show tables in database
dropdb <database_name>	drop database
dropdb <database_name> && createdb <database_name>	restart database

Note: We can connect to the <database_name> using PSequel. By default, it runs on localhost port:5432.

Useful commands

command	desc
\l	list available database
\dt	list available tables
\dn	list available schemas of the currently connected database
\du	list users and their roles
\g	executes the previous command again
\s	display command history
\?	get help on psql commands ex. \h ALTER TABLE
\q	quit `psql

DDL (Data Definition Language) commands

Desc	Code
Create table	CREATE TABLE users (name VARCHAR(50), age SMALLINT, birthday DATE)
Drop table	DROP TABLE users;
Modify table	ALTER TABLE products ADD COLUMN description text;
Add column	ALTER TABLE users ADD COLUMN score smallint;

DML (Data Manipulation Language) Commands

Desc	Code
Insert data	INSERT INTO users (name, age, birthday) VALUES ('Andrei', 31, '1970-01-25');
View data	SELECT * FROM users;
Update data	UPDATE users SET age = 10 WHERE name = 'Andrei';
Delete data	DELETE FROM users WHERE name = 'John';

Queries

Desc	Code
The where clause	SELECT * FROM users WHERE name LIKE 'A%'; -- name starts with 'A'
Sort	SELECT * FROM users ORDER BY score DESC;

Functions

Mathematical Functions and Operators

SELECT AVG(score) FROM users;
SELECT SUM(age) FROM users;
SELECT COUNT(age) FROM users;

Data Types

• smallint
• int
• bigint
• boolean
• date
• money
• numeric
• text

Resources

• PostgreSQL: SQL Commands
• Github: PostgreSQL commands

Face Detection App

$ createdb 'face-detection';
$ psql 'face-detection';

• connect to the 'face-detection' database in the PSequel tool.
  • Host: localhost
  • Port: 5432
  • Database: face-detection

Create users and login tables

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email TEXT UNIQUE NOT NULL,
    entries BIGINT DEFAULT 0,
    joined TIMESTAMP NOT NULL
);

CREATE TABLE login (
    id SERIAL PRIMARY KEY,
    hash  VARCHAR(100) NOT NULL,
    email TEXT UNIQUE NOT NULL

);

Install KNEX

Knex.js is a "batteries included" SQL query builder for Postgres, MSSQL, MySQL, MariaDB, SQLite3, Oracle, and Amazon Redshift designed to be flexible, portable, and fun to use.

npm install knex