Using MySQL with Deno

Kasra Madadipouyaby Kasra Madadipouya
Using MySQL with Deno

In the penultimate Deno article, I wrote on how to build REST APIs with Deno and Oak. In that tutorial, we used an array as a dummy storage placeholder to not overwhelm ourselves with database connection and other stuff. For this article, we are going to take that example one more step further and connect it to a real database. We will be using MySQL with Deno and replace that dummy storage.

A word on MySQL driver shortage in Deno

For this article, I did a fair amount of googling to find a good MySQL driver. The bitter reality is at the moment there are not many options to choose from. The only existing MySQL driver is deno_mysql created by EnokMan (AKA manyuanrong). Even that driver is not complete and not working with all MySQL versions (more on that later).

For using MySQL with Deno, we stick to the deno_mysql driver as of now. Keep in mind that you may see some strange behaviors from this driver as it still is a work in progress. If you find a bug, once you’ve made sure obviously, open a ticket and provide all the necessary details.

Now without further ado, let’s talk about the implementation.

We are going to create a service called storage.js to connect to MySQL and act as a persistence layer. This way we make sure that the database code doesn’t leak into the other part of code. Additionally, we will create a configuration file, config.js to be exact, to externalize any database and application configurations such as HTTP port, database credentials, and so on.

Implementing configuration

It’s easier to start with config.js. All we need to do is to read a bunch of environment variables and make them available through config.js. We rely on the native Deno API to read environment variables. If you are unfamiliar with it, check the How to read environment variables in Deno article.

Let’s now create config.js file to read the following environment variables,

  • WEB_PORT: HTTP port, default 8080
  • DATABASE_HOST: Hostname of the MySQL, default localhost
  • DATABASE_USERNAME: MySQL username, default root
  • DATABASE_PASSWORD: MySQL password, default secret
  • DATABASE_NAME: The database name, default user_db

Implementation:

let config = {};

config.database = {};

config.web = {};

config.database.host = Deno.env.get("DATABASE_HOST") || "localhost";
config.database.username = Deno.env.get("DATABASE_USERNAME") || "root";
config.database.password = Deno.env.get("DATABASE_PASSWORD") || "secret";
config.database.name = Deno.env.get("DATABASE_NAME") || "user_db";
config.web.port = Deno.env.get("WEB_PORT") || 8080;

export default config;

As you can see, the code is very straightforward and there’s nothing to explain about it.

Implementing storage service with Deno MySQL

Now we have to create the persistence layer that connects to MySQL and performs different operations on it. Based on the available REST APIs, we need to create the following functions:

  • getUsers : lists all users
  • getUserById: gets a user by id
  • addUser: inserts a user
  • deleteUser: deletes a user
  • updateUser: updates an existing user

First, we create the service directory and add the storage.js file into it. Then we can add the MySQL configuration to it as follows:

import config from "../config.js";

const client = await new Client().connect({
    hostname: config.database.host,
    username: config.database.username,
    password: config.database.password,
    db: config.database.name,
    poolSize: 10
});

In the above code, we load the configuration from config.js file and pass them to the MySQL driver. The connection pool part is optional, I just added there for completeness sake.

Then we can implement the aforementioned functions,

import { Client } from "https://deno.land/x/mysql/mod.ts";
import config from "../config.js";

const client = await new Client().connect({
    hostname: config.database.host,
    username: config.database.username,
    password: config.database.password,
    db: config.database.name,
    poolSize: 10
});

const getUsers = async () => {
    return await client.query("SELECT id, first_name AS firstName, last_name as lastName, age FROM users");
}

const getUserById = async (id) => {
    return await client.query(`SELECT id, first_name AS firstName, last_name AS lastName, age ` +
        `FROM users WHERE id = ?`, [id]);
}

const addUser = async (firstName, lastName, age) => {
    const result = await client.execute(`INSERT INTO users(first_name, last_name, age) VALUES(?, ?, ?)`, [firstName, lastName, age]);
    return getUserById(result.lastInsertId);
}

const deleteUser = async (id) => {
    return (await client.execute("DELETE FROM users WHERE id = ?", [id])).affectedRows > 0 ? true : false;
}

const updateUser = async (id, firstName, lastName, age) => {
    const result = await client.execute("UPDATE users SET first_name = ?, last_name = ?, age = ? WHERE id = ?", [firstName, lastName, age, id]);
    return getUserById(id);
}

export default {
    getUsers,
    getUserById,
    addUser,
    deleteUser,
    updateUser
};

