jsquery-core

v0.3.0

Published

4 months ago

Enterprise-grade SQL query builder with advanced performance optimization, caching, and lazy evaluation. Convert JavaScript objects to SQL with 99%+ cache hit rates.

jsQuery

A lightweight JavaScript library that converts JavaScript objects into SQL statements. Build complex SQL queries using familiar JavaScript syntax.

✨ Features

🔄 Object-to-SQL Conversion: Transform JS objects to SQL statements
🛡️ SQL Injection Protection: Built-in escaping and validation
🏗️ Query Builder: Support for SELECT, INSERT, UPDATE, DELETE
🔧 Functions & Operators: 60+ MySQL functions and comparison operators
🎯 Subqueries: Nested query support
✅ Type Safe: Input validation and error handling
🧪 Tested: Comprehensive test suite with 23+ test cases
🚀 High Performance: Enterprise-grade caching and optimization
⚡ Lazy Evaluation: Defer compilation until needed
📊 Performance Monitoring: Built-in metrics and profiling
🎛️ Configurable: Multiple optimization profiles

📦 Installation

npm install jsquery-core

🚀 Quick Start

const JSQuery = require('jsquery-core');

// Basic usage
const jsQuery = new JSQuery();
const query = jsQuery.selectQuery({
  select: ["id", "name", "email"],
  from: { table: "users" },
  where: { active: 1 }
});
// Output: SELECT id, name, email FROM users WHERE active = 1

// High-performance mode (recommended for production)
const optimized = new JSQuery({ 
  performance: true,  // Enable all optimizations
  cache: true,        // Query caching
  pooling: true       // Object pooling
});

// Advanced query with functions
const advancedQuery = optimized.selectQuery({
  select: [
    "id",
    JSQuery.fn('upper', 'name', 'display_name'),
    JSQuery.fn('date_format', 'created_at', '%Y-%m-%d', 'created_date'),
    JSQuery.fn('if', 'age >= 18', "'Adult'", "'Minor'", 'age_group')
  ],
  from: { table: "users" },
  where: {
    created_at: JSQuery.fn('gtthan', JSQuery.fn('date_sub', JSQuery.fn('now'), 30, 'DAY'))
  }
});

// Lazy evaluation for complex queries
const lazyQuery = optimized.lazy()
  .select({
    select: ['id', 'name'],
    from: { table: 'users' }
  })
  .where({ active: 1 })
  .orderBy(['created_at DESC'])
  .limit(0, 10);

// SQL is compiled only when needed
const sql = lazyQuery.toSql();

// Performance monitoring
const stats = optimized.getPerformanceStats();
console.log('Cache Hit Rate:', stats.cache.sql.hitRate);

🔍 API Reference

Select Queries

// Basic SELECT
jsQuery.selectQuery({
  select: ["column1", "column2"],
  from: { table: "table_name" }
});

// SELECT with WHERE
jsQuery.selectQuery({
  select: ["*"],
  from: { table: "users" },
  where: { 
    age: JSQuery.fn('gtthan', 18),
    status: 'active'
  }
});

// SELECT with JOIN
jsQuery.selectQuery({
  select: ["u.name", "p.title"],
  from: { table: "users", options: { as: "u" } },
  join: [{
    table: "posts",
    type: "LEFT JOIN",
    foreignKeys: ["user_id"],
    options: { as: "p" }
  }]
});

Insert Queries

jsQuery.insertQuery({
  insert: {
    table: "users",
    fieldValue: [
      { name: "John", email: "[email protected]", age: 30 },
      { name: "Jane", email: "[email protected]", age: 25 }
    ]
  }
});

Update Queries

jsQuery.updateQuery({
  update: {
    table: "users",
    fieldValue: { 
      name: "John Updated",
      last_modified: JSQuery.fn('now')
    }
  },
  where: { id: 1 }
});

Delete Queries

jsQuery.deleteQuery({
  delete: { table: "users" },
  where: { inactive: 1 }
});

🛠️ Available Functions & Operators

Comparison Operators

