Deno + Oak connect to MySQL

How to Use MySQL With Deno and Oak

Original post by Adeel Imran

Originally published: 2020-06-07

Translator: hylerrix

Note: this article follows freeCodeCamp translation specification, and will be included in the translation of “The Art of Deno Research”.

Note: “The art of Deno research” ebook official website is online! deno-tutorial.js.org

Note: Recently started preparing the original JavaScript modular from zero to Deno module mechanism series & conceived in Oak open source applications…

I recently wrote an article about Deno + Oak building a cool Todo API that didn’t involve using databases. You can check out my Github repository adeelibr/ Deno-Playground’s chapter_1: Oak for the entire code at the time.

Translator’s note: The translated version of Deno + Oak Building a Cool Todo API is here, and the Demo is also available in the ebook repository.

This article will take you further and learn how to integrate MySQL into our Deno + Oak project.

If you want to see the full code for this article at any time, you can find it in chapter_2:mysql.

I’m going to assume that you read the previous article, but if not, you can read it here and come back to it.

Before we begin, please make sure that you have a MySQL client installed and that you can run it successfully:

MySQL Community Server
MySQL Workbench [download here]

I also wrote a short tutorial for MacOS users on how to install MySQL.

If you are running on Windows, you can use the same tools as above, or use XAMPP to quickly run MySQL instances on your machine.

Once you’ve got MySQL up and running, we’re ready to explore this article.

Let’s get started

Assuming you’ve read the last article, we’ll write the following functionality:

Create a connection to the MySQL database.
Write a small script that will reset the database every time we restart the Deno server;
Perform CRUD operations on a data table;
Connect the CURD operation to our API controller.

One last thing before we get started: The details of Git changes since I added the MySQL version to the previous article can be found here.

In your project root directory (mine is called chapter_2:mysql, yours is free), create a db folder and create a config.ts in it and add the following:

export const DATABASE: string = "deno";
export const TABLE = {
  TODO: "todo"};Copy the code

There’s nothing new here, just the name of the database we defined and a TABLE object. With this export, our project will have a database named “denO” with a data table named “Todo” in it.

Next, create another file named client.ts in the DB folder and fill it with the following contents:

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

const client = await new Client();
client.connect({
  hostname: "127.0.0.1",
  username: "root",
  password: "",
  db: ""});Copy the code

This code contains several functions.

We deconstructed the Client variable from a third party mysql module in Deno. This variable can be used to connect to the database and perform specified add, delete, change and query tasks.

client.connect({
  hostname: "127.0.0.1",
  username: "root",
  password: "",
  db: ""});Copy the code

Client has a built-in connect method, which is used to set the value of hostname, USERNAME, password and DB fields, to set the connection configuration with MySQL.

Please make sure your username does not have a password set, because the current Deno MySQL module cannot connect to users with passwords. If you don’t know how to clear user passwords, you can read here.

I leave the Database field blank here because I want to manually select it later in the script.

Let’s add a script to initialize a database with the alias “deno” and create a table called “todo” for it.

In the db/client.ts file we add the following:

