Deno Rest APIs CRUD mysql
Tags: deno, mysql, dero, crud
Published: 2021-04-08 08:27:40

Hi everyone, my name is Herudi, to day i'm build CRUD mysql with **[Deno](https://deno.land/)**. CRUD is a Create Read Update Delete. CRUD is a basic code for programmer to learn it. **Deno** is a secure runtime for **JavaScript and TypeScript**. **Deno** is a simple, modern and secure runtime for **JavaScript** and **TypeScript** that uses V8 and is built in Rust. I also build a **[Dero](https://deno.land/x/dero)** is a Fast micro framework for **Deno**. i also love a simple code. and let's go write the code.
### Create database and items table.
```mysql
CREATE DATABASE IF NOT EXISTS `dero-crud`;
USE `dero-crud`;
CREATE TABLE IF NOT EXISTS `items` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(100) NOT NULL,
	`brand` VARCHAR(100) NOT NULL,
	`price` INT(11) NOT NULL,
	PRIMARY KEY (`id`)
)
ENGINE=InnoDB DEFAULT CHARSET=latin1;
```
### Rest APIs (route)
```ts
// find all items
GET /api/v1/items

// search items with query params text
GET /api/v1/items/search?text=blabla

// find by items id
GET /api/v1/items/:id

// save items
POST /api/v1/items

// update items by id
PUT /api/v1/items/:id

// delete by items id
DELETE /api/v1/items/:id


```
### Make the simple mysql connection ***client.ts***.
```ts
import { Client } from "https://deno.land/x/mysql@v2.8.0/mod.ts";

export default await new Client().connect({
    hostname: "127.0.0.1",
    username: "root",
    db: "dero-crud",
    password: "secret"
});
```
Full doc mysql for deno => https://github.com/denodrivers/mysql

### Make the class items for validator ***items/items.ts***.
see doc [class-validator](https://github.com/typestack/class-validator)
```ts
import { IsNumber, IsString } from "https://cdn.skypack.dev/class-validator?dts";

export default class Items {
    @IsString()
    name!: string;

    @IsString()
    brand!: string;

    @IsNumber()
    price!: number;
}

```
### Make the service for Rest APIs ***items/items_service.ts***.
```ts
import client from "./../client.ts";
import Items from "./items.ts";

export default class ItemsService {

    async findAll() {
        const sql = `select * from items`;
        const { rows } = await client.execute(sql);
        return {
            status: 200,
            data: rows
        }
    }

    async findById(id: number) {
        const sql = `select * from items where id = ?`;
        const { rows } = await client.execute(sql, [id]);
        const data = rows && rows.length ? rows[0] : null;
        return {
            status: 200,
            data
        }
    }

    async search(text: string | null | undefined) {
        if (!text) {
            throw new Error("query parameter text is required");
        }
        const sql = `select * from items where name like '%${text}%' or brand like '%${text}%'`;
        const { rows } = await client.execute(sql);
        return {
            status: 200,
            data: rows
        }
    }

    async save(body: Items) {
        const sql = `insert into items(name, brand, price) values(?, ?, ?)`;
        await client.execute(sql, [
            body.name,
            body.brand,
            body.price
        ]);
        return {
            status: 201,
            messsage: "Success save items"
        };
    }

    async update(id: number, body: Items) {
        const sql = `update items set name = ?, brand = ?, price = ? where id = ?`;
        await client.execute(sql, [
            body.name,
            body.brand,
            body.price,
            id
        ]);
        return {
            status: 200,
            messsage: "Success update items"
        }
    }

    async destroy(id: number) {
        const sql = `delete from items where id = ?`;
        await client.execute(sql, [id]);
        return {
            status: 200,
            messsage: "Success delete items"
        }
    }
}
```

### Make the controller for Rest APIs ***items/items_controller.ts***.
```ts
import { 
    Get,
    Post,
    Put,
    Delete,
    Status,
    Controller,
    BaseController,
    Validate,
    Inject
} from "https://deno.land/x/dero@1.1.3/mod.ts";
import ItemsService from "./items_service.ts";
import Items from "./items.ts";

@Controller("/items")
class ItemsController extends BaseController {

    @Inject(ItemsService)
    private readonly itemsService!: ItemsService;

    @Get()
    findAll() {
        return this.itemsService.findAll();
    }

    @Get("/search")
    search() {
        const { text } = this.request.query;
        return this.itemsService.search(text);
    }

    @Get("/:id")
    findById() {
        const { id } = this.request.params;
        return this.itemsService.findById(Number(id));
    }

    @Status(201)
    @Validate(Items)
    @Post()
    save() {
        const body = this.request.parsedBody as Items;
        return this.itemsService.save(body);
    }

    @Validate(Items)
    @Put("/:id")
    update() {
        const { id } = this.request.params;
        const body = this.request.parsedBody as Items;
        return this.itemsService.update(Number(id), body);
    }

    @Delete("/:id")
    destroy() {
        const { id } = this.request.params;
        return this.itemsService.destroy(Number(id));
    }
}

export default ItemsController;

```

### Now, create ***app.ts***
```ts
import { Dero } from "https://deno.land/x/dero@1.1.3/mod.ts";
import { validateOrReject } from "https://cdn.skypack.dev/class-validator?dts";
import ItemsController from './items/items_controller.ts';
import client from './client.ts';

class App extends Dero {
    constructor() {
        super({
            // register class validator
            classValidator: validateOrReject
        });

        // register class controller with prefix /api/v1
        this.use({ 
            prefix: "/api/v1", 
            class: [
                ItemsController
            ] 
        });
    }

    public async start(port: number) {
        await this.listen(port, async (err) => {
            if (err) {
                console.log(err);
                await client.close();
            }
            console.log("> Running on port " + port);
        })
    }
}

await new App().start(3000);
```

Now, you can run app.ts : 
```bash
deno run --allow-net --allow-read app.ts
```

I hope the code above can help you in finding crud deno mysql.
full source code https://github.com/herudi/dero/tree/main/examples/with_crud_mysql

> very grateful if you give a star or fork.