PostgreSQL Schema Design

The Postgres database is rich with features well beyond that of any other database. However, most developers do not know the extent to which they can leverage the features in Postgres to completely express their application business logic in the database.

Often developers may find themselves re-implementing authentication and authorization in their apps, when Postgres comes with application level security features out of the box. Or perhaps developers may rewrite basic insert functions with some extra app logic where that too may be handled in the database.

This reimplementation of features that come with Postgres is not just an inefficient way to spend developer resources, but may also result in an interface that is slower than if the logic was implemented in Postgres itself. PostGraphile aims to make developers more efficient and their APIs faster by packaging the repeatable work in one open source project that encourages community contributions.

In this tutorial we will walk through the Postgres schema design for a forum application with users who can login and write forum posts. While we will discuss how you can use the schema we create with PostGraphile, this article should be useful for anyone designing a Postgres schema.

If you haven't installed PostGraphile already, you can follow ourQuick Start Guide to get PostGraphile up and running.

Table of Contents

• • Set Returning Functions

• Authentication and Authorization

The Basics

Setting Up Your Schemas

All of our database objects will go into one or two custom Postgres schemas. A schema is essentially a namespace, it allows you to create tables with the same name like a.person and b.person .

You can name your schema anything, we recommend naming your schema after your app. This way if you are working on multiple apps in the same database (this might only realistically happen in development), you can easily query the databases of the different apps. We are going to create two schemas: forum_example , and forum_example_private . To create these schemas we use the CREATE SCHEMA command.

create schema forum_example;
create schema forum_example_private;

You could create more or less schemas, it is all up to you and how you want to structure your database. We decided to create two schemas. One of which, forum_example , is meant to hold data users can see, whereas forum_example_private will never be directly accessible to users.

Theoretically we want a user to be able to log in directly to our Postgres database, and only be able to create, read, update, and delete data for their user all within SQL. This is a mindshift from how we traditionally use a SQL database. Normally, we assume whoever is querying the database has full visibility into the system as the only one with database access is our application. In this tutorial, we want to restrict access at the database level. Don’t worry though! Postgres is very secure about this, users will have no more permissions then that which you explicitly grant.

Note:When starting PostGraphile, you will want to use the name of the schema you created with the --schema option, like so: postgraphile --schema forum_example . Also, don’t forget to add the --watch flag, with watch mode enabled PostGraphile will update your API as we add tables and types throughout this tutorial.

The Person Table

Now we are going to create the tables in our database which will correspond to our users. We will do this by running the Postgres CREATE TABLE command. Here is the definition for our person table:

create table forum_example.person (
  id               serial primary key,
  first_name       text not null check (char_length(first_name) < 80),
  last_name        text check (char_length(last_name) < 80),
  about            text,
  created_at       timestamp default now()
);

Now we have created a table with id , first_name , last_name , about , and created_at columns (we will add an updated_at column later). Let’s break down exactly what each line in this command does, we will only do this once. If you already understand, you can skip ahead.

1. create table forum_example.person : This tells Postgres that we are creating a table in the forum_example schema named person . This table will represent all of our forum’s users.
2. id serial primary key : This line establishes an auto-incrementing id field which is always guaranteed to be unique. The first person we create will have an id of 1, the second user will have an id of 2, and so on. The primary key bit is also very important. PostGraphile will use the primary key of a table in many places to uniquely identify an object, including the globally unique id field.
3. first_name text not null check (char_length(first_name) < 80) : We want all of our users to enter their first name and last name seperately, so this column definition will create a column named first_name , of type text , that is required ( not null ), and that must be less than 80 characters long ( check (char_length(first_name) < 80) ). Check constraints are a very powerful feature in Postgres for data validation.
4. last_name text check (char_length(last_name) < 80) : This is very similar to our column definition for first_name , except it is missing not null . This means that unlike the first_name column, last_name is not required.
5. about text : We want users to be able to express themselves! So they get to write a mini forum post which will go on their profile page.
6. created_at timestamp default now() : This final column definition will provide us with some extra meta-information about their user. If not specified explicitly, the created_at timestamp will default to the time the row was inserted.

