alpha-sql

MySQL Wrapper Module

A lightweight, Promise-based wrapper for MySQL operations that simplifies database interactions in Node.js applications.

Features

• Promise-based API for clean async/await usage
• Connection management (connect/disconnect)
• Table creation with schema definition
• Comprehensive CRUD operations
• Advanced query features:
  • WHERE conditions with AND/OR logic
  • JOIN support
  • GROUP BY with HAVING clauses
  • ORDER BY (ASC/DESC)
  • LIMIT and OFFSET pagination
  • Subqueries and expressions
  • Custom filters and functions
  • Window functions
  • WITH clause support

Installation

npm install alpha-sql

Basic Usage

const db = require('alpha-sql');

// Connect to database
await db.connect({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'my_database'
});

// Create a table
await db.createTable('users', {
  id: { type: 'INT', isnull: false, isprimarykey: true },
  name: 'VARCHAR(255)',
  email: 'VARCHAR(255)',
  created_at: 'TIMESTAMP DEFAULT CURRENT_TIMESTAMP'
});

// Insert data
await db.insert('users', {
  id: 1,
  name: 'John Doe',
  email: '[e-mail]'
});

// Find all records
const users = await db.findAll('users');

// Disconnect when done
await db.disconnect();

API Reference

Connection Management

connect(options)

Establishes a connection to MySQL database.

await db.connect({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'my_database'
});

disconnect()

Closes the active database connection.

await db.disconnect();

Table Operations

createTable(tableName, schemaObj)

Creates a new table with the specified schema.

await db.createTable('users', {
  id: { type: 'INT', isnull: false, isprimarykey: true },
  name: 'VARCHAR(255)',
  email: { type: 'VARCHAR(255)', isnull: false },
  department_id: { 
    type: 'INT', 
    isRefKey: true, 
    refCol: { table: 'departments', column: 'id' } 
  }
});

Data Manipulation

insert(tableName, dataObj)

Inserts a record into the specified table.

await db.insert('users', {
  name: 'John Doe',
  email: '[e-mail]'
});

update(options)

Updates records based on specified conditions.

await db.update({
  table: 'users',
  set: { name: 'Jane Doe' },
  where: { id: 1 }
});

deleteRecords(options)

Deletes records based on specified conditions.

await db.deleteRecords({
  table: 'users',
  where: { id: 1 }
});

Data Retrieval

findAll(tableName, options)

Retrieves all records from a table.

// Basic usage
const users = await db.findAll('users');

// With pagination
const users = await db.findAll('users', { limit: 10, offset: 20 });

findColumns(tableName, colArray, options)

Retrieves specific columns from a table.

const users = await db.findColumns('users', ['id', 'name', 'email']);

findByWhere(tableName, whereObj, options)

Retrieves records that match specific conditions (AND logic).

const users = await db.findByWhere('users', { department: 'IT', active: true });

findByOR(tableName, whereObj, options)

Retrieves records that match any of the specified conditions (OR logic).

const users = await db.findByOR('users', { department: 'IT', department: 'HR' });

findWithOrderASC(tableName, whereObj, options)

Retrieves records with ascending order.

const users = await db.findWithOrderASC('users', { active: true }, { 
  orderBy: 'created_at',
  limit: 10
});

findWithOrderDESC(tableName, whereObj, options)

Retrieves records with descending order.

const users = await db.findWithOrderDESC('users', { active: true }, { 
  orderBy: 'created_at',
  limit: 10
});

findGroup(tableName, groupByCols, havingObj, whereObj, selectedCols, options)

Retrieves records with grouping and having clauses.

const result = await db.findGroup(
  'orders', 
  ['customer_id'], 
  { 'COUNT(*)': { '>': 5 } }, 
  { status: 'completed' }, 
  ['customer_id', 'COUNT(*) as order_count']
);

Advanced Query Builder

find(options)

General-purpose query builder with support for advanced features.

const results = await db.find({
  table: 'users',
  columns: ['id', 'name', 'email'],
  joins: [
    { type: 'INNER', table: 'departments', on: { left: 'users.department_id', right: 'departments.id' } }
  ],
  where: { 'users.active': true },
  or: { 'departments.name': 'IT' },
  filters: { 'users.last_login': { '>': '2023-01-01' } },
  groupBy: ['departments.name'],
  having: { 'COUNT(*)': { '>': 5 } },
  orderBy: { 'users.name': 'ASC' },
  limit: 10,
  offset: 20,
  distinct: true,
  alias: 'u',
  subqueries: { 
    'total_orders': 'SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id' 
  },
  expressions: ['CONCAT(first_name, " ", last_name) AS full_name'],
  functions: ['COUNT(*) as user_count'],
  window: 'w AS (PARTITION BY department_id ORDER BY hire_date)',
  withClause: 'dept_counts AS (SELECT department_id, COUNT(*) as count FROM employees GROUP BY department_id)'
});

rawQuery(sql, values)

Executes a raw SQL query with optional parameter binding.

const results = await db.rawQuery(
  'SELECT * FROM users WHERE created_at > ? AND status = ?', 
  ['2023-01-01', 'active']
);

Error Handling

All functions return Promises, so errors can be caught using try/catch with async/await:

try {
  const users = await db.findAll('users');
  console.log(users);
} catch (error) {
  console.error('Database error:', error);
}

Best Practices

• Always close the connection when your application shuts down
• Use parameter binding (done automatically by the library) to prevent SQL injection
• Consider using transactions for operations that need to be atomic
• For complex queries, use the general-purpose find method

License

MIT