This post is part of a series of posts created by the two newest members of our Developer Advocate team here at IBM Cloud Data Services. In honour of the book Seven Databases in Seven Weeks by Eric Redmond and Jim R. Wilson, we challenged Lorna and Matt to take a new database from our portfolio every day, get it set up and working, and write a blog post about their experiences. Each post reflects the story of their day with a new database. We’ll update our seven-days GitHub repo with example code as the series progresses. —The Editors

  • Database type: SQL-Compliant RDBMS
  • Best tool for: Creating systems that value data integrity and where complexity may be beyond that of other RDBMS offerings
Postgres logo
It’s everyone’s favorite blue elephant: PostgreSQL!

Overview

PostgreSQL is an open source database in the familiar RDBMS style, boasting 15 years of active development and a focus on data integrity and reliability. It has a proven history of performing at an enterprise level.

PostgreSQL has an extensive selection of data types and other functionality not always found in competitors. It is also the most SQL-compliant of the major offerings, with a main goal of being fully compliant with the SQL standard. This will come in handy when you need to integrate your application with other SQL based systems.

As with most open source software, getting yourself up and running is as simple as downloading and installing. For today, we are going to use the cloud based offering from Compose to create a simple database and show you how to get started with PostgreSQL. We’ll demonstrate a few of the features you may not be so familiar with (spoilers: the array data type and constraints on insert).

Create Your Database

Create an account on Bluemix if you don’t have one already, and add a service “Compose for PostgreSQL” to your account. Whilst your PostgreSQL instance is spinning up, head over to the PostgreSQL downloads page and install the psql command line tool.

Find your psql connection string for the command line from Service Credentials tab on the dashboard for the PostgreSQL service, and use that plus your password to connect. It should look something like this (copy from your bluemix page rather than the example below):

psql "sslmode=require host=bluemix-sandbox-dal-9-portal.4.dblayer.com port=18940 dbname=compose user=sevendays"

Once you have entered your password (visible in the uri field) you should end up at a prompt for the default database, which is named compose.

compose=>

Congratulations, you are now connected to your PostgreSQL database!

Although the folks at Compose are kind enough to provide us with a compose database as a starting point, today we’ll be building an application to represent the books in a book store’s product catalog. With that in mind, let’s start by creating a database for our book data.

-- Create database
CREATE DATABASE bookstore;

In PostgreSQL, your connection is to a specific database. To use the new database, you’ll need to reconnect. Luckily there’s a handy way to do it while retaining all other connection details:

\connect bookstore

Design the Bookstore Database

Now to create some tables. As we mentioned earlier, PostgreSQL is an RDBMS-style database, so we need to define our table before we can use it. Creating a basic schema should be a simple task for anyone with some SQL experience. Check out our example:

-- Create books table with auto increment key
CREATE TABLE books
(
    book_id serial primary key,
    title text not null,
    description text not null,
    author_id int not null
);

-- Create authors table with auto increment key
CREATE TABLE authors
(
    author_id serial primary key,
    name text not null
);

Here we are creating two tables: books and authors. Both have auto-incrementing primary keys (the serial keyword indicates auto-incrementing). You may also have noticed that the books table has an author_id column, which could be used as a foreign key — we want to add a simple constraint here to help enforce this relationship and improve the integrity of our database.

-- Add a foreign key constraint so author must exist before adding a book
ALTER TABLE books ADD FOREIGN KEY (author_id) REFERENCES authors (author_id);

This means that any value added in books.author_id must exist in authors.author_id.

To check that our tables have been added successfully, we can do the following:

bookstore=> \dt;
        List of relations
 Schema |  Name   | Type  | Owner 
--------+---------+-------+-------
 public | authors | table | admin
 public | books   | table | admin
(2 rows)

And to check the definitions of our tables we can do the following:

bookstore=> \d books;
                                 Table "public.books"
   Column    |       Type       |                        Modifiers                        
-------------+------------------+---------------------------------------------------------
 book_id     | integer          | not null default nextval('books_book_id_seq'::regclass)
 title       | text             | not null
 description | text             | not null
 author_id   | integer          | not null
Indexes:
    "books_pkey" PRIMARY KEY, btree (book_id)
Foreign-key constraints:
    "books_author_id_fkey" FOREIGN KEY (author_id) REFERENCES authors(author_id)

Add the Book Data

Inserting data is as simple as you would expect from an SQL-based database:

-- Add an author
INSERT INTO authors (name) VALUES ('Roald Dahl');

-- Add a book
INSERT INTO books (title, description, author_id) VALUES ('The BFG', 'On a dark, silvery moonlit night, Sophie is snatched from her bed by a giant. Luckily it is the Big Friendly Giant, the BFG, who only eats snozzcumbers and glugs frobscottle.', 1);

Remember the constraint we added at the end of the previous section? Well, lets see what happens when we try to add data into the books table that doesn’t comply with our foreign key constraint:

