Building a Todo API with Platformatic DB

In this tutorial, we will build a simple ToDo application using Platformatic DB. Platformatic DB makes it easy to create endpoints that return data from a database application. It automatically generates REST/GraphQL endpoints by mapping your database and then exposes these endpoints to your API consumers.

This guide will walk you through the steps to build Todo CRUD API with Platformatic, highlighting the differences compared to building traditional APIs.

Prerequisites

Before we begin, make sure you have the following installed:

• Node.js (v18 or higher)
• Platformatic CLI

Building a Todo API

This application is a quick way to get started with building APIs on Platformatic. We will be building a simple CRUD API to manage Todo items. Creating a Todo API with Platformatic DB is as easy as creating a database and then mapping it to a GraphQL or REST API.

Let's get started!

Setting Up the Project

To create and run your Platformatic Todo application, run the following commands:

npx create-platformatic@latest

Run the command to start your application:

npm start

Setting up Migrations

Platformatic DB uses SQLite as the default database for any Platformatic DB application, you can see the SQL definition in the .env file in the root folder of your application.

For the Todo API, we need two tables, Users and Todos, let's edit the migrations generated by Platformatic CLI to add these tables:

Creating a User table

To create the users table, navigate to the db/migrations directory and edit 001.do.sql file, and add the schema below:

CREATE TABLE IF NOT EXISTS User (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

And let's edit the migrations/001.undo.sql file to look like this:

DROP TABLE User;

Before we apply the migrations, let's create a new table for our Todos, to do that create another file 002.do.sql and inside it, add the schema for your Todos table.

CREATE TABLE IF NOT EXISTS Todo (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    title TEXT NOT NULL,
    description TEXT,
    due_date DATE,
    completed BOOLEAN DEFAULT 0
);

And again, add a new file 002.undo.sql to drop the table.

DROP TABLE Todo;

note

See the Glossary for terminologies and definitions used in Platformatic DB.

Now let's apply the migrations we just created by running the command below:

npx platformatic db migrations apply

Notice that after running migrations, you get a global.d.ts and a types folder with all our types and interfaces automatically generated by Platformatic DB. The global.d.ts file is used for querying the Platformatic database.

Now, start your Platformatic DB application by running:

npm start

Now you'll see this screen when you open http://0.0.0.0:3042/ in your browser:

Testing API endpoints

To test our API endpoints from Platformatic, click on the OpenAPI Documentation link on this page http://0.0.0.0:3042/. This will open the Swagger editor editor with all the API endpoints we just created.

Click on Test request and test the Create Todo endpoint as shown below:

You should get a 200 OK status code for a successful request.

Conclusion

Congratulations! You have successfully created a simple Todo API using Platformatic. This tutorial covered the basics of setting up a Platformatic project, defining a schema, configuring the service, and creating API endpoints. For more advanced features and configurations, refer to the Platformatic API Documentation.