Building REST APIs with Platformatic DB

Introduction

Building web APIs using the REST architectural style can help us create well structured and efficient APIs for our web applications. However, it can be difficult to apply effectively.

If we want to build a REST API with Node.js, we'll typically start by designing the API interface, integrating a database library, wiring up CRUD endpoints, adding request validation, implementing filtering and pagination, documenting the API — the list goes on! It's a lot of repetitive and time consuming work. Platformatic DB provides a solid foundation for building a REST API, helping us skip a lot of that painful groundwork.

What is a REST API? REST (REpresentational State Transfer) is a group of software architecture design constraints. A REST API is an HTTP service that applies at least some of those constraints. It provides consistent URLs for accessing and manipulating resources, such as rows in a database. By making an HTTP GET request to the URL for a resource, for example GET /tasks/5, we can retrieve that resource in a machine readable format (typically JSON or XML). REST APIs generally also provide URLs to create, update and delete resources, by making requests using the POST, PUT and DELETE HTTP methods.

When we start an app that's built with Platformatic DB, a REST API is automatically mapped from the tables in our database. This can be an existing database, or we can write SQL migrations and apply them with the Platformatic CLI to create the schemas for new database tables.

In this guide we’re going to take a tour of the automatic REST API that Platformatic DB generates for us. We’ll learn about all of the features it provides, as well as how we can extend it with our own custom REST API routes.

You can see the example application code for this guide on GitHub.

The REST API is enabled by default in Platformatic DB apps, but if we ever need to disable it we can set db.openapi to false in our platformatic.db.json configuration.

Entities

