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.

Discover PlanetScale: The MySQL serverless database
PlanetScale is a Serverless database for MySQL. In this tutorial, I show to use the CLI to manage your database and perform tasks like creating a branch, connecting to the shell, executing the query, and deploying to production.
Teco TutorialsEric Cabrel TIOGO

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.

Database schema of our task manager.
Database schema of our task manager.

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:

Folder structure generated by the TypeORM CLI.
Folder structure generated by the TypeORM CLI.

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:

Connect to the remote database branch from the local computer
Connect to the remote database branch from the local computer

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:

Execute the migration in the database.

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.

Test our REST API with Postman
Test our REST API with 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>
Deploy the dev branch on the main branch.

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 😉