And that’s our person table! Pretty simple, right?

The syntax and features of the Postgres CREATE TABLE command are fairly easy to learn and understand. Creating tables is the easiest, but also the most fundamental part of your schema design.

Note:We prefer singular identifers like forum_example.person over forum_example.people because when you create a table, it is like you are creating a class in a statically typed language. Classes have singular names like “Person” while collections will often have plural names like “People.” Table as a class is a better analogy than table as a collection because Postgres itself will internally call tables “classes.”

create extension if not exists "uuid-ossp";

create table forum_example.person (
  id uuid primary key default uuid_generate_v1mc(),
  ...
);

create extension if not exists "pgcrypto";

create table forum_example.person (
  id uuid primary key default gen_random_uuid(),
  ...
);

Table Documentation

Now that we have created our table, we want to document it within the Postgres database. By adding comments to our table and its columns using the Postgres COMMENT command, we will allow tools like PostGraphile to display rich domain specific documentation.

To add comments, just see the SQL below:

comment on table forum_example.person is 'A user of the forum.';
comment on column forum_example.person.id is 'The primary unique identifier for the person.';
comment on column forum_example.person.first_name is 'The person’s first name.';
comment on column forum_example.person.last_name is 'The person’s last name.';
comment on column forum_example.person.about is 'A short description about the user, written by the user.';
comment on column forum_example.person.created_at is 'The time this person was created.';

Incredibly simple, yet also incredibly powerful.

Note:Feel free to write your comments in Markdown! Most tools, including GraphiQL which PostGraphile uses, will render your comments with the appropriate styles.

With this we have completed our person table, now let’s create a table for our forum posts.

The Post Table

The users of our forum will want to be able to create posts. That’s the entire reason we have a forum after all. To create the post table we go through a very similar process as creating our forum_example.person table, but first we want to create a type we will use in one of the columns. See the SQL below:

create type forum_example.post_topic as enum (
  'discussion',
  'inspiration',
  'help',
  'showcase'
);

The Postgres CREATE TYPE command will let you create a custom type in your database which will allow you to do some really cool things. You can create a composite type which is basically a typed object in GraphQL terms, you can create a range type which represents exactly what you might think, or you can create an enum type which is what we did here.

Enum types are a static set of values, you must use one of the string values that make up the enum in any column of the enum’s type. Having this type is useful for us, because we want our forum posts to have one, or none, topics so user’s may easily see what a post is about.

enum PostTopic {
  DISCUSSION
  INSPIRATION
  HELP
  SHOWCASE
}

create type my_schema.my_type as (
  foo integer,
  bar integer
);

type MyType {
  foo: Int
  bar: Int
}

Now it is time to actually create our post table:

create table forum_example.post (
  id               serial primary key,
  author_id        integer not null references forum_example.person(id),
  headline         text not null check (char_length(headline) < 280),
  body             text,
  topic            forum_example.post_topic,
  created_at       timestamp default now()
);

comment on table forum_example.post is 'A forum post written by a user.';
comment on column forum_example.post.id is 'The primary key for the post.';
comment on column forum_example.post.headline is 'The title written by the user.';
comment on column forum_example.post.author_id is 'The id of the author user.';
comment on column forum_example.post.topic is 'The topic this has been posted in.';
comment on column forum_example.post.body is 'The main body text of our post.';
comment on column forum_example.post.created_at is 'The time this post was created.';

Pretty basic. Our headline is twice as long as a tweet, and to use our forum_example.post_topic type we wrote it as the column type just as we may write integer as the column type. We also made sure to include comments.

Now that we have gone over the basics, let’s explore Postgres functions and see how we can use them to extend the functionality of our database.

Database Functions

The Postgres CREATE FUNCTION command is truly amazing. It allows us to write functions for our database in SQL, and other languages including JavaScript and Ruby!