If our database contains a users table and a tasks table with the following schemas:

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  username VARCHAR(255) NOT NULL,
  display_name VARCHAR(255) NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE tasks (
  id INTEGER PRIMARY KEY,
  description TEXT NOT NULL,
  priority INTEGER NOT NULL,
  user_id INTEGER NOT NULL REFERENCES users(id),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

When we start our Platformatic DB app, the following entities will automatically be mapped for us:

Plural Name	Singular Name	Primary Key	Fields
`users`	`user`	`id`	`username`, `displayName`, `createdAt`, `updatedAt`
`tasks`	`task`	`id`	`description`, `priority`, `userId`, `createdAt`, `updatedAt`

Automatic entity types: If we have types.autogenerate enabled in our platformatic.db.json configuration, if we apply migrations with the Platformatic CLI it will generate type definitions for us. These can be used in JavaScript or TypeScript applications to provide type hinting and type checking when writing code that works with entities.

After the entities have been mapped from our database tables, Platformatic DB then generates a REST API for us.

REST API

Let's take a look at the features of the automatic REST API.

Routes

REST API routes are created for each mapped entity in our database. These routes allow us to carry out common create, read, update and delete (CRUD) operations.

Here are the routes that are generated for our users and tasks entities:

Route	Purpose
`GET /users`	Retrieve multiple users
`POST /users`	Create a new user
`PUT /users`	Update multiple users
`GET /users/{id}`	Retrieve a specific user
`PUT /users/{id}`	Update a specific user
`DELETE /users/{id}`	Delete a specific user
`GET /tasks`	Retrieve multiple tasks
`POST /tasks`	Create a new task
`PUT /tasks`	Update multiple tasks
`GET /tasks/{id}`	Retrieve a specific task
`PUT /tasks/{id}`	Update a specific task
`DELETE /tasks/{id}`	Delete a specific task

When there are entities with relationships, Platformatic DB creates nested relationship routes too. These are the routes that are created for our related users and tasks entities:

Route	Purpose
`GET /users/{id}/tasks`	Retrieve the tasks for a specific user
`GET /tasks/{id}/users`	Retrieve the user for a specific task

Platformatic DB provides interactive documentation for all of these API routes. See 'Interactive API documentation with Swagger UI'.

If we need to have rows in one database table that are related to many rows in another table, we can create a "join table" to store those relationships. Platformatic DB will then automatically create many-to-many relationship routes for us. You can learn more about how this works in the API documentation for the Platformatic SQL OpenAPI package.

There are a couple of things that are automatically handled by our REST API routes:

When we create or update a user or task entity via the API, the createdAt and updatedAt fields will be automatically populated for us.
By default, the API routes will only return up to 10 entities. This can be changed with the limit parameter (see 'Paginating entities').

Paginating entities

We can use the following query string parameters to help us paginate the entities that are returned when we make a request to a REST API route:

limit — The maximum number of entities to include in the response. If this parameter isn't set, it defaults to 10.
offset — The number of rows to skip before beginning to return the rows.
totalCount — If set to true, the response will include the total count of entities in an X-Total-Count response header. We can use this to help us determine where we are within all of the available entity rows.

Here's an example request that combines all of those parameters:

curl --verbose --request GET \
  'http://127.0.0.1:3042/tasks?limit=4&offset=4&totalCount=true'

And here's the response:

< HTTP/1.1 200 OK
< x-total-count: 12
< content-type: application/json; charset=utf-8
< 

[
  {
    "id": 5,
    "description": "Practice playing guitar",
    "priority": 3,
    "userId": 1,
    "createdAt": "1682558555736",
    "updatedAt": "1682558555736"
  },
  {
    "id": 6,
    "description": "Buy dog snacks",
    "priority": 2,
    "userId": 2,
    "createdAt": "1682558555743",
    "updatedAt": "1682558555743"
  },
  {
    "id": 7,
    "description": "Submit article for review",
    "priority": 2,
    "userId": 2,
    "createdAt": "1682558555743",
    "updatedAt": "1682558555743"
  },
  {
    "id": 8,
    "description": "Return faulty headphones",
    "priority": 2,
    "userId": 2,
    "createdAt": "1682558555743",
    "updatedAt": "1682558555743"
  }
]

Learn more about pagination in the API documentation for the Platformatic SQL OpenAPI package.

Request validation

Every route in the automatic REST API performs validation on the route URL parameters, query string and request body (for POST and PUT requests). This validation is performed using JSON Schemas, the default validation method used by Fastify, which Platformatic is built on top of.

Here's the JSON schema that Platformatic DB generates and uses to validate request bodies that contain a user entity:

{
  "$id": "User",
  "title": "User",
  "description": "A User",
  "type": "object",
  "properties": {
    "id": {
      "type": "integer"
    },
    "username": {
      "type": "string"
    },
    "displayName": {
      "type": "string"
    },
    "createdAt": {
      "type": "string",
      "nullable": true
    },
    "updatedAt": {
      "type": "string",
      "nullable": true
    }
  },
  "required": [
    "username",
    "displayName"
  ]
}

And here's the JSON schema that's used to validate a task entity:

{
  "$id": "Task",
  "title": "Task",
  "description": "A Task",
  "type": "object",
  "properties": {
    "id": {
      "type": "integer"
    },
    "description": {
      "type": "string"
    },
    "priority": {
      "type": "integer"
    },
    "userId": {
      "type": "integer"
    },
    "createdAt": {
      "type": "string",
      "nullable": true
    },
    "updatedAt": {
      "type": "string",
      "nullable": true
    }
  },
  "required": [
    "description",
    "priority",
    "userId"
  ]
}

If any request property fails validation, for example by passing an invalid request body:

curl --verbose --request POST \
  'http://localhost:3042/users' \
  --header 'content-type: application/json' \
  --data '{ "username": "frankie.gth" }'

The API route will return a 400 (Bad Request) HTTP status code and the validation error, for example:

< HTTP/1.1 400 Bad Request
< content-type: application/json; charset=utf-8

{
  "statusCode": 400,
  "error": "Bad Request",
  "message": "body must have required property 'displayName'"
}

The entity JSON schema is also used to serialize the response body from the API route, which can help improve the speed of sending responses from the API.

Filtering entities

If we want to filter the entities that are retrieved from the database, we can set where.[field].[operator] or where.or parameters in our requests to the API routes.

Here are some requests for tasks that show this in action:

where.description.like=buy

Request:

curl --verbose --request 'GET' \
  'http://127.0.0.1:3042/tasks?where.description.like=%buy%'

Response body:

[
  {
    "id": 3,
    "description": "Buy plants",
    "priority": 1,
    "userId": 1,
    "createdAt": "1682558555736",
    "updatedAt": "1682558555736"
  },
  {
    "id": 6,
    "description": "Buy dog snacks",
    "priority": 2,
    "userId": 2,
    "createdAt": "1682558555743",
    "updatedAt": "1682558555743"
  }
]

where.priority.in=1,2

Request:

curl --verbose --request 'GET' \
  'http://127.0.0.1:3042/tasks?where.priority.in=1,2'

Response body:

[
  {
    "id": 2,
    "description": "Fix kitchen tap",
    "priority": 1,
    "userId": 1,
    "createdAt": "1682558555736",
    "updatedAt": "1682558555736"
  },
  ...,
  {
    "id": 11,
    "description": "Plan summer vacation",
    "priority": 2,
    "userId": 2,
    "createdAt": "1682558555743",
    "updatedAt": "1682558555743"
  }
]

where.or=(priority.eq=1|description.like=%fix%)

If there are multiple where.[field].[operator] parameters, they're combined with the AND operator when Platformatic DB executes the database query to retrieve data. If we want to specify multiple conditions and combine them with the OR operator, we can use where.or.

Request:

curl --verbose --request 'GET' \
  'http://127.0.0.1:3042/tasks/?where.or=(priority.eq=1|description.like=%fix%)'

Response body:

[
  {
    "id": 2,
    "description": "Fix kitchen tap",
    "priority": 1,
    "userId": 1,
    "createdAt": "1682558555736",
    "updatedAt": "1682558555736"
  },
  {
    "id": 3,
    "description": "Buy plants",
    "priority": 1,
    "userId": 1,
    "createdAt": "1682558555736",
    "updatedAt": "1682558555736"
  },
  {
    "id": 9,
    "description": "Walk the dog",
    "priority": 1,
    "userId": 2,
    "createdAt": "1682558555743",
    "updatedAt": "1682558555743"
  }
]

Sorting entities

We can sort entities when they're retrieved from the database by adding a orderby.[field] parameter to the URL query string. For example:

curl --verbose --request 'GET' \
  'http://127.0.0.1:3042/tasks?orderby.priority=asc'

We can set the value to asc for ascending order and desc for descending order.

We can also order by multiple fields:

curl --verbose --request 'GET' \
  'http://127.0.0.1:3042/tasks?orderby.priority=asc&orderby.description=desc'

Limiting returned fields

The API routes will return all fields for an entity by default, but if we want to restrict the fields that are returned, we can pass a fields parameter.

For example:

curl --verbose --request 'GET' \
  'http://127.0.0.1:3042/tasks?fields=description,priority,userId'

And the response we receive looks like this:

[
  {
    "description": "Write grocery list",
    "priority": 3,
    "userId": 1
  },
  ...,
  {
    "description": "Go for a run",
    "priority": 3,
    "userId": 2
  }
]

Documentation

Providing comprehensive and up-to-date documentation is an important part of building an API. Platformatic DB provides two types of documentation for our REST API.

Automatic OpenAPI schema

The OpenAPI Specification is a specification language for HTTP APIs that provides a standardized means to define your API to others. You can quickly discover how an API works, configure infrastructure, generate client code, and create test cases for your APIs.

— Source: OpenAPI Initiative

The OpenAPI schema that's automatically generated by Platformatic DB can be imported into tools like Postman or Insomnia to help us test our REST API. The OpenAPI specification is also supported by many other tools.

We can output and view the generated OpenAPI schema for our API with the Platformatic CLI:

npx platformatic db schema openapi

And if want to save the OpenAPI schema to a file that we can use with another tool, we can do that with this command:

npx platformatic db schema openapi > tasks-api.openapi.json

Interactive API documentation with Swagger UI

Screenshot of the interactive Swagger UI in a web browser, showing all the REST API endpoints

The built-in Swagger UI reads the OpenAPI schema for our REST API and generates interactive documentation for us. It shows all of the available REST API routes, along with the parameters they accept and example responses. It also allows us to make requests to our API.

The Swagger UI documentation is available under http://127.0.0.1:3042/documentation/.

Configuration

There are a number of ways that we can configure the automatic REST API that's generated by Platformatic DB. We can do this by adding settings in our app's platformatic.db.json configuration.

Setting a URL prefix for REST API routes

By default, all API routes are available under the root path, /. If we want to, we can set a custom prefix for all API endpoints, for example:

{
  "db": {
    ...
    "openapi": {
      "prefix": "/api"
    }
  }
}

Ignoring tables or fields

We might want to exclude specific tables from being mapped in the automatic REST API:

{
  "db": {
    ...
    "openapi": {
      "ignore": {
        "users": true
      }
    }
  }
}

Or exclude specific fields in a table from being mapped:

{
  "db": {
    ...
    "openapi": {
      "ignore": {
        "tasks": {
          "priority": true
        }
      }
    }
  }
}

Configuring the limit for API route responses

We can configure the default number of entities that are returned by API routes, as well as set a maximum value for the limit parameter:

{
  ...
  "db": {
    ...
    "limit": {
      "default": 50,
      "max": 1000
    }
  }
}

Extending the OpenAPI schema

If we want to extend the automatically generated OpenAPI schema, we can pass an object that conforms to the OpenAPI specification:

{
  "db": {
    ...
    "openapi": {
      "info": {
        "title": "Tasks API",
        "description": "The ultimate API for managing tasks"
      }
    }
  }
}

Learn more about extending the OpenAPI schema in the Platformatic DB configuration documentation.

Extending the automatic REST API

At some point we might want to extend the automatic REST API that's generated by Platformatic DB. We can do this by creating our own Fastify plugins and adding REST API endpoints inside of them.

Here's an example plugin that adds a GET /high-priority-tasks API route:

/// <reference path="./global.d.ts" />
'use strict'

/** @param {import('fastify').FastifyInstance} app */
module.exports = async function (app) {
  app.get('/high-priority-tasks', async function (request, reply) {
    const tasks = await app.platformatic.entities.task.find({
      where: {
        priority: {
          eq: 1
        }
      }
    })

    return tasks
  })
}

You can learn more about extending the automatic REST API in this guide, or by watching these how-to videos:

How-to video: Create a plugin

https://www.youtube.com/watch?v=6srzPzL_iKM&

We can add to and extend the functionality of our Platformatic applications by creating Fastify plugins. In this video we learn how to create a plugin and configure our application to use it.

How-to video: Add REST API routes

https://www.youtube.com/watch?v=1_U8DIkYTH8

In this video we dive into some of the features of the Fastify web framework. We learn how we can use them to add REST API routes to a Platformatic application.

Using standalone Platformatic packages

There are two packages that Platformatic DB integrates to provide us with automatic REST API functionality:

@platformatic/sql-mapper: Maps the schemas of SQL database tables to entity objects.
@platformatic/sql-openapi: Generates a fully featured REST API at runtime, based on the entities that have been mapped by the SQL Mapper.

If we want full flexibility, instead of using Platformatic DB, we can integrate these packages directly into our own Fastify applications.

Wrapping up

Platformatic DB makes it faster to build robust and fully featured REST APIs. We also have the freedom to build on top of the foundations it provides whenever we want to add our own custom functionality.

If you're ready to start building REST APIs with Platformatic DB, check out our:

Quick Start Guide — Get up and running in 2 minutes!
Build a secure web application with Platformatic, Auth0 and Next.js tutorial — Learn how to build a full stack blog application with authentication and authorization functionality.
Movie Quotes App Tutorial — Learn how to build a full stack application with Platformatic DB and Astro.
Platformatic Client release blog post — Learn how to generate API clients for your Platformatic DB app's REST API.
SQL to OpenAPI reference documentation
SQL Mapper reference documentation

The example application code for this guide is available on GitHub.

Building REST APIs with Platformatic DB

Table of contents

Building REST APIs with Platformatic DB

Introduction

Entities

REST API

Routes

Paginating entities

Request validation

Filtering entities

Sorting entities

Limiting returned fields

Documentation

Automatic OpenAPI schema

Interactive API documentation with Swagger UI

Configuration

Setting a URL prefix for REST API routes

Ignoring tables or fields

Configuring the limit for API route responses

Extending the OpenAPI schema

Extending the automatic REST API

How-to video: Create a plugin

How-to video: Add REST API routes

Using standalone Platformatic packages

Wrapping up