Getting Started

Your First (simple) API

Let’s start with the simplest thing possible. We will expose some tables directly for reading and writing by anyone.

Start by making a database

createdb demo1

We’ll set it up with a film example (courtesy of Jonathan Harrington). Copy the following into your clipboard:

BEGIN;

CREATE TABLE director
(
  name text NOT NULL PRIMARY KEY
);

CREATE TABLE film
(
  id serial PRIMARY KEY,
  title text NOT NULL,
  year date NOT NULL,
  director text REFERENCES director (name)
    ON UPDATE CASCADE ON DELETE CASCADE,
  rating real NOT NULL DEFAULT 0,
  language text NOT NULL
);

CREATE TABLE festival
(
  name text NOT NULL PRIMARY KEY
);

CREATE TABLE competition
(
  id serial PRIMARY KEY,
  name text NOT NULL,
  festival text NOT NULL REFERENCES festival (name)
    ON UPDATE CASCADE ON DELETE CASCADE,
  year date NOT NULL
);

CREATE TABLE film_nomination
(
  id serial PRIMARY KEY,
  competition integer NOT NULL REFERENCES competition (id)
    ON UPDATE NO ACTION ON DELETE NO ACTION,
  film integer NOT NULL REFERENCES film (id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  won boolean NOT NULL DEFAULT true
);

COMMIT;

Apply it to your new database by running

# On OS X
pbpaste | psql demo1

# Or Linux
# xclip -selection clipboard -o | psql demo1

Start the PostgREST server and point it at the new database. (See the installation instructions.)

postgrest postgres://postgres:@localhost:5432/demo1 -a postgres --schema public

Note about database users

If you installed PostgreSQL with Homebrew on Mac then the database username may be your own login rather than postgres.

Populating Data

Let’s use PostgREST to populate the database. Install a REST client such as Postman. Now let’s insert some data as a bulk post in CSV format:

POST http://localhost:3000/festival
Content-Type: text/csv

name
Venice Film Festival
Cannes Film Festival

In Postman it will look like this

Festival bulk insert in postman

Festival bulk insert in postman

Notice that the post type is raw and that Content-Type: text/csv set in the Headers tab.

The server returns HTTP 201 Created. Because we inserted more than one item at once there is no Location header in the response. However sometimes you want to learn more about items which you just inserted. To have the server include the full results, include the header Prefer: return=representation.

At this point if you send a GET request to /festival it should return

[
  {
    "name": "Venice Film Festival"
  },
  {
    "name": "Cannes Film Festival"
  }
]

Now that you’ve seen how to do a bulk insert, let’s do some more and fully populate the database.

Post the following to /competition:

Now /director:

And /film:

Finally /film_nomination:

Getting and Embedding Data

First let’s review which films are stored in the database:

GET http://localhost:3000/film

It gives us back a list of JSON objects. What if we care only about the film titles? Use select to shape the output:

GET http://localhost:3000/film?select=title
[
  {
    "title": "Chuang ru zhe"
  },
  {
    "title": "The Look of Silence"
  },
  {
    "title": "Fires on the Plain"
  },
  ...
]

Here is where it gets cool. PostgREST can embed objects in its response through foreign key relationships. Earlier we created a join table called film_nomination. It joins films and competitions. We can ask the server about the structure of this table:

OPTIONS http://localhost:3000/film_nomination
{
  "pkey": [
    "id"
  ],
  "columns": [
    {
      "references": null,
      "default": "nextval('film_nomination_id_seq'::regclass)",
      "precision": 32,
      "updatable": true,
      "schema": "public",
      "name": "id",
      "type": "integer",
      "maxLen": null,
      "enum": [],
      "nullable": false,
      "position": 1
    },
    {
      "references": {
        "schema": "public",
        "column": "id",
        "table": "competition"
      },
      "default": null,
      "precision": 32,
      "updatable": true,
      "schema": "public",
      "name": "competition",
      "type": "integer",
      "maxLen": null,
      "enum": [],
      "nullable": false,
      "position": 2
    },
    {
      "references": {
        "schema": "public",
        "column": "id",
        "table": "film"
      },
      "default": null,
      "precision": 32,
      "updatable": true,
      "schema": "public",
      "name": "film",
      "type": "integer",
      "maxLen": null,
      "enum": [],
      "nullable": false,
      "position": 3
    },
    {
      "references": null,
      "default": "true",
      "precision": null,
      "updatable": true,
      "schema": "public",
      "name": "won",
      "type": "boolean",
      "maxLen": null,
      "enum": [],
      "nullable": false,
      "position": 4
    }
  ]
}

From this you can see that the columns film and competition reference their eponymous tables. Let’s ask the server for each film along with names of the competitions it entered. You don’t have to do any custom coding. Send this query:

GET http://localhost:3000/film?select=title,competition{name}
[
  {
    "title": "Chuang ru zhe",
    "competition": [
      {
        "name": "Golden Lion"
      }
    ]
  },
  {
    "title": "The Look of Silence",
    "competition": [
      {
        "name": "Golden Lion"
      }
    ]
  },
  ...
]

The relation flows both ways. Here is how to get the name of each competition’s name and the movies shown at it.

GET http://localhost:3000/competition?select=name,film{title}
[
  {
    "name": "Golden Lion",
    "film": [
      {
        "title": "Chuang ru zhe"
      },
      {
        "title": "The Look of Silence"
      },
      ...
    ]
  },
  {
    "name": "Palme d'Or",
    "film": [
      {
        "title": "The Wonders"
      },
      {
        "title": "Foxcatcher"
      },
      ...
    ]
  }
]

Why not learn about the directors too? There is a many-to-one relation directly between films and directors. We can alter our previous query to include directors in its results.

GET http://localhost:3000/competition?select=name,film{title,director{*}}
[
  {
    "name": "Golden Lion",
    "film": [
      {
        "title": "Manglehorn",
        "director": {
          "name": "David Gordon Green"
        }
      },
      {
        "title": "Belye nochi pochtalona Alekseya Tryapitsyna",
        "director": {
          "name": "Andrey Konchalovskiy"
        }
      },
      ...
    ]
  },
  ...
]

Singular Responses

How do we ask for a single film, for instance the second one we inserted?

GET http://localhost:3000/film?id=eq.2

It returns

[
  {
    "id": 2,
    "title": "The Look of Silence",
    "year": "2014-01-01",
    "director": "Joshua Oppenheimer",
    "rating": 8.3,
    "language": "Indonesian"
  }
]

Like any query, it gives us a result set, in this case an array with one element. However you and I know that id is a primary key, it will never return more than one result. We might want it returned as a JSON object, not an array. To express this preference include the header Prefer: plurality=singular. It will respond with

{
  "id": 2,
  "title": "The Look of Silence",
  "year": "2014-01-01",
  "director": "Joshua Oppenheimer",
  "rating": 8.3,
  "language": "Indonesian"
}

Why this approach to singular responses?

PostgREST knows which columns comprise a primary key for a table, so why not automatically choose plurality=singular when these column filters are present? The fact is it could come as a shock to a client that by adding one more filter condition it can change the entire response format.

Then why not expose another kind of route such as /film/2 to indicate one particular film? Because this does not accommodate compound keys. The convention complects a plurality preference with table key assumptions. We should separate concerns.

It turns out you can still have routes like /film/2. Use a proxy such as Nginx. It can rewrite routes such as /films/2 into /films?id=eq.2 and add the Prefer header to make the results singular.

Conclusion

This tutorial showed how to create a database with a basic schema, run PostgREST, and interact with the API. The next tutorial will show how to enable security for a multi-tenant blogging API.