Create a simple Deno + MySQL API
Tags: deno, typescript, javascript
Published: 2020-05-20 20:08:04

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](https://github.com/karlodelarosa/deno-rest-api) 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:
```yml
server: deno-db
user: root
password: root
db: deno-db
```

You should automatically see the *user* table and one test data

User Schema:
```sql
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
```typescript
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
```typescript
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*
```typescript
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
```typescript
export interface UserInterface {
    name: string;
    country: string
}
```

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

*getAllUsers.ts*
```typescript
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*
```typescript
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*
```typescript
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*
```typescript
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*
```typescript
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*
```typescript
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*
```typescript
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](https://twitter.com/KarloDelaRosa3)

Source code: [GitHub](https://github.com/karlodelarosa/deno-rest-api)