Skip to main content
Using Deno in production at your company? Earn free Deno merch.
Give us feedback
Module

x/mysql/test.ts

MySQL driver for Deno
Extremely Popular
Latest
File
import { assertEquals, assertThrowsAsync, semver } from "./test.deps.ts";import { ConnnectionError, ResponseTimeoutError,} from "./src/constant/errors.ts";import { createTestDB, delay, isMariaDB, registerTests, testWithClient,} from "./test.util.ts";import { log as stdlog } from "./deps.ts";import { log } from "./src/logger.ts";import { configLogger } from "./mod.ts";
testWithClient(async function testCreateDb(client) { await client.query(`CREATE DATABASE IF NOT EXISTS enok`);});
testWithClient(async function testCreateTable(client) { await client.query(`DROP TABLE IF EXISTS users`); await client.query(` CREATE TABLE users ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(100) NOT NULL, is_top tinyint(1) default 0, created_at timestamp not null default current_timestamp, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; `);});
testWithClient(async function testInsert(client) { let result = await client.execute(`INSERT INTO users(name) values(?)`, [ "manyuanrong", ]); assertEquals(result, { affectedRows: 1, lastInsertId: 1 }); result = await client.execute(`INSERT INTO users ?? values ?`, [ ["id", "name"], [2, "MySQL"], ]); assertEquals(result, { affectedRows: 1, lastInsertId: 2 });});
testWithClient(async function testUpdate(client) { let result = await client.execute( `update users set ?? = ?, ?? = ? WHERE id = ?`, ["name", "MYR🦕", "created_at", new Date(), 1], ); assertEquals(result, { affectedRows: 1, lastInsertId: 0 });});
testWithClient(async function testQuery(client) { let result = await client.query( "select ??,`is_top`,`name` from ?? where id = ?", ["id", "users", 1], ); assertEquals(result, [{ id: 1, name: "MYR🦕", is_top: 0 }]);});
testWithClient(async function testQueryErrorOccurred(client) { assertEquals(client.pool, { size: 0, maxSize: client.config.poolSize, available: 0, }); await assertThrowsAsync( () => client.query("select unknownfield from `users`"), Error, ); await client.query("select 1"); assertEquals(client.pool, { size: 1, maxSize: client.config.poolSize, available: 1, });});
testWithClient(async function testQueryList(client) { const sql = "select ??,?? from ??"; let result = await client.query(sql, ["id", "name", "users"]); assertEquals(result, [ { id: 1, name: "MYR🦕" }, { id: 2, name: "MySQL" }, ]);});
testWithClient(async function testQueryTime(client) { const sql = `SELECT CAST("09:04:10" AS time) as time`; let result = await client.query(sql); assertEquals(result, [{ time: "09:04:10" }]);});
testWithClient(async function testQueryBigint(client) { await client.query(`DROP TABLE IF EXISTS test_bigint`); await client.query(`CREATE TABLE test_bigint ( id int(11) NOT NULL AUTO_INCREMENT, bigint_column bigint NOT NULL, PRIMARY KEY (id) ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4`);
const value = "9223372036854775807"; await client.execute( "INSERT INTO test_bigint(bigint_column) VALUES (?)", [value], );
const result = await client.query("SELECT bigint_column FROM test_bigint"); assertEquals(result, [{ bigint_column: BigInt(value) }]);});
testWithClient(async function testQueryDecimal(client) { await client.query(`DROP TABLE IF EXISTS test_decimal`); await client.query(`CREATE TABLE test_decimal ( id int(11) NOT NULL AUTO_INCREMENT, decimal_column decimal(65,30) NOT NULL, PRIMARY KEY (id) ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4`);
const value = "0.012345678901234567890123456789"; await client.execute( "INSERT INTO test_decimal(decimal_column) VALUES (?)", [value], );
const result = await client.query("SELECT decimal_column FROM test_decimal"); assertEquals(result, [{ decimal_column: value }]);});
testWithClient(async function testQueryDatetime(client) { await client.useConnection(async (connection) => { if (isMariaDB(connection) || semver.lt(connection.serverVersion, "5.6.0")) { return; }
await client.query(`DROP TABLE IF EXISTS test_datetime`); await client.query(`CREATE TABLE test_datetime ( id int(11) NOT NULL AUTO_INCREMENT, datetime datetime(6) NOT NULL, PRIMARY KEY (id) ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4`); const datetime = new Date(); await client.execute( ` INSERT INTO test_datetime (datetime) VALUES (?)`, [datetime], );
const [row] = await client.query("SELECT datetime FROM test_datetime"); assertEquals(row.datetime.toISOString(), datetime.toISOString()); // See https://github.com/denoland/deno/issues/6643 });});
testWithClient(async function testDelete(client) { let result = await client.execute(`delete from users where ?? = ?`, [ "id", 1, ]); assertEquals(result, { affectedRows: 1, lastInsertId: 0 });});
testWithClient(async function testPool(client) { assertEquals(client.pool, { maxSize: client.config.poolSize, available: 0, size: 0, }); const expect = new Array(10).fill([{ "1": 1 }]); const result = await Promise.all(expect.map(() => client.query(`select 1`)));
assertEquals(client.pool, { maxSize: client.config.poolSize, available: 3, size: 3, }); assertEquals(result, expect);});
testWithClient(async function testQueryOnClosed(client) { for (const i of [0, 0, 0]) { await assertThrowsAsync(async () => { await client.transaction(async (conn) => { conn.close(); await conn.query("SELECT 1"); }); }, ConnnectionError); } assertEquals(client.pool?.size, 0); await client.query("select 1");});
testWithClient(async function testTransactionSuccess(client) { const success = await client.transaction(async (connection) => { await connection.execute("insert into users(name) values(?)", [ "transaction1", ]); await connection.execute("delete from users where id = ?", [2]); return true; }); assertEquals(true, success); const result = await client.query("select name,id from users"); assertEquals([{ name: "transaction1", id: 3 }], result);});
testWithClient(async function testTransactionRollback(client) { let success; await assertThrowsAsync(async () => { success = await client.transaction(async (connection) => { // Insert an existing id await connection.execute("insert into users(name,id) values(?,?)", [ "transaction2", 3, ]); return true; }); }); assertEquals(undefined, success); const result = await client.query("select name from users"); assertEquals([{ name: "transaction1" }], result);});
testWithClient(async function testIdleTimeout(client) { assertEquals(client.pool, { maxSize: 3, available: 0, size: 0, }); await Promise.all(new Array(10).fill(0).map(() => client.query("select 1"))); assertEquals(client.pool, { maxSize: 3, available: 3, size: 3, }); await delay(500); assertEquals(client.pool, { maxSize: 3, available: 3, size: 3, }); await client.query("select 1"); await delay(500); assertEquals(client.pool, { maxSize: 3, available: 1, size: 1, }); await delay(500); assertEquals(client.pool, { maxSize: 3, available: 0, size: 0, });}, { idleTimeout: 750,});
testWithClient(async function testReadTimeout(client) { await client.execute("select sleep(0.3)");
await assertThrowsAsync(async () => { await client.execute("select sleep(0.7)"); }, ResponseTimeoutError);
assertEquals(client.pool, { maxSize: 3, available: 0, size: 0, });}, { timeout: 500,});
testWithClient(async function testLargeQueryAndResponse(client) { function buildLargeString(len: number) { let str = ""; for (let i = 0; i < len; i++) { str += i % 10; } return str; } const largeString = buildLargeString(512 * 1024); assertEquals( await client.query(`select "${largeString}" as str`), [{ str: largeString }], );});
testWithClient(async function testExecuteIterator(client) { await client.useConnection(async (conn) => { await conn.execute(`DROP TABLE IF EXISTS numbers`); await conn.execute(`CREATE TABLE numbers (num INT NOT NULL)`); await conn.execute( `INSERT INTO numbers (num) VALUES ${ new Array(64).fill(0).map((v, idx) => `(${idx})`).join(",") }`, ); const r = await conn.execute(`SELECT num FROM numbers`, [], true); let count = 0; for await (const row of r.iterator) { assertEquals(row.num, count); count++; } assertEquals(count, 64); });});
// For MySQL 8, the default auth plugin is `caching_sha2_password`. Create user// using `mysql_native_password` to test Authentication Method Mismatch.testWithClient(async function testCreateUserWithMysqlNativePassword(client) { const { version } = (await client.query(`SELECT VERSION() as version`))[0]; if (version.startsWith("8.")) { // MySQL 8 does not have `PASSWORD()` function await client.execute( `CREATE USER 'testuser'@'%' IDENTIFIED WITH mysql_native_password BY 'testpassword'`, ); } else { await client.execute( `CREATE USER 'testuser'@'%' IDENTIFIED WITH mysql_native_password`, ); await client.execute( `SET PASSWORD FOR 'testuser'@'%' = PASSWORD('testpassword')`, ); } await client.execute(`GRANT ALL ON test.* TO 'testuser'@'%'`);});
testWithClient(async function testConnectWithMysqlNativePassword(client) { assertEquals( await client.query(`SELECT CURRENT_USER() AS user`), [{ user: "testuser@%" }], );}, { username: "testuser", password: "testpassword" });
testWithClient(async function testDropUserWithMysqlNativePassword(client) { await client.execute(`DROP USER 'testuser'@'%'`);});
testWithClient(async function testSelectEmptyString(client) { assertEquals( await client.query(`SELECT '' AS a`), [{ a: "" }], ); assertEquals( await client.query(`SELECT '' AS a, '' AS b, '' AS c`), [{ a: "", b: "", c: "" }], ); assertEquals( await client.query(`SELECT '' AS a, 'b' AS b, '' AS c`), [{ a: "", b: "b", c: "" }], );});
registerTests();
Deno.test("configLogger()", async () => { let logCount = 0; const fakeHandler = new class extends stdlog.handlers.BaseHandler { constructor() { super("INFO"); } log(msg: string) { logCount++; } }();
await stdlog.setup({ handlers: { fake: fakeHandler, }, loggers: { mysql: { handlers: ["fake"], }, }, }); await configLogger({ logger: stdlog.getLogger("mysql") }); log.info("Test log"); assertEquals(logCount, 1);
await configLogger({ enable: false }); log.info("Test log"); assertEquals(logCount, 1);});
await createTestDB();
await new Promise((r) => setTimeout(r, 0));// Workaround to https://github.com/denoland/deno/issues/7844