Morsel SQL Database

Morsel SQL Database is a comprehensive SQL database management module for Node.js. It offers functionalities for various database operations, including querying, transactions, and schema management.

Installation

To use the morsel-sql-db module, ensure you have Node.js installed, then install the module using npm:

npm install morsel-sql-db

Usage

Import and Configuration

First, import the morselSQL class and create an instance with your database configuration:

const morselSQL = require('morsel-sql-db'); // Import the module

const config = {
    host: 'localhost',
    user: 'root',
    password: 'password',
    database: 'my_database'
};

const db = new morselSQL(config);

Connecting to the Database

To establish a connection:

await db.connect();

Disconnecting from the Database

To close the connection:

await db.disconnect();

Querying

Run a raw SQL query:

const result = await db.query('SELECT * FROM my_table WHERE id = ?', [1]);
console.log(result);

Transactions

Execute multiple queries in a transaction:

const queries = [
    { sql: 'INSERT INTO my_table (name) VALUES (?)', params: ['John'] },
    { sql: 'UPDATE my_table SET name = ? WHERE id = ?', params: ['Doe', 1] }
];

const results = await db.transaction(queries);
console.log(results);

CRUD Operations

Insert

Insert a row:

await db.insert('my_table', { name: 'John', age: 30 });

Bulk insert multiple rows:

const rows = [
    { name: 'John', age: 30 },
    { name: 'Jane', age: 25 }
];

await db.bulkInsert('my_table', rows);

Select

Select rows:

const rows = await db.select('my_table', { age: 30 });
console.log(rows);

Select with a join:

const rows = await db.selectWithJoin('my_table', 'INNER', 'another_table', 'my_table.id = another_table.my_table_id', { age: 30 });
console.log(rows);

Update

Update rows:

await db.update('my_table', { name: 'Jane' }, { id: 1 });

Delete

Delete rows:

await db.delete('my_table', { id: 1 });

Advanced Operations

Count

Count rows:

const count = await db.count('my_table', { age: 30 });
console.log(count);

Exists

Check if rows exist:

const exists = await db.exists('my_table', { id: 1 });
console.log(exists);

Truncate

Truncate a table:

await db.truncate('my_table');

Drop Table

Drop a table:

await db.dropTable('my_table');

Create Table

Create a table:

const columns = {
    id: 'INT AUTO_INCREMENT PRIMARY KEY',
    name: 'VARCHAR(255)',
    age: 'INT'
};

await db.createTable('my_table', columns);

Get Table Columns

Retrieve table columns:

const columns = await db.getTableColumns('my_table');
console.log(columns);

Get Tables

List all tables:

const tables = await db.getTables();
console.log(tables);

Get Database Size

Get the database size:

const size = await db.getDatabaseSize();
console.log(`Database size: ${size} MB`);

Optimize Table

Optimize a table:

await db.optimizeTable('my_table');

Repair Table

Repair a table:

await db.repairTable('my_table');

Show Process List

Show the current processes:

const processes = await db.showProcessList();
console.log(processes);

Show Status

Show database status:

const status = await db.showStatus();
console.log(status);

Show Variables

Show database variables:

const variables = await db.showVariables();
console.log(variables);

Set Variable

Set a database variable:

await db.setVariable('max_connections', 200);

Get User Privileges

Retrieve user privileges:

const privileges = await db.getUserPrivileges('user');
console.log(privileges);

Grant Privileges

Grant privileges to a user:

await db.grantPrivileges('user', 'ALL PRIVILEGES');

Revoke Privileges

Revoke privileges from a user:

await db.revokePrivileges('user', 'ALL PRIVILEGES');

Error Handling

All methods throw errors if something goes wrong. Use try-catch blocks to handle errors:

try {
    await db.connect();
} catch (error) {
    console.error('Error:', error.message);
}

License

This module is licensed under the ISC License. See the LICENSE file for details.

Contributing

To contribute, please submit a pull request or open an issue on GitHub.

Contact

For questions or feedback, contact [email protected].