INSERT INTO books (title, description, author_id) VALUES ('Should fail', 'Because author_id does not exist', 100);
-- #ERROR:  insert or update on table "books" violates foreign key constraint "books_author_id_fkey"
-- #DETAIL:  Key (author_id)=(100) is not present in table "authors".

As expected, we are unable to add this book to our table as the supplied author_id is not present in the authors table — excellent!

Querying the Books Data

Again, getting data out of your PostgreSQL database is nice and easy:

-- Get everything from the books table
SELECT * FROM books;

-- Get everything form the authors table
SELECT * FROM authors;

And the join syntax should also be familiar to anyone who has done this in other flavours of SQL:

-- Join between books and authors
SELECT books.title, authors.name FROM books INNER JOIN authors ON books.author_id = authors.author_id;
           title           |      name       
---------------------------+-----------------
 The BFG                   | Roald Dahl
 A Squash and a Squeeze    | Julia Donaldson
 The Gruffalos Child       | Julia Donaldson
 The Snail And The Whale   | Julia Donaldson
 James and the Giant Peach | Roald Dahl
 The Gruffalo              | Julia Donaldson
 Room On The Broom         | Julia Donaldson

Here we are using our foreign key to join the books and authors tables together and pull out the title and name fields.

Changing Data

Updating existing data can be achieved using the UPDATE command:

UPDATE books SET description='On a dark, silvery moonlit night, Sophie is snatched from her bed by a giant. Luckily it is the Big Friendly Giant, the BFG, who only eats snozzcumbers and glugs frobscottle. But there are other giants in Giant Country.' WHERE book_id = 1;

Here we are updating the description of one of our books, namely the book whose book_id is 1.

Deleting is a similar process:

DELETE FROM books WHERE book_id = 1;

Important: Be careful when you are deleting or updating your data. If you forget to include the WHERE clause at the end of your statement, you are going to update or delete every row in your table — probably not what you intended to do!

PostgreSQL Has an Array Data Type

One of the areas in which PostgreSQL stands out from the crowd is the extensive range of data types it offers. One of these which may not be familiar to users of other SQL databases is the Array data type. PostgreSQL allows a column to be defined as a multi-dimensional array!

Below you will see that we are adding a new column releases to our books table, to record the release dates of each edition of a book. The [] notation is used to define this column as an Array.

ALTER TABLE books ADD COLUMN releases date[] NULL;

We can then update our book records to include this data, defining a set of dates per row:

UPDATE books SET releases = '{1984-05-03, 1998-03-08}' WHERE book_id = 1;
UPDATE books SET releases = '{1987-12-06, 1995-06-23}' WHERE book_id = 2;

We can then query this array in a number of ways:

-- Find any book where the first release was after January 1st 2000
SELECT title, releases FROM books WHERE releases[1] > '2000-01-01';

-- Find any book where ANY release was after January 1st 2000
SELECT title, releases FROM books WHERE '2000-01-01' < ANY ( releases );

-- Find out how many releases each book has
SELECT title, releases, cardinality(releases) num_releases FROM books;

Yes, you read that right! In PostgreSQL, array indexing starts at 1, not at 0.

If a book gets a new edition, we will want to add a new release date to our array. Add it using the array_append function.

UPDATE books SET releases = array_append(releases, '2001-05-07') WHERE book_id = 1;

There are many other array functions available that can be used to manipulate arrays in PostgreSQL.

Using Check Constraints to Sanity-Check Data

We added a foreign key constraint earlier, but PostgreSQL also features something called a check constraint, which allows you to validate data against a simple boolean expression before storing it in the database.

Here we add a new column price with a Check Constraint in place to make sure that the price must be greater than 5.

ALTER TABLE books ADD COLUMN price float constraint price_check check(price > 5);
UPDATE books SET price = 6.95 WHERE book_id = 1;

We can see this check constraint in action by attempting to update an existing book to have a price of 5 or below:

UPDATE books SET price = 2.5 WHERE book_id = 1;                                  
-- #ERROR:  new row for relation "books" violates check constraint "price_check"
-- #DETAIL:  Failing row contains (1, The BFG, On a dark, silvery moonlit night, Sophie is snatched from her bed by a giant ..., 1, {1984-05-03, 1998-03-08, 2001-05-07}, 6.95)

This is another example of how PostgreSQL ranks data integrity high on its list of priorities.

Conecting To PostgreSQL From Your Application

PostgreSQL is well-supported by all the programming languages that we tried. You should have an easy time working with it, regardless of your stack. For quick reference, we’ve provided some super-simple examples of getting connected to our PostgreSQL database hosted on Compose and fetching data in a number of different languages (PHP, NodeJS, Python and Go). You can find all the snippets below in our seven-days GitHub repo.

Connecting to PostgreSQL from PHP

To connect from PHP you’ll need to have the PDO_PGSQL extension installed in PHP in order to run the code below (more information in the php.net documentation). Using PDO gives us access to an easy, object-oriented interface. PDO uses a DSN (Data Source Name) as the first parameter to its constructor, and here we can reuse the connection string we used with the command-line client, putting it after the initial pgsql: part of that parameter.

