DEV Community

Cover image for Create a simple Deno + MySQL API
Karlo Dela Rosa
Karlo Dela Rosa

Posted on • Edited on

13 6

Create a simple Deno + MySQL API

This is a simple concept of how we can make a REST API using Deno and Typescript.

Here's my simple RESTful API

If you are in a hurry just click here for the source code.

Here are the stacks:

  1. Deno
  2. Docker
  3. MySQL

Why Docker?

  • Because I want you to experience real data manipulation using the database rather than text files or objects, so I provided a local Database.

Why I didn't containerize Deno?

  • Because I don't want Docker to be a bottleneck. If you don't want Docker, it's fine you can use your own DB and run Deno server alone. I'm just giving you options.

Let's Start

Install Deno:

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

Install Docker for local database (If you have personal DB, just use it and create the table needed)


Clone the repository:

git clone https://github.com/karlodelarosa/deno-rest-api.git

Run Deno:

deno run --allow-net --allow-read --allow-write index.ts

Run MySQL Container:

docker-compose up -d

You should be able to see a running container for MySQL and Adminer

CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                     NAMES
fa3924041b59        adminer             "entrypoint.sh docke…"   6 seconds ago       Up 4 seconds        0.0.0.0:50000->8080/tcp   deno-adminer
7bf14f3a94ca        mysql:5.7.12        "docker-entrypoint.s…"   6 seconds ago       Up 4 seconds        0.0.0.0:3306->3306/tcp    deno-db

Open Adminer

localhost:50000

Credentials:

server: deno-db
user: root
password: root
db: deno-db

You should automatically see the user table and one test data

User Schema:

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `country` varchar(50) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1

Steps:

1. Create index.ts this will serve as our server for this App

import { Application } from 'https://deno.land/x/oak/mod.ts'
import router from './routes/routes.ts';

const app = new Application();

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

app.use((ctx) => {
  ctx.response.body = "Welcome to deno-rest-api";
});

await app.listen({ port: 8000 });

2. Connect to MySQL Database, create db/MySqlClient.ts

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

const client = await new Client().connect({
  hostname: "localhost",
  username: "root",
  db: "deno-db",
  password: "root",
});

export default client

3. Create routes/routes.ts

import { Router } from 'https://deno.land/x/oak/mod.ts'
import { getAllUsers } from '../handler/getAllUsers.ts';
import { getUser } from '../handler/getUser.ts';
import { addUser } from '../handler/addUser.ts';
import { updateUser } from '../handler/updateUser.ts';
import { deleteUser } from '../handler/deleteUser.ts';

const router = new Router()

router.get("/users", getAllUsers)
.get("/user/:id", getUser)
.post("/user", addUser)
.put("/user/:id", updateUser)
.delete("/user/:id", deleteUser)

export default router

4. Create a contract, it's an Interface for our data model

export interface UserInterface {
    name: string;
    country: string
}

5. Create handlers, this will catch the requests and process the logics.

getAllUsers.ts

import client from '../db/MySqlClient.ts';
import { search } from '../repository/user.ts';

export async function getAllUsers ({ response }: { response: any }) { 
  const result = await search();
  response.body = result.rows;
}

getUser.ts

import client from '../db/MySqlClient.ts';
import * as doesUserExist from '../specification/doesUserExist.ts';
import { search } from '../repository/user.ts';

export async function getUser ({ params, response }: { params: any; response: any }) {
    const hasRecord = await doesUserExist.isSatisfiedBy(params.id);
    let status = 200;

    if (hasRecord) {
      const result = await search(params);
      response.body = result.rows;
    } else {
      response.body = { "error": "User not found!" };
      status = 400;
    }

    response.status = status;
};

addUser.ts

import client from '../db/MySqlClient.ts';
import { insert } from '../repository/user.ts';
import { UserInterface } from '../contract/userInterface.ts';

export async function addUser ({ request, response }: { request: any; response: any }) {
    const body = await request.body();
    const userInfo: UserInterface = body.value;
    let status = 200;

    if (userInfo.hasOwnProperty('name') && userInfo.hasOwnProperty('country')) {
      response.body = await insert(userInfo);
    } else {
      response.body = { "error": "Invalid request!" };
      status = 400;
    }

    response.status = status;
}

updateUser.ts

