office_spirit_mysql - MySQL and MariaDB driver for Deno.

// To download and run this example:
// export DSN='mysql://root:hello@localhost/tests'
// curl 'https://raw.githubusercontent.com/jeremiah-shaulov/office_spirit_mysql/v0.19.6/README.md' | perl -ne '$y=$1 if /^```(.)?/;  print $_ if $y&&$m;  $m=$y&&($m||m~<example-p9mn>~)' > /tmp/example-p9mn.ts
// deno run --allow-env --allow-net /tmp/example-p9mn.ts

import {MyPool} from 'https://deno.land/x/office_spirit_mysql@v0.19.6/mod.ts';

// Create a connections pool. This is the only way in this library to create server connections
await using pool = new MyPool(Deno.env.get('DSN') || 'mysql://root:hello@localhost/tests');

// Get a connection object. The actual TCP connection will be established on the first query
using conn = pool.getConn();

// Execute queries that don't return rows
await conn.query("CREATE TEMPORARY TABLE t_log (id integer PRIMARY KEY AUTO_INCREMENT, message text)");
await conn.query("INSERT INTO t_log (message) VALUES ('Message 1'), ('Message 2'), ('Message 3')");

// Execute query that returns rows
for await (const row of conn.query("SELECT * FROM t_log"))
{	console.log(row);
}

import {MyPool} from 'https://deno.land/x/office_spirit_mysql@v0.19.6/mod.ts';

await using pool = new MyPool(Deno.env.get('DSN') || 'mysql://root:hello@localhost/tests');
using conn = pool.getConn();
const version = await conn.queryCol("SELECT Version()").first();
console.log(version);

import {MyPool} from 'https://deno.land/x/office_spirit_mysql@v0.19.6/mod.ts';

await using pool = new MyPool(Deno.env.get('DSN') || 'mysql://root:hello@localhost/tests');
const version = await pool.forConn
(	async conn =>
    {	const version = await conn.queryCol("SELECT Version()").first();
        return version;
    }
);
console.log(version);

import {MyPool} from 'https://deno.land/x/office_spirit_mysql@v0.19.6/mod.ts';

await using pool = new MyPool(Deno.env.get('DSN') || 'mysql://root:hello@localhost/tests');
pool.forConn
(	async conn =>
    {	const version = await conn.queryCol("SELECT Version()").first();
        console.log(version);
    }
);

// To download and run this example:
// export DSN='mysql://root:hello@localhost/tests'
// curl 'https://raw.githubusercontent.com/jeremiah-shaulov/office_spirit_mysql/v0.19.6/README.md' | perl -ne '$y=$1 if /^```(.)?/;  print $_ if $y&&$m;  $m=$y&&($m||m~<example-65ya>~)' > /tmp/example-65ya.ts
// deno run --allow-env --allow-net /tmp/example-65ya.ts

import {MyPool, Dsn} from 'https://deno.land/x/office_spirit_mysql@v0.19.6/mod.ts';
import {assert} from 'https://deno.land/std@0.224.0/assert/assert.ts';
import {assertEquals} from 'https://deno.land/std@0.224.0/assert/assert_equals.ts';

const dsn1 = new Dsn(Deno.env.get('DSN') || 'mysql://root:hello@localhost');
const dsn2 = new Dsn(dsn1); // copy
dsn2.schema = 'information_schema'; // change schema

await using pool = new MyPool(dsn1); // `dsn1` will be the default DSN for this pool
using session = pool.getSession();

const conn1 = session.conn(); // default DSN
const conn2 = session.conn(); // the same object
const conn3 = session.conn(undefined, true); // another connection to default DSN
const conn4 = session.conn(dsn2); // connection to different DSN

assert(conn1 == conn2);
assert(conn2 != conn3);

const connId2 = conn2.queryCol("SELECT Connection_id()").first();
const connId3 = conn3.queryCol("SELECT Connection_id()").first();
const connId4 = conn4.queryCol("SELECT Connection_id()").first();

const connIds = await Promise.all([connId2, connId3, connId4]);
console.log(connIds); // prints 3 different connection ids
assertEquals(new Set(connIds).size, 3);

// To download and run this example:
// export DSN='mysql://root:hello@localhost/tests'
// curl 'https://raw.githubusercontent.com/jeremiah-shaulov/office_spirit_mysql/v0.19.6/README.md' | perl -ne '$y=$1 if /^```(.)?/;  print $_ if $y&&$m;  $m=$y&&($m||m~<example-pf4z>~)' > /tmp/example-pf4z.ts
// deno run --allow-env --allow-net /tmp/example-pf4z.ts

import {MyPool} from 'https://deno.land/x/office_spirit_mysql@v0.19.6/mod.ts';

await using pool = new MyPool(Deno.env.get('DSN') || 'mysql://root:hello@localhost/tests');
using conn = pool.getConn();

await conn.queryVoid("CREATE TEMPORARY TABLE t_log (id integer PRIMARY KEY AUTO_INCREMENT, message text)");
await conn.queryVoid("INSERT INTO t_log (message) VALUES ('Message 1'), ('Message 2'), ('Message 3')");

// use ResultsetsPromise.all()
console.log(await conn.query("SELECT * FROM t_log").all());

// use Resultsets.all()
const res = await conn.query("SELECT * FROM t_log");
console.log(res.columns);
console.log(await res.all());

// To download and run this example:
// export DSN='mysql://root:hello@localhost/tests'
// curl 'https://raw.githubusercontent.com/jeremiah-shaulov/office_spirit_mysql/v0.19.6/README.md' | perl -ne '$y=$1 if /^```(.)?/;  print $_ if $y&&$m;  $m=$y&&($m||m~<example-ksv8>~)' > /tmp/example-ksv8.ts
// deno run --allow-env --allow-net /tmp/example-ksv8.ts

import {MyPool} from 'https://deno.land/x/office_spirit_mysql@v0.19.6/mod.ts';