<?php

$db = new PDO("pgsql:sslmode=require host=bluemix-sandbox-dal-9-portal.4.dblayer.com port=18940 dbname=bookstore user=sevendays password=DBEIQOCFEBUBQYFI");

$stmt = $db->query('SELECT * FROM books');

$result = $stmt->execute();
if($result) {
    print_r($stmt->fetchAll(PDO::FETCH_ASSOC));
}

Once you’re connected, the PDO aspect will make PostgreSQL interactions into a familiar experience if you’re a MySQL user. Many projects use either an ORM or another database wrapper, so you may not be accustomed to working directly with PDO. The vast majority of these libraries will seamlessly support PostgreSQL, but look out for situations where raw SQL is used as there are differences between the SQL syntax supported by different databases.

Connecting to PostgreSQL from Node.js

From Node.js we can include PostgreSQL functionality in our applications by bringing in the pg library. Once it is installed, we can easily connect to our PostgreSQL database by crafting the connection string, as seen in the example below:

const pg = require('pg');
const conString = "postgres://sevendays:DBEIQOCFEBUBQYFI@bluemix-sandbox-dal-9-portal.4.dblayer.com:18940/bookstore";

// create connection
pg.connect(conString, function(err, client, done) {
  
  // handle the error
  if(err) throw err;
  
  // do the query!
  client.query('SELECT * FROM books', function(err, result) {

    // handle error
    if(err) throw err;

    // log out the results
    console.log(result.rows);

    // close connection
    client.end();
    
  });

});

Look at the rest of the library documentation for how pg lets you work with PostgreSQL from your Node.js applications.

Connecting to PostgreSQL from Python

Python has a good PostgreSQL library called psycopg2, which is strongly recommended for your Python projects and, happily, is also pretty painless to use. Here it is in action in a simple example:

import psycopg2
import psycopg2.extras

conn = psycopg2.connect("sslmode=require host=bluemix-sandbox-dal-9-portal.4.dblayer.com port=18940 dbname=bookstore user=sevendays password=DBEIQOCFEBUBQYFI")

cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)

# query the DB
cur.execute("SELECT * FROM books")
row = cur.fetchone()
while row:
    print("Title: " + row["title"])
    print("Description: " + row["description"])
    print("Author_id: " + str(row["author_id"]))
    print("Price: " + str(row["price"]))
    print("Releases: " + str(row["releases"]))
    row = cur.fetchone()

cur.close()

By default, the cursor will fetch data as a simple tuple. In this example, the psycopg2.extras library has been used to enable setting up the cursor to return dicts instead and so allow us to access the various fields by name. Returning the dict structure makes our code much more readable and maintainable, especially in a more complex project than this one! In Python string concatenation, for data types that are not strings (in our case, the int, float and array columns), they need to be cast to strings before they can be printed.

Connecting to PostgreSQL from Go

Go is a newer entry to the world of scripting languages, but it’s already very mature and has all the libraries you would expect it to have — including support for PostgreSQL. Support is available in the pq package, which extends the database/sql package to add PostgreSQL support. The pattern looks much like the other languages in that we can use our command-line connection string and then query the database:

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/lib/pq"
)

func main() {
    db, err := sql.Open("postgres", "sslmode=require host=bluemix-sandbox-dal-9-portal.4.dblayer.com port=18940 dbname=bookstore user=sevendays password=DBEIQOCFEBUBQYFI")
    checkErr(err)
    defer db.Close()

    rows, err := db.Query("SELECT * FROM books")
    checkErr(err)

    fmt.Println("Book ID | Title                     | Description  ")
    for rows.Next() {
        var book_id int
        var author_id int
        var title string
        var description string
        var price float32
        var releases string
        err = rows.Scan(&book_id, &title, &description, &author_id, &releases, &price)
        checkErr(err)
        fmt.Printf(" %6v | %25v | %20v \n", book_id, title, description)
    }
}

func checkErr(err error) {
    if err != nil {
        panic(err)
    }
}

To fetch data from the database, we first define the variables that will hold this data, then inside the loop we Scan each row and populate those variables as appropriate before quickly outputting them.

Conclusion

PostgreSQL has been a stalwart of the open source database world for a long time, and it has earned its excellent reputation. Beloved particularly by the Python and Perl communities, basically every programming language provides excellent support for PostgreSQL.

While many web applications use the traditionally lighter MySQL option, PostgreSQL is a more fully-featured and, in some cases, more performant option while still being similar enough to other SQL databases to feel quite familiar. Especially given the wide library support and growing options for PostgreSQL hosting, it’s an exciting technology going through an exciting time.

Do you have a language you’d like to see a simple PostgreSQL example for? Let us know in the comments below, or share your own on https://github.com/ibm-cds-labs/seven-days — pull requests are very much welcome!

Join The Discussion

Your email address will not be published. Required fields are marked *