Intro to PostgreSQL
Create and Query Postgres Database
Why Postgres
There’s a number of options available when it comes to choosing a database when starting on a new project. You could use JavaScript-based MongoDB which stores data as documents. While you can link related data to documents in MongoDB, using a relationship-based database could be a better option if you’re looking to run complex queries.
Postgres uses SQL which stands for Structured Query Language. This is a human-readable language that has the ability to get very specific results. Postgres, MySQL, SQLite, and Oracle are all SQL databases that store information in a table-like format.
One advantage of Postgres is that you can use JSON as a datatype. This is helpful when you want to be able to have a column that can store a variety of content types like videos, polls, images, etc. Another reason you may choose Postgres is for its scalability and compatibility with applications running on NodeJS.
Create Database and Tables
After following the steps to download and install the Postgres, you can enter the CLI by typing ‘psql’ in your terminal. Here’s a list of common commands that are used in the Postgres CLI that will allow you to view existing tables and changes made to the database.
\c database_name to connect\l shows all databases\d shows all tables in database\? shows all commands\h shows queries you can useq will allow to leave the list / quit
As I mentioned earlier, SQL is very human-readable. Creating a database is as simple as typing ‘CREATE DATABASE’. Although you don’t have to capitalize all keywords, it is conventional to do so.
CREATE DATABASE database_name_here;
//remember to always end with semicolonCREATE TABLE users (user_id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY, username VARCHAR (25) UNIQUE NOT NULL;
The code above will create a database and a table called users. Each user will have a unique id that is an integer. This id will act as a primary key that is automatically generated upon creation. If you have worked with MongoDB a primary key is equivalent to the _id. It’s used for creating relationships between separate tables of data. VARCHAR is a data type that refers to text that we can put a maximum value on. If we don’t need to limit this data, we can use the TEXT type instead. By including UNIQUE NOT NULL, we are explicitly saying that if a username is not unique an error will be thrown and it will not be saved.
To add a user to our newly created table, we can use the INSERT INTO keywords followed by the table name, columns, and values for those columns. The values should be in single-quotes — never double-quotes.
INSERT INTO users (username) VALUES ('codingchamp');Select and Query Clauses
Every query that we run will begin with the keyword SELECT. If you want to select all of the data from a table, you can run something like this…
SELECT * FROM users;To get more specific data you can use clauses. Clauses are additional keywords like WHERE and ORDER BY that allow us to drill down into the table data further and reduce it to return only what we need at the moment. Here’s an example.
SELECT user_id, username FROM users ORDER BY username DESC LIMIT 10;This will return the primary key and username of the first ten results from the users table sorted by username in descending order. The default order of results is ascending, so that’s what you’ll get if you don’t include the DESC clause.
The order of clauses is important. Although you’ll likely not need to use all of these clauses at once, here’s the acceptable order of operations when querying.
- FROM, including JOINs
- WHERE
- GROUP BY
- HAVING
- WINDOW functions
- SELECT
- DISTINCT
- UNION
- ORDER BY
- LIMIT and OFFSET
You can read more about each of these clauses and their use cases here.
Update and Delete
Changing the values within our tables is as simple as defining what we want to set as the new values and querying for the data we want to change.
UPDATE users SET username = 'codequeen' WHERE user_id = 2;Deleting is just as easy.
DELETE FROM users WHERE user_id = 2;Foreign Keys
Remember that the beauty of SQL databases is that they are relational and we can build connections. In our users table example, we have a primary key that is always generated on the creation of a user and will always be unique. We can map that primary key to another table to refer to that user instance. This is called a foreign key.
CREATE TABLE comments (comment_id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,user_id INT REFERENCES users(user_id) ON DELETE CASCADE,comment TEXT NOT NULL);
In the new comments table, the user_id is defined as an INT REFERENCE data type that acts as a foreign key to link the user_id to a specific comment instance. ON DELETE CASCADE tells Postgres to delete all the comments that are associated with a specific user if that user has been deleted. This helps us from keeping data that is no longer useful.
Join Tables
Now that we have a foreign key on the comment table, we can implement a join table. Join tables allow us to display related data. They help us to follow the coding principle of a single source of truth by pulling data from one table into another, instead of adding it manually which could lead to conflicts.
SELECTcomment_id, comments.user_id, users.usernameFROMcommentsINNER JOINusersONcomments.user_id = users.user_id;
The code above would create a table with a column for comment id, user id, and username. It’s pulling this information from the comments table and the users table. They are connected by the comment’s foreign key (comments.user_id) that matches the user’s primary key (users.user_id).
INNER JOIN is just one of several join tables. The most commonly used join tables are inner, left, and right. Choosing the right join table will depend on where the information you need is stored. Here’s a brief description of the types of join tables available to use.
INNER: must have a match on both tables, else don’t include it in tableLEFT: include anything from the left table whether it has a match on right tableRIGHT: opposite of leftOUTER: only join things that don’t have matches on either tableFULL OUTER: join everything from both tablesCROSS JOIN: creates every possible combination of joining data from both tables (cartesian product)*careful with CROSS JOIN bc it will likely be a massive amount of data
I found this diagram to also be useful for understanding the differences between each type of join table.
Additional Resources
We’ve only dipped our toes into the basic functionality and features of Postgres. I hope that this has piqued your curiosity about SQL databases. Here are some helpful resources to help you get more acclimated to this fantastic tool.