await using pool = new MyPool(Deno.env.get('DSN') || 'mysql://root:hello@localhost/tests');
using conn = pool.getConn();

await conn.queryVoid("CREATE TEMPORARY TABLE t_log (id integer PRIMARY KEY AUTO_INCREMENT, message text)");
await conn.queryVoid("INSERT INTO t_log (message) VALUES ('Message 1'), ('Message 2'), ('Message 3')");

// use ResultsetsPromise.first()
console.log(await conn.query("SELECT Count(*) FROM t_log").first());

// use Resultsets.first()
const res = await conn.query("SELECT Count(*) FROM t_log");
console.log(res.columns);
console.log(await res.first());

// To download and run this example:
// export DSN='mysql://root:hello@localhost/tests'
// curl 'https://raw.githubusercontent.com/jeremiah-shaulov/office_spirit_mysql/v0.19.6/README.md' | perl -ne '$y=$1 if /^```(.)?/;  print $_ if $y&&$m;  $m=$y&&($m||m~<example-rlut>~)' > /tmp/example-rlut.ts
// deno run --allow-env --allow-net /tmp/example-rlut.ts

import {MyPool} from 'https://deno.land/x/office_spirit_mysql@v0.19.6/mod.ts';

await using pool = new MyPool(Deno.env.get('DSN') || 'mysql://root:hello@localhost/tests');
using conn = pool.getConn();

await conn.queryVoid("CREATE TEMPORARY TABLE t_log (id integer PRIMARY KEY AUTO_INCREMENT, message text)");
await conn.queryVoid("INSERT INTO t_log (message) VALUES ('Message 1'), ('Message 2'), ('Message 3')");

// for await loop
for await (const row of conn.query("SELECT * FROM t_log"))
{	console.log(row);
}

// ResultsetsPromise.forEach()
await conn.query("SELECT * FROM t_log").forEach
(	row =>
    {	console.log(row);
    }
);

// To download and run this example:
// export DSN='mysql://root:hello@localhost/tests'
// curl 'https://raw.githubusercontent.com/jeremiah-shaulov/office_spirit_mysql/v0.19.6/README.md' | perl -ne '$y=$1 if /^```(.)?/;  print $_ if $y&&$m;  $m=$y&&($m||m~<example-ajdy>~)' > /tmp/example-ajdy.ts
// deno run --allow-env --allow-net /tmp/example-ajdy.ts

import {MyPool} from 'https://deno.land/x/office_spirit_mysql@v0.19.6/mod.ts';
import {assertEquals} from 'https://deno.land/std@0.224.0/assert/assert_equals.ts';

await using pool = new MyPool(Deno.env.get('DSN') || 'mysql://root:hello@localhost/tests');
using conn = pool.getConn();

await conn.queryVoid("CREATE TEMPORARY TABLE t_log (id integer PRIMARY KEY AUTO_INCREMENT, message text)");
await conn.queryVoid("INSERT INTO t_log (message) VALUES ('Message 1'), ('Message 2'), ('Message 3')");

const count = await conn.queryCol("SELECT Count(*) FROM t_log").first();
console.log(count); // prints 3
assertEquals(count, 3);

MyConn.queryVoid(sql: SqlSource, params?: Params): Promise<Resultsets<void>> {...}
MyConn.query<ColumnType=ColumnValue>(sql: SqlSource, params?: Params): ResultsetsPromise<Record<string, ColumnType>> {...}
MyConn.queryMap<ColumnType=ColumnValue>(sql: SqlSource, params?: Params): ResultsetsPromise<Map<string, ColumnType>> {...}
MyConn.queryArr<ColumnType=ColumnValue>(sql: SqlSource, params?: Params): ResultsetsPromise<ColumnType[]> {...}
MyConn.queryCol<ColumnType=ColumnValue>(sql: SqlSource, params?: Params): ResultsetsPromise<ColumnType> {...}

type SqlSource =
    string |
    Uint8Array |
    ({readonly readable: ReadableStream<Uint8Array>} | Deno.Reader) & ({readonly size: number} | Deno.Seeker) |
    ToSqlBytes;
type Params = any[] | Record<string, any> | null;
class ResultsetsPromise<Row> extends Promise<Resultsets<Row>> {...}
type ColumnValue = bigint | Date | Uint8Array | JsonNode;
type JsonNode = null | boolean | number | string | JsonNode[] | {[member: string]: JsonNode};

// To download and run this example:
// export DSN='mysql://root:hello@localhost/tests'
// curl 'https://raw.githubusercontent.com/jeremiah-shaulov/office_spirit_mysql/v0.19.6/README.md' | perl -ne '$y=$1 if /^```(.)?/;  print $_ if $y&&$m;  $m=$y&&($m||m~<example-7bvr>~)' > /tmp/example-7bvr.ts
// deno run --allow-env --allow-net /tmp/example-7bvr.ts

import {MyPool, ColumnValue} from 'https://deno.land/x/office_spirit_mysql@v0.19.6/mod.ts';
import {assertEquals} from 'https://deno.land/std@0.224.0/assert/assert_equals.ts';

await using pool = new MyPool(Deno.env.get('DSN') || 'mysql://root:hello@localhost/tests');
using conn = pool.getConn();

await conn.queryVoid("CREATE TEMPORARY TABLE t_log (id integer PRIMARY KEY AUTO_INCREMENT, message text)");
await conn.queryVoid("INSERT INTO t_log (message) VALUES ('Message 1'), ('Message 2'), ('Message 3')");

const row = await conn.query("SELECT * FROM t_log WHERE id=1").first();
if (row)
{	// The type of `row` here is `Record<string, ColumnValue>`
    let message = '';
    // Remember that the `message` column can also be null
    if (typeof(row.message) == 'string') // Without this check, the error will be: Type 'ColumnValue' is not assignable to type 'string'
    {	message = row.message;
    }
    console.log(message); // Prints 'Message 1'
    assertEquals(message, 'Message 1');
}

