Building API Server Using Deno and MySQL

In this tutorial, we will build a simple API Server using Deno, Oak, and MySQL.

Grab the code from Github to understand better.

Prerequistics

You need to have Deno and MySQL installed in your system. To install Deno in Mac or Linux based system, run the following command.

curl -fsSL https://deno.land/x/install/install.sh | sh

In Windows, you can install Deno using Powershell.

iwr https://deno.land/x/install/install.ps1 -useb | iex

Let’s set up the MySQL.

Setting up MySQL

Assuming you have installed MySQL in your system. Login to MySQL server and create a new database.

Note: Make sure you have created a user in MySQL with no password. Deno MySQL driver is still in WIP and it does not work with password authentication, yet

create database codeforgeek_test;

Then, select the database.

use codeforgeek_test;

Create a new table in the database.

  CREATE TABLE users (
      id int(11) NOT NULL AUTO_INCREMENT,
      name varchar(100) NOT NULL,
      created_at timestamp not null default current_timestamp,
      PRIMARY KEY (id)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Let’s check out our code.

Deno Server using Oak

Oak is a third-party module to develop HTTP servers in Deno. Oak is inspired by the Koa framework of Node.js.

Here is our Server code.

import { Application } from 'https://deno.land/x/oak/mod.ts'
import router from './routes.ts'
const port = Deno.env.get("PORT") || 3000

const app = new Application()

app.use(router.routes())
app.use(router.allowedMethods())

console.log(`Server running on port ${port}`)

await app.listen({ port: +port })

Here is the router code.

import { Router } from 'https://deno.land/x/oak/mod.ts'
import { getUsers, getUser, addUsers, updateUsers, deleteUsers } from './controllers/users.ts'

const router = new Router()

router.get('/api/v1/users', getUsers)
    .get('/api/v1/users/:id', getUser)
    .post('/api/v1/users', addUsers)
    .put('/api/v1/users/:id', updateUsers)
    .delete('/api/v1/users/:id', deleteUsers)

export default router

We specified five routes in the Oak router. Each route calling a specified function that in turn deals with MySQL.

In a controllers folder, we have written all the functions that performs CRUD operations in MySQL database.

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

const client = await new Client().connect({
    hostname: dbCreds.hostname,
    username: dbCreds.user,
    db: dbCreds.database,
    poolSize: dbCreds.poolsize,
    password: dbCreds.password,
  });

const getUsers = async ({ response }: { response: any }) => {
    try {
        const result = await client.execute("SELECT * FROM users")
        response.body = {
            success: true,
            data: result.rows
        }
    } catch (err) {
        response.status = 500
        response.body = {
            success: false,
            msg: err.toString()
        }
    }
}

const getUser = async ({ params, response }: { params: { id: string }, response: any }) => {
    try {
        const result = await client.execute("SELECT * FROM users WHERE ?? = ?", ["id", params.id])
        response.body = {
            success: true,
            data: result.rows
        }
    } catch (err) {
        response.status = 500
        response.body = {
            success: false,
            msg: err.toString()
        }
    }
}

const addUsers = async ({ request, response }: { request: any, response: any }) => {
    const body = await request.body()
    const data = body.value

    if (!request.hasBody) {
        response.status = 400
        response.body = {
            success: false,
            msg: 'No data'
        }
    } else {
        try {
            const result = await client.execute("INSERT INTO users(name) VALUES(?)",[
            data.name])

            response.status = 201
            response.body = {
                success: true,
                data: result
            }
        } catch (err) {
            response.status = 500
            response.body = {
                success: false,
                msg: err.toString()
            }
        }
    }
}

const updateUsers = async({ params, request, response }: { params: { id: string }, request: any, response: any }) => {
    await getUser({ params: { "id": params.id }, response })

    if(response.status === 404) {
        response.body = {
            success: false,
            msg: response.body.msg
        }
        response.status = 404
        return
    } else {
        const body =  await request.body()
        const data = body.value

        if (!request.hasBody) {
            response.status = 400
            response.body = {
                success: false,
                msg: 'No data'
            }
        } else {
            try {
                const result = await client.execute("UPDATE users SET ??=? WHERE ??=?",
                ["name", data.name,
                "id", params.id])

                response.status = 200
                response.body = {
                    success: true,
                    data: result
                }
            } catch (err) {
                response.status = 500
                response.body = {
                    success: false,
                    msg: err.toString()
                }
            }
        }
    }
}

const deleteUsers = async ({ params, response }: { params: { id: string }, response: any }) => {
    await getUser({ params: { "id": params.id }, response })

    if(response.status === 404) {
        response.body = {
            success: false,
            msg: response.body.msg
        }
        response.status = 404
        return
    } else {
        try {
            const result = await client.query("DELETE FROM users WHERE ??=?", ["id", params.id])

            response.status = 200
            response.body = {
                success: true,
                msg: `Users with id ${params.id} has been deleted`
            }
        } catch (err) {
            response.status = 500
                response.body = {
                    success: false,
                    msg: err.toString()
                }
        }
    }
}

export { getUsers, getUser, addUsers, updateUsers, deleteUsers }

Our config.ts file contains the database credentials.

const dbCreds = {
    user: "root",
    database: "codeforgeek_test",
    password: "",
    hostname: "127.0.0.1",
    port: 3306,
    poolsize: 3,
}

export { dbCreds }

Let’s run and test the code.

Testing Deno API Server

Run the program using the following command.

deno run --allow-net --allow-env server.ts

Now, open any API testing software to test out API. You can use Postman, Insomnia, or any sort of tool.

Get all users

Here is the API route.

GET localhost:3000/api/v1/users/

API Server Using Deno - GET route

Create a user

Here is the API route.

POST localhost:3000/api/v1/users/

Deno API Server - POST route

Update a user

Here is the API route.

PUT localhost:3000/api/v1/users/

Deno API Server - PUT route

Delete a user

Here is the API route.

DELETE localhost:3000/api/v1/users/

Deno API Server - DELETE route

Summary

Deno is still in development stage and so the third party libraries. This tutorial is for study purposes only and I do not recommend it for production uses. I hope you got the gist of how to use Deno to connect to databases and build a simple API server using the Oak framework.

Pankaj Kumar
Pankaj Kumar
Articles: 208