Connect with Node.js
SingleStoreDB is wire-compliant with MySQL. You can use the Node.js JavaScript runtime to connect to your SingleStoreDB database, whether you're using an ORM or straight SQL.
To connect from Node.js to SingleStoreDB using SSL, see Connect with Node.js using SSL.
Prerequisites
Install (or update to) a supported version of Node.js (v14 or newer). You can download the Node.js installer from https://nodejs.org/en/download/.
Install the
mysql2
module as a dependency:npm install --save mysql2
Connection Details
To connect your Node.js application to your SingleStoreDB Managed Service workspace, you'll need the following:
Host: the endpoint for your SingleStoreDB workspace. You can find the endpoint in the Portal.
Port: default is
3306
.User:
admin
Password
Here is an example of a Node connection for a workspace:
const db = mysql.createConnection({ host: 'svc-1234ebc1-ab6f-123d-8e67-cd5d066ca0e8-ddl.aws-region-1.svc.singlestore.com', user: 'admin', password: 'hjk3874yishf' database: 'dbExecTest' });
Using Node.js with SingleStoreDB
Create a project folder, and initialize this project as an NPM project.
mkdir ss_node cd ss_node npm init -y
Set up a new workspace at portal.singlestore.com. See the Getting Started with SingleStoreDB Cloud guide for more information.
Once the workspace is deployed, use the SQL Editor (in the Navigation pane on the left) or the command-line, to set up a new database and create tables.
Add the following code block to the
index.js
file of your Node application to setup the connection to the new database, using the MySQL2 NPM package.import mysql from 'mysql2/promise'; //Modify the connection details to match the details specified while //deploying the SingleStore workspace: const HOST = 'admin_endpoint_of_the_workspace'; const USER = 'admin'; const PASSWORD = 'admin_password'; const DATABASE = 'name_of_the_database'; // main is run at the end async function main() { let singleStoreConnection; try { singleStoreConnection = await mysql.createConnection({ host: HOST, user: USER, password: PASSWORD, database: DATABASE }); console.log("You have successfully connected to SingleStore."); } catch (err) { console.error('ERROR', err); process.exit(1); } finally { if (singleStoreConnection) { await singleStoreConnection.end(); } } } main();
To connect with Node.js using SSL, see the Connect with Node.js using SSL topic.
Run the following command to test the connectivity to your database. This command executes the
index.js
file created in the previous step.node index.js
If the connection is successful, the following message is displayed:
You have successfully connected to SingleStore.
Examples
The following examples show how to perform basic CRUD operations using Node.js. These examples use the messages
table.
CREATE TABLE IF NOT EXISTS messages ( id BIGINT AUTO_INCREMENT PRIMARY KEY, content VARCHAR(300) NOT NULL, createdate TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP );
Create Operation
The following example adds a row using the INSERT
command.
async function create({conn, content}) { const [results] = await conn.execute( 'INSERT INTO messages (content) VALUES (?)', [content] ); return results.insertId; };
// call the create() function inside the main() function as: const id = await create({conn, content: 'New message has been logged.'}); console.log(`Inserted row id is: ${id}`);
Read Operation
The following example reads the row inserted in the example above.
async function readN({conn, id}) { const [rows] = await conn.execute( 'SELECT id, content, createdate FROM messages WHERE id = ?', [id] ); return rows[0]; };
// call the readN() function inside the main() function as: const msg = await readN({conn, id:1}); console.log('Read one row:'); if (msg == null) { console.log('No message entry with this ID.'); } else { console.log(`${msg.id}, ${msg.content}, ${msg.createdate}`); }
Update Operation
The following example updates the row inserted in the Create Operation example above.
async function updateN({conn, content, id}) { await conn.execute( 'UPDATE messages SET content = ? WHERE id = ?', [content,id] ); const [upd] = await conn.execute( 'SELECT id, content, createdate FROM messages WHERE id = ?', [id] ); return upd[0]; }
//call the updateN() function inside the main() function as: const i = await updateN({conn, id:1, content: 'This row has been updated.'}); console.log(`Updated row id ${i.id}, ${i.content}`);
Delete Operation
The following example deletes the row inserted in the Create Operation example above.
async function deleteN({conn, id}) { await conn.execute( 'DELETE FROM messages WHERE id = ?', [id] ); }
//call the deleteN() function inside the main() function as: await deleteN({conn, id:1});
More Information
To build a SingleStoreDB application using JavaScript and Node.js, refer to the following training: Building a SingleStore Application Using Javascript and Node.js
Using Stored Procedures: GitHub
Using ORM: GitHub