The following is a basic Postgres function:

create function add(a int, b int) returns int as $$
 select a + b
$$ language sql stable;

Note the form. The double dollar signs ( $$ ) open and close the function, and at the very end we have language sql stable . language sql means that the function is written in SQL, pretty obvious. If you wrote your function in Ruby it may be language plruby . The next word, stable , means that this function does not mutate the database. By default Postgres assumes all functions will mutate the database, you must mark your function with stable for Postgres, and PostGraphile, to know your function is a query and not a mutation.

Note:If you are interested in running JavaScript or Ruby in Postgres, check out PL/V8 and PL/ruby respectively. It is recommended that you use SQL and PL/pgSQL (which comes native with Postgres) whenever you can (even if they are a pain). There is plenty of documentation and StackOverflow answers on both SQL and PL/pgSQL. However, there are alternatives if you so choose.

That function above isn’t so useful for us in our schema, so let’s write some functions which will be useful. We will define three.

First, a function which will concatenate the users first and last name to return their full name:

create function forum_example.person_full_name(person forum_example.person) returns text as $$
  select person.first_name || ' ' || person.last_name
$$ language sql stable;

comment on function forum_example.person_full_name(forum_example.person) is 'A person’s full name which is a concatenation of their first and last name.';

Second, a function which will get a summary of a forum post:

create function forum_example.post_summary(
  post forum_example.post,
  length int default 50,
  omission text default '…'
) returns text as $$
  select case
    when post.body is null then null
    else substr(post.body, 0, length) || omission
  end
$$ language sql stable;

comment on function forum_example.post_summary(forum_example.post, int, text) is 'A truncated version of the body for summaries.';

Third, a function that will get a person’s most recent forum post.

create function forum_example.person_latest_post(person forum_example.person) returns forum_example.post as $$
  select post.*
  from forum_example.post as post
  where post.author_id = person.id
  order by created_at desc
  limit 1
$$ language sql stable;

comment on function forum_example.person_latest_post(forum_example.person) is 'Get’s the latest post written by the person.';

Don’t get too stuck on the function implementations. It is fairly easy to discover how to express what you want in SQL through a quick search of the Postgres documentation (which is excellent!). These functions are here to give you some examples of what functions in Postgres look like. Also note how we added comments to our functions with the COMMENT command, just like we add comments to our tables.

type Person {
  id: Int!
  firstName: String!
  lastName: String
  ...
  fullName: String
  latestPost: Post
}

Set Returning Functions

Sometimes it is useful to not just return single values from your function, but perhaps entire tables. What returning a table from a function could mean is you could define a custom ordering, hide rows that were archived, or return a user’s activity feed perhaps. In our case, this Postgres feature makes it easy for us to implement search:

create function forum_example.search_posts(search text) returns setof forum_example.post as $$
  select post.*
  from forum_example.post as post
  where position(search in post.headline) > 0 or position(search in post.body) > 0
$$ language sql stable;

comment on function forum_example.search_posts(text) is 'Returns posts containing a given search term.';

The difference with this function and the ones before is the return signature reads returns setof forum_example.post . This function will therefore return all of the posts that match our search condition and not just one.

{
  searchPosts(search: "Hello, world!", first: 5) {
    edges {
      cursor
      node {
        headline
        body
      }
    }
  }
}

Note:Postgres has awesome text searching capabilities - if you want high quality full text searching you don’t need to look outside Postgres. Instead look into the Postgres Full Text Search functionality. It is a great feature, but a bit much for our simple example, so we just used a simple string position function instead.

Note:Returning an array ( returns post[] ), and returning a set ( returns setof post ) are two very different things. When you return an array, every single value in the array will always be returned. However, when you return a set it is like returning a table. Users can paginate through a set using limit and offset , but not an array.

Triggers

You can also use Postgres functions to define triggers. Triggers in Postgres allow you to hook into events that are happening on your tables such as inserts, updates, or deletes. You define your triggers with the CREATE TRIGGER command, and all trigger functions must return the special type trigger .

