Writing type-safe Javascript code with Prisma and MySQL

Photo by Pop & Zebra on Unsplash

This tutorial is an in-depth look at creating a type-safe API server using JavaScript, Node.js, Express, Joi, Prisma, and MySQL. You will learn how to create a server using Express, validate requests with Joi, and use Prisma to perform CRUD operations on your database.

If you are unfamiliar, CRUD stands for create, read, update, and delete. These are operations an API implements that enable programmers to access and modify data. Your Express API server will be feature-complete.

Before we begin

In this tutorial, you will learn how to:

• Impose type-safety on a Javascript API using Prisma and validation.
• Create a functional, modern backend with Express, Prisma, and MySQL.
• Add CRUD operations and HTTP requests.
• Model your data with Prisma.

What is type safety?

Type safety is a standard enforced by programming languages to reduce or eliminate type errors. For instance, if a method requires integer arguments and the programmer were to pass a list of strings, a type-safe language would throw an error.

JavaScript is not inherently a type-safe language, but you can impose some level of type safety. Prisma is type-safe, because the data model it generates is strongly typed. But without validation, you’re better off using Joi to validate your input, and Prisma to write the validated input to your MySQL database.

What is Prisma?

Prisma is a query-builder for JavaScript and TypeScript. It is compatible with a number of different SQL servers, including MySQL, Postgres, and SQLite, with MongoDB and Microsoft SQL server in pre-release. Prisma simplifies database operations and enforces type safety in your queries.

Prisma works by autogenerating a client that is type-aware and specific to your project. It’s also capable of creating a schema automatically, by pulling information from your database server. Prisma also features a visual editor, called Prisma Studio, and a command-line interface (CLI) for interacting with your database.

Writing a type-safe API with Prisma

The example below uses Prisma Client and Node.js to create an API that will communicate with a MySQL database server. It will use a database schema created for a bakery, specifically an address book table describing the bakery’s commercial suppliers.

Setting up the project

First you will need to create a directory for your project. This example will use a project directory called javascript-prisma-mysql. Use these commands to create the directory and navigate to it::

mkdir javascript-prisma-mysql
cd javascript-prisma-mysql

Once you are in the correct directory, you should initialize your Node.js project.

Initializing your Node.js project

Before you can start the server, you will have to initialize the project. Run this command in your project’s root directory:

npm init

After you hit enter, you will see an interactive wizard has started. It will ask questions about your project. It is okay to hit “enter” and accept the defaults for every line. Running the command with the -y argument will skip this wizard and generate your project without requiring input.

The npm init interactive wizard.

When the wizard has finished, it will generate the files Node needs to run the application. Now you can move on to installing the project dependencies.

Installing project dependencies

You will need to install a number of packages before beginning to write code. For this project, you will be using the following Node.js packages:

Express is the framework you will be using to build out your API server. It provides the mechanisms needed to handle HTTP requests and routing. It is provided by the express package.

The Prisma CLI will allow you to invoke Prisma-specific commands using npx. It will need to be installed separately, as a dev dependency, from the prisma package.

Prisma client is the query-builder that enables communication with your MySQL database using the schema generated using the Prisma CLI. Prisma will ensure type-safe API responses. You will install it from the @prisma/client package.

Joi is a data validation framework that you will use to check that incoming HTTP requests are properly typed. You will install it from the joi package.

Joi validation for Express is the middleware you will use to apply Joi data validation rules to your Express API requests. It is provided by the express-joi-validation package.

Make sure you have Node.js installed, then run these commands in your project directory:

npm install express joi express-joi-validation @prisma/client
npm install prisma — save-dev

When installation is finished, create a new file to match the project entry point you specified in the previous step. If you follow the steps exactly, it will need to be named index.js.

Beginning your Express API with Prisma

You should begin by editing index.js to create an API server. In the example, the server runs on port 3000 and displays a hello message to confirm it’s working.

The first code

You can start by creating a Hello World app and defining your first route, as follows:

/** Required Node.js modules */
const Express = require(‘express’);
/** Initialize Express */
const app = Express();
app.use(Express.json());
const port = 3000;
/** Routes */
app.get(‘/’, (req, res) => {
res.send(“Hello, API server!”);
});
/** Server */
app.listen(port, () => {
console.log(`API listening on localhost: ${port}`);
});

The code above implements an Express API and defines a single route. You start by importing the required module using the statement const Express = require(‘express’); .

Then you define the variables for the Express instance and the port your API will listen on. You will define the root route of your Express API with a function call. In this case, you use the app.get() function to define a handler for HTTP get requests.

The first argument to app.get() specifies which path Express will listen for requests on, and the second argument is a callback containing req and res objects which contain the HTTP request and response respectively.

