PostgreSQL — Getting started

PostgreSQL is a relatively popular relational database that uses and extends the SQL language. In this article we're going to set up a new PostgreSQL database using docker, and look at a few methods we can use to interact with our database with some simple CRUD operations.

Setting up our docker PostgreSQL database

The easiest way I find to have a repeatable and sharable instance of a docker image running is to use a docker-compose.yml file:

services:
  postgres:
    image:postgres
    container_name:'postgres'
    ports:
      - '5432:5432'
    volumes:
      - ./:/app
    environment:
      - POSTGRES_DB=test
      - POSTGRES_USER=test
      - POSTGRES_PASSWORD=test

We're using the official postgres image and we've just named the container 'postgres', but you can name this whatever you would like. There is port mapping to the default postgres port (5432) which means that our container can communicate on this port. The entire current directory has been added to a volume mapped to app within the container. This will be useful for experimenting locally, but I would advise against this in a real world application — either have a narrower focus or modify your dockerfile to COPY files when the container is being created. Finally we have some environment variables which the postgres image will use to create a user and database for us to use within the running container.

Assuming that you have docker running on your machine, it should be as simple as running docker-compose up from your favourite terminal. You should see some output with this line appearing towards the bottom: database system is ready to accept connections

Now if we open up a new tab or window in our terminal we can typedocker ps and hopefully see something like this:

CONTAINER ID   IMAGE      COMMAND                  CREATED         STATUS         PORTS                    NAMES
2c17c288c33f   postgres   "docker-entrypoint.s…"   8 seconds ago   Up 7 seconds   0.0.0.0:5432->5432/tcp   postgres

Interacting with our database

Like most modern databases PostgreSQL has an interactive terminal that you can use to query the database. If you had postgres installed on your own machine, it would be as simple as typing psql into your terminal. Since we are using docker, we need to exec into the container to access the interactive terminal. It's quite simple to do this:

docker exec -it postgres psql -U test

docker exec tells docker to execute a command against a running container. In this case we have supplied the container name “postgres” along with the command “psql -U test”. The order of the arguments and flags here are very important as the-it flag is for thedocker exec command and the-Uflag is for the psql command. The-it flag instructs docker to create an interactive window and the -U flag is for passing a username to psql. This username should match the name defined in the environment variables in thedocker-compose.yml file.

When you run that command you should hopefully see your command prompt change to test=#. As an aside, If the prompt has a hash like this it means that you are a database superuser. If you were to log in with a different user who didn't have superuser privileges, the prompt would appear like thisotheruser=> with an arrow instead of a hash.

Let's check that we can run some simple SQL:

SELECT version();

This will return the current version of postgres that is running in the container. At the time of writing for me it was 15.1. We can also do some simple arithmetic with SQL:

SELECT 1+2;

Which should return 3.

Modifying our database using queries written in sql files

psql is a great tool for doing some investigations or quick checks, however we will commonly be working with queries which can get a little long and complicated, so I'd recommend having these in their own file so that they can be nicely formatted and easily version controlled.

We're going to create a very simple sql file to create a new table called “friends”. Create a new file calledcreate_friends_table.sql and enter the following:

CREATE TABLE friends (
 id SERIAL PRIMARY KEY,
 name varchar(80),
 age int
); 

This query will create a new table called “friends” which will have the columns id,name andage. Each of these columns are given a type. int refers to an integer value and varchar(80) for strings up to length 80. The type definition forid isSERIAL — this means that it will create an incrementing numerical value everytime you add a new row to the table (without you specifying it).PRIMARY KEY is used to highlight this field as a unique value that can be used to reference an entry. This will be very useful when we come to building a relationship between two tables.

We can now run this query using our exec command with slightly different arguments for psql:

docker exec postgres psql -U test -f /app/create_friends_table.sql

We've removed the -it flag as we do not require an interactive window, and we've added the-f flag with the path to our sql file. You may be wondering at this point why our path has/app at the start. This is because of the volume that we created in ourdocker-compose.yml file (./:/app). Everything at the directory with yourdocker-compose.yml file on your local machine will be available in the container at/app.

Running this command should give a response ofCREATE TABLE.

Some basic CRUD operations

You've already seen an example of aCREATE command, but we need to understand how to add, read, update and delete data from this and other tables. For any SQL that follows, you can either use the interactive psql command or create an sql file and run the command at the end of the last section (replacing the filename).

Insert

We're going to INSERT some friends into the friends table (Sadly I only have a few friends, maybe it's because I keep track of them in a postgresql database…)

INSERT INTO friends (name, age) 
VALUES ('Bob', 29), ('Sam', 38), ('Fred' , 21);

This SQL reads quite nicely — we're inserting into the friends table some values. The (name, age) part of the query is optional but recommended, it defines the order in which you are supplying the values. Without this, the query will assume that you will add values in the same order that columns were created on the table. In this scenario, both would yield the same result, but by naming the columns like this we could change the order, or omit certain columns if appropriate. Finally, we provide 3 rows of data by wrapping each row in brackets and separating them with a comma.

Notice as well that I have not included theid column. You can add this if you want, but it is not recommended as you would need to find the correct next value to use, and you would likely run into issues when multiple queries were being run concurrently (it's possible, but just let SERIAL do it's thing).

Select

Now that we have some rows to read, let's have a look at them.

SELECT * FROM friends;

This is the most simple way to read a table, but it will return absolutely everything from the table friends. This is often not what you want, so in a future article I will explain how we can use SQL to narrow down our query. This command should return this if you've been following along:

 id | name | age 
----+------+-----
 1  | Bob  | 29
 2  | Sam  | 38
 3  | Fred | 21

Notice that the id has been populated for us with auto incrementing values.

Update

My good friend Bob has just turned 30! We need to update his record in the database. There's a few ways we can do this. I'll start with the most basic one.

Since we know that Bob has an id of 1 in our friends table, we can do this:

UPDATE friends
SET age = 30
WHERE id = 1;

Running this and then the SELECT query again, should return

 id | name | age 
----+------+-----
 2  | Sam  | 38
 3  | Fred | 21
 1  | Bob  | 30

Notice that for me Bob has moved to the bottom and his age has been updated to 30. By default, the SELECT query will return the results in whatever manner seems most efficient, however we can specify how toORDER the response in our SELECT query:

SELECT * FROM friends ORDER BY id;

Specifying the id in the query is not often possible, so we often will need to perform some sort of search on the data. We're technically already doing this with WHERE id = 1.WHERE can also be used withSELECT statements to limit the results in the same way. For selecting ‘Bob', we can use theWHERE clause like this:

SELECT * FROM friends 
WHERE name = 'Bob';

Which will return just Bob's entry:

 id | name | age 
----+------+-----
 1  | Bob  | 30

It's good practice to test yourWHERE clause withSELECT queries before using it in yourUPDATE query to make sure that you're only going to be updating the record(s) you expect to.

Delete

There are similar considerations when deleting data to updating data. You need to be sure that you're deleting the correct rows, so always make sure the WHERE clause is correct before performing a delete.

Sadly at Bob's 30th birthday party we had a falling out, so I need to remove him from the database:

DELETE FROM friends 
WHERE name = 'Bob';

Next Steps

This is just the very basics of interacting with a PostgreSQL database. In future articles I will expand on these fundamentals. We'll look more into querying a table using conditions, joining data from multiple tables using relationships and using transactions to ensure data is modified in a safe and reliable way.

Other stories about PostgreSQL:

Dumping, Relationships and more

Not that kind of dump…

27 Feb 2023

I also write about:

FrontEnd

Testing

TypeScript

Golang

Working Practices