To demonstrate how triggers work, we will define a trigger that sets an updated_at column on our forum_example.person and forum_example.post tables whenever a row is updated. Before we can write the trigger, we need to make sure forum_example.person and forum_example.post have an updated_at column! To do this we will use the ALTER TABLE command.

alter table forum_example.person add column updated_at timestamp default now();
alter table forum_example.post add column updated_at timestamp default now();

Our updated_at column has now been added to our tables and looks exactly like our created_at column. It’s a timestamp which defaults to the time the row was created. Next, let us define our triggers:

create function forum_example_private.set_updated_at() returns trigger as $$
begin
  new.updated_at := current_timestamp;
  return new;
end;
$$ language plpgsql;

create trigger person_updated_at before update
  on forum_example.person
  for each row
  execute procedure forum_example_private.set_updated_at();

create trigger post_updated_at before update
  on forum_example.post
  for each row
  execute procedure forum_example_private.set_updated_at();

To define our trigger we ran three commands. First we created a function named set_updated_at in our forum_example_private schema because we want no one to directly call this function as it is simply a utility. forum_example_private.set_updated_at also returns a trigger and is implemented in PL/pgSQL .

After we define our forum_example_private.set_updated_at function, we can use it in the triggers we create with the CREATE TRIGGER command. The triggers will run before a row is updated by the UPDATE command and will execute the function on every row being updated.

Note:If you want to do some CPU intensive work in triggers, perhaps consider using Postgres’s pub/sub functionality by running the NOTIFY command in triggers and then use the LISTEN command in a worker service. If Node.js is your platform of choice, you could use the pg-pubsub package to make listening easier.

That’s about it as far as Postgres functions go! They are a fun, interesting, and useful topic to understand when it comes to good Postgres schema design. Always remember, the Postgres documentation is your best friend as you try to write your own functions. Some important documentation articles we mentioned for your reference are as follows:

• CREATE FUNCTION
• CREATE TRIGGER
• PL/pgSQL

Next up, we are going to learn about auth in Postgres and PostGraphile!

Authentication and Authorization

Authentication and authorization is incredibly important whenever you build an application. You want your users to be able to login and out of your service, and only edit the content your platform has given them permission to edit. Postgres already has great support for authentication and authorization using a secure role based system, so PostGraphile just bridges the gap between the Postgres role mechanisms and HTTP based authorization.

However, before we can dive into implementing authentication, we are missing some pretty important data in our schema. How are users supposed to even login? Not by guessing their first and last name one would hope, so we will define another table which will store user emails and passwords.

Storing Emails and Passwords

To store user emails and passwords we will create another table in the forum_example_private schema.

create table forum_example_private.person_account (
  person_id        integer primary key references forum_example.person(id) on delete cascade,
  email            text not null unique check (email ~* '^[email protected]+\..+$'),
  password_hash    text not null
);

comment on table forum_example_private.person_account is 'Private information about a person’s account.';
comment on column forum_example_private.person_account.person_id is 'The id of the person associated with this account.';
comment on column forum_example_private.person_account.email is 'The email address of the person.';
comment on column forum_example_private.person_account.password_hash is 'An opaque hash of the person’s password.';

Warning:Never store passwords in plaintext! The password_hash column will contain the user’s password after it has gone through a secure hashing algorithm like Bcrypt . Later in this tutorial we will show you how to securely hash a password in Postgres.

Why would we choose to create a new table in the forum_example_private schema instead of just adding columns to forum_example.person ? There are a couple of answers to this question. The first and most fundamental is seperation of concerns. By moving email and password_hash to a second table we make it much harder to accidently select those values when reading forum_example.person . Also, users will not have the permission to directly query data from forum_example_private (as we will see) making this approach more secure. This approach is also good for PostGraphile as the forum_example_private schema is never exposed in PostGraphile, so you will never accidently expose password hashes in GraphQL.