You will place the route handler logic in the body of app.get(). To demonstrate functionality, when a user visits the root, your API server displays a hello message.

Finally, you call the Express listen method and log some information about the server. In this example, a short message is printed to the console saying the server is running and displaying the port your API server is listening on.

You may notice the example has not yet included Prisma or Joi. That is okay, the focus is on getting the server started before adding anything else.

Now that your code is written, you can start the server with this command:

node index.js

You’ll see the message we logged to the console:

The output of the running Node server.

When you open a browser and visit http://localhost:3000, you should see the hello message:

The welcome message that you’ll see when running the app.

Once you have reached that stage, you can shut down the server with Ctrl+C in the console, and re-run node index.js to restart it whenever you like.

Introducing Joi and Prisma for a type-safe API

In order to show the importance of type safety in an API, here is an example of an unsafe API server:

const Express = require('express');
/** Initialize Express */
const app = Express();
app.use(Express.json())
const port = 3000;
/** Routes */
app.post('/unsafe_add', (req, res) => {
  let answer = Number(req.body.integer) + 10;
  res.send(String(answer));
});
/** Server */
app.listen(port, () => {
  console.log(`API listening on localhost: ${port}`);
});});

As you can see, the code is similar to the previous example. This API server implements a route called unsafe_add that will add 10 to a given number. The logic for this route casts a body parameter named integer as a number, then adds it to 10, and sends back the result as a string.

Here is a sample request and response using Postman:

At first glance it appears to work fine. But what if you pass in a value that cannot be cast as a number by accident? For example, the word ‘twenty five’:

This time the API returns the not-a-number (NaN) value because it was unable to perform the cast successfully. Imagine the issues you might face in a large production codebase without type safety.

Implementing a type-safe API with Prisma and Joi

Now that you can see the value of having a type-safe API server, it is time to focus on the implementation.

Importing and instantiating a new Prisma client

First you will need to add the required module for Prisma to your source code and instantiate a new Prisma client:

/** Required Node.js modules */
const Prisma = require('prisma/prisma-client');
/** Instantiate Prisma client */
const prisma = new Prisma.PrismaClient();

Now the client has been imported and instantiated, but it will not be ready to use prior to Prisma’s initialization. Run this command in your project directory:

npx prisma init

When the command is finished, you will notice new files in your project directory. Prisma created a file in the root of your directory named .env which stores environmental variables. It also created a directory called prisma, and a file in that directory named schema.prisma.

Edit your .env file and specify information about your database connection. If you don’t already have a MySQL database running locally, you can purchase a managed database.

In order to enable Prisma’s connection to your database, you will have to change the URI stored in .env to match your settings:

DATABASE_URL=localhost://username:password@localhost:3306/bakery_demo

In the URL above, you should edit the host, username, password, and table to match your settings. For more information on URL formatting, please see the Prisma URL connection guide for MySQL.

You will also need to write a Prisma schema for your API. This schema will describe the objects stored in your database so Prisma can generate a client to store and retrieve them. This example will use the Supplier table from the Prisma bakery demo as a model for the schema.

Create your schema by editing the file prisma/schema.prisma. To begin with, your schema will contain information about the connection to your database:

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

It is important that the provider matches your database. The Prisma database connector defaults to postgresql, but you should change it to mysql for this project. Take a look at the complete list of Prisma database connectors for more information.

Once you’ve specified the correct database connector, add your database model at the end of the Prisma schema in prisma/prisma.schema. The Supplier model in this demo contains a single integer field for the supplier id, and the rest of the fields are strings:

model Supplier {
  id        Int    @id @default(autoincrement())
  name      String @unique
  contact   String
  email     String
  phone_num String
  address   String
}

Run the following command to push your schema to the database and generate a Prisma client for your API:

npx prisma db push

After the client has been generated, you will be able to use it to query your database. Since the client is unique to your project, you will be able to access fields in your database using IntelliSense.

Now it is time to set up Joi to validate incoming requests to your API.

Importing Joi and creating the Joi schema

The first step to set up Joi is importing the modules. At the top of your code, add these lines:

const Joi = require(‘joi’)
const joiMiddleware = require(‘express-joi-validation’).createValidator({})

The first line will import the core Joi module, and enable you to write validation rules. The second line imports the Joi module which contains middleware for Express APIs.

Joi middleware is a function that is passed to your routes which is called before the route logic. If validation fails, the routing logic never gets executed.

Joi uses its own schema to validate input passed to the API. You can base your Joi schema off of the one in prisma/schema.prisma. You will need to model the same fields so Joi can validate Supplier objects properly. Add the validator schema to your index.js file:

/** Supplier validation schema for Joi */
const supplierSchema = Joi.object({
  Id: Joi.number().greater(0),
  name: Joi.string().alphanum().min(1).max(191).required(),
  contact: Joi.string().alphanum().min(1).max(191).required(),
  email: Joi.string().email().max(191).required(),
  phone_num: Joi.string().length(10).pattern(/^[0-9]+$/).required(),
  address: Joi.string().alphanum().min(1).max(191).required(),
});

As you can see, Joi’s validation schemas are made up of methods chained together. The id field will only accept numbers greater than 0 as input. The other fields have to be at least one character long.

The example uses a maximum of 191 characters for strings, because that is the maximum length of each field in the MySQL schema used. Prisma maps strings in the model to varchar(191) fields in MySQL by default.

Here is a list with more validation methods for Joi:

• bool(). Joi type used for validating boolean objects.
• bool().falsy(). Valid if the boolean is false or a value equivalent to false, such as 0.
• bool().truthy(). Valid if the boolean is true or a value equivalent to true, such as 1.
• bool().validate(value). Valid if the boolean matches value, which can be true or false.
• date(). Joi type used for validating date or time objects.
• date().greater(date). Valid if the date is sooner than the specified date in MM-DD-YYY format.
• date().less(date). Valid if the date is later than the specified date in MM-DD-YYY format.
• date().timestamp([options]). Valid if the timestamp is in the same format as the specified option, which can be unix or javascript.
• number(). Joi type used for validating numeric objects.
• number().greater(value). Valid if the number is greater than the specified value.
• number().less(value). Valid if the number is less than the specified value.
• number().max(value). Valid if the number is less than the maximum specified value.
• number().min(value). Valid if the number is greater than the specified minimum value.
• number().positive(). Valid if the number is positive.
• number().negative(). Valid if the number is negative.
• number().validate(value). Valid if the number matches the specified value.
• string(). Joi type used for validating string objects.
• string().alphanum(). Valid if the string contains only alphanumeric characters.
• string().creditCard(). Valid if the string is found to be a credit card number using the Luhn algorithm.
• string().domain([options]). Valid if the string is a domain name. An array of options can be specified.
• string().email([options]). Valid if the string is an email address. An array of options can be specified.
• string().length(value). Valid if the string length matches the specified value.
• string().lowercase(). Valid if the string is in lowercase. Strings that are not lowercase will be forced to lowercase by default.
• string().min(value). Valid if the string is longer than the minimum specified value.
• string().max(value). Valid if the string is shorter than the maximum specified value.
• string().pattern(regex). Valid if the string matches the regular expression specified in regex.
• string().uppercase(). Valid if the string is in uppercase. Strings that are not uppercase will be forced to uppercase by default.

The full list of Joi schema validation methods can be found in the Joi API documentation. For clarity, here is the source code up to this point:

/** Required Node.js modules */
const Express = require('express');
const Joi = require('joi')
const joiMiddleware = require('express-joi-validation').createValidator({})
/** Supplier validation schema for Joi */
const supplierSchema = Joi.object({
  id: Joi.number().greater(0),
  name: Joi.string().alphanum().min(1).max(191),
  contact: Joi.string().alphanum().min(1).max(191),
  email: Joi.string().email().max(191),
  phone_num: Joi.string().length(10).pattern(/^[0-9]+$/),
  address: Joi.string().alphanum().min(1).max(191),
});
/** Initialize Express */
const app = Express();
app.use(Express.json());
const port = 3000;
/** Routes */
app.get('/', (req, res) => {
  res.send("Hello, API server!");
});
/** Server */
app.listen(port, () => {
  console.log(`API listening on localhost: ${port}`);
});

Implementing CRUD

With the setup complete and your validator written, you can now focus on writing type-safe Javascript with Prisma. In this section, you will learn to write all the CRUD methods for your API.

Each of the CRUD operations will be written in a new method in the Routes section. In a production environment, these are often split off into their own source files. But for demonstration purposes, the example will leave everything in index.js.

Creating records with Prisma and MySQL

Records are typically created with a POST request, so you will need to hold true to that convention here. The below example includes the following route to handle record creation:

/** Create a new supplier and write it to MySQL */
app.post("/supplier/create", joiMiddleware.query(supplierSchema), async (req, res) => {
  try {
    /** Use Prisma to write the data to our MySQL database */
    res.body = await prisma.supplier.create({
      data: {
        name: req.body.name,
        contact: req.body.contact,
        email: req.body.email,
        phone_num: req.body.phone_num,
        address: req.body.address,
      }
    });
  /** Send a response telling the user our data has been written */
  res.status(200).send({"message": "OK"});
  }
  /** If Prisma fails to write to the MySQL database */
  /** Catch the error and send it as our response */
  catch (err) {
    res.status(500).send(err);
  }
});

