sail-sail / mysql2

MySQL client for Deno with focus on performance. Supports prepared statements, non-utf8 encodings, binary log protocol, compression much more

mysql2

MySQL client for Deno with focus on performance. Supports prepared statements, non-utf8 encodings, binary log protocol, compression much more

fock by https://github.com/sidorares/node-mysql2

usage

import * as mysql2 from "https://deno.land/x/mysql2/mod.ts";

const pool = mysql2.createPool({
  host: "127.0.0.1",
  port: 3306,
  user: "test_user",
  password: "test_password",
  database: "nest_database",
  connectionLimit: 4,
});
const result = await pool.query("SELECT 1");
console.log(result[0]); // [ { "1": 1 } ]
await pool.end();

mysql2's Issues

Benchmark against deno.land/x/mysql

Hi, I have been working on some benchmarks that I want to share with you:

import { Client, configLogger } from "https://deno.land/x/[email protected]/mod.ts";
import * as mysql2 from "https://deno.land/x/[email protected]/mod.ts";

await configLogger({ enable: false });

Deno.bench({
  name: "mysql",
  group: "database",
  baseline: true,
  fn: async () => {
    const mysqlClient = await new Client().connect({
      hostname: "localhost",
      username: "root",
      password: "your-root-password",
      db: "Jupiter",
      debug: false,
    });

    await mysqlClient.query("SELECT name, age FROM People");
    await mysqlClient.close();
  },
});

Deno.bench({
  name: "mysql2",
  group: "database",
  fn: async () => {
    const mysql2Pool = mysql2.createPool({
      host: "localhost",
      user: "root",
      password: "your-root-password",
      database: "Jupiter",
      connectionLimit: 4,
    });

    await mysql2Pool.query("SELECT name, age FROM People");
    await mysql2Pool.end();
  },
});

The results are:

However we can preserve our created pool and change a little the benchmark.

import { Client, configLogger } from "https://deno.land/x/[email protected]/mod.ts";
import * as mysql2 from "https://deno.land/x/[email protected]/mod.ts";

await configLogger({ enable: false });

const mysqlClient = await new Client().connect({
  hostname: "localhost",
  username: "root",
  password: "your-root-password",
  db: "Jupiter",
  debug: false,
});

const mysql2Pool = mysql2.createPool({
  host: "localhost",
  user: "root",
  password: "your-root-password",
  database: "Jupiter",
  connectionLimit: 4,
});

Deno.bench({
  name: "mysql",
  group: "database",
  baseline: true,
  fn: async () => {
    await mysqlClient.query("SELECT name, age FROM People");
  },
});

Deno.bench({
  name: "mysql2",
  group: "database",
  fn: async () => {
    await mysql2Pool.query("SELECT name, age FROM People");
  },
});

The results change:

So basically I can conclude that the pool creation is taking some time to initialize. Do you think that can be improved? Just really curious about it.

Furthermore thank you for this library!

BinlogDump example

Can I see an example of how to use BinlogDump?

A more complete readme.md

Hello. I'm really grateful you ported this library to Deno. What a gem!

I wanted to propose a more complete readme.md for new & existing devs who run into mysql2 for Deno.

mysql2

MySQL 8 client for Deno with focus on performance. Supports prepared statements, non-utf8 encodings, binary log protocol, compression and much more.

Forked from https://github.com/sidorares/node-mysql2

To-Do app Queryflow Example

1. Install MySQL 8 or rent a MySQL Managed Database from a cloud service.
2. Create a database with the following SQL query

CREATE DATABASE IF NOT EXISTS `devdb` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT ENCRYPTION = 'N';

3. Run example

// deno run --allow-env --allow-net todo.ts

import * as mysql2 from "https://deno.land/x/mysql2/mod.ts";

const pool = mysql2.createPool({
  host: "127.0.0.1",
  port: 3306,
  user: "test_user",
  password: "test_password",
  database: "nest_database",
  connectionLimit: 4,
  timezone: "+00:00" // Sync app timezone with MySQL server UTC timezone
});

/* CREATE temp table tblTodos in database memory */
const sqlCREATE1 = 
`CREATE TEMPORARY TABLE IF NOT EXISTS tblTodos (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  userId BIGINT UNSIGNED DEFAULT NULL,
  CHECK (userId >= 0),
  todoCreated TIMESTAMP DEFAULT NULL,
  todoModified TIMESTAMP DEFAULT NULL,
  todoStatus VARCHAR(16) DEFAULT NULL,
  todoTitle VARCHAR(128) DEFAULT NULL, 
  todoBody VARCHAR(1024) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE = INNODB
  AUTO_INCREMENT=2001
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_0900_ai_ci;`;

/* SELECT 1 shows an empty table */
const sqlSELECT1 = 
`SELECT 
  * 
FROM 
  tblTodos;`;

/* INSERT 1 adds a Pending todo record to the table */
const sqlINSERT1 = 
`INSERT INTO tblTodos (
  id, userId,
  todoCreated, todoModified, todoStatus,
  todoTitle, todoBody
) 
VALUES 
  (
    NULL, 1001, 
    NOW(), NOW(), 'Pending', 
    'Shopping List #1', 'Apples & Oranges'
  );`;

