Learning The PERN Stack — Postgres

Welcome back to the series !

So in this article we’re gonna dive into SQL and databases and Postgresql in specific and we’ll cover many useful things about databases and SQL in general so let’s jump right into it

So firstly what is a database ?

A simple google search and from wikipedia to be exact will give you the following answer

A database is an organized collection of data, generally stored and accessed electronically from a computer system. Where databases are more complex they are often developed using formal design and modeling techniques.

In simple terms, a database is a collection of tables that contain related data in a way that we can programmatically retrieve and alter depending on the purpose of the software we’re building

Databases are used everywhere, this very website right now medium.com has a database to keep track of its users and what is being published, your school has a database to keep track of students and their grades which is Microsoft Access a very famous software to create and manage databases for less tech people

What Is SQL ?

SQL or structured query language is a programming languages that is only used to interact with databases and has no other usage at all unlike any programming language

SQL is very simple and straightforward and might vary a tiny bit depending on the DBMS being used so MYSQL is a bit different than SQL server and so on

SQL uses declarative english-like statements to interact with the data in your database, some of the examples are :

SELECT * FROM customers this selects everything from the table named customers

Other commands include DROP TABLE , CREATE TABLE , ALTER TABLE and may others we’ll look into

Installing Postgresql And Learning Some SQL

Now it’s time to install Postgres and set it up and see what other tools it gives us to help us manage our databases and tables

1. First off go to https://www.postgresql.org/download/
2. Pick your operating system -in my case it’s Windows- then it’ll prompt you to a new page
3. On the platform support page click on “download the installer”
4. Choose your OS and architecture then press “Download” and the rest of the installer are quite simple, just keep clicking “Next” and leave the default settings
5. During installing it’ll ask you to set a password ALWAYS REMEMBER THIS PASSWORD WE’LL NEED IT SO SOON
6. You can untick the stack builder option at the end of the installation

This should be it to be ready to learn some SQL

GUI Vs. CMD

Supposing the installation all went as planned you should now be able to find Pgadmin which is the GUI -graphical user interface- for Postgres and you’ll also be able to run Psql which is the CMD / terminal way of interacting with your databases

Now if this is your first time and you just wanna get your hands dirty then you’re 95% gonna go for the GUI but on some serious notes and real life wise, you’ll need to stick to the CMD for many reason

1. Being able to use the terminal / command line gives people that “professional” impression about you as it’s an essential skill in IT in general
2. It helps you save time and be more productive, with few key strokes you get the job done and saves you all the clicking and browsing
3. This article focuses on teaching you SQL programming and how to do the usual tasks with commands only and not a GUI because in the simplest real life scenario you will have to SSH into a server and that way all you will have is a command line so you need to be prepared

Learning SQL Basics

Now let’s launch Psql as it’ll be our main focus for this article and feel free to explore Pgadmin on your own

On the first time you launch Psql it’ll ask you for some details regarding the server you’re trying to connect to and such stuff just click ENTER on all that like in this picture

Then enter your password from the installation process earlier and you’ll be met with such screen

Congrats this means you’re now using Postgresql correctly

Now let’s see what are the important commands you need to learn for both the CMD tool and SQL itself

Psql Commands

The following are the commands we need to use in Psql and they’re not really SQL specific commands

1. \l : to list all the databases
2. \c [database name] : to move into a database
3. \dt : to list all the tables inside the database you’re connected to

Now that’s just the most important commands regarding Psql as a software and not anything SQLish

Learning SQL

So as we mentioned, a database is just a bunch of tables that are somehow related to each other so let me just leave this cheat sheet down below then explain each command and how to use it

CREATE DATABASE [database name]; # create a new database
CREATE TABLE [schema]; # create a new table and declare a schema
INSERT INTO [table name][list of columns] VALUES [the values in order]
SELECT * FROM [table name] # view all columns in a table
# OR 
SELECT [list of columns separated by commas] FROM [table name]
# OR
SELECT */[] FROM [talbe name] WHERE [condition];

Now we firstly need to create a new database and for this demo we’re gonna name it “employeesdb” so in Psql type in CREATE DATABASE employeesdb; and we move into it using \c employeesdb

Now to create a table we need to define what is called a schema, and a schema is just a way for us to structure the data we want to insert into our tables so let’s create a table called “employees” inside of our database that has 4 columns

CREATE TABLE employees (
userid serial primary key,
fullname text not null,
age int not null,
address text not null
)

Now you must be wondering what the heck is this all about so let me show you how the schema syntax works

CREATE TABLE [table name](
column_name data tyoe and constraints
)

Now in postgresql there are many data types but the most common are text and int and for full reference of the other data types please check out this link

How To Query Data From Our Database ?

It’s now time to start displaying some data so we see what have we done so far and to also practice more SQL so let’s begin by displaying all the data inside our employees table

SELECT * FROM employees and this will return us all the records inside the table just like in this pic down below

Or we could select specific columns like SELECT fullname FROM employees; which will only return us the names stored in our database

Using The Where Clause

The where clause allows us to use conditional statements when querying our database, for example matching a username like SELECT * FROM users WHERE username="john wick" or selecting data based on some logical condition like SELECT * FROM employees WHERE age>35

This way we can build dynamic queries and in the future we’ll learn how to query our database from our front end to retrieve specific info based on what the user asks for

Inserting Data Into A Table

Now that we have our table set up it’s time to insert some dummy data to experiment with it so let’s add a new employee called Mary who is 23 and lives in New York

INSERT INTO employees(name,age,address)VALUES('Mary',23,'New York');

As you can see we didn’t have to insert a value for userid as it’s supplied by the database itself since we identified it to be serial so it’s auto generated and incremented per each insertion

Deleting A Table / Record In A Table

Deleting a record from a table in SQL is fairly simple as you’d only need to specify a unique value to distinguish the record to be deleted and that value is called primary key and in our case it’s the userid column so we’ll use that in our where clause as follows

# DELETING MARY 
DELTE FROM employees WHERE userid = 2; # OR ANY OTHER USERID 

IMPORTANT NOTE : don’t forget to supply a where clause or else the whole table will be deleted

To delete an entire table and its content DROP TABLE employees;; and this will delete our employees table

Updating A Table In SQL

Now it’s time to update a table so a perfect use case is : one of our employees relocated to a new address and we need to update the info we have on them, this won’t be as simple as the commands before it but still not that complex so let’s see the syntax and change Mary’s address to Texas

UPDATE[table_name] SET [column_name]=[new_value] WHERE id=[value];

So basically all we have to supply is the table name, in our case is ‘employees’ and the column to be changed and the new value so it’s SET address='Texas' and provide the condition for the WHERE clause so we change the correct record so WHERE userid=1 , So let’s suppose we have this particular table here

So now let’s change Mary’s address to Texas

UPDATE employees SET address='Texas' WHERE userid=1; so now our table should look like this

What’s Next ?

Now that we’ve learned the basics of SQL and postgresql database in general we’re ready to move on to building our backend / API in the next chapter where we will be using a node.js framework called Expres.js to create a RESTful API to interact with the database so we’ll be able to work with our databsse through code rather than Psql because we’re building something for the real world so we want our users to be able to make changes through the client app

Thanks a lot for reading and i hope to get back to you asap ♥