Deno.js MySQL MariaDB Driver Tutorial to Perform CRUD Operations Using deno_mysql in TypeScript Full Project For Beginners

 

 

deno_mysql

 

MySQL and MariaDB database driver for Deno.

 

 

 

API

Connect to MySQL Database

 

 

 

import { Client } from "https://deno.land/x/mysql/mod.ts";
const client = await new Client().connect({
  hostname: "127.0.0.1",
  username: "root",
  db: "dbname",
  password: "password",
});

 

 

connect pool

Create client with connection pool.

pool size is auto increment from 0 to poolSize

 

import { Client } from "https://deno.land/x/mysql/mod.ts";
const client = await new Client().connect({
  hostname: "127.0.0.1",
  username: "root",
  db: "dbname",
  poolSize: 3, // connection limit
  password: "password",
});

 

 

create database

await client.execute(`CREATE DATABASE IF NOT EXISTS enok`);
await client.execute(`USE enok`);

 

 

create table

await client.execute(`DROP TABLE IF EXISTS users`);
await client.execute(`
    CREATE TABLE users (
        id int(11) NOT NULL AUTO_INCREMENT,
        name varchar(100) NOT NULL,
        created_at timestamp not null default current_timestamp,
        PRIMARY KEY (id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
`);

 

 

insert

let result = await client.execute(`INSERT INTO users(name) values(?)`, [
  "manyuanrong",
]);
console.log(result);
// { affectedRows: 1, lastInsertId: 1 }

 

update

let result = await client.execute(`update users set ?? = ?`, ["name", "MYR"]);
console.log(result);
// { affectedRows: 1, lastInsertId: 0 }

 

 

delete

 

 

let result = await client.execute(`delete from users where ?? = ?`, ["id", 1]);
console.log(result);
// { affectedRows: 1, lastInsertId: 0 }

 

 

query

const username = "manyuanrong";
const users = await client.query(`select * from users`);
const queryWithParams = await client.query(
  "select ??,name from ?? where id = ?",
  ["id", "users", 1],
);
console.log(users, queryWithParams);

 

See also  Build a jsPDF Playground to Generate PDF Documents From HTML Template & Images Using Javascript Full Project For Beginners

 

 

execute

There are two ways to execute an SQL statement.

First and default one will return you an rows key containing an array of rows:

const { rows: users } = await client.execute(`select * from users`);
console.log(users);

The second one will return you an iterator key containing an [Symbol.asyncIterator] property:

await client.useConnection(async (conn) => {
  // note the third parameter of execute() method.
  const { iterator: users } = await conn.execute(
    `select * from users`,
    /* params: */ [],
    /* iterator: */ false,
  );
  for await (const user of users) {
    console.log(user);
  }
});

The second method is recommended only for SELECT queries that might contain many results (e.g. 100k rows).

transaction

const users = await client.transaction(async (conn) => {
  await conn.execute(`insert into users(name) values(?)`, ["test"]);
  return await conn.query(`select ?? from ??`, ["name", "users"]);
});
console.log(users.length);

close

await client.close();

Logging

The driver logs to the console by default.

To disable logging:

import { configLogger } from "https://deno.land/x/mysql/mod.ts";
await configLogger({ enable: false });

Leave a Reply