5min Intro

Overview

One hour tutorial? Ain't Nobody Got Time for That! Ok, ok, I hear you, time is money, that's why we'll concentrate on getting something up and running fast, the essence of the system. We won't bother with git, migrations, test, loose model-api coupling, CI and all those other nice and important things for a production system (we'll do that in the next tutorial). We'll build a barebones API for a to-do app. We'll have only two models, "todo" and "user" and the system will provide signup and login for users, and they will be able to perform CRUD operations on the to-do's.

Database Instance

The first thing we'll need is a database. subZero service runs in AWS us-east-1 so that's where we'll create our PostgreSQL RDS instance to have low latency between the database and subZero (other regions will be available).

Create the database

To create the database we'll use a handy cloudformation stack that will spin up a free tier RDS instance in your AWS account. Just click the button below and fill all the fields.

Create Stack

Note

If you prefer to customize the database instance type (in case you want to keep it after this tutorial), follow this link to create one. Make sure you select PostgreSQL, make it publicly available and then add a rule in it's security group to allow subzero to connect to it.

Connect to the database

When the stack creation completes, click on the Outputs tab where you will find connection information for the database.

Connection Info

Use your preferred PostgreSQL GUI client (ex. DBeaver, Postico) to connect to the database or just use psql in command line (if you don't already have it, it's easy to install).

If you are going to use psql, save the connection string in a temporary env variable so that we can easily use it later.

export CONNECTION_STRING="<ConnectionString>" # mac/linux
set CONNECTION_STRING=<ConnectionString> # windows

And now let's check the connection

psql $CONNECTION_STRING # mac/linux
psql %CONNECTION_STRING% # windows

The database is ready; let's move to the next step.

subZero Account

Go to the signup page and create your account then login. Once in, click the big "plus" button and follow the wizard.

Choose a custom subdomain for your API w

Provide the database connection info (remember to use the authenticator role here) w

Fill in the settings as shown below (and write down your JWT secret key, we'll need it later) w

Review and create

w

Open the page https://your-subdomain-here.subzero.cloud

w

and give GraphiQL IDE a spin

w

Toy Schema

So now we have our API live, but it's empty, so let's start our clock and see how long it will take us to get a to-do api ready.

Load Libraries

Let's load in out database a few convinience libraries which will make life a bit easier (they are just a collection of handy sql/plpgsql functions). If you are using a GUI client, load these libraries by copy/pasting and executing the sql:

If you went for psql, run these onliners (for windows, the last part of the command is | psql %CONNECTION_STRING%):

curl -s https://raw.githubusercontent.com/subzerocloud/subzero-starter-kit/master/db/src/libs/request.sql \
| psql $CONNECTION_STRING
curl -s https://raw.githubusercontent.com/subzerocloud/subzero-starter-kit/master/db/src/libs/response.sql \
| psql $CONNECTION_STRING
curl -s https://raw.githubusercontent.com/subzerocloud/subzero-starter-kit/master/db/src/libs/pgjwt.sql \
| psql $CONNECTION_STRING

Data Model

And now the meat of the API, the thing we are here for. First we create the api schema and the core tables/models that will hold our data.

drop schema if exists api cascade;
create schema api;

-- core models
create table api.users (
    id                   serial primary key,
    email                text not null unique,
    password             text not null,
    check (email ~* '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+[.][A-Za-z]+$')
);

create table api.todos (
    id    serial primary key,
    todo  text not null,
    owner_id int references api.users(id) default request.user_id()
);

Authentication

Let's create the functions handling login / signup functionality. I bet even if you've never written a Pl/pgSQL function in your life, you can figure out in 2 seconds what's going on. Remember to replace JWT-SECRET-GOES-HERE with the JWT secret you used when creating the subZero app. I know, I know, hardcoding secrets in code is a big no-no (and of course there is a better way than this) but we are going for "fast" here so bear with me.

-- login / signup
drop type if exists api.customer cascade;
create type api.customer as (id int, email text);

create or replace function public.new_session(usr api.users) returns api.customer as $$
declare
    token text;
begin
    token := pgjwt.sign(
        json_build_object(
            'role','customer',
            'user_id', usr.id,
            'exp', extract(epoch from now())::integer + 3600
        ),
        'JWT-SECRET-GOES-HERE'
    );
    perform response.set_cookie('SESSIONID', token, settings.get('jwt_lifetime')::int,'/');
    return (
        usr.id,
        usr.email
    );
end
$$ stable language plpgsql;

create or replace function api.login(email text, password text) returns api.customer as $$
declare
    usr api.users;
begin
    select *
    from api.users as u
    where u.email = $1 and u.password = public.crypt($2, u.password)
    into usr;

    if usr is NULL then
        raise exception 'invalid email/password';
    else
        return public.new_session(usr);
    end if;
end
$$ security definer language plpgsql;
revoke all privileges on function api.login(text, text) from public;


create or replace function api.signup(email text, password text) returns api.customer as $$
declare
    usr api.users;
begin
    insert into api.users as u
    (email, password) values
    ($1, public.crypt($2, public.gen_salt('bf')))
    returning u.* into usr;

    return public.new_session(usr);
end
$$ security definer language plpgsql;
revoke all privileges on function api.signup(text, text) from public;

Authorization

Just because there are tables and functions in our api schema, does not mean people can just start accessing them. We need to give each role (anonymous and customer) specific priveleges.

-- privileges
create role customer;
grant customer to authenticator;

-- allow customers to perform all CRUD operations
-- notice how we don't allow access to owner_id field
grant usage on schema api to customer;
grant
  select(id, todo), 
  insert(todo), 
  update(todo), 
  delete 
on api.todos to customer;
grant usage on sequence api.todos_id_seq to customer;
grant execute on function api.login(text,text) to customer;

-- allow customers to access only their rows
alter table api.todos enable row level security;
create policy todos_access_policy on api.todos to customer 
using ( request.user_id() = owner_id );

-- allow anonymous users to access login/signup
grant usage on schema api to anonymous;
grant execute on function api.login(text,text) to anonymous;
grant execute on function api.signup(text,text) to anonymous;

Now that we changed the schema in our database, we need to refresh it (subZero caches your database schema structure).

w

Pencils Down

Take it for a spin

Open the GraphiQL IDE and try these GraphQL queries. Note that each query has a corresponding REST request, we'll try them out in the next tutorial.

mutation {
  signup(email: "alice@email.com", password: "pass"){
    id email
  }
}

mutation{
  login(email: "alice@email.com", password: "pass"){
    id email
  }
}
mutation {
  insert {
    todo(input:{todo: "Complete the tutorial"}){
      id
      todo
    }
  }
}
mutation {
  insert {
    todos(input:[
      {todo: "Give it a spin"},
      {todo: "Check out the REST API"}
    ]){
      id
      todo
    }
  }
}
{
  todo(id:1){
    todo
  }
}
{
  todos(
    where:{
      id:{gte: 2}
    }
  ){
    id
    todo
  }
}
{
  todos(
    where:{
      todo:{ilike: "*REST*"}
    }
  ){
    id
    todo
  }
}

That's all Folks

Belive it or not, we are done, that's the entire code needed to power this toy API. There is no ball of mud that we are somehow loading into your database behind the scene (remember the authenticator user has no privileges?). If it weren't for the login/signup functionality, we'd basically have only the table definitions and the security policies (grant and policy statements).

Now I know people with some backend experience are thinking things like "OMG, there are so many steps and good practices skipped here, this will turn bad. There is no separation between the data model and the api, there is no input validation, not to mention that debuging, testing and deploying code in the database is going to be a special kind of torture and don't even get me started on business logic in the database, I've read those articles".

Before you close the browser and say "well this is 5 minutes I am not getting back", let me just say a few things:

  • This tutorial is to demonstrate the absolute essence of the system, it is not something you would put in production and running queries directly on your live database is not how you deploy the code.

  • Step back for a second and appreciate how little code was required to go from a simple table in your database to a quite powerful and flexible API that is capable of understanding both REST & GraphQL and has some basic authentication/authorization mechanism in place and you have full control of that mechanism, it does not come "as a service".

  • For the entire tutorial we used your database and we used a standard sql client to manage the database structure and data. You might've glanced over this but it's an important aspect. You are in direct control of your database which means you call the shots, subZero does not monopolize access to your data and does not require the other components that need access to the database to through the subZero api. You can use every piece of software/technology developed in the last 30 years to work with your data directly.

If I've sparked your curiosity and you have some time to spare, go to the next tutorial and see how a production system is setup with this stack.