Looking at the code above, you can see the query to Joi middleware in the route definition. But Prisma is doing its own validation, and any errors that it throws are handled by the catch block. For instance, the Prisma model specifies that each Supplier needs a unique name. You can use Postman to see how Prisma handles a duplicate name:

A 500 Internal Server Error status of an API request with a duplicate name shown in the Postman interface.

Note the 500 Internal Server Error, along with the target field in the JSON response saying that Prisma requires a unique name.

Reading records from MySQL with Prisma

Here’s another route using Prisma, but this time you will be returning a Supplier object matching a given name.

Since you will return all information about a Supplier based on the name, that is the only variable you will need to validate. Once you have the new schema, write the code to return your Supplier object:

/** Get a supplier by name, return the object in our response */
app.get('/supplier', async (req, res) => {
  try {
    const supplier = await prisma.supplier.findUnique({
      where: {
        name: req.body.name,
      }
    });
    /** Send the Supplier object as our response */
    res.status(200).send({"object": supplier});
  }
  /** When Prisma fails to read from database, catch the error and send it as our response */
  catch (err) {
    res.status(500).send(err);
  }
});

When a valid name is passed to the /supplier route on the API server, the Supplier object is returned as a JSON array:

The Supplier object JSON in a successful request.

You can see the query on the left and the response on the right. Prisma was able to find the Supplier object using its findUnique() method.

Updating records in MySQL with Prisma

Next, try building out the Update method. In this example you will use the Supplier id to update the corresponding object. Once the correct supplier is found, Prisma will patch in the updated data and return a message indicating success:

/** Update an existing supplier */
app.patch("/supplier/update", joiMiddleware.query(supplierSchema), async (req, res) => {
  try {
    /** Use Prisma to write the data to our MySQL database */
    await prisma.supplier.update({
      where: {
        id: req.body.id,
      },
      data: {
        name: req.body.name,
        contact: req.body.contact,
        email: req.body.email,
        phone_num: req.body.phone_num,
        address: req.body.address,
     }
   });
   /** Send a response telling the user our data has been written */
   res.status(200).send({"message": "OK"});
  }
/** When Prisma fails to write to database, catch the error and send it as response */
  catch (err) {
    res.status(500).send(err);
  }
})

If you look closely, you might notice the update method looks like a combination of read and create. This example first queries by id, then uses Prisma to overwrite the name, contact, email, phone_num, and address fields in the Supplier object.

You might be surprised to see the example passing in all of the fields. It is easy to imagine that MySQL might overwrite information in the database with empty strings. But JSON objects drop undefined strings automatically, so it is safe to update the entire object.

Now check the database to see if the update worked. The example below uses Prisma Studio to browse the database. Here is the data before it was updated:

The original Supplier record shown in the Prisma Studio interface.

And here is the data after the update request:

The updated Supplier record shown in the Prisma Studio interface.

As you can see, the contact name and email address were changed as expected.

Deleting records from MySQL with Prisma

Deleting a Supplier object is very similar to reading. In this case, instead of using Prisma’s findFirst() method, you can use the delete() method. Here is the code for the delete operation:

/** Delete a supplier with a given name */
app.delete('/supplier/delete', joiMiddleware.query(supplierSchema), async (req, res) => {
  try {
    const supplier = await prisma.supplier.delete({
      where: {
        name: req.body.name,
      }
    });
    res.status(200).send({"message": "deleted OK"});
  }
  /** If Prisma fails to delete the object, catch the error and send it as response */
  catch (err) {
    res.status(500).send(err);
  }
});

You can confirm the object is deleted upon receiving the expected response:

The confirmation of object deletion.

With the delete method implemented, all of the CRUD operations are implemented. The resulting API is sparse, but complete.

Type-safe Javascript API calls with Prisma

As you can see from the code, it is possible to get some degree of type-safety in Javascript with Prisma and careful validation. Another solution might be to implement this API using Typescript, which is type-safe by default. But for many projects, the level of type-safety Prisma offers is enough.

Here is the full source code for reference:

Improvements and changes

If you are interested in backend programming with Javascript and Prisma, you might want to spend some time refining this CRUD implementation. The example was based on decisions made in the interest of keeping things easy.

One change you might make would be to the route and controller logic. They should be split into their own files entirely. Production APIs routes and controllers in a modular way.

Prisma brings type-safety to Javascript

This example only touched on a few of Prisma’s features. It offers a simple, declarative way to model large or complex databases. Since you only modelled a single table, you might not realize how well it scales.

Prisma offers a lot more than type-safe database queries. Prisma Studio and the CLI make database management and migrations easy.

For an in-depth API implementation using Prisma, take a look at Building a modern backend with TypeScript, PostrgreSQL, and Prisma. If you need guidance, you can join the Prisma Slack channel.