// To download and run this example:
// export DSN='mysql://root:hello@localhost/tests'
// curl 'https://raw.githubusercontent.com/jeremiah-shaulov/office_spirit_mysql/v0.19.6/README.md' | perl -ne '$y=$1 if /^```(.)?/;  print $_ if $y&&$m;  $m=$y&&($m||m~<example-dbu0>~)' > /tmp/example-dbu0.ts
// deno run --allow-env --allow-net /tmp/example-dbu0.ts

import {MyPool} from 'https://deno.land/x/office_spirit_mysql@v0.19.6/mod.ts';
import {assertEquals} from 'https://deno.land/std@0.224.0/assert/assert_equals.ts';

await using pool = new MyPool(Deno.env.get('DSN') || 'mysql://root:hello@localhost/tests');
using conn = pool.getConn();

await conn.queryVoid("CREATE TEMPORARY TABLE t_log (id integer PRIMARY KEY AUTO_INCREMENT, message text)");
await conn.queryVoid("INSERT INTO t_log (message) VALUES ('Message 1'), ('Message 2'), ('Message 3')");

// Use query<any>()
const row = await conn.query<any>("SELECT * FROM t_log WHERE id=1").first();
if (row)
{	// The type of `row` here is `Record<string, any>`
    const message: string = row.message;
    console.log(message); // Prints 'Message 1'
    assertEquals(message, 'Message 1');
}

const dsn1 =  new Dsn('mysql://app:app@localhost');
const dsn2 =  new Dsn('mysql://app:app@localhost/?datesAsString');
const dsn3 =  new Dsn(`mysql://app:app@localhost/?correctDates#SET NAMES utf8mb4, collation_connection='utf8mb4_unicode_ci', sql_mode='', time_zone='UTC'`);

const objectDate = new Date(YYYY, MM, DD, HH, MM, SS, mmm);
const stringDate = `${d.getFullYear()}-${d.getMonth()+1}-${d.getDate()} ${d.getHours()}:${d.getMinutes()}:${d.getSeconds()}.${(d.getMilliseconds()+'').padStart(3, '0')}`;

// To download and run this example:
// export DSN='mysql://root:hello@localhost/tests'
// curl 'https://raw.githubusercontent.com/jeremiah-shaulov/office_spirit_mysql/v0.19.6/README.md' | perl -ne '$y=$1 if /^```(.)?/;  print $_ if $y&&$m;  $m=$y&&($m||m~<example-2dm1>~)' > /tmp/example-2dm1.ts
// deno run --allow-env --allow-net /tmp/example-2dm1.ts

import {MyPool} from 'https://deno.land/x/office_spirit_mysql@v0.19.6/mod.ts';

await using pool = new MyPool(Deno.env.get('DSN') || 'mysql://root:hello@localhost/tests');
using conn = pool.getConn();

await conn.query("SET time_zone = 'UTC'");

const refDate = new Date(2022, 2, 17, 10, 0, 0);
const dateBack = await conn.queryCol("SELECT From_unixtime(@t / 1000)", {t: refDate.getTime()}).first();

console.log(refDate);
console.log(dateBack);

// To download and run this example:
// export DSN='mysql://root:hello@localhost/tests'
// curl 'https://raw.githubusercontent.com/jeremiah-shaulov/office_spirit_mysql/v0.19.6/README.md' | perl -ne '$y=$1 if /^```(.)?/;  print $_ if $y&&$m;  $m=$y&&($m||m~<example-aymx>~)' > /tmp/example-aymx.ts
// deno run --allow-env --allow-net /tmp/example-aymx.ts

import {Dsn, MyPool} from 'https://deno.land/x/office_spirit_mysql@v0.19.6/mod.ts';

const dsn = new Dsn(Deno.env.get('DSN') || 'mysql://root:hello@localhost/tests');
dsn.correctDates = true; // THE DIFFERENCE IS HERE
await using pool = new MyPool(dsn);
using conn = pool.getConn();

await conn.query("SET time_zone = 'UTC'");

const refDate = new Date(2022, 2, 17, 10, 0, 0);
const dateBack = await conn.queryCol("SELECT From_unixtime(@t / 1000)", {t: refDate.getTime()}).first();

console.log(refDate);
console.log(dateBack);

// To download and run this example:
// export DSN='mysql://root:hello@localhost/tests'
// curl 'https://raw.githubusercontent.com/jeremiah-shaulov/office_spirit_mysql/v0.19.6/README.md' | perl -ne '$y=$1 if /^```(.)?/;  print $_ if $y&&$m;  $m=$y&&($m||m~<example-10go>~)' > /tmp/example-10go.ts
// deno run --allow-env --allow-net /tmp/example-10go.ts

import {MyPool} from 'https://deno.land/x/office_spirit_mysql@v0.19.6/mod.ts';

await using pool = new MyPool(Deno.env.get('DSN') || 'mysql://root:hello@localhost/tests');
using conn = pool.getConn();

await conn.queryVoid("CREATE TEMPORARY TABLE t_log (id integer PRIMARY KEY AUTO_INCREMENT, `time` timestamp, message text)");
await conn.queryVoid("INSERT INTO t_log SET `time`=Now(), message='Message 1'");

const row = await conn.query("SELECT `time` + INTERVAL ? DAY AS 'time', message FROM t_log WHERE id=?", [3, 1]).first();
console.log(row);

// To download and run this example:
// export DSN='mysql://root:hello@localhost/tests'
// curl 'https://raw.githubusercontent.com/jeremiah-shaulov/office_spirit_mysql/v0.19.6/README.md' | perl -ne '$y=$1 if /^```(.)?/;  print $_ if $y&&$m;  $m=$y&&($m||m~<example-1f17>~)' > /tmp/example-1f17.ts
// deno run --allow-env --allow-net /tmp/example-1f17.ts

