Build a contacts app with Node, Express, and MySQL

The database is an essential component of every application as it provides the requisite data for the application to function and must be a primary consideration when designing/implementing an application. Another important consideration is whether to render the application on the client's browser or the application server. By taking advantage of the faster load time SSR (Server Side Rendering) provides, your application can serve your users even better.

In this article, I will show you how to implement SSR using Express and Pug. PlanetScale will provide the database service - allowing you to enjoy all the benefits of MySQL via a highly scalable and reliable service provider, while Prisma will be used as an ORM (Object Relational Mapping) tool. To keep things simple, you will focus on creating basic features for managing customer data and rendering the user interface using Pug templates.

Getting Started

Setting up PlanetScale Database

With PlanetScale, you can either create a new database via the dashboard or the pscale CLI. Whichever you choose to use is a matter of preference. While this article will show you the steps via the dashboard, the corresponding CLI command will be shown below. Note that whichever route you choose, you have to be logged in. The command to login via the CLI is shown below.

pscale auth login

To complete the CLI authentication process, a confirmation page will be opened in your browser. Confirm that the displayed code matches the one displayed in your terminal by clicking Confirm code.

Having done that, you can proceed with creating a new database.

Create a database via the dashboard

Go to the PlanetScale dashboard and click "New database" > "Create new database". You'll be presented with a page to name your database, select a region, and choose a plan.

Give your database a name and select the region closest to you or your application. For this tutorial, we will name the database express_crm_db and use the default region. Select your desired plan, then click “Create database” to complete the creation process. You will be redirected to the database dashboard as shown below.

Next, you need the database credentials. To get them, click the “Get connection strings” button on the right side of the dashboard. This shows you the database authentication credentials as well as a sample code for connecting to your database for different programming languages.

Create a database via CLI

To create a new database via the CLI, use the following command.

pscale db create express_crm_db

Next, set up the credentials via the CLI using the following command.

pscale password create express_crm_db main express-app

A table with the following values will be displayed once the process is completed.

NAME  BRANCH   ID   USERNAME   ACCESS HOST URL   ROLE  ROLE DESCRIPTION  PASSWORD

Make a note of these values as they are only shown once. In particular, you will require USERNAME, PASSWORD, and ACCESS HOST URL.

Copy the database credentials and keep them handy as you will need them when you start building the application. For security, these details are only displayed once and if you lose your credentials, you will need to generate a new set.

Now we have everything we need to build and connect our application to our database.

Set up application

To get started, create a new project folder and navigate into it. You can do it with the following command.

mkdir express_crm && cd $_

Next, issue the following command to initialize the project, generate and populate package.json file

npm init -y

Also, use the following command to add the dev dependencies to the project.

npm install concurrently nodemon prisma @prisma/client typescript ts-node @types/node --save-dev

Now initialize TypeScript using the following command.

npx tsc --init

A new file named tsconfig.json will be created containing the default TypeScript configuration. Open it and modify the code to match the following.

{
  "compilerOptions": {
    "target": "es2016",
    "module": "commonjs",
    "esModuleInterop": true,
    "forceConsistentCasingInFileNames": true,
    "rootDir": "./",
    "outDir": "./dist"
  }
}

Next, add the following dev entry to the scripts object in package.json. This will simplify the process of building and running the application.

  "scripts": {
    "dev": "concurrently \"npx tsc --watch\" \"nodemon -q dist/app.js\""
  }

With this script, you can run npm run dev start your application and watch your .ts files. When changes are detected, the project will automatically be rebuilt and restarted with the latest version of your code.

Connect the application to PlanetScale

Prisma will be used to handle interactions with the PlanetScale database. To get started, run the following command.

npx prisma init

This command does two things. First, it creates a .env file that will hold environment variables for the application such as the database URL. It also creates a schema file named schema.prisma in a directory named prisma. This file contains everything database-related (including the database models). Open it and update it to match the following.

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


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


