Getting started with Postgres

Laptop showing the Postgres logo

Postgres is one of the most popular online databases in existence. It powers countless web applications around the world. In this tutorial, we're going to cover how to set up and manipulate this relational database, creating a foundation for a future web application based around Python's Flask framework.

To start, let's install Postgres. The PostgreSQL project provides ready-to-use packages or installers for Linux, macOS, Windows, Solaris, and BSD, but on our Debian-based Linux system we'll just use the package manager by typing: apt-get install postgresql.

Installing the system should start Postgres running as a server daemon. Check this with systemctl status postgresql. If it isn't running, you can start it yourself:

Swipe to scroll horizontally
sudo service postgresql start

If you want to stop it, then:

Swipe to scroll horizontally
sudo service postgresql stop

But don't do that just now, because we want to play with it. To start, we're going to become the postgres user. In Linux, you do that with a simple sudo -u postgres -i.

From here, we can access psql, which is a terminal-based interface to Postgres that lets us enter queries and see the responses. To do this, just type psql.

The default user doesn't ship with a password, but leaving it that way will trip us up later when we use Python to access the database. Let's use psql to create a password for Postgres. For demonstration purposes, we'll use one you'd never use in production:

Swipe to scroll horizontally
ALTER USER postgres PASSWORD 'postgres';

If the system returns ALTER ROLE, you're all set.

Psql uses the \list or \l command to show the current databases.

Swipe to scroll horizontally
NameOwnerEncoding Collate Ctype Access privileges
postgres postgres UTF8 en_CA.UTF-8en_CA.UTF-8Row 1 - Cell 5
template0 postgres UTF8 en_CA.UTF-8en_CA.UTF-8=c/postgres +
postgres=CTc/postgres
template1 postgres UTF8 en_CA.UTF-8en_CA.UTF-8=c/postgres +
postgres=CTc/postgres

postgres is the default database that the database is created with. The other two databases are templates. Postgres uses template1 when you create a database yourself, enabling you to define a standard schema by editing this template. template0 is a standard schema that you can use to replace template1 with an out-of-the-box template in case it becomes corrupted.

Rather than using the standard Postgres database, we'd like to create our own. We're going to document old video games, so let's call it retronerds.

Swipe to scroll horizontally
CREATE DATABASE retronerds;

\list your databases again and there it is:

Swipe to scroll horizontally
NameOwnerEncoding Collate Ctype Access privileges
postgres postgres UTF8 en_CA.UTF-8en_CA.UTF-8Row 1 - Cell 5
retronerdspostgres UTF8 en_CA.UTF-8en_CA.UTF-8Row 2 - Cell 5
template0 postgres UTF8 en_CA.UTF-8en_CA.UTF-8=c/postgres +
postgres=CTc/postgres
template1 postgres UTF8 en_CA.UTF-8en_CA.UTF-8=c/postgres +
postgres=CTc/postgres

Our video games database needs a table to contain its data. We'd like a title, the game's description, its year of release, a publisher, and a genre.

We'll start by connecting to our database. From within psql, type \c retronerds. Then:

Swipe to scroll horizontally
CREATE TABLE games (id SERIAL PRIMARY KEY, title VARCHAR(255) NOT NULL, description VARCHAR(1024) NOT NULL, yor INTEGER NOT NULL, publisher VARCHAR(255) NOT NULL, genre VARCHAR(255) NOT NULL);

id gives us an ID unique to each record, called a PRIMARY KEY. The VARCHAR instructions tell us the maximum characters allowed in that field. NOT NULL means that we can't leave that field blank.

Now, check that the table is there with a \dt (for database tables):

Swipe to scroll horizontally
List of relations
Schema NameTypeOwner
public gamestablepostgres

You can also examine the structure of the table - its schema - with \d games:

Swipe to scroll horizontally
Table "public.games"
Column Type Collation Nullable Default
id integer Row 1 - Cell 2 not nullnextval('games_id_seq'::regclass)
title character varying(255)Row 2 - Cell 2 not nullRow 2 - Cell 4
description character varying(1024)Row 3 - Cell 2 not nullRow 3 - Cell 4
yor integer Row 4 - Cell 2 not nullRow 4 - Cell 4
publisher character varying(255) Row 5 - Cell 2 not nullRow 5 - Cell 4
genre character varying(255) Row 6 - Cell 2 not nullRow 6 - Cell 4

Note that because we used the SERIAL descriptor for id when creating our table, the schema tells us that it will automatically increase id every time we add a record. It has also set up the database to index the records using id.

So far, so good, but there's nothing in our table yet. Querying the table in psql with SELECT * FROM games; gives us:

Swipe to scroll horizontally
id title description yor publisher genre
Row 0 - Cell 0 Row 0 - Cell 1 Row 0 - Cell 2 Row 0 - Cell 3 Row 0 - Cell 4 Row 0 - Cell 5

Let's change that. We could use psql to manually add records at this point, but eventually we want to use a Flask app to control our database, which means getting a Python program to do it for us. To make that work, we'll use a python library called psycopg2.

Start a new terminal to keep your psql session alive in the original window. In our new terminal, with Python 3 installed, we'll make a virtual environment to support psycopg2. We don't technically need to do this, but it's a way to keep our system tidy and reliable. Think of it as a room where Python can keep all the libraries it needs for one project without cluttering things up for other projects. Python 3 uses the venv command for this, which we had to install manually on Ubuntu:

Swipe to scroll horizontally
sudo apt-get install python3-venv

Then we'll create the virtual environment, calling it flask-app.

Swipe to scroll horizontally
python3 -m venv flask-app

Then, 'enter' that room by activating the virtual environment:

Swipe to scroll horizontally
source flask-app/bin/activate

Your terminal prompt will change to reflect the virtual environment that you're using. Don't forget to deactivate the virtual environment when you want to install Python libraries for projects other than this one.

For a production system, you'd install the library from source. For our purposes, we can use the Python package manager to do it for us.

Swipe to scroll horizontally
python -m pip install psycopg2-binary

Now it's time to connect to the database. Here's a Python program that will import psycopg2 and create a database connection before creating a nested list with three video game records. For the purposes of the exercise, these are currently hard-coded into the program, although future tutorials will look at cleaner ways to automate the process of adding more entries.

The program then loops through each of those records, building an SQL query for each one by using string formatting to insert the necessary values from each list into the query. The cursor.execute command queues each of these queries, and the conn.commit command writes them to Postgres:

Swipe to scroll horizontally
import psycopg2#set up db connection
conn = psycopg2.connect('dbname=retronerds user=postgres host=localhost password=postgres')
cursor = conn.cursor()#create and run transactions
videogames = [
['Jet Set Willy', 'Split-screen platformer in which Miner Willy has to collect all the items left in his house after a party.', '1984', 'Software Projects','Platform'],
['Valhalla', 'Collect six mythical Norse objects while kicking around Asgard', '1983', 'Legend', 'Adventure'],
['Chuckie Egg', 'Collect all the eggs before the time expires. Watch out for the hens!', '1983', 'A&F Software', 'Platform']
]
cols = ['title', 'description', 'yor', 'publisher', 'genre']
for game in videogames:
SQL = "INSERT INTO games (title, description, yor, publisher, genre) VALUES (%s, %s, %s, %s, %s);"
cursor.execute(SQL, game)#commit the transactions
conn.commit()#close the db
conn.close()

Save this as insert-game.py and then run it with python3 insert-game.py. Now, pop back into Postgres and connect to the retronerds database using the steps outlined above. A quick SELECT * FROM games; reveals that our titles are all there.

Swipe to scroll horizontally
id title description yor publisher genre
1Jet Set WillySplit-screen platformer in which Miner Willy has to collect all the items left in his house after a party.1984 Software Projects Platform
2Valhalla Collect six mythical Norse objects while kicking around Asgard1983 Legend Adventure
3Chuckie EggCollect all the eggs before the time expires. Watch out for the hens!1983 A&F Software Platform

Notice how our Python program didn't specify an ID for any of these records. The database schema knew to insert one each for them and increment it automatically.

We've set up Postgres and shown how to manipulate it using both the native psql tool and Python, via a special library. It's the basis for building a create/read/update/delete (CRUD)-style system that you could use to keep records of any kind.

Other things that you could explore in the future include adding more tables to create a more complex record system with more entities. For example, you might pull the publisher field into its own table, so that you could store more information about each publisher. You could then use a foreign key to link a game to one or more publishers.

However, a system like this also needs a front end so that people who aren't psql ninjas can use it, and in a future tutorial, we'll look at the basics of creating a Python application that will interact with the system in more meaningful ways, including being able to add extra records without directly coding them into a Python program.

Danny Bradbury

Danny Bradbury has been a print journalist specialising in technology since 1989 and a freelance writer since 1994. He has written for national publications on both sides of the Atlantic and has won awards for his investigative cybersecurity journalism work and his arts and culture writing. 

Danny writes about many different technology issues for audiences ranging from consumers through to software developers and CIOs. He also ghostwrites articles for many C-suite business executives in the technology sector and has worked as a presenter for multiple webinars and podcasts.