What is a database ?

A database is simply a place that does these 3 things

1. Store data
2. Manipulate data
3. Retrieve data

That's it, no more, no less. This data is usually stored on a computer server.

You put data on the server and now can perform Create, Read, Update, Delete (CRUD) operations on it using the database's accompanying engine

What are tables ?

Tables are like collections of entries in a database, where each collection specifies a particular set of entries.

This is done to effectively manage separately defined data but integrate it all under a common roof for effective communication between the data types.

Eg : Amazon.com, among other tables has separately defined tables for Users and Products. In this manner, although these tables are separately defined and managed they can communicate with each other (say, when a user from Users table places an order for a product in the Products table )

What is a relational database ?

It is a database that will always have a specific set of properties, that need to be compulsarily defined for every entry stored in it.

Eg : Amazon.com will not store a product in its relational database if important specs like the product price or details are not properly specified.

This set of compulsary conditions that every entry stored in it is called a database's schema.

The databases that do not adhere to such specific set of properties for each entry are called non-relational databases. Eg : MongoDB, Google Firebase, Apache Cassandra

What is SQL

In the most simple terms, it is a language that allows us to talk to the database, tell it to store, manipulate or retrieve our data. It is primarily used with relational databases.

What is Postgres ?

Postgres is simply a database engine. It simply takes the SQL code that we have written and tells the database to perform whichever CRUD command we have specified.

Now, lets get the ball rolling by installing Postgres and the GUI client on our system. We shall be choosing the GUI tool as follows :

1. Postico => For MacOS users
2. pgAdmin4 => For Windows and Linux users

For windows

Simply download the postgreSQL .exe file from here and run it to get started

PostgreSQL: Windows installers

Install the GUI client => pgAdmin4, from this link and just run the .exe/.msi file to let Windows Installer handle the installation for you

PostgreSQL: File Browser

For Linux users

How To Install and Use PostgreSQL on Ubuntu 18.04 | DigitalOcean

This tutorial provides instructions on how to install the PostgreSQL database software on an Ubuntu 18.04 server. It also includes instructions for basic database management.

DigitalOceanJustin Ellingwood

## Installing postgres
sudo apt install postgresql postgresql-contrib
curl https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo apt-key add

## Installing the GUI client => pgAdmin4
# Create the repository configuration file:
sudo sh -c 'echo "deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'

#
# Install pgAdmin
#

# Install for both desktop and web modes:
sudo apt install pgadmin4

# Install for desktop mode only:
sudo apt install pgadmin4-desktop

# Install for web mode only: 
sudo apt install pgadmin4-web 

# Configure the webserver, if you installed pgadmin4-web:
sudo /usr/pgadmin4/bin/setup-web.sh

For MacOS users

brew install postgresql

Install the GUI client : Postico, from this link and follow the setup instructions to install and configure it on your system

Postico – a modern PostgreSQL client for the Mac

Things you have just installed

Creating your first database

PostgreSQL, by default, assigns postgres as a default user using which you can access your database

If you are a Linux user, you first need to enter the following command to switch over to the postgres account on your server. You can also create your own custom account but the postgres account is something you get by default on installing postgres.

sudo -i -u postgres

Simply this command to login as user and start interacting with Postgres' database engine

psql -U postgres

NOTE : You will be asked to set a password for the default postgres user the first time around. In case you forget it, you can refer to this link to get it sorted

I forgot the password I entered during postgres installation

I either forgot or mistyped (during the installation) the password to the default user of Postgres. I can’t seem to be able to run it and I get the following error: psql: FATAL: password authenti...

Stack Overflowhilarl

Enter the following command to create your first table :

CREATE DATABASE my_first_db;

Connecting to this database :
This is done by typing the command specifying the following things :

1. Database name : Any one of the created databases
2. Username : Username to connect to database. For remote services like AWS, Heroku, Google Cloud Platform, that offer these databases as a managed service, they themselves supply all these details. By default, on your local machine, it is postgres
3. Hostname : The IP address which is used to connect the SQL server (i.e. the database engine) to your local machine. By default it is localhost i.e. http://127.0.0.1
4. Port : The port on which this particular host is extending this database engine as a service. By default, this port is 5432 for Postgres

\c my_first_db postgres localhost 5432

Helpful commands :

1. To get list of all available databases

\d

2. To delete a database : Exercise this command with EXTREME CAUTION as it will erase all your data

DROP DATABASE your_database_name

Once you have connected to your database you can insert your first table in it. I would recommend an online tool like Mockaroo to play with random sample data while you're still in the learning phase

CREATE TABLE people [
	person_id INT NOT NULL PRIMARY KEY,
    	person_name VARCHAR(100)
];

Thus, we have created a table ie collection of a people and given a schema that each person should have an

1. person_id :  an id which is an integer, cannot be null and is a primary identifier of any entry on the table
2. person_name : a string of maximum 100 characters

Helpful Commands

1. To get list of available tables

\dt

2. To get list of description of schema of a particular table

\d people

Now, lets populate this table with entries

INSERT INTO people (person_id, person_name)VALUES(1, 'John Doe');
INSERT INTO people (person_id, person_name)VALUES(2, 'Jen Doe');

If you have a pre-written .sql file on your machine, that you wish to feed queries from into the table, use this command :

\i /home/user_name/Desktop/person.sql

Where the person.sql file could be basically everything we've done in this database, after connecting to it.

CREATE TABLE people [
	person_id INT NOT NULL PRIMARY KEY,
    	person_name VARCHAR(100)
];

INSERT INTO people (person_id, person_name)VALUES(1, 'John Doe');
INSERT INTO people (person_id, person_name)VALUES(2, 'Jen Doe');

Finally, you can top this mini-tutorial by executing the simplest SQL query ever => to fetch all the entries from this table

SELECT * FROM people

Conclusion

That's all for now. Welcome to the one of the most brilliant problems in computer and data science - database management. There's a lot to learn and know from here and trust me, this is one domain which requires a lot of braincrunching but feels very fulfilling.
I would highly recommend you to watch this amazing course on PostgreSQL for further digging in into the concept

Learn PostgreSQL Tutorial - Full Course for Beginners

Learn how to use PostgreSQL in this full course. PostgreSQL is a general purpose and object-relational database management system. It is the most advanced op...

YouTube

I hope you learnt something from here and wish you the very best of luck on your developer journey ! 🚀