import client from '../db/MySqlClient.ts';
import * as doesUserExist from '../specification/doesUserExist.ts';
import { UserInterface } from '../contract/userInterface.ts';
import { update } from '../repository/user.ts';

export async function updateUser ({ request, response, params }: { request: any; response: any; params: any }) {
    const body = await request.body()
    const userInfo: UserInterface = body.value 
    const hasRecord = await doesUserExist.isSatisfiedBy(params.id);
    let responseMessage = {};
    let status = 200;

    if (hasRecord) {
      responseMessage = await update(userInfo.name, userInfo.country, params.id);
    } else {
      responseMessage = { "error": "User not found!" };
      status = 400;
    }

    response.body = responseMessage;
    response.status = status;
}

deleteUser.ts

import client from '../db/MySqlClient.ts';
import * as doesUserExist from '../specification/doesUserExist.ts';
import { remove } from '../repository/user.ts';

export async function deleteUser ({ params, response }: { params: any; response: any }) {
    const hasRecord = await doesUserExist.isSatisfiedBy(params.id);
    let responseMessage = {};
    let status = 200;

    if (hasRecord) {
      responseMessage = await remove(params.id);
    } else {
      responseMessage = { "error": "User not found!" };
      status = 400;
    }

    response.body = responseMessage
    response.status = status
}

6. Create specification. Read about Domain-Driven Design.

doesUserExist.ts

import client from '../db/MySqlClient.ts';

export async function isSatisfiedBy(id:number) {
    const result = await client.query(`SELECT COUNT(*) count FROM user WHERE id = ?`, [id]);
    return result[0].count >= 1;
}

7. Create the User Repository, this is where queries are stored for User APIs

user.ts

import client from '../db/MySqlClient.ts';

interface Key {
    id?: any
}

export async function search(params:Key = {}) { 
    const isSpecific = Object.keys(params).length !== 0;
    if (isSpecific) {
        return await client.execute(`SELECT * FROM user WHERE id = ?`, [params.id]);
    } else {
        return await client.execute(`SELECT * FROM user`);   
    }
}

export async function insert({ name, country }: { name: string; country: string }) {
    return await client.execute(`INSERT INTO user(name, country) values(?,?)`, [
        name, country
    ]);
}

export async function update(name: string, country: string, id: string) {
    return await client.execute(`UPDATE user SET name= ?, country= ? WHERE id = ?`, [
        name, country, id
    ]);
}

export async function remove(id: string) {
    return await client.execute(`DELETE FROM user WHERE id = ?`, [id]); 
}

Try it on POSTMAN.

Follow me on twitter

Source code: GitHub

SurveyJS custom survey software

JavaScript UI Library for Surveys and Forms

Generate dynamic JSON-driven forms directly in your JavaScript app (Angular, React, Vue.js, jQuery) with a fully customizable drag-and-drop form builder. Easily integrate with any backend system and retain full ownership over your data, with no user or form submission limits.

View demo

Top comments (5)

Collapse
 
aminnairi profile image
Amin

Hi there,

I noticed you were using Deno and some other Docker images. Why not take the leap and use Deno inside Docker for maximum portability?

Here is an image I made for this purpose: hub.docker.com/r/aminnairi/deno

Collapse
 
karlodelarosa3 profile image
Karlo Dela Rosa • Edited

Hi! I already tried it, but I've decided to separate it for now for the sake of simplicity. I will try a full blown docker system in my next project. But I appreciate it! Thank you 🙂

Collapse
 
aminnairi profile image
Amin

Noted. Good luck for your project!

Collapse
 
sutin1234 profile image
thinny • Edited

fixed body not found

const body = await request.body({ type: 'json'});
const userInfo: UserInterface = await body.value;

Collapse
 
karlodelarosa3 profile image
Karlo Dela Rosa

I will check this error. Thanks for noticing

👋 Kindness is contagious

Dive into an ocean of knowledge with this thought-provoking post, revered deeply within the supportive DEV Community. Developers of all levels are welcome to join and enhance our collective intelligence.

Saying a simple "thank you" can brighten someone's day. Share your gratitude in the comments below!

On DEV, sharing ideas eases our path and fortifies our community connections. Found this helpful? Sending a quick thanks to the author can be profoundly valued.

Okay