model Client {
  id             Int             @id @default(autoincrement())
  name           String          @db.VarChar(255)
  isCorporate    Boolean         @default(false)
  yearFounded    String          @db.VarChar(255)
  primaryContact PrimaryContact?
}


model PrimaryContact {
  id           Int    @id @default(autoincrement())
  name         String @db.VarChar(255)
  phoneNumber  String @db.VarChar(255)
  emailAddress String @db.VarChar(255)
  client       Client @relation(fields: [clientId], references: [id], onDelete: Cascade)
  clientId     Int    @unique
}

This schema declares two models: Client and PrimaryContact. Both models have a one-to-one relationship.

The datasource is also declared. The provider is set to mysql since PlanetScale is based on MySQL. The DATABASE_URL is retrieved from the .env file which we will update shortly. The last line specifies prisma as the relation mode. This prevents Prisma from adding foreign key constraints, which are unsupported by PlanetScale.

Next, open the newly created .env file and add the following.

DATABASE_URL='mysql://<USERNAME>:<PASSWORD>@<ACCESS_HOST_URL>/express_crm_db?sslcert=/etc/ssl/cert.pem'
APP_PORT=8000

The APP_PORT variable will be used later in the application to listen for, and handle requests.

The DATABASE_URL is the environment variable used by Prisma for connecting to the database. An sslCert query is added to ensure that the connection is SSL secured.

With these changes in place, update your database schema using the following command.

npx prisma db push

Additionally, this command reads your Prisma schema and generates a version of Prisma Client that is tailored to the models you declared earlier.

Set up Express

Next install run the following command to install the necessary packages to enable this project as a web application:

npm install dotenv express express-validator pug

You also need to add @types/express as a dev dependency:

npm install @types/express --save-dev

Next, create a new folder named views at the root of the project folder. This folder will hold all the Pug templates to be rendered by the application.

Create a new file named base.pug in the views directory. This is the base template and will have blocks which will contain content specific to pages that utilize the template. The base template will contain the aspects of the application that is common across all pages like includes styling, head content, and scripts. Add the following to views/base.pug.

block variables
doctype html
html
  head
    meta(charset="utf-8")
    link(rel="shortcut icon", href="https://planetscale.com/favicon.svg")
    link(rel="shortcut icon", href="https://planetscale.com/favicon.ico")
    meta(
      name="viewport",
      content="width=device-width, initial-scale=1, shrink-to-fit=no"
    )
    meta(name="theme-color", content="#000000")
    title #{ title } | PlanetScale CRM
    link(
      rel="stylesheet",
      href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css",
      integrity="sha384-EVSTQN3/azprG1Anm3QDgpJLIm9Nao0Yz1ztcQTwFspd3yD65VohhpuuCOmLASjC",
      crossorigin="anonymous"
    )
    link(
      rel="stylesheet",
      href="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.10.5/font/bootstrap-icons.css"
    )


  body
    #root(style="margin: 2%")
      nav.navbar.navbar-expand-sm.navbar-light.bg-light
        .container-fluid
          a.navbar-brand(href="/clients") Express CRM
          button.navbar-toggler(
            type="button",
            data-bs-toggle="collapse",
            data-bs-target="#navbarCollapse"
          )
            span.navbar-toggler-icon
          #navbarCollapse.collapse.navbar-collapse
            ul.nav.navbar-nav.ms-auto
              li.nav-item.dropdown
                a.nav-link.dropdown-toggle(href="#", data-bs-toggle="dropdown") Clients
                .dropdown-menu.dropdown-menu-end
                  a.dropdown-item(href="/clients") View All
                  a.dropdown-item(href="/client/create") Add New


      block layout-content
    script(
      src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js",
      integrity="sha384-MrcW6ZMFYlzcLA8Nl+NtUVF0sA7MsXsP1UyJoMp4YLEuNSfAP+JcXn/tWtIaxVXM",
      crossorigin="anonymous"
    )

