Skip to content

Add a new data model

Now that you've got a feel for the type of API subZero provides, it's time to see how you actually create the endpoints that serve these APIs. This is where things get interesting. In this tutorial we'll be adding a comment data model and comments api endpoint. This data model will have a relation to the todo data model in the sens that for each todo we can have multiple comments.

Project structure

Let's first take a look at the project structure. Notice that the bulk of the code is in db/src/ folder. This is where you'll spend most of the time when developing your API, defining the database schema.

├── db                        # Database schema source files and tests
│   └── src                   # Schema definition
│       ├── api               # Api entities available as REST and GraphQL endpoints
│       ├── data              # Definition of source tables that hold the data
│       ├── libs              # A collection of modules used throughout the code
│       ├── authorization     # Application level roles and their privileges
│       ├── sample_data       # A few sample rows
│       └── init.sql          # Schema definition entry point
├── html                      # Place your static frontend files here
├── tests                     # Tests for all the components
│   ├── db                    # pgTap tests for the db
│   ├── graphql               # GraphQL interface tests
│   └── rest                  # REST interface tests
├── docker-compose.yml        # Defines Docker services, networks and volumes
└── .env                      # Project configurations

Live reloading

While iterating on our API, we'll be using subzero-cli's watch that will live reload the our code that runs in the database so that we can keep it in normal files and work on them in our favorite editor.


Traditionally, when working on the code that lives/runs in the database, be that tables, views, store procedures or triggers, you would have to manually apply your changes directly in the database by executing the DDL queries to see the new code in action. This often served as a reason to avoid having any logic at the database level (even it was the perfect place for that logic) just because iterating on that code was such a pain. subZero devotes considerable attention not only to the production features/capabilities of our tools but also to the development workflow when using those tools.

While in the root of your project, execute these commands: Start fresh (restart our stack)

docker-compose down
docker-compose up -d

And now start monitoring our project files for changes

subzero watch

Open your project files in your favorite editor and let's start working.

Define the data model

Create a file in db/src/data/ called comment.sql with the following content.

create table comment (
  id           serial primary key,
  body         text not null,
  todo_id      int not null references todo(id),
  user_id      int references "user"(id) default request.user_id()
alter table comment enable row level security;

In order to have this table definition loaded up in the database we also need to include it in our data schema. Open db/src/data/schema.sql file and right at the end, add the following line.

-- ... there are some lines above
\ir comment.sql

Define the API endpoint

Now that we have our table that will hold the comments, let's define the endpoint through which our users will be interacting with the comments in the table. Create a file in db/src/api/ called comments.sql with the following content.

create or replace view comments as
select id, body, todo_id, user_id
from data.comment;

Just like with our data model table, we also need to include this file in our api schema. Open db/src/api/schema.sql file and right at the end, add the following line.

-- ... there are some lines above
\ir comments.sql


Note that it was possible to create the comment table in the api schema (and maybe name it comments), thus making the table itself the api endpoint. Having the tables holding the data separate from the schema that holds our api endpoint will give us the following advantages while iterating on our api

  • Decoupled data model (your data model is not an API)
  • Ability to maintain backward compatibility when evolving the data model
  • Ability to version our api by having multiple api schemas (apiv1, apiv2)
  • Ability to drop/create our api schema, or just entities in that schema, without worrying about potential data loss

There is however a slight disadvantage to this method as we'll see in the next section, the need for a intermediate role called api that acts as the views owner (more details about pros and cons here)

Access privileges

Since we've defined our data model and the endpoint, you might be tempted to try and interact with it, after all it's already defined, right? Well, let's try it, why not?

curl http://localhost:8080/rest/comments
and the response will be
{"hint":null,"details":null,"code":"42501","message":"permission denied for view comments"}

You should notice two things about this error message. First, the database knows about the view comments (otherwise the error would have been relation "api.comments" does not exist). This means that subzero-cli did it's job and applied to the database the new schema changes, all we had to do is create the files and save them.

The second thing to notice, and this is the important part, just because a table or a view exists in your database, it's not accessible by default through the api. You have to be very specific about what is accessible through the API, and this is a good thing.

The first thing we'll define is the privileges the API has when it tries to access the comment table holding the data.

Open db/src/authorization/privileges.sql file and add the following lines at the end

-- allow the api to access underlying table,
-- this is not the same as giving users access though
-- notice how for insert/update we only allow the api to supply the body,todo_id
-- but not id,user_id columns
    insert (body, todo_id), 
    update (body, todo_id), 
on data.comment to api;

-- set the policy that defines what rows from the table are accessible to the api
-- this policy can also take into account the specific application user
-- that is trying to access the data ( notice the use of request.user_id() )
drop policy if exists comment_access_policy on data.comment;
create policy comment_access_policy on data.comment to api 
using (
    -- allow only authenticated users to see the comments in the system
    request.user_role() = 'webuser'
with check (
    -- authenticated users can only update/delete their comment
    request.user_role() = 'webuser' and request.user_id() = user_id

-- set the owner of the comments view (the endpoint) to the correct user
-- this is needed so that the above policy is applied
alter view api.comments owner to api;

Now in the same file, add the next few line to specify the privileges our application users have when they try to access the comments endpoint

-- authenticated users can see (select) everything 
-- but they can insert/update only specific columns
    insert (body, todo_id),
    update (body, todo_id),
on api.comments to webuser;

-- this line is needed so that authenticated users can "request" new ids
-- from the sequence used by the id column
grant usage on data.comment_id_seq to webuser;

-- anonymous users do not have access to the comments at all, but if we wanted to enable that
-- we could have a line similar to this one
-- grant select (id, body, todo_id) on api.comments to anonymous;

And that's all folks, that's all the configuration we need to define the access privileges for our API users. We had more comments in those lines then actual grant commands. Even a non-developer (your customer maybe?) can review a file like this and check if the access policy are correct because it reads like english sentences.

Check the result

Let's give our newly defined model and access policies a spin.

Make sure we are logged in

curl -X POST --cookie-jar cookies.txt \
-d '{"email":"","password":"pass"}' \

Add a new comment

curl -X POST --cookie cookies.txt \
-d '{"body":"new comment", "todo_id":1}' \

Add multiple comments at the same time (just because we can)

curl -X POST --cookie cookies.txt \
-d '[{"body":"new comment in batch", "todo_id":1}, {"body":"another new comment in batch", "todo_id":2}]' \

Now let's try and hack the api and impersonate another user when commenting

curl -X POST --cookie cookies.txt \
-d '{"body":"new comment ;)", "todo_id":1, "user_id": 2}' \

Check if those comments are where they are supposed to be.

curl --cookie cookies.txt http://localhost:8080/rest/comments

Let's now try and get a particular todo with all the comments for that todo (this is the cool part and a very powerful feature)

curl --cookie cookies.txt \