import { Client } from "https://deno.land/x/mysql/mod.ts";
// Load the configuration in the configuration file
import { DATABASE, TABLE } from "./config.ts";
const client = await new Client();
client.connect({
  hostname: "127.0.0.1",
  username: "root",
  password: "",
  db: ""});const run = async() = > {// Create a database (if it has not been created before)
  await client.execute(CREATE DATABASE IF NOT EXISTS ${DATABASE});
  // Select our database
  await client.execute(USE ${DATABASE});
  // If a table named Todo has already been created, delete it
  await client.execute(DROP TABLE IF EXISTS ${TABLE.TODO});
  // Create Todo table
  await client.execute(CREATE TABLE ${TABLE.TODO} (
        id int(11) NOT NULL AUTO_INCREMENT,
        todo varchar(100) NOT NULL,
        isCompleted boolean NOT NULL default false, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;) ; }; run();export default client;
Copy the code

Here we import DATABASE and TABLE from our earliest configuration files and create the associated databases and tables using the run() method.

Let’s take a snippet of code related to the run() method. I’ve written comments in the code to help you understand.

const run = async() = > {// Create a database (if it has not been created before)
  await client.execute(CREATE DATABASE IF NOT EXISTS ${DATABASE});
  // Select our database
  await client.execute(USE ${DATABASE});
  // If a table named Todo has already been created, delete it
  await client.execute(DROP TABLE IF EXISTS ${TABLE.TODO});
  // Create Todo table
  await client.execute(CREATE TABLE ${TABLE.TODO} (
        id int(11) NOT NULL AUTO_INCREMENT,
        todo varchar(100) NOT NULL,
        isCompleted boolean NOT NULL default false, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;) ; }; run();Copy the code

Create a file nameddenoIf the database already exists, skip this step.
Select our current namedenoThe database of.
indenoDatabase, if nametodoIf the table exists, delete it.
Next, in thedenoCreate a new one in the databasetodoTable, and define its table structure: the table structure will contain a unique, self-growing, numericidField; Will also contain a name namedtodoString field of; It also contains a file namedisCompletedBoolean field of; The final will beidField definition primary key.

The reason I wrote this code is because I don’t want to have information in a MySQL instance that isn’t intuitively visible in the code. With this code, it reinitializes everything every time you restart the server.

You don’t have to write this code. But if you don’t, you have to create the database and tables manually.

You can also check db Creation and table Creation documentation for Deno MySQL module.

To get back to the gist of the article, we’ve already accomplished two of the four goals mentioned above:

Create a connection to the MySQL database.
Write a small script that will reset the database every time we restart the Deno server.

This means that 50% of the content of this article is covered. Unfortunately, you can’t test any data manipulation functionality yet. Let’s quickly add a few CRUD features to see how it works.

Perform CRUD operations on the data table and add functionality to the API controller

We need to write the Todo interface first. Create the interfaces/ todo.ts file and add the following:

export default interfaceTodo { id? :number, todo? :string, isCompleted? :boolean,}Copy the code

In the code? The symbol indicates that the key is optional. And the reason we’re doing that is because we’re going to have places where we only need a few of these keys.

If you want to learn more about optional properties in TypeScript, check them out here.

Next, create a folder called Models in the root directory and create a file in it called todo.ts and add the following:

import client from ".. /db/client.ts";
// Load the configuration file
import { TABLE } from ".. /db/config.ts";
// Load the interface file
import Todo from ".. /interfaces/Todo.ts";

export default {
  * @param id * @returns Returns a Boolean value representing the presence of the corresponding toDO element in the table
  doesExistById: async ({ id }: Todo) => {},
  /** * returns all the contents of the toDO table * @returns an array full of TODO elements */
  getAll: async() = > {},/** * returns the corresponding toDO element * @param id * @returns returns a todo element */
  getById: async ({ id }: Todo) => {},
  /** * Add a new todo element to toDO * @param todo * @param isCompleted */
  add: async (
    { todo, isCompleted }: Todo,
  ) => {},
  /** * Modify the contents of a todo element * @param ID * @param todo * @param isCompleted * @returns Returns a number representing the number of rows affected */
  updateById: async ({ id, todo, isCompleted }: Todo) => {},
  /** * Removes the specified element by ID * @param ID * @returns INTEGER (count of effect rows) */
  deleteById: async ({ id }: Todo) => {},
};
Copy the code

At this point, every function is empty, so don’t worry, we’ll fill it in.

Next create the controllers/todo.ts file and make sure to fill it with the following:

// Load the interface file
import Todo from ".. /interfaces/Todo.ts";
// Load the model action file
import TodoModel from ".. /models/todo.ts";

export default {
  @route GET /todos */
  getAllTodos: async ({ response }: { response: any}) = > {},/** * @description adds a todo element * @route POST /todos */
  createTodo: async (
    { request, response }: { request: any; response: any },
  ) => {},
  /** * @description gets the specified todo element * @route GET todos/:id */
  getTodoById: async (
    { params, response }: { params: { id: string }; response: any },
  ) => {},
  /** * @description updates the specified todo element by id * @route PUT todos/:id */
  updateTodoById: async (
    { params, request, response }: {
      params: { id: string };
      request: any;
      response: any;
    },
  ) => {},
  /** * @description removes the specified todo element * @route DELETE todos/:id */
  deleteTodoById: async (
    { params, response }: { params: { id: string }; response: any},) => {},};Copy the code

This file is currently empty, so start filling it in.

[Get] Gets all Todos apis

Add concrete logic to the getAll method in the models/todo.ts file:

import client from ".. /db/client.ts";
// config
import { TABLE } from ".. /db/config.ts";
// Interface
import Todo from ".. /interfaces/Todo.ts";

export default {
   /** * returns all toDO tables * @returns array of todos */
  getAll: async() = > {return await client.query(`SELECT * FROM ${TABLE.TODO}`); }},Copy the code

We use SQL native syntax directly to get everything in the table.

In addition to the connect method (used in the DB /client.ts file), the client exposes another method, query. With the client.query method, we can run MySQL queries directly from Deno code.

Next open the controllers/todo.ts file and populate getAllTodos:

// interfaces
import Todo from ".. /interfaces/Todo.ts";
// models
import TodoModel from ".. /models/todo.ts";

export default {
  /** * @description GET all todo * @route GET /todos */
  getAllTodos: async ({ response }: { response: any= > {})try {
      const data = await TodoModel.getAll();
      response.status = 200;
      response.body = {
        success: true,
        data,
      };
    } catch (error) {
      response.status = 400;
      response.body = {
        success: false,
        message: `Error: ${error}`}; }}},Copy the code

All we do here is import the TodoModel object and use the getAll method we just defined in it. Since we need this function to handle asynchronous procedures of type Promise, we define the entire function as async/await.

After the todomodel.getall () method returns an array, we wrap the array and set the response status of Response.body to 200.

If an exception such as a Promise is reported during execution, the program returns a response body with a status code of 400 to the user by entering a catch block (success is false and message is the cause of the error).

That’s it. Now let’s run it on the terminal.

Please make sure your MySQL instance is running, then enter:

$ deno run --allow-net server.ts 
Copy the code

Otherwise, your terminal should have something like this:

This is what the terminal looks like when I run the server from the command line.

The terminal tells us two things:

Deno API server runs successfully on port 8080.
The Deno API server successfully connected to the MySQL client127.0.0.1:3306 （http://localhost:3306).

Let’s test our API. I’m using Postman, but you can test any API you like.

After executing [GET] localhost:8080/todos, we GET a list of all todos.

Although the toDOS list now returns an empty array, we will get more data as we successfully add data to the ToDO table.

That’s great. One API and we only have four left.

[Post] Added a Todo API

In the models/todo.ts file, add the following to the add() function:

export default {
   /** * Add a row to toDO * @param todo * @param isCompleted */
  add: async (
    { todo, isCompleted }: Todo,
  ) => {
    return await client.query(
      `INSERT INTO ${TABLE.TODO}(todo, isCompleted) values(? ,?) `, [ todo, isCompleted, ], ); }},Copy the code

The add function deconstructs the todo and isCompleted variables in the argument list.

Meanwhile, add: async ({todo, isCompleted}: todo) => {} fragment and ({todo, isCompleted}: {todo:string, isCompleted: Boolean}) statements are equivalent. But we’ve defined the Todo interface in interfaces/ todo.ts:

export default interfaceTodo { id? :number, todo? :string, isCompleted? :boolean,}Copy the code

At this point we will simply write add: async ({todo, isCompleted}: todo) => {}. This statement tells TypeScript that the current function takes two arguments: todo, which is a string, and isCompleted, which is a Boolean.

If you want to learn more about interfaces, check out the TypeScript documentation for a great introduction.

There is also the following code in the add function:

return await client.query(
  `INSERT INTO ${TABLE.TODO}(todo, isCompleted) values(? ,?) `,
  [
    todo,
    isCompleted,
  ],
);
Copy the code

This is a MySQL query that can be split into two parts:

INSERT INTO ${TABLE.TODO}(todo, isCompleted) values(?, ?). The two question marks indicate that the value of the variable needs to be used.
The other part[todo, isCompleted]Is the variable used in the previous section and its value will be replaced(?, ?).
Table.TodoIs a fromdb/config.tsThe string read in with the value”todo“.

Next, in our controllers/todo.ts file, write the createTodo() function:

export default {
   /** * @description add todo * @route POST /todos */
  createTodo: async (
    { request, response }: { request: any; response: any= > {},)const body = await request.body();
    if(! request.hasBody) { response.status =400;
      response.body = {
        success: false,
        message: "No data provided"};return;
    }

    try {
      await TodoModel.add(
        { todo: body.value.todo, isCompleted: false}); response.body = { success:true,
        message: "The record was added successfully"}; }catch (error) {
      response.status = 400;
      response.body = {
        success: false,
        message: `Error: ${error}`}; }}},Copy the code

We continue to break it down into two parts:

The first part

const body = await request.body();
if(! request.hasBody) { response.status =400;
  response.body = {
    success: false,
    message: "No data provided"};return;
}
Copy the code

What we’re doing here is checking to see if the request data is passed in the body when the user requests the current interface. If not, a response body with a 400 status code including SUCCESS: false and message:

is returned.

The second part

try {
  await TodoModel.add(
    { todo: body.value.todo, isCompleted: false}); response.body = { success:true,
    message: "The record was added successfully"}; }catch (error) {
  response.status = 400;
  response.body = {
    success: false,
    message: `Error: ${error}`}; }Copy the code

Next, if there are no unexpected errors, the todomodel.add () function is called and a response body with a status of 200 is returned indicating to the user that the function was executed successfully.

Otherwise, you enter a catch section that returns a function execution error and its cause, just like the API described earlier.

Now we’re done. Open your terminal and make sure your MySQL is running. Enter:

$ deno run --allow-net server.ts
Copy the code

Open Postman and test if the current API works properly:

Executing [POST] localhost:8080/todos => will add a new data to the todo list.

[GET] localhost:8080/todos => will return all toDos and you can see that the newly added toDO has been added to the database.

Great, we’ve got two apis, only three to go.

[GET] Queries the API of a Todo by ID

In your models/todo.ts file, populate the doesExistById() and getById() functions with their contents:

export default {
   /** * Takes in the id params & checks if the todo item exists * in the database * @param id * @returns boolean to tell if an entry of todo exits in table */
  doesExistById: async ({ id }: Todo) => {
    const [result] = await client.query(
      `SELECT COUNT(*) count FROM ${TABLE.TODO} WHERE id = ? LIMIT 1`,
      [id],
    );
    return result.count > 0;
  },
  /** * return todo * against it. * @param id * @returns object of todo */
  getById: async ({ id }: Todo) => {
    return await client.query(
      `SELECT * FROM ${TABLE.TODO}WHERE id = ? `, [id], ); }},Copy the code

Let’s take a look at the two functions one by one:

doesExistByIdFunctions are deconstructed from the argument listidVariable, and returns onebooleanBoolean value to indicate whether the unique TOdo you want to test exists in the database.

const [result] = await client.query(
  `SELECT COUNT(*) count FROM ${TABLE.TODO} WHERE id = ? LIMIT 1`,
  [id],
);
return result.count > 0;
Copy the code

We check the existence of the specified todo by the count value. Returns true if its value is greater than 0, false otherwise.

getByIdThe function returns the corresponding data with the specified id:

return await client.query(
  `SELECT * FROM ${TABLE.TODO}WHERE id = ? `,
  [id],
);
Copy the code

The above line directly executes the MySQL statement to query the data by ID and return the result.

Next, open the controllers/todo.ts file and populate the getTodoById controller:

export default {
   @route GET todos/:id */
  getTodoById: async (
    { params, response }: { params: { id: string }; response: any= > {},)try {
      const isAvailable = await TodoModel.doesExistById(
        { id: Number(params.id) },
      );

      if(! isAvailable) { response.status =404;
        response.body = {
          success: false,
          message: "No todo found"};return;
      }

      const todo = await TodoModel.getById({ id: Number(params.id) });
      response.status = 200;
      response.body = {
        success: true,
        data: todo,
      };
    } catch (error) {
      response.status = 400;
      response.body = {
        success: false,
        message: `Error: ${error}`}; }}},Copy the code

This code can also be broken into two smaller pieces:

const isAvailable = await TodoModel.doesExistById(
  { id: Number(params.id) },
);

if(! isAvailable) { response.status =404;
  response.body = {
    success: false,
    message: "No todo found"};return;
}
Copy the code

First we check if the todo we are looking for exists in the database with the following code:

const isAvailable = await TodoModel.doesExistById(
  { id: Number(params.id) },
);
Copy the code

Here we need to convert params.id to the Number numeric class because the interface states that our ID key must be a Number. Next we pass the params.id converted to a value to the doesExistById method, which returns a Boolean value.

Then check the Boolean value, if false returns the response body containing the 404 status code as above:

if(! isAvailable) { response.status =404;
  response.body = {
    success: false,
    message: "No todo found"};return;
}
Copy the code

The second part is:

try {
  const todo: Todo = await TodoModel.getById({ id: Number(params.id) });
  response.status = 200;
  response.body = {
    success: true,
    data: todo,
  };
} catch (error) {
  response.status = 400;
  response.body = {
    success: false,
    message: `Error: ${error}`}; }Copy the code

This code is very similar to the previous one. We get the specified data from the database to the toDO variable and return the body of the response. If there are any errors during execution, the response body will contain the error message back to the user.

Now open your terminal and make sure your MySQL is running. Enter:

$ deno run --allow-net server.ts
Copy the code

Open Postman to test whether the current interface works properly.

Keep in mind that we reset the database every time we restart the server. If you don’t want this functionality, you can comment out the entire run method in the db/client.ts file.

Executing [POST] localhost:8080/todos => will add a new todo.

Executing [POST] localhost:8080/todos => will return all toDos.

Executing [GET] localhost:8080/todos/:id => will look up the specified TODO and return its contents.

Executing [GET] localhost:8080/todos/ => returns a response body containing the 404 status code and error information.

So far we have the following apis:

Get all todos
Create a new todo
Gets the specified Todo by ID

The only remaining APIS:

Updates the specified Todo by ID
Removes the specified Todo by ID

[PUT] Updates a Todo API by ID

Let’s start by creating the Models code for this API. Enter the models/todo.ts file and fill the updateById method with its contents:

** * Update a specified todo * @param ID * @param todo * @param isCompleted * @returns INTEGER (count of effect rows) */ updateById:  async ({ id, todo, isCompleted }: Todo) => { const result = await client.query( `UPDATE ${TABLE.TODO} SET todo=? , isCompleted=? WHERE id=? `, [ todo, isCompleted, id, ], ); // return count of rows updated return result.affectedRows; },Copy the code

The updateById method will deconstruct three variables from the parameter list: ID, TODO, and isCompleted.

We wrote a MySQL statement directly to execute the query:

const result = await client.query(
  `UPDATE ${TABLE.TODO}SET todo=? , isCompleted=? WHERE id=? `,
  [
    todo,
    isCompleted,
    id,
  ],
);
Copy the code

This code updates the value of the specified TODO’s isCompleted by id.

MySQL > alter table table rows affected by MySQL statement

// return count of rows updated
return result.affectedRows;
Copy the code

The row value can only be 0 or 1 and never exceed 1. Because the ids in our table are unique — there is no case for different ToDos sharing the same ID.

Next, open the controllers/todo.ts file and fill the updateTodoById method with its contents:

updateTodoById: async (
  { params, request, response }: {
    params: { id: string };
    request: any;
    response: any; = > {},)try {
    const isAvailable = await TodoModel.doesExistById(
      { id: Number(params.id) },
    );
    if(! isAvailable) { response.status =404;
      response.body = {
        success: false,
        message: "No todo found"};return;
    }

    // Update todo if it is found
    const body = await request.body();
    const updatedRows = await TodoModel.updateById({
      id: Number(params.id), ... body.value, }); response.status =200;
    response.body = {
      success: true,
      message: `Successfully updated ${updatedRows} row(s)`}; }catch (error) {
    response.status = 400;
    response.body = {
      success: false,
      message: `Error: ${error}`}; }},Copy the code

This code is almost identical to the code in the previous apis. Here’s the difference:

// if todo found then update todo
const body = await request.body();
const updatedRows = await TodoModel.updateById({
  id: Number(params.id), ... body.value, });Copy the code

We pass the jSON-formatted body data passed in by the user directly to the todomodel.updateByID function.

Remember to convert the variable type of the ID to numeric to comply with the type constraints of the interface.

This line of code executes and returns the number of affected lines. We return it directly wrapped in the response body. If any errors occur during execution, a general error message will be caught and returned.

Let’s restart the server to see if it works. Make sure your MySQL is running and type in terminal:

$ deno run --allow-net server.ts
Copy the code

Open Postman to test whether the current interface works properly:

Executing [PUT] localhost:8080/todos/:id => updates the toDO content with the specified ID

Executing [GET] localhost:8080/todos/ => will return all toDO lists to verify that the update was successful.

[DELETE] Removes a Todo API by ID

Create a deleteById function in your models/todo.ts file and fill it with the following:

/** * Remove the corresponding todo * @param ID * @returns INTEGER (count of effect rows) */
deleteById: async ({ id }: Todo) => {
  const result = await client.query(
    `DELETE FROM ${TABLE.TODO}WHERE id = ? `,
    [id],
  );
  // return count of rows updated
  return result.affectedRows;
},
Copy the code

Here we use MySQL to delete the specified element based on the deconstructed ID value and return the number of affected rows. The number of affected rows can still only be 0 or 1, because the ID will correspond to at most one element.

Next, open the controllers/todo.ts file and populate the deleteByTodoId method:

@route DELETE todos/: ID */
deleteTodoById: async (
  { params, response }: { params: { id: string }; response: any= > {},)try {
    const updatedRows = await TodoModel.deleteById({
      id: Number(params.id),
    });
    response.status = 200;
    response.body = {
      success: true,
      message: `Successfully updated ${updatedRows} row(s)`}; }catch (error) {
    response.status = 400;
    response.body = {
      success: false,
      message: `Error: ${error}`}; }},Copy the code

Here we hand the deconstructed params.id to the todomodel.deleteByID method and return the number of rows affected in the database during this execution.

If any errors occur during execution, the standard error response body is returned.

Let’s check to see if this API works.

Please make sure your MySQL is running and type in terminal:

$ deno run --allow-net server.ts
Copy the code

Open Postman to test:

Doing [GET] localhost:8080/todos/ => will GET all toDos.

Executing [DELETE] localhost:8080/todos/:id => will DELETE the element with the specified ID.

Executing [GET] localhost:8080/todos/ => will return a list of all toDos to see if the toDos you wanted to remove are still there.

This concludes the Deno + Oak + MySQL tutorial.

The code for the entire article can be seen here: github.com/adeelibr/de… . If you have any questions, you can communicate on it. Or submit your PR to the warehouse.

If you find this series helpful, share it on your social network. I also follow @adeelibr on Twitter. I’d love to hear any thoughts you have.

Translator’s note: Welcome to follow my official id: @ningowood

Let’s get started

Perform CRUD operations on the data table and add functionality to the API controller

[Get] Gets all Todos apis

[Post] Added a Todo API

[GET] Queries the API of a Todo by ID

[PUT] Updates a Todo API by ID

[DELETE] Removes a Todo API by ID

Related Posts

Introduction to Mysql

01_PHP basics

Singly linked list data structure of the rotating list | 15 sets