import {MyPool} from 'https://deno.land/x/office_spirit_mysql@v0.19.6/mod.ts';

await using pool = new MyPool(Deno.env.get('DSN') || 'mysql://root:hello@localhost/tests');
using conn = pool.getConn();

await conn.queryVoid("CREATE TEMPORARY TABLE t_log (id integer PRIMARY KEY AUTO_INCREMENT, `time` timestamp, message text)");
await conn.queryVoid("INSERT INTO t_log SET `time`=Now(), message='Message 1'");

const row = await conn.query("SELECT `time` + INTERVAL @days DAY AS 'time', message FROM t_log WHERE id=@`id`", {days: 3, id: 1}).first();
console.log(row);

interface ToSqlBytes
{	toSqlBytesWithParamsBackslashAndBuffer(putParamsTo: any[]|undefined, noBackslashEscapes: boolean, buffer: Uint8Array): Uint8Array;
}

// To download and run this example:
// export DSN='mysql://root:hello@localhost/tests'
// curl 'https://raw.githubusercontent.com/jeremiah-shaulov/office_spirit_mysql/v0.19.6/README.md' | perl -ne '$y=$1 if /^```(.)?/;  print $_ if $y&&$m;  $m=$y&&($m||m~<example-855m>~)' > /tmp/example-855m.ts
// deno run --allow-env --allow-net /tmp/example-855m.ts

import {MyPool} from 'https://deno.land/x/office_spirit_mysql@v0.19.6/mod.ts';

// 1. Define the generator

const encoder = new TextEncoder;

// Generates SELECT query for demonstrational purposes only
class SqlSelectGenerator
{	constructor(private table: string, private idValue: number)
    {
    }

    toSqlBytesWithParamsBackslashAndBuffer(putParamsTo: any[]|undefined, noBackslashEscapes: boolean, buffer: Uint8Array)
    {	let sql;
        if (putParamsTo)
        {	putParamsTo.push(this.idValue);
            sql = `SELECT * FROM ${this.table} WHERE id = ?`;
        }
        else
        {	sql = `SELECT * FROM ${this.table} WHERE id = ${this.idValue}`;
        }
        const {read, written} = encoder.encodeInto(sql, buffer);
        if (read == sql.length)
        {	return buffer.subarray(0, written);
        }
        return encoder.encode(sql);
    }
}

// 2. Use the generator

await using pool = new MyPool(Deno.env.get('DSN') || 'mysql://root:hello@localhost/tests');
using conn = pool.getConn();

await conn.query("CREATE TEMPORARY TABLE t_log (id integer PRIMARY KEY AUTO_INCREMENT, `time` timestamp, message text)");
await conn.query("INSERT INTO t_log SET `time`=Now(), message='message'");

const rows = await conn.query<any>(new SqlSelectGenerator('t_log', 1), []).all();
console.log(rows);

// To download and run this example:
// export DSN='mysql://root:hello@localhost/tests'
// curl 'https://raw.githubusercontent.com/jeremiah-shaulov/office_spirit_mysql/v0.19.6/README.md' | perl -ne '$y=$1 if /^```(.)?/;  print $_ if $y&&$m;  $m=$y&&($m||m~<example-t6u5>~)' > /tmp/example-t6u5.ts
// deno run --allow-env --allow-net /tmp/example-t6u5.ts

import {MyPool} from 'https://deno.land/x/office_spirit_mysql@v0.19.6/mod.ts';

const N_ROWS = 100;
const N_QUERIES = 800;

await using pool = new MyPool(Deno.env.get('DSN') || 'mysql://root:hello@localhost/tests');
using conn = pool.getConn();

// CREATE DATABASE
await conn.query("DROP DATABASE IF EXISTS test1");
await conn.query("CREATE DATABASE `test1`");

// USE
await conn.query("USE test1");

// CREATE TABLE
await conn.query("CREATE TABLE t_log (id integer PRIMARY KEY AUTO_INCREMENT, val integer)");

// INSERT
let sql = "INSERT INTO t_log (val) VALUES (0)";
for (let i=1; i<N_ROWS; i++)
{	sql += `,(${i})`;
}
await conn.queryVoid(sql);

// Begin tests
console.log('Begin tests');

// Text Protocol
let since = Date.now();
let sum = 0;
for (let i=0; i<N_QUERIES; i++)
{	const n = 1 + Math.floor(Math.random() * N_ROWS);
    sum += Number(await conn.queryCol("SELECT val FROM t_log WHERE id = "+n).first());
}
console.log(`Text Protocol took ${(Date.now()-since) / 1000} sec (random=${sum})`);

// Named params
since = Date.now();
sum = 0;
for (let i=0; i<N_QUERIES; i++)
{	const n = 1 + Math.floor(Math.random() * N_ROWS);
    sum += Number(await conn.queryCol("SELECT val FROM t_log WHERE id = @n", {n}).first());
}
console.log(`Named params took ${(Date.now()-since) / 1000} sec (random=${sum})`);

// Positional params
since = Date.now();
sum = 0;
for (let i=0; i<N_QUERIES; i++)
{	const n = 1 + Math.floor(Math.random() * N_ROWS);
    sum += Number(await conn.queryCol("SELECT val FROM t_log WHERE id = ?", [n]).first());
}
console.log(`Positional params took ${(Date.now()-since) / 1000} sec (random=${sum})`);

// Positional params prepared once
since = Date.now();
sum = 0;
await using stmt = await conn.prepareCol("SELECT val FROM t_log WHERE id = ?");
for (let i=0; i<N_QUERIES; i++)
{	const n = 1 + Math.floor(Math.random() * N_ROWS);
    sum += Number(await stmt.exec([n]).first());
}
console.log(`Positional params prepared once took ${(Date.now()-since) / 1000} sec (random=${sum})`);

// Drop database that i created
await conn.query("DROP DATABASE test1");

