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 exampleGET /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 thePOST
,PUT
andDELETE
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
tofalse
in ourplatformatic.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 ourplatformatic.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
ortask
entity via the API, thecreatedAt
andupdatedAt
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 to10
.offset
— The number of rows to skip before beginning to return the rows.totalCount
— If set totrue
, the response will include the total count of entities in anX-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"
}
]
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
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
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
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.
The example application code for this guide is available on GitHub.