What is a database ?
A database is simply a place that does these 3 things
- Store data
- Manipulate data
- 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 :
- Postico => For MacOS users
- pgAdmin4 => For Windows and Linux users
Simply download the postgreSQL .exe file from here and run it to get started
Install the GUI client => pgAdmin4, from this link and just run the .exe/.msi file to let Windows Installer handle the installation for you
For Linux users
## 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
Things you have just installed
|Postgresql Server (psql)||
|pgAdmin4 / Postico||
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
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 :
- Database name : Any one of the created databases
- 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
- 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
- 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 :
- To get list of all available databases
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
- person_id : an id which is an integer, cannot be null and is a primary identifier of any entry on the table
- person_name : a string of maximum 100 characters
- To get list of available tables
2. To get list of description of schema of a particular table
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 :
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
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
I hope you learnt something from here and wish you the very best of luck on your developer journey ! 🚀