Begin tests
Text Protocol took 0.281 sec (random=39308)
Named params took 0.387 sec (random=38258)
Positional params took 0.433 sec (random=39652)
Positional params prepared once took 0.221 sec (random=38693)

// To download and run this example:
// export DSN='mysql://root:hello@localhost/tests'
// curl 'https://raw.githubusercontent.com/jeremiah-shaulov/office_spirit_mysql/v0.19.6/README.md' | perl -ne '$y=$1 if /^```(.)?/;  print $_ if $y&&$m;  $m=$y&&($m||m~<example-wr6k>~)' > /tmp/example-wr6k.ts
// deno run --allow-env --allow-net /tmp/example-wr6k.ts

import {MyPool} from 'https://deno.land/x/office_spirit_mysql@v0.19.6/mod.ts';
import {assertEquals} from 'https://deno.land/std@0.224.0/assert/assert_equals.ts';

await using pool = new MyPool(Deno.env.get('DSN') || 'mysql://root:hello@localhost/tests');
using conn = pool.getConn();

// CREATE TABLE
await conn.query("CREATE TEMPORARY TABLE t_messages (id integer PRIMARY KEY AUTO_INCREMENT, message text)");

// INSERT
await using prepared = await conn.prepare("INSERT INTO t_messages SET message=?");
for (let i=1; i<=3; i++)
{	await prepared.exec(['Message '+i]);
}

// SELECT
const rows = await conn.query("SELECT * FROM t_messages").all();
console.log(rows);
assertEquals
(	rows,
    [	{id: 1, message: 'Message 1'},
        {id: 2, message: 'Message 2'},
        {id: 3, message: 'Message 3'},
    ]
);

Resultsets<Row>.exec(params: any[]): ResultsetsPromise<Row>

// To download and run this example:
// export DSN='mysql://root:hello@localhost/tests'
// curl 'https://raw.githubusercontent.com/jeremiah-shaulov/office_spirit_mysql/v0.19.6/README.md' | perl -ne '$y=$1 if /^```(.)?/;  print $_ if $y&&$m;  $m=$y&&($m||m~<example-np7j>~)' > /tmp/example-np7j.ts
// deno run --allow-env --allow-net /tmp/example-np7j.ts

import {MyPool} from 'https://deno.land/x/office_spirit_mysql@v0.19.6/mod.ts';

await using pool = new MyPool(Deno.env.get('DSN') || 'mysql://root:hello@localhost/tests');
using conn = pool.getConn();

await conn.query("CREATE TEMPORARY TABLE t_log (id integer PRIMARY KEY AUTO_INCREMENT, `time` timestamp, message text)");
await conn.query("INSERT INTO t_log SET `time`=Now(), message='long long message'");

const row = await conn.makeLastColumnReadable("SELECT `time`, message FROM t_log WHERE id=1");
if (row?.message instanceof ReadableStream)
{	await row.message.pipeTo(Deno.stdout.writable, {preventClose: true});
}

// To download and run this example:
// export DSN='mysql://root:hello@localhost/tests'
// curl 'https://raw.githubusercontent.com/jeremiah-shaulov/office_spirit_mysql/v0.19.6/README.md' | perl -ne '$y=$1 if /^```(.)?/;  print $_ if $y&&$m;  $m=$y&&($m||m~<example-7xum>~)' > /tmp/example-7xum.ts
// deno run --allow-env --allow-net /tmp/example-7xum.ts

import {MyPool} from 'https://deno.land/x/office_spirit_mysql@v0.19.6/mod.ts';

await using pool = new MyPool(Deno.env.get('DSN') || 'mysql://root:hello@localhost/tests');
using conn = pool.getConn();

await conn.queryVoid("CREATE TEMPORARY TABLE t_log (id integer PRIMARY KEY AUTO_INCREMENT, `time` timestamp, message text)");

using file = await Deno.open('/etc/passwd', {read: true});
// Write the file to db
await conn.queryVoid("INSERT INTO t_log SET `time`=Now(), message=?", [file.readable]);

// Read the contents back from db
const row = await conn.makeLastColumnReadable("SELECT `time`, message FROM t_log WHERE id=1");
if (row?.message instanceof ReadableStream)
{	await row.message.pipeTo(Deno.stdout.writable, {preventClose: true});
}

MyConn.query(sql: SqlSource, params?: object|null): ResultsetsPromise;

type SqlSource =
    string |
    Uint8Array |
    ({readonly readable: ReadableStream<Uint8Array>} | Deno.Reader) & ({readonly size: number} | Deno.Seeker) |
    ToSqlBytes;

// To download and run this example:
// export DSN='mysql://root:hello@localhost/tests'
// curl 'https://raw.githubusercontent.com/jeremiah-shaulov/office_spirit_mysql/v0.19.6/README.md' | perl -ne '$y=$1 if /^```(.)?/;  print $_ if $y&&$m;  $m=$y&&($m||m~<example-h9zl>~)' > /tmp/example-h9zl.ts
// deno run --allow-env --allow-net /tmp/example-h9zl.ts

import {MyPool} from 'https://deno.land/x/office_spirit_mysql@v0.19.6/mod.ts';

await using pool = new MyPool(Deno.env.get('DSN') || 'mysql://root:hello@localhost/tests');
using conn = pool.getConn();

const filename = await Deno.makeTempFile();
try
{	await Deno.writeTextFile
    (	filename,
        `	CREATE TEMPORARY TABLE t_log (id integer PRIMARY KEY AUTO_INCREMENT, c_time timestamp, message text);

            INSERT INTO t_log SET c_time=Now(), message='long long message';
        `
    );

    using file = await Deno.open(filename, {read: true});
    await conn.queriesVoid(file);

    console.log(await conn.query("SELECT c_time, message FROM t_log").all());
}
finally
{	await Deno.remove(filename);
}