/* SELECT 2 shows the Pending todo record */
const sqlSELECT2 = 
`SELECT 
  * 
FROM 
  tblTodos 
WHERE 
  todoStatus = 'Pending';`;

/* UPDATE 1 changes todo status from Pending to Complete */
const sqlUPDATE1 = 
`UPDATE 
  tblTodos 
SET 
  todoModified = NOW(), 
  todoStatus = 'Complete'
WHERE 
  id = 2001
  AND userId = 1001;`;

/* SELECT 3 shows the Complete todo record */
const sqlSELECT3 = 
`SELECT 
  * 
FROM 
  tblTodos 
WHERE 
  todoStatus = 'Complete'
  AND userId = 1001;`;

/* DELETE 1 deletes todo from table */
const sqlDELETE1 = 
`DELETE FROM 
  tblTodos 
WHERE 
  id = 2001
  AND userId = 1001;`;

/* SELECT 4 once again shows an empty table */
const sqlSELECT4 = 
`SELECT 
  * 
FROM 
  tblTodos;`;

/* DROP 1 deletes table tblTodos from database */
const sqlDROP1 = 
`DROP 
  TEMPORARY TABLE IF EXISTS tblTodos;`;

const connection = await pool.getConnection();

try {

  const create1Result = await connection.execute(sqlCREATE1);
  if (create1Result) console.log("Table tblToDos created.");

  const select1Result = await connection.execute(sqlSELECT1);
  if (select1Result) console.log("Table tblToDos contains", select1Result[0].length, "records.");

  const insert1Result = await connection.execute(sqlINSERT1);
  if (insert1Result) console.log(insert1Result[0].affectedRows, "record(s) inserted.", "id:", insert1Result[0].insertId);

  const select2Result = await connection.execute(sqlSELECT2);
  if (select2Result) console.log(select2Result[0]);

  const update1Result = await connection.execute(sqlUPDATE1);
  if (update1Result) console.log(update1Result[0].affectedRows, "record(s) updated.");

  const select3Result = await connection.execute(sqlSELECT3);
  if (select3Result) console.log(select3Result[0]);

  const delete1Result = await connection.execute(sqlDELETE1);
  if (delete1Result) console.log(delete1Result[0].affectedRows, "record(s) deleted.");

  const select4Result = await connection.execute(sqlSELECT4);
  if (select4Result) console.log("Table tblToDos contains", select1Result[0].length, "records.");

  const drop1Result = await connection.execute(sqlDROP1);
  if (drop1Result) console.log("Table tblToDos droped.");

} catch(error) {

  // Build a smaller MySQL error message.
  const errorPart1 = error.message.split(";")[0];
  const errorPart2 = error.message.split(" use ")[1];
  console.log(`%cError: ${errorPart1} ${errorPart2}`, "color: #e53935");

} finally {

  connection.release();

}

await pool.end();

Output:

Table tblToDos created.
Table tblToDos contains 0 records.
1 record(s) inserted. id: 2001
[
  {
    id: 2001,
    userId: 1001,
    todoCreated: 2022-09-09T21:24:24.000Z,
    todoModified: 2022-09-09T21:24:24.000Z,
    todoStatus: "Pending",
    todoTitle: "Shopping List #1",
    todoBody: "Apples & Oranges"
  }
]
1 record(s) updated.
[
  {
    id: 2001,
    userId: 1001,
    todoCreated: 2022-09-09T21:24:24.000Z,
    todoModified: 2022-09-09T21:24:24.000Z,
    todoStatus: "Complete",
    todoTitle: "Shopping List #1",
    todoBody: "Apples & Oranges"
  }
]

Querying database that requires SSL

When trying to connect to PlanetScale using the mysql2 library in Deno (https://deno.land/x/[email protected]) I can't figure out how not to get the server does not allow insecure connections, client must use SSL/TLS error message. The same code works fine when running in Node using mysql2 library.

The connection string I am using looks like:

{
host: hostname,
port: 3306,
user: username,
password: password,
database: database,
ssl: {rejectUnauthorized: true}
}

It appears that Deno does not recognize that SSL is to be used.

Node std issue in deno 1.31.1

https://deno.land/[email protected]/node/node_specifiers

This is now the recommended way to import node specifiers. Using deno 1.31.1 with this module results in:

error: Module not found "https://deno.land/std/node/buffer.ts" at https://deno.land/x/[email protected]/deps.ts:2:24

They are no longer publishing a node directory in the Deno standard library

文档介绍用法的内容太少了，比如CRUD

前端开发的人看这么点文档不知道如何使用的

import * as mysql2 from "https://deno.land/x/[email protected]/mod.ts";

const pool = mysql2.createPool({
  host: "127.0.0.1",
  port: 3306,
  user: "root",
  password: "root",
  // database: "denomysql",
  connectionLimit: 4,
});
const result = await pool.query("show databases;");
console.log(result[0]);
await pool.end();

和 https://github.com/denodrivers/mysql 的区别优势是什么？

如何处理mysql返回的数据？

我连接数据库后，查询得到[ { id: 1, todo: "study", isCompleted: 1 } ]，那么如何拿到todo的值呢，我用数组下标0索引拿报错了