deno_mysql

MySQL and MariaDB database driver for Deno.

On this basis, there is also an ORM library: Deno Simple Orm

欢迎国内的小伙伴加我专门建的 Deno QQ 交流群：698469316

API

connect

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);

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: */ true,
  );
  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);

TLS

TLS configuration:

• caCerts([]string): A list of root certificates (must be PEM format) that will be used in addition to the default root certificates to verify the peer's certificate.
• mode(string): The TLS mode to use. Valid values are "disabled", "verify_identity". Defaults to "disabled".

You usually need not specify the caCert, unless the certificate is not included in the default root certificates.

import { Client, TLSConfig, TLSMode } from "https://deno.land/x/mysql/mod.ts";
const tlsConfig: TLSConfig = {
  mode: TLSMode.VERIFY_IDENTITY,
  caCerts: [
    await Deno.readTextFile("capath"),
  ],
};
const client = await new Client().connect({
  hostname: "127.0.0.1",
  username: "root",
  db: "dbname",
  password: "password",
  tls: tlsConfig,
});

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 });

Test

The tests require a database to run against.

docker container run --rm -d -p 3306:3306 -e MYSQL_ALLOW_EMPTY_PASSWORD=true docker.io/mariadb:latest
deno test --allow-env --allow-net=127.0.0.1:3306 ./test.ts

Use different docker images to test against different versions of MySQL and MariaDB. Please see ci.yml for examples.