Skip to main content

SingleStore-Nodejs

SingleStore client for Node.js with focus on performance. Supports configurable connection behavior, prepared statements, compression, ssl and much more.

NPM Version NPM Downloads Node.js Version License

Installationโ€‹

SingleStore-Nodejs is free from native bindings and can be installed on Linux, Mac OS or Windows without any issues.

npm install --save singlestore-nodejs

First Queryโ€‹

To explore more queries examples, please visit the example sections Simple Queries and Prepared Statements.

// Get the client
import singlestore from 'singlestore-nodejs/promise';

// Create the connection to database
const connection = await singlestore.createConnection({
  host: 'localhost',
  user: 'root',
  database: 'test',
});

// A simple SELECT query
try {
  const [results, fields] = await connection.query(
    'SELECT * FROM `table` WHERE `name` = "Page" AND `age` > 45'
  );

  console.log(results); // results contains rows returned by server
  console.log(fields); // fields contains extra meta data about results, if available
} catch (err) {
  console.log(err);
}

// Using placeholders
try {
  const [results] = await connection.query(
    'SELECT * FROM `table` WHERE `name` = ? AND `age` > ?',
    ['Page', 45]
  );

  console.log(results);
} catch (err) {
  console.log(err);
}

Using Prepared Statementsโ€‹

SingleStore-Nodejs supports prepared statements primarily for compatibility with MySQL drivers and existing codebases. However, SingleStore automatically compiles and optimizes both text and binary protocol queries, so you get performance benefits even with regular queries.

Important Notes:

  • SingleStore compiles queries sent through both text and binary protocols automatically
  • You get query optimization benefits with regular .query() calls
  • Some queries (like EXPLAIN, SHOW commands) cannot be run as prepared statements
  • Manual preparation with prepare/unprepare methods is generally not recommended

If you don't know why prepared statements are traditionally important in other databases, please check these discussions:

SingleStore-Nodejs provides execute helper for compatibility. You can also manually prepare / unprepare statements with prepare / unprepare methods if needed for compatibility, but regular queries are typically preferred.

To explore more Prepared Statements and Placeholders examples, please visit the example section Prepared Statements.

import singlestore from 'singlestore-nodejs/promise';

try {
  // create the connection to database
  const connection = await singlestore.createConnection({
    host: 'localhost',
    user: 'root',
    database: 'test',
  });

  // execute will internally call prepare and query
  const [results, fields] = await connection.execute(
    'SELECT * FROM `table` WHERE `name` = ? AND `age` > ?',
    ['Rick C-137', 53]
  );

  console.log(results); // results contains rows returned by server
  console.log(fields); // fields contains extra meta data about results, if available
} catch (err) {
  console.log(err);
}
tip

If you execute same statement again, it will be picked from a LRU cache which will save query preparation time and give better performance.

Using Connection Poolsโ€‹

Connection pools help reduce the time spent connecting to the SingleStore server by reusing a previous connection, leaving them open instead of closing when you are done with them.

This improves the latency of queries as you avoid all of the overhead that comes with establishing a new connection.

To explore more Connection Pools examples, please visit the example section createPool.

import singlestore from 'singlestore-nodejs/promise';

// Create the connection pool. The pool-specific settings are the defaults
const pool = singlestore.createPool({
  host: 'localhost',
  user: 'root',
  database: 'test',
  waitForConnections: true,
  connectionLimit: 10,
  maxIdle: 10, // max idle connections, the default value is the same as `connectionLimit`
  idleTimeout: 60000, // idle connections timeout, in milliseconds, the default value 60000
  queueLimit: 0,
  enableKeepAlive: true,
  keepAliveInitialDelay: 0,
});
note

The pool does not create all connections upfront but creates them on demand until the connection limit is reached.

You can use the pool in the same way as connections (using pool.query() and pool.execute()):

try {
  // For pool initialization, see above
  const [rows, fields] = await pool.query('SELECT `field` FROM `table`');
  // Connection is automatically released when query resolves
} catch (err) {
  console.log(err);
}

Alternatively, there is also the possibility of manually acquiring a connection from the pool and returning it later:

// For pool initialization, see above
const conn = await pool.getConnection();

// Do something with the connection
await conn.query(/* ... */);

// Don't forget to release the connection when finished!
pool.releaseConnection(conn);
  • Additionally, directly release the connection using the connection object:
conn.release();

Using Promise Wrapperโ€‹

SingleStore-Nodejs also supports Promise API. Which works very well with ES7 async await.

import singlestore from 'singlestore-nodejs/promise';

async function main() {
  // create the connection
  const connection = await singlestore.createConnection({
    host: 'localhost',
    user: 'root',
    database: 'test',
  });

  // query database
  const [rows, fields] = await connection.execute(
    'SELECT * FROM `table` WHERE `name` = ? AND `age` > ?',
    ['Morty', 14]
  );
}

SingleStore-Nodejs uses default Promise object available in scope. But you can choose which Promise implementation you want to use.

// get the client
import singlestore from 'singlestore-nodejs/promise';

// get the promise implementation, we will use bluebird
import bluebird from 'bluebird';

// create the connection, specify bluebird as Promise
const connection = await singlestore.createConnection({
  host: 'localhost',
  user: 'root',
  database: 'test',
  Promise: bluebird,
});

// query database
const [rows, fields] = await connection.execute(
  'SELECT * FROM `table` WHERE `name` = ? AND `age` > ?',
  ['Morty', 14]
);

SingleStore-Nodejs also exposes a .promise() function on Pools, so you can create a promise/non-promise connections from the same pool.

import singlestore from 'singlestore-nodejs';

async function main() {
  // create the pool
  const pool = singlestore.createPool({
    host: 'localhost',
    user: 'root',
    database: 'test',
  });

  // now get a Promise wrapped instance of that pool
  const promisePool = pool.promise();

  // query database using promises
  const [rows, fields] = await promisePool.query('SELECT 1');
}

SingleStore-Nodejs exposes a .promise() function on Connections, to "upgrade" an existing non-promise connection to use promise.

const singlestore = require('singlestore-nodejs');

// create the connection
const conn = singlestore.createConnection({
  host: 'localhost',
  user: 'root',
  database: 'test',
});

conn
  .promise()
  .query('SELECT 1')
  .then(([rows, fields]) => {
    console.log(rows);
  })
  .catch(console.log)
  .then(() => conn.end());

Array Resultsโ€‹

If you have two columns with the same name, you might want to get results as an array rather than an object to prevent them from clashing. This is a deviation from the Node MySQL library.

For example: SELECT 1 AS `foo`, 2 AS `foo`.

You can enable this setting at either the connection level (applies to all queries), or at the query level (applies only to that specific query).

Connection Levelโ€‹

const conn = await singlestore.createConnection({
  host: 'localhost',
  database: 'test',
  user: 'root',
  rowsAsArray: true,
});

Query Levelโ€‹

try {
  const [results, fields] = await conn.query({
    sql: 'SELECT 1 AS `foo`, 2 AS `foo`',
    rowsAsArray: true,
  });

  console.log(results); // in this query, results will be an array of arrays rather than an array of objects
  console.log(fields); // fields are unchanged
} catch (err) {
  console.log(err);
}
Getting Help

Need help? Ask your question on Stack Overflow or GitHub. If you've encountered an issue, please file it on GitHub.