MySQL

A MySQL client without any dependencies that supports prepared statements.

Github: https://github.com/Rektonomist/nodejs-mysql-light

MySQL
Table of Contents
Creating a connection and sending a query
Creating and executing a prepared statement
Class: mysql_light.Connection
PreparedStatement
Response objects
Data types
TODO

Creating a connection and sending a query

This package currently exports a single class: Connection.

const mysql_light = require('mysql-light');

var connection = new mysql_light.Connection();

connection.on('connect', function(login_response) {
    console.log("Connected!");
});

connection.on('error', function(err) {
    console.log("Couldn't connect to server:", err);
});

connection.connect({
    host: "127.0.0.1",
    port: 3306
}, {
    user: "root",
    password: null,
    database: "test"
}); //Note: you don't have to wait for the connection to finish before sending any commands, because commands are added to a command queue.

connection.query("SELECT * FROM `table1`", function(response, columns, rows) {
    for (var i = 0; i < rows.length; i++) {
        console.log("Row " + (i + 1) + ":");
        for (var j = 0; j < columns.length; j++) {
            console.log("\t" + columns[j] + ": " + rows[i][columns[j].name]); //Note: you can use rows[i][j] too, but rows[i] isn't an array, so you cannot use rows[i].length to get the amount of columns
        }
    }
}, function(err) {
    console.log("Couldn't query server:", err);
});

Creating and executing a prepared statement

connection.prepare("SELECT * FROM `table1` WHERE id=?", function(response) {
    if (response.type == "OK") {
        var stmt = response.stmt;
        
        stmt.execute([6], function(response, columns, rows) {
            for (var i = 0; i < rows.length; i++) {
                console.log("Row " + (i + 1) + ":");
                for (var j = 0; j < columns.length; j++) {
                    console.log("\t" + columns[j] + ": " + rows[i][columns[j].name]);
                }
            }
        }, function (error) {
            console.log("Connection error!", error);
        });
        
        stmt.close(function (error) {
            console.log("Connection error!", error);
        });
        //Note: the prepared statement will only be closed after the execute statement completes, because all actions are queued.
    } else {
        console.log("Failed to prepare statement:", response.message);
    }
}, function(error) {
    console.log("Connection error!", error);
});

Class: mysql_light.Connection

A class that represents a single connection to a MySQL server. This class can be used to query the server and prepare statements.

new mysql_light.Connection()

Creates a connection.

Event: 'connect'

response : OK response sent by the server.

Emitted when the client connects and logs in to the MySQL server.

Event: 'timeout'

Emitted if the underlying socket emits the timeout event.

Event: 'error'

error : Error details

Emitted if the client cannot connect to the server or the server sends malformed data.

Event: 'close'

hadError : true if the socket had a transmission error

Emitted when the connection gets closed.

connection.connect(socket_options, login_options)

For the available socket_options, see socket.connect(options).

The available login_options are:

user : The user that will connect to the database.
password : Optional password if the user has a password.
database : The database the user will connect to.

Connects to the database.

connection.query(query, response, error)

query : The query to execute.
response : Either a function or an object containing 4 functions.
error : Error handler that takes 1 argument - the error.

If response is a function, it can take 3 arguments:

response : The server's response.
columns : If the query returned results, this will be an array of the column definitions.
rows : If the query returned results, this will be an array of rows.

If the response is an object, it must contain a result property that is a function that handles the 3 arguments above, and it can optionally contain the following properties:

row : A function that handles each row as the row arrives. Takes 2 arguments: row and columns. row is the row that was received, and columns is an array of the column definitions. If this function is defined, then the result function will receive empty arrays as columns and rows.
end : A function that gets called when all queries have finished executing. No arguments.
local_infile : Currently unimplemented, but will be a function that handles LOCAL_INFILE responses by the server.

Sends a query to the server.

connection.prepare(query, response, error)

query : The query to prepare.
response : A function with one argument - a prepare response from the server (prepare OK or ERR).
error : Error handler that takes 1 argument - the error.

Prepares a statement.

connection.setDatabase(database, response, error)

database : A string of the database's name.
response : A function that takes one argument - the response from the server (OK or ERR).
error : Error handler that takes 1 argument - the error.

connection.setTimeout, connection.ref, connection.unref

See socket.setTimeout, socket.ref, socket.unref

connection.close()

Waits until all queued commands have been sent, and then closes the connection. Calling it twice will close the connection without waiting for the queued commands.

PreparedStatement

An object that represents a prepared statement.

stmt.id

The id of this prepared statement.

stmt.params

An array of column definitions that are parameters for this prepared statement.

stmt.execute(parameters, response, error)

parameters : An array of parameters to be used. All parameters must be passed as strings.
response : Same as connection.query(), except prepared statements don't support LOCAL_INFILE, so a LOCAL_INFILE handler is never needed. See Data types for how these values will be returned.
error : Error handler that takes one argument - the error.

Executes this prepared statement.

stmt.reset(response, error)

response : A function that takes one argument - the response from the server.
error : Error handler that takes one argument - the error.

Resets any data has been already been applied to this statement.

stmt.close(error)

error : Error handler that takes one argument - the error.

Closes the prepared statement and releases any resources the server allocated.

Response objects

response

type : A string that is either "OK" or "ERR".

If the type is "OK":