Next, create a new file named index.pug in the views folder to be the index page of the application. Add the following to it:

extends base


block layout-content
  h1= message

Next, create an entry point for your application. At the root of the project folder, create a new file named app.ts and add the following code to it:

import express from 'express'
import * as dotenv from 'dotenv'


dotenv.config()


// Create a new Express app
const app = express()


app.set('views', './views')
app.set('view engine', 'pug')


// declare an index route
app.get('/', (req, res) => {
  res.render('index', { title: 'Hey', message: 'Hello, World ✌️' })
})


const { APP_PORT: port } = process.env || 8000


// Listen for request on specified ports
app.listen(port, () => console.log(`Server started on port ${port}`))

What you have done so far is:

1. Load environment variables using dotenv
2. Create a new Express app
3. Set the directory containing the application views
4. Set the application’s view engine
5. Declare an index route which will render the index.pug template you created earlier.
6. Listen for requests - either on the port specified in .env or port 8000

Start the application using the following command.

npm run dev

Open the application in your browser to see your index page.

Form validation

The next thing you’ll handle is validation. To add new clients, the user will fill out a form. However, it is important to ensure that the user isn’t filling in incorrect (or even dangerous) data. To do this, the submitted form must be validated before saving the data to the database. You’ve already added express-validator to your project, with that, you can create a middleware function to validate the user input and return errors if any.

To do this, create a new folder named utility at the root of the project folder. In this folder, create a new file named validator.ts and add the following code to it.

import { body } from 'express-validator'


export const clientInputCheck = () => [
  body('clientName').trim().not().isEmpty().withMessage("Please provide the client's name"),
  body('isCorporate').trim().isIn(['on', '']),
  body('yearFounded')
    .trim()
    .not()
    .isEmpty()
    .withMessage('Year founded cannot be empty')
    .isInt()
    .withMessage('Please enter a valid year'),
  body('contactName').trim().not().isEmpty().withMessage("Please provide the primary contact's name"),
  body('contactPhoneNumber')
    .trim()
    .not()
    .isEmpty()
    .withMessage("Please provide the primary contact's phone number")
    .isLength({
      min: 11,
      max: 11
    })
    .withMessage('Please provide a valid phone number with not less than or more than 11 digits'),
  body('contactEmailAddress')
    .trim()
    .not()
    .isEmpty()
    .withMessage("Please provide the primary contact's email address")
    .isEmail()
    .withMessage('Please enter a valid email address')
]


export interface ErrorResponse {
  type: string
  value: string
  msg: string
  path: string
  location: string
}


export const formatErrorResponse = (input: ErrorResponse[]) => {
  return input.reduce((accumulator, currentItem) => {
    accumulator[currentItem.path] = currentItem.msg
    return accumulator
  }, {})
}

The clientInputCheck function returns an array of validation rules built using the body function - one for each field in the client form.

Next, an interface named ErrorResponse is declared. This matches the content of the error object generated by express-validator if the user input for a field violates the corresponding rule.

Finally, a function named formatErrorResponse is declared which takes an array of ErrorResponse objects and reduces them to a single object. This object is used by the Pug template to provide feedback to the user as to the nature of the invalid input.

Controller functions

Next, write the functions that will handle client-related requests. For this tutorial, you will have four such functions:

1. An index function that renders all the clients
2. A create function that renders the form for adding a new client
3. A store function that handles the submitted form, and saves the new client if the submitted form is valid. If the form is invalid, then the form is re-rendered with appropriate error messages displayed. For this article, all form validations will be handled server-side.
4. A destroy function that deletes a client from the database.

Create a new folder named controllers at the root of the project folder and in it a new file named client.ts. Add the following code to controllers/client.ts.

import { validationResult } from 'express-validator'
import { ErrorResponse, formatErrorResponse } from '../utility/validator'
import { PrismaClient } from '@prisma/client'


const prisma = new PrismaClient()