There are a couple of things to note about the code,

  • The result of getUsers and getUserById functions are arrays. But in the case of getUserById only a single element should be in the array.
  • INSERT, UPDATE, and DELETE statements return the same result that is an object consisting of affectedRows and lastInsertId. In the case of INSERT both affectedRows and lastInsertId should be non-zero. For UPDATE and DELETE only affectedRows should be non-zero. If it’s zero means the query has failed.
  • To avoid confusion intentionally I offload all error handling and basic logic to other parts of the code and make this layer as thin as possible. This is for demonstration purposes only to help readers to understand how to use the library. Don’t use this pattern as an example to build applications with.

Glue everything together in the example

The last step is to go back to the index.js (the main application file), and replace that dummy array with the actual MySQL 🙂

The changes are easy and straightforward, though it should be applied in different places. That’s why I created a separate snippet for each API change.

Since the deno_mysql library returns all results as promises, we need to make the controllers async as well. Fortunately, that’s easy in Oak, we just need to add async keyword before the endpoint handler. See the first line of the following snippet.

GET /v1/users API,

router.get("/v1/users", async (context) => {
  context.response.body = await storage.getUsers();
})

GET /v1/users/:id API,

router.get("/v1/users/:id", async (context) => {
  if (context.params && context.params.id && !isNaN(parseInt(context.params.id))) {
    const user = (await storage.getUserById(context.params.id))[0];
    if (!user) {
      context.response.status = 404;
      context.response.body = { error: `User ${context.params.id} not found` };
    } else {
      context.response.body = user;
    }
  } else {
    context.response.status = 400;
    context.response.body = { error: "Id must be number" };
  }
})

Here we added an extra if condition to ensure the return result of storage.getByUserId is not empty. If it’s we return 404.

POST /v1/users API,

router.post("/v1/users", async (context) => {
  if (!context.request.hasBody) {
    context.response.status = 400;
    context.response.body = { error: "Request body cannot be empty" };
  } else {
    const { firstName, lastName, age } = (await context.request.body(true)).value
    const ageInt = parseInt(age);
    if (firstName && lastName && ageInt) {
      let addedUser = (await storage.addUser(firstName, lastName, ageInt))[0];
      context.response.status = 201;
      context.response.body = addedUser;
    } else {
      context.response.status = 400;
      context.response.body = { error: "Invalid payload's provided" };
    }
  }
})

PUT /v1/users/:id API,

router.put("/v1/users/:id", async (context) => {
  if (!context.params || !context.params.id ||
    isNaN(parseInt(context.params.id) || !context.request.hasBody)) {
    context.response.status = 400;
    context.response.body = { error: "Invalid request" };
  } else {
    const result = await storage.getUserById(context.params.id);
    if (result.length < 1) {
      context.response.status = 404;
      context.response.body = { error: `User ${context.params.id} not found` };
    } else {
      const id = context.params.id;
      const { firstName, lastName, age } = (await context.request.body()).value
      const ageInt = parseInt(age);
      if (firstName && lastName && ageInt) {
        context.response.status = 200;
        context.response.body = (await storage.updateUser(id, firstName, lastName, ageInt))[0];
      } else {
        context.response.status = 400;
        context.response.body = { error: "Invalid payload's provided" };
      }
    }
  }
})

DELETE /v1/users/:id API,

delete("/v1/users/:id", async (context) => {
  if (context.params && context.params.id && !isNaN(parseInt(context.params.id))) {
    let isDeleted = await storage.deleteUser(context.params.id);
    if (isDeleted) {
      context.response.status = 204;
    } else {
      context.response.status = 404;
      context.response.body = { error: `User ${context.params.id} not found` };
    }
  } else {
    context.response.status = 400;
    context.response.body = { error: "Id must be number" };
  }
})

Again the same thing. We check whether the user is deleted successfully or not. It’s done by reading affectedRows. If it’s zero deletedUser returns false which as the result, the API returns 404, 204 otherwise.

Now to run the code all you need is to have a running MySQL, fill the details in config.js if needed, and run the project as follow,

$ deno run --allow-env --allow-read --allow-net index.js

Troubleshooting

If you get the following error:

error: Uncaught Error: Not supported
â–º auth.ts:43:13
throw new Error("Not supported");

First, check your MySQL version. If it is +5.7 then you have two options:

  • Downgrade your MySQL to 5.7.x
  • Set empty password for authentication

The reason for the error is because the MySQL 8 authentication has not implemented in the driver yet, see issue #16 and #37 on GitHub.

Complete working example

You can the final code on my GitHub page at the link below,
https://github.com/kasramp/deno-oak-rest

The code comes with MySQL 5.7 Docker file (see docker directory) and some dummy records. To get it up and running follow instructions on the README file.

Inline/featured images credits

You might also like

How to read environment variables in Deno

How to read environment variables in Deno

Generate and validate JWT in Deno

Generate and validate JWT in Deno

Is Node.js dying?

Is Node.js dying?