MySQL connection in Deno


Controllers and CURD operations

In the controller file (under controllers folder), we use the client object(db) to perform the query and execution of the state statements. All the CURD operations are handles here and the those are organized as functions. Finally we export them.

To perform execution of statement we have to use the execute method, provided by the driver.

import db from "../config/database.ts";
export default {
  async index(ctx: any) {
    try {
      //NOTE Get all the documents
      const result = await db.client.query("SELECT * FROM todos");
      ctx.response.body = result;
      ctx.response.status = 201;
    } catch (error) {
      ctx.response.body = 'Some Error occurs ' + console.error();
      ctx.response.status = 401;
    }
  },
  async delete({ params, response }: { params: any, response: any }) {
    try {
      // NOTE using params

      const id = params.id;
      await db.client.execute('delete from todos where ??=?', ["id", id]).then((result) => {
        response.status = 200
        response.body = 'Entry deleted'
      }).catch((err) => {
        response.status = 400
        response.body = { error: err }
      })

    } catch (error) {
      response.status = 400
      response.body = { error: error }
    }
  }
  ,
  async update({ params, request, response }: { params: any, request: any, response: any }) {
    try {
      const id = params.id;
      const body = request.body({ type: "json" })
      const todo = await body.value;
      // NOTE Updating Collection
      await db.client.execute('update todos set ??=?,??=? where ??=?', ["item", todo.item, "description", todo.description, "id", id]).then(() => {
        response.status = 200
        response.body = 'Entery Updated'
      }).catch((e) => {
        response.status = 400
        response.body = { error: 'got an error' }

      })


    } catch (error) {
      response.status = 400
      response.body = { error: error }
    }
  },
  async findById({ params, response }: { params: any, response: any }) {
    try {
      const id = params.id;
      //NOTE Get a single entry
      const todos = await db.client.query('select * from todos where id=?', [id])
      response.status = 201
      response.body = todos

    } catch (error) {
      response.status = 400
      response.body = { error: error }
    }

  },
  async newTodo({ request, response }: { request: any, response: any }) {
    try {
      //NOTE Getting the body of the request
      const body = request.body({ type: "json" })
      const todo = await body.value;
      await db.client.execute('insert into todos (item,description)values(?,?)', [todo.item, todo.description]).then((model) => {
        response.status = 201
        response.body = model
      }).catch((error) => {
        console.log('caught errors while saving');

        response.body = { error: console.error };
        response.status = 401
      })
    } catch (error) {
      console.log(error);
    }
  },
   
}

Routes

In the routes we used those controllers to add functionality to the routes

import Controller from '../controllers/Controller.ts';
import { Router } from 'https://deno.land/x/oak@v6.0.1/mod.ts';
const router = new Router();
router.post('/todo', Controller.newTodo)
router.get('/todo/:id', Controller.findById)
router.put('/todo/:id', Controller.update)
router.delete('/todo/:id', Controller.delete)
router.get('/', Controller.index)
export default router;

In this route typescript we import the controller functions we created and define routes using Oak router.

main.ts

It is starting point of our application , in which we used two middle ware one for base/not found route and another the main routes in the page

 import { Application } from 'https://deno.land/x/oak@v6.0.1/mod.ts';
import { config } from "https://deno.land/x/dotenv/mod.ts";
import router from './routes/routes.ts'

const env =  config()
const app = new Application();
const PORT = +env.PORT || 3000 //+convert it number 
const HOST = env.APP_HOST  
app.use(router.routes())
app.use( ()=>{'NotFound'});
app.listen({ port:PORT });
console.log(`Server running ${HOST} on port ${PORT}`);

The complete MySQL project can be found @ My GitHub Repo.

You may love to read these Deno posts

Published by

Manoj

A developer,teacher and a blogger obsessed with Python,Dart and open source world from India

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.