Using PlanetScale and TypeORM to build a Node.js REST API
Photo by Andréas BRUN / Unsplash
PlanetScale is a Serverless database for MySQL that brings a new concept of managing our database schema. It has a CLI to work the database branching, but we need a tool that makes it easier to interact with the database at the application level.
This is where the ORM comes into action; there are many ORMs in the Node.js ecosystem, and TypeORM is one of the most popular. This tutorial will show how combining these tools can provide a great developer experience while building application features.
What we will build
We will build a REST API that allows users to manage their tasks, aka a task manager. Here is the single table we will have in our database. I designed the schema using DrawSQL.

Here are the endpoints we will create:
- [POST] /tasks - Create a task
- [PATCH] /tasks/:id - Update a task (name, description, status, due date)
- [DELETE] /tasks/:id - Delete a task
- [GET] /tasks - Retrieve all tasks
- [GET] /tasks/:id - Retrieve a task
Create the database on PlanetScale
To create a database on PlanetScale, you to create an account. You can use the Web UI or CLI tool. I wrote a whole post on how to work with the PlanetScale CLI.
We will create a database and promote the main branch to production:
pscale auth login
pscale database create task-manager-db
pscale branch promote task-manager-db main
Our database is ready for production; we can now continue setting up the project.
Create the Node.js project
With TypeORM, you can quickly initialize a Node.js project using the CLI; check out this link to learn more about it. Here is the command to create a project with a MySQL database:
npx typeorm init --name node-typeorm-planetscale --database mysql
We get the following folder structure:

Install the dependencies with the command:
yarn install
Create the task's model
Rename the file src/entity/User.ts
to src/entity/Task.ts
then, add the code below:
import {
Entity,
PrimaryGeneratedColumn,
Column,
CreateDateColumn,
UpdateDateColumn,
Index
} from "typeorm";
export enum TaskStatus {
PENDING = "pending",
IN_PROGRESS = "in_progress",
DONE = "done"
}
@Entity({ name: "tasks" })
export class Task {
@PrimaryGeneratedColumn()
id: number;
@Column({ unique: true })
name: string;
@Column({ nullable: true })
description: string;
@Index()
@Column({ type: 'varchar', length: 20, default: TaskStatus.PENDING })
status: TaskStatus;
@Column({ name: 'due_date', type: 'date' })
dueDate: Date;
@CreateDateColumn({ name: 'created_at' })
createdAt: Date;
@UpdateDateColumn({ name: 'updated_at' })
updatedAt: Date;
}
Now we need to generate the migration for this code and execute it in our database but, before that, we need to set up the connection to the database.
Create the database branch and connect to it
Let's use PlanetScale CLI to create a new branch from the main branch:
pscale branch create task-manager-db create-tasks-table
PlanetScale allows creating a secure proxy connection between our local computer and the remote database branch to connect to the database from our localhost environment. This has the advantage of not worrying about credentials management.
Here is the command to connect to the database branch:
pscale connect task-manager-db create-tasks-table --port 3308
We got the output below:

The flag --port
is specified to prevent errors if port 3306 is already used (the default port for a MySQL instance). So, If you receive an error about the 3308 already used, you can replace it with a new one.
We can now connect to the database from our local computer. You might wonder what are the credentials? Here they are:
- Host: 127.0.0.1
- user: root
- Password:
- Port: 3308
- Database: task-manager-db
The equivalent as connection string is:
mysql://root:@127.0.0.1:3308/task-manager-db
Run the migrations on a development branch
Before running the migrations, we need to update the TypeORM config with the database credentials; otherwise, it will not know where to execute the migration. Open the file ormconfig.json
with the database credentials above; the content looks like this:
{
"type": "mysql",
"host": "localhost",
"port": 3308,
"username": "root",
"password": "",
"database": "task-manager-db",
"synchronize": true,
"logging": false,
"entities": [
"src/entity/**/*.ts"
],
"migrations": [
"src/migration/**/*.ts"
],
"subscribers": [
"src/subscriber/**/*.ts"
],
"cli": {
"entitiesDir": "src/entity",
"migrationsDir": "src/migration",
"subscribersDir": "src/subscriber"
}
}
Save and close the file.
Here is the command to generate the migration from our entity class using TypeORM:
yarn typeorm migration:generate -n create-tasks-table
The migration file is generated inside the folder migration
and here is the content:
import {MigrationInterface, QueryRunner} from "typeorm";
export class createTasksTable1642869891226 implements MigrationInterface {
name = 'createTasksTable1642869891226'
public async up(queryRunner: QueryRunner): Promise<void> {
await queryRunner.query(`CREATE TABLE \`tasks\` (\`id\` int NOT NULL AUTO_INCREMENT, \`name\` varchar(255) NOT NULL, \`description\` varchar(255) NULL, \`status\` varchar(20) NOT NULL DEFAULT 'pending', \`due_date\` date NOT NULL, \`created_at\` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), \`updated_at\` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), INDEX \`IDX_6086c8dafbae729a930c04d865\` (\`status\`), UNIQUE INDEX \`IDX_396d500ff7f1b82771ddd812fd\` (\`name\`), PRIMARY KEY (\`id\`)) ENGINE=InnoDB`);
}
public async down(queryRunner: QueryRunner): Promise<void> {
await queryRunner.query(`DROP INDEX \`IDX_396d500ff7f1b82771ddd812fd\` ON \`tasks\``);
await queryRunner.query(`DROP INDEX \`IDX_6086c8dafbae729a930c04d865\` ON \`tasks\``);
await queryRunner.query(`DROP TABLE \`tasks\``);
}
}
Now, we only need to execute the migration in our database:
yarn typeorm migration:run
We get the following output:

Write the logic for each REST endpoint
First, let's install the Express that we will use to create the endpoint route and link it to the corresponding logic.
yarn add express
yarn add -D @types/express @types/node
Inside the folder, src
, create a folder called controller
then, add inside a file called task.controller.ts
and finally, add the content below:
import { Request, Response } from "express";
import { getRepository } from "typeorm";
import { Task, TaskStatus } from "../entity/Task";
type CreateTaskInput = {
name: string;
description?: string;
dueDate: Date;
}
type UpdateTaskInput = Partial<CreateTaskInput> & {
status?: TaskStatus;
};
export const createTask = async (req: Request, res: Response) => {
const input: CreateTaskInput = req.body;
try {
const createdTask = await getRepository(Task).save({ ...input });
return res.json({ data: createdTask });
} catch (error) {
console.error(error);
return res.status(400).json({ message: error.message })
}
};
export const updateTask = async (req: Request, res: Response) => {
const id = Number(req.params.id);
const input: UpdateTaskInput = req.body;
const task = await getRepository(Task).findOne({ id });
if (!task) {
return res.status(404).json({ message: `The task with the id "${id}" not found.`})
}
try {
await getRepository(Task).update({ id }, { ...input });
const updatedTask = await getRepository(Task).findOneOrFail({ id });
return res.json({ data: updatedTask });
} catch (error) {
console.error(error);
return res.status(400).json({ message: error.message })
}
};
export const deleteTask = async (req: Request, res: Response) => {
const id = Number(req.params.id);
const task = await getRepository(Task).findOne({ id });
if (!task) {
return res.status(404).json({ message: `The task with the id "${id}" not found.`})
}
await getRepository(Task).delete({ id });
return res.json({ message: "The task has been deleted successfully!" });
};
export const retrieveTask = async (req: Request, res: Response) => {
const id = Number(req.params.id);
const task = await getRepository(Task).findOne({ id });
if (!task) {
return res.status(404).json({ message: `The task with the id "${id}" not found.`})
}
return res.json({ data: task });
};
export const retrieveAllTasks = async (req: Request, res: Response) => {
const tasks = await getRepository(Task).find({ order: { createdAt: "DESC" } });
return res.json({ data: tasks });
};
This file has five methods: createTask(), updateTask(), deleteTask(), retrieveTask(), retrieveAllTasks() that will assigned to each endpoint.
Create the server and API routes
Replace the content of the file src/index.ts
by the content below:
import "reflect-metadata";
import express from "express";
import http from "http";
import { createConnection } from "typeorm";
import {
createTask,
deleteTask,
retrieveAllTasks,
retrieveTask,
updateTask
} from "./controller/task.controller";
const SERVER_PORT = 8100;
const app = express();
const router = express.Router();
// Express middleware
app.use(express.urlencoded({ extended: true }));
app.use(express.json());
router.post('/tasks', createTask);
router.patch('/tasks/:id', updateTask);
router.delete('/tasks/:id', deleteTask);
router.get('/tasks/:id', retrieveTask);
router.get('/tasks', retrieveAllTasks);
const server = http.createServer(app);
server.listen(SERVER_PORT, async () => {
// Connect to database
try {
await createConnection();
} catch (err) {
console.error(err);
process.exit(1);
}
console.info(`Server started at the port ${SERVER_PORT}`);
});
Update your tsconfig.json to add these properties:
{
// .... previous tsconfig options here ....
"allowSyntheticDefaultImports": true,
"esModuleInterop": true
}
Start the server and test
Run the following command to start the server: yarn start
Pick your favorite HTTP Client and test your API by sending requests to the endpoints created. I use Postman.

The API is working as expected ?
Deploy the database in production
Since everything is working in the local environment, we can update the main branch. We will create a deployment request.
pscale deploy-request create task-manager-db create-tasks-table
pscale deploy-request review task-manager-db <deploy-number> --approve
pscale deploy-request deploy task-manager-db <deploy-number>

The last thing to do is delete a database branch we used since it is no longer needed. Run the command below, then confirm the deletion.
pscale branch delete task-manager-db create-tasks-table
Wrap up
At the end of this tutorial, we saw how to use TypeORM to work with PlanetScale. As you can see, the combination of the two makes it easier to implement our features.
You can find the code source on the GitHub repository.
Follow me on Twitter or subscribe to my newsletter to not miss the upcoming posts and the tips and tricks I share every week.
Happy to see you soon ?