# Building REST APIs with Platformatic DB

# 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](https://oss.platformatic.dev/docs/reference/db/introduction?utm_campaign=Blog%20post%20-%20Building%20REST%20APIs%20with%20Platformatic%20DB&utm_medium=blog&utm_source=Platformatic%20Blog) 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](https://en.wikipedia.org/wiki/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](https://oss.platformatic.dev/docs/reference/db/introduction?utm_campaign=Blog%20post%20-%20Building%20REST%20APIs%20with%20Platformatic%20DB&utm_medium=blog&utm_source=Platformatic%20Blog), 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](https://github.com/platformatic/examples/tree/main/applications/tasks-api-platformatic-db?utm_campaign=Blog%20post%20-%20Building%20REST%20APIs%20with%20Platformatic%20DB&utm_medium=blog&utm_source=Platformatic%20Blog).

> 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:

```sql
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](#heading-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](https://oss.platformatic.dev/docs/reference/sql-openapi/api?utm_campaign=Blog%20post%20-%20Building%20REST%20APIs%20with%20Platformatic%20DB&utm_medium=blog&utm_source=Platformatic%20Blog#many-to-many-relationships) 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](#heading-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:

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

And here's the response:

```plaintext
< 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](https://docs.platformatic.dev/docs/packages/sql-openapi/api#pagination) 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](https://www.fastify.io/docs/latest/Reference/Validation-and-Serialization/?utm_campaign=Blog%20post%20-%20Building%20REST%20APIs%20with%20Platformatic%20DB&utm_medium=blog&utm_source=Platformatic%20Blog), 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:

```json
{
  "$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:

```json
{
  "$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:

```bash
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:

```plaintext
< 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](https://www.fastify.io/docs/latest/Reference/Validation-and-Serialization/?utm_campaign=Blog%20post%20-%20Building%20REST%20APIs%20with%20Platformatic%20DB&utm_medium=blog&utm_source=Platformatic%20Blog#serialization) 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:*

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

*Response body:*

```json
[
  {
    "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:*

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

*Response body:*

```json
[
  {
    "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:*

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

*Response body:*

```json
[
  {
    "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:

```bash
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:

```bash
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:

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

And the response we receive looks like this:

```json
[
  {
    "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*](https://www.openapis.org/?utm_campaign=Blog%20post%20-%20Building%20REST%20APIs%20with%20Platformatic%20DB&utm_medium=blog&utm_source=Platformatic%20Blog)

The OpenAPI schema that's automatically generated by Platformatic DB can be imported into tools like [Postman](https://www.postman.com/api-platform/api-client/) or [Insomnia](https://insomnia.rest/) to help us test our REST API. The OpenAPI specification is also supported by [many other tools](https://openapi.tools/?utm_campaign=Blog%20post%20-%20Building%20REST%20APIs%20with%20Platformatic%20DB&utm_medium=blog&utm_source=Platformatic%20Blog).

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

```bash
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:

```bash
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](https://cdn.hashnode.com/res/hashnode/image/upload/v1682958320314/f75b39e1-18d4-4445-9d53-2f0d9e3d784b.png align="center")

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:

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

### Ignoring tables or fields

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

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

Or exclude specific fields in a table from being mapped:

```json
{
  "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:

```json
{
  ...
  "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](https://swagger.io/specification/?utm_campaign=Blog%20post%20-%20Building%20REST%20APIs%20with%20Platformatic%20DB&utm_medium=blog&utm_source=Platformatic%20Blog):

```json
{
  "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](https://oss.platformatic.dev/docs/reference/db/configuration?utm_campaign=Blog%20post%20-%20Building%20REST%20APIs%20with%20Platformatic%20DB&utm_medium=blog&utm_source=Platformatic%20Blog#db).

## 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](https://oss.platformatic.dev/docs/reference/db/plugin?utm_campaign=Blog%20post%20-%20Building%20REST%20APIs%20with%20Platformatic%20DB&utm_medium=blog&utm_source=Platformatic%20Blog) and adding REST API endpoints inside of them.

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

```javascript
/// <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](https://oss.platformatic.dev/docs/guides/add-custom-functionality/extend-rest?utm_campaign=Blog%20post%20-%20Building%20REST%20APIs%20with%20Platformatic%20DB&utm_medium=blog&utm_source=Platformatic%20Blog), 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`](https://oss.platformatic.dev/docs/reference/sql-mapper/introduction?utm_campaign=Blog%20post%20-%20Building%20REST%20APIs%20with%20Platformatic%20DB&utm_medium=blog&utm_source=Platformatic%20Blog): Maps the schemas of SQL database tables to entity objects.
    
* [`@platformatic/sql-openapi`](https://oss.platformatic.dev/docs/reference/sql-openapi/introduction?utm_campaign=Blog%20post%20-%20Building%20REST%20APIs%20with%20Platformatic%20DB&utm_medium=blog&utm_source=Platformatic%20Blog): 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](https://www.fastify.io/?utm_campaign=Blog%20post%20-%20Building%20REST%20APIs%20with%20Platformatic%20DB&utm_medium=blog&utm_source=Platformatic%20Blog) 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**](https://oss.platformatic.dev/docs/getting-started/quick-start-guide?utm_campaign=Blog%20post%20-%20Building%20REST%20APIs%20with%20Platformatic%20DB&utm_medium=blog&utm_source=Platformatic%20Blog) — Get up and running in 2 minutes!
    
* [**Build a secure web application with Platformatic, Auth0 and Next.js tutorial**](https://blog.platformatic.dev/build-a-secure-web-application-with-platformatic-auth0-and-nextjs?utm_campaign=Blog%20post%20-%20Building%20REST%20APIs%20with%20Platformatic%20DB&utm_medium=blog&utm_source=Platformatic%20Blog) — Learn how to build a full stack blog application with authentication and authorization functionality.
    
* [**Movie Quotes App Tutorial**](https://oss.platformatic.dev/docs/getting-started/movie-quotes-app-tutorial?utm_campaign=Blog%20post%20-%20Building%20REST%20APIs%20with%20Platformatic%20DB&utm_medium=blog&utm_source=Platformatic%20Blog) — Learn how to build a full stack application with Platformatic DB and Astro.
    
* [**Platformatic Client release blog post**](https://blog.platformatic.dev/its-platformatic-client-time-in-the-v0190-release?utm_campaign=Blog%20post%20-%20Building%20REST%20APIs%20with%20Platformatic%20DB&utm_medium=blog&utm_source=Platformatic%20Blog) — Learn how to generate API clients for your Platformatic DB app's REST API.
    
* [**SQL to OpenAPI reference documentation**](https://oss.platformatic.dev/docs/reference/sql-openapi/introduction?utm_campaign=Blog%20post%20-%20Building%20REST%20APIs%20with%20Platformatic%20DB&utm_medium=blog&utm_source=Platformatic%20Blog)
    
* [**SQL Mapper reference documentation**](https://oss.platformatic.dev/docs/reference/sql-mapper/introduction?utm_campaign=Blog%20post%20-%20Building%20REST%20APIs%20with%20Platformatic%20DB&utm_medium=blog&utm_source=Platformatic%20Blog)
    

> The example application code for this guide is available [on GitHub](https://github.com/platformatic/examples/tree/main/applications/tasks-api-platformatic-db?utm_campaign=Blog%20post%20-%20Building%20REST%20APIs%20with%20Platformatic%20DB&utm_medium=blog&utm_source=Platformatic%20Blog).