// To download and run this example:
// export DSN='mysql://root:hello@localhost/tests'
// curl 'https://raw.githubusercontent.com/jeremiah-shaulov/office_spirit_mysql/v0.19.6/README.md' | perl -ne '$y=$1 if /^```(.)?/;  print $_ if $y&&$m;  $m=$y&&($m||m~<example-khw0>~)' > /tmp/example-khw0.ts
// deno run --allow-env --allow-net /tmp/example-khw0.ts

import {MyPool} from 'https://deno.land/x/office_spirit_mysql@v0.19.6/mod.ts';
import {dirname} from "https://deno.land/std@0.224.0/path/mod.ts";

await using pool = new MyPool(Deno.env.get('DSN') || 'mysql://root:hello@localhost/tests');

// Set handler for LOAD DATA LOCAL INFILE queries
const ALLOWED_DIRS = ['/tmp'];
pool.options
(	{	onLoadFile(filename: string)
        {	if (ALLOWED_DIRS.includes(dirname(filename)))
            {	return Deno.open(filename, {read: true});
            }
            else
            {	return Promise.resolve(undefined);
            }
        }
    }
);

// Download some public example CSV file from github
const filename = await Deno.makeTempFile();
const data = await fetch('https://raw.githubusercontent.com/lukes/ISO-3166-Countries-with-Regional-Codes/master/all/all.csv');
await Deno.writeFile(filename, data.body ?? new Uint8Array);

using conn = pool.getConn();

// Create temporary table, load the data to it, and then select it back:

// CREATE TABLE
await conn.queryVoid
(	`	CREATE TEMPORARY TABLE t_countries
        (	country_code char(2) CHARACTER SET latin1 NOT NULL PRIMARY KEY,
            country_name varchar(128) NOT NULL
        )
    `
);

// SQL-quote filename, because `LOAD DATA LOCAL INFILE` doesn't accept parameters
const filenameSql = await conn.queryCol("SELECT Quote(?)", [filename]).first();

// LOAD DATA
const res = await conn.queryVoid
(	`	LOAD DATA LOCAL INFILE ${filenameSql}
        INTO TABLE t_countries
        FIELDS TERMINATED BY ','
        ENCLOSED BY '"'
        IGNORE 1 LINES
        (@name, @code)
        SET
            country_code = @code,
            country_name = @name
    `
);
console.log(res.statusInfo);

// SELECT
console.log(await conn.query("SELECT * FROM t_countries LIMIT 3").all());

// To download and run this example:
// export DSN='mysql://root:hello@localhost/tests'
// curl 'https://raw.githubusercontent.com/jeremiah-shaulov/office_spirit_mysql/v0.19.6/README.md' | perl -ne '$y=$1 if /^```(.)?/;  print $_ if $y&&$m;  $m=$y&&($m||m~<example-rjwg>~)' > /tmp/example-rjwg.ts
// deno run --allow-env --allow-net /tmp/example-rjwg.ts

import {MyPool} from 'https://deno.land/x/office_spirit_mysql@v0.19.6/mod.ts';
import {assertEquals} from 'https://deno.land/std@0.224.0/assert/assert_equals.ts';

await using pool = new MyPool(Deno.env.get('DSN') || 'mysql://root:hello@localhost/tests');
using conn = pool.getConn();

const resultsets = await conn.queries
(	`	CREATE TEMPORARY TABLE t_log (id integer PRIMARY KEY AUTO_INCREMENT, message text);

        INSERT INTO t_log (message) VALUES ('Message 1'), ('Message 2'), ('Message 3');

        SELECT * FROM t_log;
    `
);

assertEquals(resultsets.affectedRows, 0);

await resultsets.nextResultset();
assertEquals(resultsets.affectedRows, 3);

await resultsets.nextResultset();
assertEquals(resultsets.columns.length, 2);

for await (const row of resultsets)
{	console.log(row);
}

function MyConn.use(schema: string): void;

// To download and run this example:
// export DSN='mysql://root:hello@localhost/tests'
// curl 'https://raw.githubusercontent.com/jeremiah-shaulov/office_spirit_mysql/v0.19.6/README.md' | perl -ne '$y=$1 if /^```(.)?/;  print $_ if $y&&$m;  $m=$y&&($m||m~<example-c5vl>~)' > /tmp/example-c5vl.ts
// deno run --allow-env --allow-net /tmp/example-c5vl.ts

import {MyPool} from 'https://deno.land/x/office_spirit_mysql@v0.19.6/mod.ts';
import {assertEquals} from 'https://deno.land/std@0.224.0/assert/assert_equals.ts';

await using pool = new MyPool(Deno.env.get('DSN') || 'mysql://root:hello@localhost/tests');
using conn = pool.getConn();

// Enable SQL logger
conn.setSqlLogger(true);

// CREATE DATABASE
await conn.query("DROP DATABASE IF EXISTS test1");
await conn.query("CREATE DATABASE `test1`");

// USE
conn.use("test1");

// CREATE TABLE
await conn.query("CREATE TABLE t_log (id integer PRIMARY KEY AUTO_INCREMENT, message text)");

// INSERT
await conn.query("INSERT INTO t_log SET message = 'Message 1'");

const result = await conn.queryCol("SELECT message FROM t_log WHERE id = @n", {n: 1}).first();

// Drop database that i created
await conn.query("DROP DATABASE test1");

assertEquals(result, 'Message 1');

interface SqlLogger
{	// A new connection established.
    connect?: (dsn: Dsn, connectionId: number) => Promise<unknown>;

    // Connection state reset (before returning this connection to it's pool).
    resetConnection?: (dsn: Dsn, connectionId: number) => Promise<unknown>;

    // Disconnected.
    disconnect?: (dsn: Dsn, connectionId: number) => Promise<unknown>;