Besides those arguments, moving the person’s account to a seperate table is also good database design in general. Say you have multiple types of users. Perhaps normal person users, and then ’brand‘ or ‘organization’ users. This pattern could easily allow you to go in that direction.

Note:The forum_example_private.person_account shares its primary key with forum_example.person . This way there can only be one forum_example_private.person_account for every forum_example.person , a one-to-one relationship.

Note:For an example of a much richer user profile/account/login schema, use Membership.db as a reference.

Registering Users

Before a user can log in, they need to have an account in our database. To register a user we are going to implement a Postgres function in PL/pgSQL which will create two rows. The first row will be the user’s profile inserted into forum_example.person , and the second will be an account inserted into forum_example_private.person_account .

Before we define the function, we know that we will want to hash the passwords coming into the function before inserting them into forum_example_private.person_account . To hash passwords we will need the Postgres pgcrypto extension. To add the extension, just do the following:

create extension if not exists "pgcrypto";

The pgcrypto extension should come with your Postgres distribution and gives us access to hashing functions like crypt and gen_salt which were specifically designed for hashing passwords.

Now that we have added pgcrypto to our database, let us define our function:

create function forum_example.register_person(
  first_name text,
  last_name text,
  email text,
  password text
) returns forum_example.person as $$
declare
  person forum_example.person;
begin
  insert into forum_example.person (first_name, last_name) values
    (first_name, last_name)
    returning * into person;

  insert into forum_example_private.person_account (person_id, email, password_hash) values
    (person.id, email, crypt(password, gen_salt('bf')));

  return person;
end;
$$ language plpgsql strict security definer;

comment on function forum_example.register_person(text, text, text, text) is 'Registers a single user and creates an account in our forum.';

If you do not understand what is going on here, do not worry, writing PL/pgSQL requires some trial and error along with some StackOverflow searching. What’s new here compared to our other functions is that we have a new block, declare , above our function implementation which starts with begin . In that block we declare our intention to use a variable called person of type forum_example.person . Then, in our first insert statement, the row we insert will be saved into that person variable.

After we insert a profile into forum_example.person , we use the pgcrypto extension in the expression crypt(password, gen_salt('bf')) to hash the user’s password before inserting into forum_example_private.person_account . This way we aren’t storing the password in plaintext. Read the documentation for pgcrypto on Password Hashing Functions to learn more about these functions and their characteristics.

Warning:Be very careful with logging, while we encrypt our passwords here it may be possible that in a query or server log the password will be recorded in plain text! Be careful to configure your Postgres logs so this isn’t the case. PostGraphile will never log the value of any variables the client gives it. Being careful with your logs and passwords is true in any system, but especially this one.

For an overview of passwords in Postgres past the pgcrypto documentation, see the answer to the StackOverflow question “ How can I hash passwords in Postgres? ”

At the end of the implementation you will see language plpgsql strict security definer . language plpgsql we already understand, but the other words are new. The word strict means that if the function gets null input, then the output will be automatically null as well and Postgres won’t call the function. That is password cannot be null or first_name cannot be null otherwise the result will also be null and nothing will be executed. The words security definer mean that this function is executed with the privileges of the Postgres user who created it. Remember how we said users would never be able to insert into forum_example_private.person_account ? Well this function can insert into forum_example_private.person_account because it uses the privileges of the definer.

Warning:Make sure that when you create a function with security definer there are no ‘holes’ a user could use to see or mutate more data than they are not allowed to. Since the above is a simple function, we are fine. If you don’t need security definer , try not to use it.

This function will create a user and their account, but how will we log the user in? Before we define a function which allows users to login, sign-in, authenticate, whatever you want to call it let us go over how auth works at a high level in PostGraphile. While this article is trying to be somewhat PostGraphile agnostic, the next two sections will be specific to PostGraphile, but useful to anyone wanting to learn just a little bit more about Postgres and JSON Web Tokens (JWTs).

Postgres Roles