export const index = async (req, res) => {
  const clients = await prisma.client.findMany({
    include: { primaryContact: true }
  })
  res.render('clients/index', { title: 'All Clients', clients })
}


export const create = (req, res) => {
  res.render('clients/create', { title: 'New Client' })
}


export const store = async (req, res) => {
  let errors = validationResult(req)
  if (!errors.isEmpty()) {
    const errorResponse = formatErrorResponse(errors.array() as ErrorResponse[])
    res.render('clients/create', {
      title: 'New Client',
      errors: errorResponse,
      values: req.body
    })
  } else {
    const {
      clientName: name,
      isCorporate,
      yearFounded,
      contactName,
      contactPhoneNumber,
      contactEmailAddress
    } = req.body


    await prisma.client.create({
      data: {
        name,
        isCorporate: isCorporate === 'on',
        yearFounded,
        primaryContact: {
          create: {
            name: contactName,
            phoneNumber: contactPhoneNumber,
            emailAddress: contactEmailAddress
          }
        }
      }
    })


    res.redirect('/clients')
  }
}


export const destroy = async (req, res) => {
  const { id } = req.params
  await prisma.client.delete({
    where: {
      id: +id
    }
  })
  res.redirect('/clients')
}

Using Prisma, the index function retrieves all the clients in the PlanetScale database and passes it as a template variable to the Pug template.

The store function checks for errors in the request using the validationResult function provided by express-validator. If there are any errors, they are formatted using the formatErrorResponse function declared earlier and passed as a variable to the template responsible for rendering the client form. The submitted values are also returned - this saves the user the stress of having to re-type everything. If there are no errors, then a new client is created via the Prisma create function, and the user is redirected to the list of all clients.

The destroy function retrieves the id from the request parameters and makes the appropriate Prisma delete call.

Add views for client pages

Next, you'll add two views for the client pages: one to show all the clients, and another to show the Add Client form. In the views folder, create a new folder named clients. In the clients folder, create two new files named create.pug and index.pug.

Open views/clients/index.pug and add the following code to it.

extends ../base


block layout-content
  .container(style="margin-top: 5%")
    h1 All Clients
    table.table.table-bordered.table-striped.table-hover
      thead
        tr
          th.text-center(scope="col")
          th.text-center(scope="col", colspan=2) Client
          th.text-center(scope="col", colspan=3) Primary Contact
          th.text-center(scope="col")


        tr
          th.text-center(scope="col") #
          th.text-center(scope="col") Name
          th.text-center(scope="col") Year Founded
          th.text-center(scope="col") Name
          th.text-center(scope="col") Phone Number
          th.text-center(scope="col") Email Address
          th.text-center(scope="col") Actions
      tbody
        each client, index in clients
          tr
            td.text-center= index + 1
            td.text-center= client.name
            td.text-center= client.yearFounded
            td.text-center= client.primaryContact.name
            td.text-center= client.primaryContact.phoneNumber
            td.text-center= client.primaryContact.emailAddress
            td.text-center(style="vertical-align: top; padding: 0px")
              form(method="POST", action="client/delete/" + client.id)
                button.btn
                  i.bi.bi-trash3.text-danger(style="font-size: 1.3rem")

This template takes the list of clients and renders them in a table - one row for each client. Additionally, it adds a button on each row which the user can click to delete the client.

Next, add the following code to views/clients/create.pug.

extends ../base


