Query Stream with MariaDB Connector/Node.js (Promise API)

Overview

Queries return complex data in MariaDB Connector/Node.js. Malformed queries might return errors instead of rows. Successful queries return both the rows in the result-set and metadata for those rows.

With Promise API, using the queryStream() method enables you to designate specific operations for specific return types.

Use the queryStream() function for large result sets that could be unmanageable if loaded into memory all at once. The queryStream() function makes use of event-based result set processing with which rows are processed one at a time.

For relatively small result sets, the query() function is more suitable than the queryStream() function. The query() function returns all data in the result set into memory in a single call.

Query Stream with Promise API

With Promise API, query stream can be used. The Promise API provides following function in the Connection object for query stream:

Function

Return Type

Description

connection.queryStream(sql[, values]) → Emitter

Emitter

The function is used to process large result sets using an event-driven methodology.

Unlike most of the other Promise API functions, the queryStream() function does not return a Promise object, and therefore the value returned from queryStream() must be wrapped in a Promise constructor if the value is to be returned directly from an an async function.

The connection.queryStream(sql[, values]) → Emitter function parameters are discussed in the following table:

Parameter

Type

Description

sql

string or JSON

The SQL string or JSON object for the SQL statement to run, and optionally, connection options that will supersede the default options. If a JSON object is supplied it must include a property called sql for the SQL statement to run.

values

array or object

Placeholder values.

It returns an Emitter object that emits four type of events:

Event

Description

data

Event for a row of data in the result set

fields

Event for a field or column metadata in the result set

error

Event for an error in the query stream

end

Event for the query end

Code Example: Query Stream

The following example shows how to use the query stream function to log the contact list to the console from the example table created in Setup for Examples:

// Required Modules
const mariadb = require("mariadb");

// Main function
async function main() {
   let conn;

   try {
      conn = await mariadb.createConnection({
         host: "192.0.2.50",
         user: "db_user",
         password: "db_user_password",
         database: "test",
      });

      await print_contacts(conn);
   } catch (err) {
      // Manage Errors
      console.log(err);
   } finally {
      // Close Connection
      if (conn) conn.close();
   }
}

// Print list of contacts
function print_contacts(conn) {
   return new Promise(
      (resolve, reject) => {
         resolve(
            conn
            .queryStream("SELECT * FROM test.contacts")
            .on("error", (err) => {
               console.error("Issue retrieving contact information", err);
            })
            .on("fields", (meta) => {
               console.error("Field Metadata:", meta);
            })
            .on("data", (row) => {
               console.log(`${row.first_name} ${row.last_name} <${row.email}>`);
            })
         )
      }
   );
}

main();

// Required Modules
const mariadb = require("mariadb");

// Certificate Authority (CA)",
var serverCert = [fs.readFileSync(process.env.SKYSQL_CA_PEM, "utf8")];

// Main function
async function main() {
   let conn;

   try {
      conn = await mariadb.createConnection({
         host: "example.skysql.net",
         port: 5009,
         ssl: { ca: serverCert },
         user: "db_user",
         password: "db_user_password",
         database: "test",
      });

      await print_contacts(conn);
   } catch (err) {
      // Manage Errors
      console.log(err);
   } finally {
      // Close Connection
      if (conn) conn.close();
   }
}

// Print list of contacts
function print_contacts(conn) {
   return new Promise(
      (resolve, reject) => {
         resolve(
            conn
            .queryStream("SELECT * FROM test.contacts")
            .on("error", (err) => {
               console.error("Issue retrieving contact information", err);
            })
            .on("fields", (meta) => {
               console.error("Field Metadata:", meta);
            })
            .on("data", (row) => {
               console.log(`${row.first_name} ${row.last_name} <${row.email}>`);
            })
         )
      }
   );
}

main();

  • Add an asynchronous function print_contacts(conn) for printing contacts data and metadata.

  • The async keyword declares the function to be an async function.

  • The function formats and logs to stdout a line for each contact in the table and it logs the field metadata to stderr. If it encounters an error, it logs the error message to stderr.

  • MariaDB Connector/Node.js retrieves data from the database using the queryStream() method with a SELECTSELECT statement.

  • The print_contacts(conn) function should return a Promise object because it is an async function. The constructor for Promise is used to create a Promise object for the value returned from the print_contacts(conn) function.

When the script is run, the contacts data is logged to the console :

John Smith <john.smith@example.com>
Jon Smith <jon.smith@example.com>
Johnny Smith <johnny.smith@example.com>

The field's metadata is also logged to the console:

Field Metadata: [
  ColumnDef {
    _parse: StringParser {
      dbLength: 4,
      dbOffset: 5,
      tableLength: 8,
      tableOffset: 10,
      orgTableLength: 8,
      orgTableOffset: 19,
      nameLength: 2,
      nameOffset: 28,
      orgNameLength: 2,
      orgNameOffset: 31,
      packet: [PacketNodeEncoded]
    },
    collation: Collation { index: 63, name: 'BINARY', charset: 'binary' },
    columnLength: 11,
    columnType: 3,
    flags: 16899,
    scale: 0,
    type: 'LONG'
  },
  ColumnDef {
    ...
    ...
]