Connect with Node.js

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:

  Shell

  Copy

  npm install --save mysql2

Connection Details

To connect your Node.js application to your SingleStore cluster, you'll need the following:

• Host: The IP Address or hostname of your SingleStore cluster.

• Port: Default is 3306.

• User: The SingleStore database username, for example, root.

• Password: Password of the SingleStore database user.

Here is an example of a Node connection for a cluster:

const db = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'hjk3874yishf '
    database: 'dbExecTest'
    });

Using Node.js with SingleStore

1. Create a project folder, and initialize this project as an NPM project.

   Shell

   Copy

   mkdir ss_node
   cd ss_node
   npm init -y

2. Set up a new cluster. Refer to the Deploy guide for more information.

3. Once the cluster is deployed, set up a new database and create tables.

4. 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.

   JavaScript

   Copy

   import mysql from 'mysql2/promise';
   
   //Modify the connection details to match the details specified while
   //deploying the SingleStore cluster:
   const HOST = 'ip_address_of_the_cluster';
   const USER = 'root';
   const PASSWORD = 'root_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.

5. Run the following command to test the connectivity to your database. This command executes the index.js file created in the previous step.

   Shell

   Copy

   node index.js

   If the connection is successful, the following message is displayed:

   Copy

   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
);

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}`);

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}`);
    }

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}`);

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 SingleStore 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