affected_rows : The number of rows affected by the query.
last_insert_id : The id of the row that was last inserted by the query.
warnings : null if the server doesn't support it, otherwise the number of warnings generated by the query.
status : null if the server doesn't support it, otherwise the status of the server.
info : A human-readable string sent back by the server.
session_track : null if session tracking is not enabled or the query did not change the connection's session variables, otherwise an array of session information that changed.

If the type is "OK" and the action that generated this response was prepare:

warnings : Number of warnings in the prepared statement.
stmt : The prepared statement.

If the type is "ERR":

error : The ID of the error.
sql_state : null if the server doesn't support it, otherwise a string containing the SQLSTATE.
message : A human-readable string describing the error.

If the type is "EOF":

warnings : null if the server doesn't support it, otherwise the number of warnings generated by the query.
status : null if the server doesn't support it, otherwise the status of the server.

session_track

type : One of "SESSION_TRACK_SYSTEM_VARIABLES", "SESSION_TRACK_SCHEMA", "SESSION_TRACK_STATE_CHANGE" and "unknown".
name
value

server_status

An object that contains the following boolean properties:

STATUS_IN_TRANS
STATUS_AUTOCOMMIT
MORE_RESULTS_EXISTS
STATUS_NO_GOOD_INDEX_USED
STATUS_NO_INDEX_USED
STATUS_CURSOR_EXISTS
STATUS_LAST_ROW_SENT
STATUS_DB_DROPPED
STATUS_NO_BACKSLASH_ESCAPES
STATUS_METADATA_CHANGED
QUERY_WAS_SLOW
PS_OUT_PARAMS
STATUS_IN_TRANS_READONLY
SESSION_STATE_CHANGED

column definition

An object that contains the following properties:

column_definition_version : The version of the column definition, either "4.1" or "3.20".
table : The name of the table.
name : The name of the column.
column_length : The maximum length of the column.
type : The type of the column.
type_id : The ID of the column type.
decimals : Max shown decimal digits. See the MySQL docs
default_values : A ByteBuffer that represents something (the MySQL documentation isn't very helpful).
flags : Column flags for the type.

If the column definition version is 4.1, these additional properties will also be present:

catalog : Always "def".
database : The name of the database in which this column exists.
org_table : Physical table name (as opposed to table, which is the virtual table name).
org_name : Physical column name (as opposed to name, which is the virtual column name).
character_set : The character set for this column.

Possible values for type and corresponding type_id:

DECIMAL : 0x00
TINY : 0x01
SHORT : 0x02
LONG : 0x03
FLOAT : 0x04
DOUBLE : 0x05
NULL : 0x06
TIMESTAMP : 0x07
LONGLONG : 0x08
INT24 : 0x09
DATE : 0x0a
TIME : 0x0b
DATETIME : 0x0c
YEAR : 0x0d
NEWDATE : 0x0e
VARCHAR : 0x0f
BIT : 0x10
TIMESTAMP2 : 0x11
DATETIME2 : 0x12
TIME2 : 0x13
NEWDECIMAL : 0xf6
ENUM : 0xf7
SET : 0xf8
TINY_BLOB : 0xf9
MEDIUM_BLOB : 0xfa
LONG_BLOB : 0xfb
BLOB : 0xfc
VAR_STRING : 0xfd
STRING : 0xfe
GEOMETRY : 0xff
UNKNOWN : anything else

MySQL column types

https://dev.mysql.com/doc/refman/8.0/en/numeric-type-overview.html

column flags

An object that contains the following boolean properties:

NOT_NULL
PRI_KEY
UNIQUE_KEY
MULT_KEY
BLOB
UNSIGNED
ZEROFILL
BINARY
ENUM
AUTO_INCREMENT
TIMESTAMP
SET
NO_DEFAULT_VALUE
PART_KEY
NUM

error

type : The error type, can be one of "unexpected_data", "malformed_data" or "connection_closed".

The error might have an an error property if type is "malformed_data":

error : A javascript Error, most likely a RangeError because the packet response by the server didn't have enough data.

Malformed data means the packet had too much data or too little data.

Unexpected data means that the packet contained data that is impossible for a real packet to contain.

Connection closed means that the connection was closed and therefore any command in the command queue got removed.

Data types

For non-prepared queries, all response values will be strings as specified by the MySQL documentation. For prepared queries, different values will have different formats depending on the column type.

All integers up to 32 bits and floats up to 64 bits will be represented with Javascript numbers.

STRING, VARCHAR, VAR_STRING, ENUM, SET, LONGLONG, NEWDECIMAL

This parameter will be returned as a String representation of the value.

BIT

This parameter will be returned as an array of booleans, with the rightmost bit being the first element of the array, and the leftmost bit being the last element.

LONG_BLOB, MEDIUM_BLOB, BLOB, TINY_BLOB, GEOMETRY, DECIMAL

This parameter will be returned as a ByteBuffer.

LONG, INT24

This parameter will be returned as a 32 bit integer.

SHORT, YEAR:

This parameter will be returned as a 16 bit integer.

TINY

This parameter will be returned as an 8 bit integer.

DOUBLE

This parameter will be returned as a 64 bit float.

FLOAT

This parameter will be returned as a 32 bit float.

DATE, DATETIME, TIMESTAMP

This parameter will be returned as an object with the following parameters:

year : 16 bit integer
month : 8 bit integer
day : 8 bit integer
hour : 8 bit integer
minute : 8 bit integer
second : 8 bit integer
micro_second : 32 bit integer

TIME