block layout-content
  .container(style="width: 80%; margin: auto; margin-top: 5%")
    h1 Add New Client
    form.row.g-3.needs-validation(
      action="/client",
      method="POST",
      novalidate=""
    )
      .col-md-8
        label.form-label(for="clientName") Client Name
        input#clientName.form-control(
          type="text",
          placeholder="Client Name",
          name="clientName",
          class=errors && errors.clientName ? "is-invalid" : "",
          value=values ? values.clientName : ""
        )
        if errors
          .invalid-feedback= errors.clientName
      .col-md-4
        label.form-label(for="yearFounded") Year Founded
        input#yearFounded.form-control(
          type="text",
          placeholder="Year founded",
          name="yearFounded",
          class=errors && errors.yearFounded ? "is-invalid" : "",
          value=values ? values.yearFounded : ""
        )
        if errors
          .invalid-feedback= errors.yearFounded


      .col-md-4.form-check
        input#isCorporate.form-check-input(
          type="checkbox",
          name="isCorporate",
          checked=values ? values.isCorporate == "on" : false
        )
        label.form-check-label(for="isCorporate") Client is corporate


      h3.my-3 Primary Contact
      .col-md-4
        label.form-label(for="contactName") Contact Name
        input#contactName.form-control(
          type="text",
          placeholder="Contact Name",
          name="contactName",
          class=errors && errors.contactName ? "is-invalid" : "",
          value=values ? values.contactName : ""
        )
        if errors
          .invalid-feedback= errors.contactName


      .col-md-4
        label.form-label(for="contactPhoneNumber") Phone Number
        input#contactPhoneNumber.form-control(
          type="text",
          placeholder="Phone Number",
          name="contactPhoneNumber",
          class=errors && errors.contactPhoneNumber ? "is-invalid" : "",
          value=values ? values.contactPhoneNumber : ""
        )
        if errors
          .invalid-feedback= errors.contactPhoneNumber


      .col-md-4
        label.form-label(for="contactEmailAddress") Email Address
        input#contactEmailAddress.form-control(
          type="email",
          placeholder="Email Address",
          name="contactEmailAddress",
          class=errors && errors.contactEmailAddress ? "is-invalid" : "",
          value=values ? values.contactEmailAddress : ""
        )
        if errors
          .invalid-feedback= errors.contactEmailAddress
      .col-12
        button.btn.btn-primary(type="submit") Submit

This template renders the form for adding a new client. In addition to declaring the fields of the form, the template checks if the errors variable is set, and styles each field in the form accordingly in the event that there are errors. It also checks if the values variable is present and adds each value to its proper field in the form if it is.

Update app.ts

With everything in place, all that is left to do is update the entry point of the application - app.ts to include the new routes for the controller functions, as well as the appropriate middleware to parse the body of the request. Open app.ts and update the code to match the following.

import express from 'express'
import * as dotenv from 'dotenv'
import { create, destroy, index, store } from './controllers/client'
import bodyParser from 'body-parser'
import { clientInputCheck } from './utility/validator'


dotenv.config()


const app = express()


app.use(bodyParser.urlencoded({ extended: true }))


app.set('views', './views')
app.set('view engine', 'pug')


app.get('/', (req, res) => {
  res.render('index', { title: 'Hey', message: 'Hello, World ✌️' })
})


app.get('/clients', index)
app.get('/client/create', create)
app.post('/client', clientInputCheck(), store)
app.post('/client/delete/:id', destroy)


const { APP_PORT: port } = process.env || 8000


app.listen(port, () => console.log(`Server started on port ${port}`))

If your application is still running, TypeScript will recompile your files to Javascript and nodemon will restart the application. If not, start the application using the following command.

npm run dev

Open your browser and navigate to http://127.0.0.1:8000 to open the application. From the home page, select the Clients dropdown, then select the Add New option. You will see the form for adding a new client as shown below.

Click on the "Submit" to see all the error messages as shown below.

Fill in the required details, click Submit, and you will be redirected to the list of clients as shown below.

You can test the delete action by clicking the red bin icon.

Conclusion

In this article, you learned how to build an SSR-enabled Node.js application using Express, Pug, and MySQL. Additionally, you learned how to set up your PlanetScale database via the web application as well as the CLI. As mentioned earlier, it’s a matter of preference. The web application is intuitive and makes it an absolute breeze to manage your database. However, if you love typing and have no problem remembering CLI commands, you’ll find that you can wave your PlanetScale wand as fast as your fingers and mind will allow.

The entire codebase is available on GitHub. Happy Coding!