const { getConnection, ErrorVerbosityLevel } = require('node-regatta');
async function main() {
/* Enter the Regatta connection URL */
const dbUrl = 'aaa.bbb.ccc.ddd:pppp';
/* Enter your Regatta username */
const dbUser = 'MyUserName';
/* Enter your Regatta password */
const dbPassword = 'SomeSophisticatedPassword';
try {
/*
* Establish a connection to the database using the provided credentials
* and URL.
* To pass client options or set a timeout for all requests to the database,
* use custom getConnection:
* connectCustom(user, password, host, connectionOptions, timeoutMs)
*/
let connection = await getConnection(dbUser, dbPassword, dbUrl);
console.log('Connected to the database');
/*
* Auto-commit is enabled by default. To use manual commit mode, set
* auto-commit to false.
*/
connection.setCommitMode(false);
/*
* The transaction isolation level can be set. The default is the Regatta
* transaction isolation level.
* const { TransIsoLevel } = require("node-regatta");
* connection.setTransactionIsolation(TransIsoLevel.READ_COMMITTED);
*/
/*
* The default connection timeout to execute each query is set to infinity.
* A custom timeout can be set using the setTimeout(timeoutMs) method.
*/
connection.setTimeout(3000);
/*
* Optionally, the level of detail for error messages generated by
* operations using the connection can be set.
*/
connection.setErrorVerbosity(ErrorVerbosityLevel.HIGH);
/*
* Create a new table 'users' with columns for 'id' and 'name'.
* The 'id' column is the primary key and is indexed.
*/
await connection.execute(`
CREATE TABLE users (
id INT PRIMARY KEY INDEX,
name VARCHAR(128)
)
`);
/*
* When working in manual commit mode, commit the transaction manually
* using the commit method. This finalizes the transaction and makes the
* changes permanent.
*/
await connection.commit();
console.log("Created table 'users'");
/*
* Insert multiple rows of data into the 'users' table using promise style.
*/
await connection.execute(`
INSERT INTO users (id, name)
VALUES (100, 'Mario'),
(200, 'Barbara'),
(300, 'Marcelo'),
(400, 'Henry')
`);
/*
* Commit the transaction manually to finalize the insertion of rows into
* the 'users' table and make the changes permanent.
*/
await connection.commit();
console.log("Inserted data into 'users' table using promise");
/*
* Insert multiple rows of data into the 'users' table using callback style.
*/
await new Promise((resolve, reject) => {
connection.execute(
`INSERT INTO users (id, name)
VALUES (500, 'Mike'),
(600, 'Johnny')`,
function (err) {
if (err) {
console.error('Error inserting data:', err.message);
return reject(err);
}
/* Manually commit the transaction. */
connection.commit(function (err) {
if (err) {
console.error('Error committing transaction:', err.message);
return reject(err);
}
console.log("Inserted data into 'users' table using callback");
resolve();
});
}
);
});
/*
* Insert multiple rows of data into the 'users' table using batch API.
*/
connection.addBatch("INSERT INTO users (id, name) VALUES (700, 'Logan')");
connection.addBatch("INSERT INTO users (id, name) VALUES (800, 'Nick')");
await connection.executeBatch();
/* Manually commit the transaction. */
await connection.commit();
console.log("Inserted data into 'users' table using batch API");
/* Retrieve all data from the 'users' table. */
let cursor = await connection.execute('SELECT * FROM users');
/* Manually commit the transaction. */
await connection.commit();
/*
* Get cursor description - An array containing objects for each column in
* the result set, with each object having a name and a SQLTypeCode
* property.
*/
console.log('Cursor description:', cursor.description);
/*
* Get the total number of rows in the result set for DQL queries (SELECT)
* or the number of affected rows for DML queries (INSERT, UPDATE, and
* DELETE).
*/
console.log('Total number of rows in the result:', cursor.rowCount);
/* Fetch the first 2 rows from the cursor. */
let result = await cursor.fetch(2);
console.log("First 2 rows retrieved from 'users' table:", result.rows);
/* Fetch all remaining rows. */
result = await cursor.fetchAll();
console.log("Remaining rows retrieved from 'users' table:", result.rows);
/* Close the cursor after retrieving the data. */
await cursor.close();
/* Insert a new user and then rollback the transaction. */
await connection.execute(`
INSERT INTO users (id, name)
VALUES (900, 'Paul')
`);
/*
* Rollback the transaction to undo the insertion.
* After the rollback, the user with ID 900 will not exist in the table.
* If execution is still running and needs to be stopped,
* the abort() method can be used instead.
*/
await connection.rollback();
console.log('Rollback: insertion of user with ID 900 has been undone.');
/* Drop the 'users' table to clean up the database. */
await connection.execute('DROP TABLE users');
/* Manually commit the transaction. */
await connection.commit();
console.log("Dropped table 'users'");
/*
* Close the database connection.
* Closing the connection will also close all open cursors.
*/
await connection.close();
console.log('Connection closed');
} catch (error) {
/* Handle any errors that occur during the database operations. */
console.error('Error:', error);
}
}
/* Execute the main function to perform the database operations. */
main();
