Creating API endpoints for web applications using the REST architectural framework is an extremely common practice for most backend developers. The process of creating API endpoints is a multi-step activity starting from creating the API layout, integrating the selected database library, creating the CRUD endpoints, validating requests, and implementing other desired processes.

These processes can be challenging to implement as they are time-consuming and can be complex.

In comes Platformatic DB- a tool that helps developers simplify these steps when building a REST API. When building an app with Platformatic DB, a REST API is automatically mapped from the tables in our database (this can be an pre-existing database).

Users can also write SQL migrations and apply them with the Platformatic CLI to create the schemas for new database tables.

In this tutorial, we will build a simple REST API for a shopping cart API. We will also learn how to automatically generate documentation for our API and test it using Platformatic DB.

Prerequisites

To follow this tutorial, you need to have a basic knowledge of:

Javascript
SQL databases and how to work with them.

You'll also need to have the following installed on your PC:

Node.js >= vv18.8.0 or >= v19.0.0
npm v7 or later
A code editor (for this tutorial, we will be using VS Code)

We will explore the two methods of implementing the Platformatic DB:

The automatic CLI method
The manual method

We will also be using Chat GPT to generate some of our app schemas.

Setting Up Platformatic DB

To set up Platformatic DB using the automatic CLI method:

Create a folder named ecommerce-api and open the folder in the code editor
Open your terminal in your code editor and input the code below to start the Platformatic Creator wizard

npm create platformatic@latest

3. After running the command in the terminal, you will be prompted with an interactive command-line tool. For this tutorial, select these options:

- Which kind of project do you want to create?  => DB
- Where would you like to create your project?  => . 
- Do you want to create default migrations?     => Yes
- Do you want to create a plugin?               => Yes
- Do you want to use TypeScript?                => No
- Do you want to install dependencies?          => Yes (this can take a while)
- Do you want to apply the migrations?          => Yes
- Do you want to generate types?                => Yes
- Do you want to create the github action to deploy this application to Platformatic Cloud dynamic workspace? => No
- Do you want to create the github action to deploy this application to Platformatic Cloud static workspace?  => No

Using ChatGPT to Generate Schemas

Now we will generate our schema for the shopping cart application using Chat GPT. We put in this prompt:

💡

generate sql schema for shopping cart application

Bear in mind that GPT might not generate the exact number of decimals and varchar for you.

Below is the schema generated by Chat GPT:

CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL
);

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  description TEXT
);

CREATE TABLE carts (
  id INT PRIMARY KEY,
  customer_id INT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
);

CREATE TABLE cart_items (
  id INT PRIMARY KEY,
  cart_id INT,
  product_id INT,
  quantity INT NOT NULL,
  FOREIGN KEY (cart_id) REFERENCES carts(id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

However, we will make some changes to the tables to prevent errors when migrating. To do so, update the tables to the code below:

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  description TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE cart_items (
  id INTEGER PRIMARY KEY,
  cart_id INTEGER,
  product_id INTEGER,
  quantity INTEGER NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (cart_id) REFERENCES carts(id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

CREATE TABLE carts (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
);

Integrating our schema to Platformatic DB with the Automatic CLI method

Run the command below in your terminal to undo the default migration:

npx plt db migrations apply -t 0

To integrate the schemas into Platformatic DB:

Copy and paste the above schemas into the 001.db.sql file
After creating the SQL file for the tables in the migration folder, run the following command in your terminal to run your migrations.
Run the command below to apply your migrations with the new set of tables you have created in your migrations folder:

npx platformatic db migrations apply

4. Start your server by running the command below in your terminal:

npx platformatic db start

5. You should have the response below in your terminal

6. You can skip step 4 by running step 5 directly, with the below response in your terminal

7. Your server is now listening at the URL http://127.0.0.1:3042

The following automatically generated files will appear as shown below:

1. Customer.d.ts file:

/**
 * Customer
 * A Customer
 */
declare interface Customer {
    id?: number;
    name: string;
    email: string;
    createdAt?: string | null;
    updatedAt?: string | null;
}
export { Customer };

2. Product.d.ts file:

/**
 * Product
 * A Product
 */
declare interface Product {
    id?: number;
    name: string;
    price: string;
    description?: string | null;
    createdAt?: string | null;
    updatedAt?: string | null;
}
export { Product };

3. Cart.d.ts file:

/**
 * Cart
 * A Cart
 */
declare interface Cart {
    id?: number;
    customerId?: number | null;
    createdAt?: string | null;
    updatedAt?: string | null;
}
export { Cart };

4. CartItem.d.ts:

/**
 * CartItem
 * A CartItem
 */
declare interface CartItem {
    id?: number;
    cartId?: number | null;
    productId?: number | null;
    quantity: number;
    createdAt?: string | null;
    updatedAt?: string | null;
}
export { CartItem };

5. Index.d.ts file:


import { Customer } from './Customer'
import { Product } from './Product'
import { CartItem } from './CartItem'
import { Cart } from './Cart'

  interface EntityTypes  {
    Customer: Customer
    Product: Product
    CartItem: CartItem
    Cart: Cart
  }

  export { EntityTypes, Customer, Product, CartItem, Cart }

6. Global.d.ts file:


import { EntityTypes, Cart, CartItem, Customer, Product } from './types'

7. Entities Interface:


declare module '@platformatic/sql-mapper' {
  interface Entities {
    cart: Entity<Cart>,
    cartItem: Entity<CartItem>,
    customer: Entity<Customer>,
    product: Entity<Product>,
  }
}

8. Fastify Instance Interface:

getSchema<T extends ' '>(schemaId: T)

9. Run the code below to apply your new migrations, create a new db.sqlite file, and regenerate the global.d.ts file:

npx platformatic db migrations apply

10. Run the command below to start your server on port 3042.:

npm start

11. You should have the output below in your terminal:

Generating The API

One of Platformatic's features is the ability to generate APIs on the go from the tables provided in SQL files. These generated APIs are also displayed using the Swagger API format, making testing a seamless experience.

Below is a list of APIs that have been automatically generated by Platformatic and can be easily integrated into our app’s front end.

Methods	Route
POST	/customers/
GET	/customers/
GET	/customers/{id}
GET	/customers/{id}/carts
PUT	/customers/{id}
DELETE	/customers/{id}
POST	/products/
GET	/products/
GET	/products/{id}
GET	/products/{id}/cartItemProduct
PUT	/products/{id}
DELETE	/products/{id}
POST	/carts/
GET	/carts/
GET	/carts/{id}
GET	/carts/{id}/cartItemCartId
GET	/carts/{id}/customer
PUT	/carts/{id}
DELETE	/carts/{id}
POST	/cartItems/
GET	/cartItems/
GET	/cartItems/{id}
GET	/cartItems/{id}/product
GET	/cartItems/{id}/cart
PUT	/cartItems/{id}
DELETE	/cartItems/{id}

Testing Out The API

To test out the generated API for the shopping cart app, visit the URLs below in your browser:

The APIs are displayed using the Swagger UI which is easy to interact with.

Below is an example of how to use the UI to test out the POST request to create a new customer.

Wrapping up

During the course of this tutorial, we have seen how Platformatic DB offers a fast and seamless way in which to create API endpoints using both the automatic CLI and the manual method.

You can go on to add more features to what we have built in this tutorial, such as adding more fields to each table or adding more tables to our migrations to generate more endpoints.

Get started with Platformatic:

Build robust Node.js apps with our open-source tools
Deploy your apps with Platformatic Cloud Free
Join our community on Discord

Use ChatGPT and Platformatic to Build Your API in No Time

Table of contents