    // Started to send a new query to the server.
    // `isPrepare` means that this is query preparation operation (the query is not executed, but stored on the server).
    // This function can return object that implements `SqlLoggerQuery` for further logging the query process.
    // Query SQL (if any) will be handed to the methods of `SqlLoggerQuery`.
    query?: (dsn: Dsn, connectionId: number, isPrepare: boolean, noBackslashEscapes: boolean) => Promise<SqlLoggerQuery | undefined>;

    // Deallocated prepared query or multiple queries indentified by their `stmtIds`.
    deallocatePrepare?: (dsn: Dsn, connectionId: number, stmtIds: number[]) => Promise<unknown>;

    // This callback is called when current `MyConn` object is disposed of. This happens at the end of `MyPool.forConn()`, or at the end of a block with `using conn = ...`.
    dispose?: () => Promise<unknown>;
}

// 1. In the beginning one of `appendToQuery()` or `setStmtId()` is called.
// To start writing a regular query, `appendToQuery()` is called one or multiple times.
// To write a prepared query, `setStmtId()` is called (once).
// 2. Then, in case of prepared query, a sequence of `appendToParam()` (one or multiple times) and `paramEnd()` can be called.
// 3. Then, if writing queries batch, `nextQuery()` is called, and the process repeats from the beginning.
// 4. Then, after all the queries in batch are written, `start()` is called. At this point queries are sent to the database server.
// 5. Then, when the server responds, `end()` is called.
interface SqlLoggerQuery
{	appendToQuery?: (data: Uint8Array) => Promise<unknown>;

    setStmtId?: (stmtId: number) => Promise<unknown>;

    appendToParam?: (nParam: number, data: Uint8Array|number|bigint) => Promise<unknown>;

    paramEnd?: (nParam: number) => Promise<unknown>;

    nextQuery?: () => Promise<unknown>;

    start?: () => Promise<unknown>;

    // If this was query preparation (`SqlLogger.query(_, _, true)`), `stmtId` will be the statement ID that the server returned.
    // Else `stmtId` will be `-1`.
    end?: (result: Resultsets<unknown>|Error|undefined, stmtId: number) => Promise<unknown>;
}

conn.setSqlLogger(true);

// Is the same as:

conn.setSqlLogger(new SqlLogToWritable(Deno.stderr.writable, !Deno.noColor, 10_000, 3_000, 100));

// To download and run this example:
// export DSN='mysql://root:hello@localhost/tests'
// curl 'https://raw.githubusercontent.com/jeremiah-shaulov/office_spirit_mysql/v0.19.6/README.md' | perl -ne '$y=$1 if /^```(.)?/;  print $_ if $y&&$m;  $m=$y&&($m||m~<example-mbya>~)' > /tmp/example-mbya.ts
// deno run --allow-env --allow-net /tmp/example-mbya.ts

import {MyPool, SqlLogToWritable} from 'https://deno.land/x/office_spirit_mysql@v0.19.6/mod.ts';
import {assertEquals} from 'https://deno.land/std@0.224.0/assert/assert_equals.ts';

await using pool = new MyPool(Deno.env.get('DSN') || 'mysql://root:hello@localhost/tests');

const LOG_FILE = '/tmp/sql.log';

class SqlLogToFile extends SqlLogToWritable
{	protected disposable: Disposable;

    private constructor(fileLike: {readonly writable: WritableStream<Uint8Array>}&Disposable, withColor=false)
    {	super(fileLike.writable, withColor);
        this.disposable = fileLike;
    }

    static async inst(path: string|URL, withColor=false)
    {	const fd = await Deno.open(path, {write: true, create: true, truncate: true});
        return new SqlLogToFile(fd, withColor);
    }

    async dispose()
    {	try
        {	await super.dispose();
        }
        finally
        {	this.disposable[Symbol.dispose]();
        }
    }
}

using conn = pool.getConn();

// Enable SQL logger
conn.setSqlLogger(await SqlLogToFile.inst(LOG_FILE, !Deno.noColor));

// CREATE DATABASE
await conn.query("DROP DATABASE IF EXISTS test1");
await conn.query("CREATE DATABASE `test1`");

// USE
conn.use("test1");

// CREATE TABLE
await conn.query("CREATE TABLE t_log (id integer PRIMARY KEY AUTO_INCREMENT, message text)");

// INSERT
await conn.query("INSERT INTO t_log SET message = 'Message 1'");

const result = await conn.queryCol("SELECT message FROM t_log WHERE id = @n", {n: 1}).first();

// Drop database that i created
await conn.query("DROP DATABASE test1");

assertEquals(result, 'Message 1');
console.log(`See log in ${LOG_FILE}`);

// Commit current transaction (if any), and start new.
// This is lazy operation. The corresponding command will be sent to the server later (however commit of the current transaction will happen immediately).
// To start regular transaction, call `startTrx()` without parameters.
// To start READONLY transaction, pass `{readonly: true}`.
// To start distributed transaction, pass `{xaId: '...'}`.
// If you want `conn.connectionId` to be automatically appended to XA identifier, pass `{xaId1: '...'}`, where `xaId1` is the first part of the `xaId`.
// If connection to server was not yet established, the `conn.connectionId` is not known (and `startTrx()` will not connect), so `conn.connectionId` will be appended later on first query.
MyConn.startTrx(options?: {readonly?: boolean, xaId?: string, xaId1?: string}): Promise<void>;

// Creates transaction savepoint, and returns ID number of this new savepoint.
// Then you can call `conn.rollback(pointId)`.
// This is lazy operation. The corresponding command will be sent to the server later.
// Calling `savepoint()` immediately followed by `rollback(pointId)` to this point will send no commands.
MyConn.savepoint(): number;

// If the current transaction is of distributed type, this function prepares the 2-phase commit.
// Else does nothing.
// If this function succeeds, the transaction will be saved on the server till you call `commit()`.
// The saved transaction can survive server restart and unexpected halt.
// You need to commit it as soon as possible, to release all the locks that it holds.
// Usually, you want to prepare transactions on all servers, and immediately commit them if `prepareCommit()` succeeded, or rollback them if it failed.
MyConn.prepareCommit(): Promise<void>;

