Version 1.0.1: Initial Release 🚀

This release introduces utility functions for building dynamic MySQL queries with strong type definitions. The package focuses on flexibility, reusability, and developer-friendly documentation.

Key Features

1. DELETE Query Builder (deleteQuery)
   Generate flexible SQL DELETE queries with support for:

   • WHERE conditions
   • JOIN clauses
   • Sorting (ORDER BY)
   • Pagination (LIMIT)

2. INSERT Query Builder (insertQuery)
   Dynamically construct INSERT queries with features like:

   • Single or bulk inserts
   • Handling timestamp fields (e.g., created_at, updated_at)
   • Conflict resolution (INSERT IGNORE, ON DUPLICATE KEY UPDATE)

3. SELECT Query Builder (selectQuery)
   Create advanced SELECT queries with support for:

   • Distinct selection
   • Aggregates (COUNT, SUM, AVG, etc.)
   • Grouping (GROUP BY) and filtering (HAVING)
   • Joins, recursive CTEs, subqueries, and more

4. UPDATE Query Builder (updateQuery)
   Flexible UPDATE query builder supporting:

   • Conditional updates (CASE WHEN)
   • Joins
   • Null/default value management
   • Inline calculations (SET)

5. Utility Function (mysql_datetime)
   Format dates into MySQL-compatible DATETIME strings.

Type Definitions

1. JoinsType and SortType
   These define the structure for JOIN and ORDER BY clauses.

2. DeleteQueryParams<Tables>
   Parameters for building DELETE queries:

   • table: string
   • where: string
   • joins?: JoinsType<Tables>
   • sort?: SortType<Tables>
   • limit?: string | number

3. insertQueryParams
   Parameters for building INSERT queries:

   • table: string
   • insertData: Record<string, any> | Record<string, any>[]
   • dateFields?: string[]
   • uniqueColumn?: string | null
   • onDuplicateUpdateFields?: string[]

4. SelectQueryParams<Tables>
   Parameters for building SELECT queries:

   • distinct?: boolean
   • table?: string
   • columns?: string | string[] | Record<string, string[]>
   • where?: string
   • joins?: JoinsType<Tables>
   • aggregates?: Array<Record<string, string>>
   • groupBy?: string | string[]
   • having?: string
   • limitSkip?: { limit?: number; skip?: number }

5. updateQueryParams<Tables>
   Parameters for building UPDATE queries:

   • table: string
   • updateData?: Record<string, any>
   • sort?: SortType<Tables>
   • where: string
   • limit?: string | number
   • joins?: JoinsType<Tables>
   • setCalculations?: Record<string, string>

Example Usage

DELETE Query:

const query = deleteQuery({
    table: 'users',
    where: 'age > 30',
    sort: { users: { id: -1 } },
    limit: 10,
});
console.log(query);
// Output: DELETE users FROM users WHERE age > 30 ORDER BY id DESC LIMIT 10;

INSERT Query:

const query = insertQuery({
    table: 'products',
    insertData: { name: 'Laptop', price: 1500 },
    dateFields: ['created_at'],
});
console.log(query);
// Output: INSERT INTO products (name, price, created_at) VALUES ('Laptop', 1500, CURRENT_TIMESTAMP);

SELECT Query:

const query = selectQuery({
    table: 'orders',
    columns: { orders: ['order_id', 'status'] },
    where: 'status = "shipped"',
    joins: [
        { type: 'INNER JOIN', table: 'customers', on: 'orders.customer_id = customers.id' }
    ],
    sort: { orders: { created_at: -1 } },
    limitSkip: { limit: 5 },
});
console.log(query);
// Output: SELECT orders.order_id, orders.status FROM orders 
//         INNER JOIN customers ON orders.customer_id = customers.id
//         WHERE status = "shipped" ORDER BY created_at DESC LIMIT 5;

UPDATE Query:

const query = updateQuery({
    table: 'users',
    updateData: {
        name: 'Updated Name',
        last_login: {
            case: [{ when: 'status = "active"', then: 'CURRENT_TIMESTAMP' }],
            default: 'NULL',
        },
    },
    where: 'id = 1',
});
console.log(query);
// Output: UPDATE users SET name = 'Updated Name', 
//         last_login = CASE WHEN status = "active" THEN CURRENT_TIMESTAMP ELSE NULL END WHERE id = 1;

Utility (mysql_datetime):

const formattedDate = mysql_datetime(new Date());
console.log(formattedDate);
// Output: 2024-11-24 14:32:00

Get Started

Install agile-query via npm:

npm install agile-query

This is just the beginning! Stay tuned for future updates as we expand the feature set of agile-query. 🚀