When a user logs in, we want them to make their queries using a specific PostGraphile role. Using that role we can define rules that restrict what data the user may access. So what roles do we need to define for our forum example? Remember when we were connecting to Postgres and we used a URL like postgres:///mydb ? Well, when you use a connection string like that, you are logging into Postgres using your computer account’s username and no password. Say your computer account username is buddy , then connecting with the URL postgres:///mydb , would be the same as connecting with the URL postgres://[email protected]/mydb or even specifying the port explicitly: postgres://[email protected]:5432/mydb . If you wanted to connect to your Postgres database with a password it would look like postgres://buddy:[email protected]/mydb . When you run Postgres locally, this account will probably be the superuser. So when you run postgraphile -c postgres:///mydb , you are running PostGraphile with superuser privileges. To change that let’s create a role that PostGraphile can use to connect to our database:

create role forum_example_postgraphile login password 'xyz';

We create this forum_example_postgraphile role with the CREATE ROLE command. We want to make sure our PostGraphile role can login so we specify that with the login option and we give the user a password of ‘xyz’ with the password option. Now we will start PostGraphile as such:

postgraphile -c postgres://forum_example_postgraphile:<a href="/cdn-cgi/l/email-protection" data-cfemail="e8909192a884878b898480879b9c">[email protected]</a>/mydb

When a user who does not have a JWT token makes a request to Postgres, we do not want that user to have the privileges we will give to the forum_example_postgraphile role, so instead we will create another role.

create role forum_example_anonymous;
grant forum_example_anonymous to forum_example_postgraphile;

Here we use CREATE ROLE again. This role cannot login so it does not have the login option, or a password. We also use the GRANT command to grant access to the forum_example_anonymous role to the forum_example_postgraphile role. Now, the forum_example_postgraphile role can control and become the forum_example_anonymous role. If we did not use that grant, we could not change into the forum_example_anonymous role in PostGraphile. Now we will start our server like so:

postgraphile \
  --connection postgres://forum_example_postgraphile:[email protected]/mydb \
  --default-role forum_example_anonymous

There is one more role we want to create. When a user logs in we don’t want them to use the forum_example_postgraphile role, or the basic forum_example_anonymous role. So instead we will create a role that all of our logged in users will authorize with. We will call it forum_example_person and similarly grant it to the forum_example_postgraphile role.

create role forum_example_person;
grant forum_example_person to forum_example_postgraphile;

Warning:The forum_example_postgraphile role will have all of the permissions of the roles granted to it. So it can do everything forum_example_anonymous can do and everything forum_example_person can do. This is why having a default role is important. We would not want an anonymous user to have admin access level because we have granted an admin role to forum_example_postgraphile .

Ok, so now we have three roles. forum_example_postgraphile , forum_example_anonymous , and forum_example_person . We know how forum_example_postgraphile and forum_example_anonymous get used, but how do we know when a user is logged in and should be using forum_example_person ? The answer is JSON Web Tokens.

JSON Web Tokens

PostGraphile uses JSON Web Tokens (JWTs) for authorization. A JWT is just a JSON object that has been hashed and cryptographically signed to confirm the identity of its contents. So an object like:

{
  "a": 1,
  "b": 2,
  "c": 3
}

Would turn into a token that looks like:

eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJhIjoxLCJiIjoyLCJjIjozfQ.hxhGCCCmGV9nT1slief1WgEsOsfdnlVizNrODxfh1M8

Warning:The information in a JWT can be read by anyone, so do not put private information in a JWT. What makes JWTs secure is that unless they were signed by our secret, we can not accept the information inside the JWT as truth.

This allows PostGraphile to securely make claims about who a user is. Attackers would not be able to fake a claim unless they had access to the private ‘secret’ you define when you start PostGraphile with the --jwt-secret option.

When PostGraphile gets a JWT from an HTTP request’s Authorization header, like so:

Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJhIjoxLCJiIjoyLCJjIjozfQ.hxhGCCCmGV9nT1slief1WgEsOsfdnlVizNrODxfh1M8

It will verify the token using the secret, and then