// Rollback to a savepoint, or all.
// If `toPointId` is not given or undefined - rolls back the whole transaction (XA transactions can be rolled back before `prepareCommit()` called, or after that).
// If `toPointId` is a number returned from `savepoint()` call, rolls back to that point (also works with XAs).
// If `toPointId` is `0`, rolls back to the beginning of transaction, and doesn't end this transaction (doesn't work with XAs).
// If rollback (not to savepoint) failed, will disconnect from server and throw ServerDisconnectedError.
// If `toPointId` was `0` (not for XAs), the transaction will be restarted after the disconnect if rollback failed.
MyConn.rollback(toPointId?: number): Promise<void>;

// Commit.
// If the current transaction is XA, and you didn't call `prepareCommit()` i'll throw error.
// With `andChain` parameter will commit and then restart the same transaction (doesn't work with XAs).
// If commit fails will rollback and throw error. If rollback also fails, will disconnect from server and throw ServerDisconnectedError.
MyConn.commit(andChain=false): Promise<void>;

// To download and run this example:
// export DSN='mysql://root:hello@localhost/tests'
// curl 'https://raw.githubusercontent.com/jeremiah-shaulov/office_spirit_mysql/v0.19.6/README.md' | perl -ne '$y=$1 if /^```(.)?/;  print $_ if $y&&$m;  $m=$y&&($m||m~<example-y5hf>~)' > /tmp/example-y5hf.ts
// deno run --allow-env --allow-net /tmp/example-y5hf.ts

import {MyPool} from 'https://deno.land/x/office_spirit_mysql@v0.19.6/mod.ts';

await using pool = new MyPool(Deno.env.get('DSN') || 'mysql://root:hello@localhost/tests');
using conn = pool.getConn();

// CREATE DATABASE
await conn.query("DROP DATABASE IF EXISTS test1");
await conn.query("CREATE DATABASE `test1`");

// USE
conn.use("test1");

// CREATE TABLE
await conn.query("CREATE TABLE t_log (id integer PRIMARY KEY AUTO_INCREMENT, a int)");

// Start transaction
await conn.startTrx();

// Insert a row
await conn.query("INSERT INTO t_log SET a = 123");

// Ensure that the row is present
console.log(await conn.queryCol("SELECT a FROM t_log WHERE id=1").first()); // prints: 123

// Rollback
await conn.rollback();

// The inserted row not persisted
console.log(await conn.queryCol("SELECT Count(*) FROM t_log").first()); // prints: 0

// Drop database that i created
await conn.query("DROP DATABASE test1");

await conn.startTrx({readonly: true});

await conn.startTrx({xaId: Math.random()+''});
// OR
await conn.startTrx({xaId1: Math.random()+'-'});

// Commit current transaction (if any), and start new.
// If there're active transactions, they will be properly (2-phase if needed) committed.
// Then new transaction will be started on all connections in this session.
// If then you'll ask a new connection, it will join the transaction.
// If commit fails, this function does rollback, and throws the Error.
function MySession.startTrx(options?: {readonly?: boolean, xa?: boolean}): Promise<void>;

// Create session-level savepoint, and return it's ID number.
// Then you can call `session.rollback(pointId)`.
// This is lazy operation. The corresponding command will be sent to the server later.
// Calling `savepoint()` immediately followed by `rollback(pointId)` to this point will send no commands.
// Using `MySession.savepoint()` doesn't interfere with `MyConn.savepoint()`, so it's possible to use both.
function MySession.savepoint(): number;

// Rollback all the active transactions in this session.
// If `toPointId` is not given or undefined - rolls back the whole transaction.
// If `toPointId` is a number returned from `savepoint()` call, rolls back all the transactions to that point.
// If `toPointId` is `0`, rolls back to the beginning of transaction, and doesn't end this transaction (also works with XAs).
// If rollback (not to savepoint) failed, will disconnect from server and throw ServerDisconnectedError.
// If `toPointId` was `0`, the transaction will be restarted after the disconnect if rollback failed.
function MySession.rollback(toPointId?: number): Promise<void>;

// Commit all the active transactions in this session.
// If the session transaction was started with `{xa: true}`, will do 2-phase commit.
// If failed will rollback. If failed and `andChain` was true, will rollback and restart the same transaction (also XA).
// If rollback failed, will disconnect (and restart the transaction in case of `andChain`).
function MySession.commit(andChain=false): Promise<void>;

import {MyPool, Dsn} from 'https://deno.land/x/office_spirit_mysql@v0.19.6/mod.ts';

const dsn1 = new Dsn('mysql://app:app@localhost/test1');
const dsn2 = new Dsn('mysql://app:app@localhost/test2');

await using pool = new MyPool
(	{	managedXaDsns:
        [	'mysql://manager:manager@localhost/test1',
            'mysql://manager:manager@localhost/test2',
        ],
        xaInfoTables:
        [	{dsn: 'mysql://manager:manager@localhost/test1', table: 't_xa_info_1'},
            {dsn: 'mysql://manager:manager@localhost/test1', table: 't_xa_info_2'},
            {dsn: 'mysql://manager:manager@localhost/test2', table: 't_xa_info_1'},
            {dsn: 'mysql://manager:manager@localhost/test2', table: 't_xa_info_2'},
        ]
    }
);

// Start session
using session = pool.getSession();

// Enable SQL logger
session.setSqlLogger(true);

// Start distributed transaction
await session.startTrx({xa: true});

// Get connection objects (actual connection will be established on first query)
const conn1 = session.conn(dsn1);
const conn2 = session.conn(dsn2);

// Query
await conn1.query("INSERT INTO t_log SET message = 'Msg 1'");
await conn2.query("INSERT INTO t_log SET message = 'Msg 1'");

// 2-phase commit
await session.commit();