{{announcement.body}}
{{announcement.title}}

Deno JS: CRUD and MySQL Connection

DZone 's Guide to

Deno JS: CRUD and MySQL Connection

A complete guide for developing a backend application with CRUD operations along with database connection to MySQL.

· Web Dev Zone ·
Free Resource

Deno.js is a new backend language based on the javascript framework. Deno is a simple, modern, and secure runtime for JavaScript and TypeScript that uses V8 and is built in Rust. In this tutorial, we'll learn how to develop a complete CRUD web application using Deno js (Oak as a framework) and using Mysql as database. 



Overview

This project takes an example of an Employee object having four attributes id, name, department, and isActive. We will proceed with adding an employee object in our DB and then performing further operations.

Dependencies

No dependency required. Just install Deno using curl -fsSL https://deno.land/x/install/install.sh | shand set up your DENO_INSTALL path in .bashrc.

Configuration File(server.ts):

server.ts is the main configuration file where all the configuration combines such as route definitions, port, and even logging component.

TypeScript
 




x
9
10


 
1
const app = new Application();
2
const port: number = 8080;
3
 
          
4
app.use(logger.logger);
5
app.use(logger.responseTime);
6
 
          
7
app.use(employeeRouter.routes());
8
app.use(employeeRouter.allowedMethods());
9
 
          
10
 
          
11
app.addEventListener("listen", ({ secure, hostname, port }) => {
12
  const protocol = secure ? "https://" : "http://";
13
  const url = `${protocol}${hostname ?? "localhost"}:${port}`;
14
  console.log(
15
    `${yellow("Listening on:")} ${green(url)}`,
16
  );
17
});
18
 
          
19
await app.listen({ port });



Interface Layer(Employee.ts):

Define your models here for persisting in the database. Here we have created an Employee model in Employee.ts file

TypeScript
 




xxxxxxxxxx
1


 
1
export default interface Employee {
2
  id?: number,
3
  name?: string,
4
  department?:string,
5
  isActive?:boolean
6
}



Database Layer(client.ts):

Define your SQL Connection and Create table script here.

TypeScript
 




xxxxxxxxxx
1
19


 
1
const client = await new Client();
2
 
          
3
client.connect({
4
  hostname: "127.0.0.1",
5
  username: "your db username",
6
  password: "your db password",
7
  db: "",
8
});
9
 
          
10
const run = async () => {
11
  await client.execute(`CREATE DATABASE IF NOT EXISTS ${DATABASE}`);
12
 
          
13
  await client.execute(`USE ${DATABASE}`);
14
};
15
 
          
16
run();
17
 
          
18
export default client;



Service Layer(employeeService.ts):

Define your business logic here and it also acts as a midpoint between database and controller layer.

TypeScript
 




xxxxxxxxxx
1
51


1
export default {
2
    doesExistById: async ({ id }: Employee) => {
3
        const [result] = await client.query(
4
            `SELECT COUNT(*) count FROM ${TABLE.EMPLOYEE} WHERE id = ? LIMIT 1`,
5
            [id],
6
        );
7
        return result.count > 0;
8
    },
9
 
          
10
    getAll: async () => {
11
        return await client.query(`SELECT * FROM ${TABLE.EMPLOYEE}`);
12
    },
13
 
          
14
    getById: async ({ id }: Employee) => {
15
        return await client.query(
16
            `SELECT * FROM ${TABLE.EMPLOYEE} WHERE id = ?`,
17
            [id],
18
        );
19
    },
20
 
          
21
    add: async ({name,department,isActive}: Employee,) => {
22
        return await client.query(
23
            `INSERT INTO ${TABLE.EMPLOYEE}(name, department,isActive) values(?, ?, ?)`,
24
            [
25
                name,
26
                department,
27
                isActive,
28
            ],
29
        );
30
    },
31
 
          
32
    updateById: async ({ id, name,department, isActive }: Employee) => {
33
        const result = await client.query(
34
            `UPDATE ${TABLE.EMPLOYEE} SET name=?, department=?, isActive=? WHERE id=?`,
35
            [
36
                name,
37
                department,
38
                isActive,
39
                id,
40
            ],
41
        );
42
        return result.affectedRows;
43
    },
44
 
          
45
    deleteById: async ({ id }: Employee) => {
46
        const result = await client.query(
47
            `DELETE FROM ${TABLE.EMPLOYEE} WHERE id = ?`, [id],
48
        );
49
        return result.affectedRows;
50
    },
51
};



Controller Layer (employeeController.ts):

To perform CRUD operations using HTTP methods(GET,POST,PUT,DELETE).

TypeScript
 




x


 
1
// GET
2
 getAllEmployees: async ({ response }: { response: any }) => {
3
      const data = await EmployeeService.getAll();
4
      response.status = 200;
5
      response.body = {
6
        success: true,
7
        data,
8
      };
9
  },
10
// POST
11
  createEmployee: async ({ request, response }: { request: any; response: any },) => {
12
    const body = await request.body();
13
      await EmployeeService.add(
14
        { name: body.value.name,
15
          department:body.value.department,   
16
          isActive: true },
17
      );
18
      response.body = {
19
        success: true,
20
        message: "The record was added successfully",
21
      };
22
  },
23
 
          
24
// GET by ID
25
  getEmployeeById: async (
26
    { params, response }: { params: { id: string }; response: any },
27
  ) => {
28
      const isAvailable = await EmployeeService.doesExistById(
29
        { id: Number(params.id) },
30
      );
31
      const employee: Employee = await EmployeeService.getById({ id: Number(params.id)      });
32
      response.status = 200;
33
     ...
34
  },
35
//PUT
36
  updateEmployeeById: async ({ params, request, response }: {params: { id: string };
37
    request: any;
38
    response: any;
39
  },) => {
40
      const isAvailable = await EmployeeService.doesExistById(
41
        { id: Number(params.id) },
42
      );
43
      const body = await request.body();
44
      const updatedRows = await EmployeeService.updateById({
45
        id: Number(params.id),
46
        ...body.value,
47
      });
48
      response.status = 200;
49
     ...
50
  },
51
//DELETE
52
  deleteEmployeeById: async (
53
    { params, response }: { params: { id: string }; response: any },
54
  ) => {
55
      const updatedRows = await EmployeeService.deleteById({
56
        id: Number(params.id),
57
      });
58
      response.status = 200;
59
     ...
60
  },
61
};



Routes Layer(employee.route.ts):

Define the endpoints in an application and connects to the HTTP methods in a controller.

TypeScript
 




xxxxxxxxxx
1
10


1
const router = new Router();
2
 
          
3
router
4
  .get("/employee", employeeController.getAllEmployees)
5
  .post("/employee", employeeController.createEmployee)
6
  .get("/employee/:id", employeeController.getEmployeeById)
7
  .put("/employee/:id", employeeController.updateEmployeeById)
8
  .delete("/employee/:id", employeeController.deleteEmployeeById);
9
 
          
10
export default router;



Running the Application

Run your application by the following command: deno run --allow-env --allow-net server.ts

The application will come up at base URL localhost:8080.

Test your application by appending the endpoints to base URL, PLease find the postman snapshot below:

Create an Employee:


Get Employees List:


Update Employee by Id:


Get Employee By Id:


Delete Employee By Id:

Conclusion

In this tutorial, we learned how to build a CRUD REST-API using Deno js. The complete project can be found at my Github repository: https://github.com/dheerajgupta217/getting-started-with-deno

Topics:
crud, deno, mysql 5.7, node, tutorial, typescript, web dev

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}