JSQuery.fn('eq', value) - Equals (=)
JSQuery.fn('gtthan', value) - Greater than (>)
JSQuery.fn('gtthaneq', value) - Greater than or equal (>=)
JSQuery.fn('lessthan', value) - Less than (<)
JSQuery.fn('lessthaneq', value) - Less than or equal (<=)
JSQuery.fn('noteq', value) - Not equal (!=)
JSQuery.fn('like', value) - LIKE pattern matching

MySQL Functions

Date/Time Functions

JSQuery.fn('now') - Current timestamp (NOW())
JSQuery.fn('curdate') - Current date (CURDATE())
JSQuery.fn('curtime') - Current time (CURTIME())
JSQuery.fn('date', column) - Extract date part (DATE())
JSQuery.fn('year', column) - Extract year (YEAR())
JSQuery.fn('month', column) - Extract month (MONTH())
JSQuery.fn('day', column) - Extract day (DAY())
JSQuery.fn('date_add', date, interval, unit) - Add time interval
JSQuery.fn('date_sub', date, interval, unit) - Subtract time interval
JSQuery.fn('datediff', date1, date2) - Difference between dates
JSQuery.fn('date_format', date, format) - Format date string
JSQuery.fn('unix_timestamp', date?) - Convert to Unix timestamp
JSQuery.fn('from_unixtime', timestamp, format?) - Convert from Unix timestamp

String Functions

JSQuery.fn('concat', str1, str2, ...) - Concatenate strings
JSQuery.fn('concat_ws', separator, str1, str2, ...) - Concatenate with separator
JSQuery.fn('substring', string, pos, length?) - Extract substring
JSQuery.fn('upper', string) - Convert to uppercase
JSQuery.fn('lower', string) - Convert to lowercase
JSQuery.fn('trim', string) - Remove leading/trailing spaces
JSQuery.fn('length', string) - String length
JSQuery.fn('replace', string, search, replace) - Replace substring
JSQuery.fn('left', string, length) - Left portion of string
JSQuery.fn('right', string, length) - Right portion of string

Mathematical Functions

JSQuery.fn('abs', number) - Absolute value
JSQuery.fn('round', number, decimals?) - Round number
JSQuery.fn('ceil', number) - Ceiling (round up)
JSQuery.fn('floor', number) - Floor (round down)
JSQuery.fn('pow', base, exponent) - Power function
JSQuery.fn('sqrt', number) - Square root
JSQuery.fn('mod', dividend, divisor) - Modulo operation
JSQuery.fn('rand', seed?) - Random number

Aggregate Functions

JSQuery.fn('count', column) - COUNT function
JSQuery.fn('sum', column) - SUM function
JSQuery.fn('avg', column) - Average (AVG)
JSQuery.fn('min', column) - Minimum value
JSQuery.fn('max', column) - Maximum value
JSQuery.fn('count_distinct', column) - COUNT(DISTINCT column)
JSQuery.fn('group_concat', column, separator?) - GROUP_CONCAT

Conditional Functions

JSQuery.fn('if', condition, true_value, false_value) - IF statement
JSQuery.fn('case_when', [condition1, value1], [condition2, value2], else_value) - CASE WHEN
JSQuery.fn('coalesce', value1, value2, ...) - First non-null value
JSQuery.fn('ifnull', column, default) - IFNULL function

Other Functions

JSQuery.fn('convert_tz', datetime, from_tz, to_tz) - CONVERT_TZ function

Logical Operators

JSQuery.and(condition1, condition2, ...) - AND operator
JSQuery.or(condition1, condition2, ...) - OR operator

🧪 Testing

Run the test suite:

npm test

🔒 Security

jsQuery includes built-in protection against SQL injection:

✅ String Escaping: Automatic escaping of single quotes and backslashes
✅ Input Validation: Type checking and validation for all inputs
✅ Parameter Binding: Safe parameter binding for all query types
✅ Error Handling: Descriptive error messages without exposing internals

example 1

const example1 = jsQuery.selectQuery(
  {
    select: ["_id", "idx", "name"],
    from: { 
      table: "tb_products"
    },
    where: {
      _id: 1
    },
    groupby: [
      "idx",
      "name"
    ],
    orderby: [
      "idx",
      "name"
    ],
    limit: {
      offset: 0,
      count: 10
    }
  }
);