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/

Create a user

Here is the API route.

POST localhost:3000/api/v1/users/

Update a user

Here is the API route.

PUT localhost:3000/api/v1/users/

Delete a user

Here is the API route.

DELETE localhost:3000/api/v1/users/

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.