AgileQuery

AgileQuery

TypeScript Support

alt text

1. deleteQuery Function Documentation

# `deleteQuery` Function

The `deleteQuery` function generates a DELETE SQL query based on the provided parameters. It supports filtering, joins, sorting, and limiting the number of rows to delete.

## Function Signature

```ts
export function deleteQuery<Tables extends string[]>(params: DeleteQueryParams<Tables>): string;

Parameters

  • table: The table to delete rows from.

    • Type: string

  • where: The condition to delete rows (WHERE clause).

    • Type: string

  • sort (optional): Sorting criteria for the DELETE query. Can be applied to the main table or related tables.

    • Type: SortType<Tables>

  • limit (optional): The maximum number of rows to delete.

    • Type: string | number

  • joins (optional): Joins to include in the query, supporting different JOIN types.

    • Type: JoinsType<Tables>

Example

const query = deleteQuery({
  table: 'orders',
  where: 'status = "pending"',
  joins: [
    { type: 'INNER JOIN', table: 'customers', on: 'orders.customer_id = customers.id' }
  ],
  sort: { orders: { order_date: -1 } },
  limit: 10
});

console.log(query);
// Output:
// DELETE orders FROM orders 
// INNER JOIN customers ON orders.customer_id = customers.id 
// WHERE status = "pending" 
// ORDER BY order_date DESC 
// LIMIT 10;

2. insertQuery Function Documentation

# `insertQuery` Function

The `insertQuery` function generates an INSERT SQL query. It supports inserting one or more rows, handling timestamp fields, managing unique constraints, and handling duplicate key conflicts.

## Function Signature

```ts
export function insertQuery(params: insertQueryParams): string;

Parameters

  • table: The name of the table where data will be inserted.

    • Type: string

  • insertData: The data to insert into the table, either as a single object or an array of objects.

    • Type: Record<string, string | number> | Record<string, string | number>[]

  • dateFields (optional): The names of timestamp fields to include in the insertion.

    • Type: string[]

  • uniqueColumn (optional): The name of a unique column to prevent duplicate entries.

    • Type: string | null

  • onDuplicateUpdateFields (optional): Fields to update if a duplicate key conflict occurs.

    • Type: string[]

Example

const query = insertQuery({
  table: 'products',
  insertData: [
    { name: 'Laptop', price: 1000 },
    { name: 'Phone', price: 500 }
  ],
  uniqueColumn: 'name',
  onDuplicateUpdateFields: ['price']
});

console.log(query);
// Output:
// INSERT INTO products (name, price) 
// VALUES ('Laptop', 1000), ('Phone', 500) 
// ON DUPLICATE KEY UPDATE price = VALUES(price);

3. selectSQL Function Documentation

# `selectSQL` Function

The `selectSQL` function builds a flexible MySQL SELECT query. It supports joins, aggregates, grouping, sorting, and more, to allow complex queries with various configurations.

## Function Signature

```ts
export function selectSQL<Tables extends string[]>(config: SelectQueryParams<Tables>): string;

Parameters

  • distinct (optional): Whether to apply DISTINCT to the query results.

    • Type: boolean

  • table (optional): The table to select data from.

    • Type: string

  • sort (optional): Sorting order for the query. Use 1 for ascending and -1 for descending.

    • Type: Record<string, 1 | -1>

  • limitSkip (optional): Limit and offset for pagination.

    • Type: { limit?: number; skip?: number }

  • columns (optional): Specific columns to select or * for all.

    • Type: string | string[] | { extra?: string | string[] }

  • groupBy (optional): Columns to group the results by.

    • Type: string | string[] | { extra?: string | string[] }

  • aggregates (optional): Aggregates like COUNT, SUM, AVG, etc.

    • Type: Array<{ [key in AggregateFunctions]?: string }>

  • where (optional): The WHERE clause condition.

    • Type: string

  • having (optional): The HAVING clause for filtered aggregates.

    • Type: string

  • subQueries (optional): Subqueries to include in the select columns.

    • Type: { query: string, as?: string }[]

  • joins (optional): JOIN clauses to link other tables.

    • Type: { on?: string, operator?: OperatorType | string, type?: 'JOIN' | 'INNER JOIN' | 'OUTER JOIN' | 'CROSS JOIN' | 'RIGHT JOIN' | 'LEFT JOIN' }[]

  • recursiveCTE (optional): Recursive CTE for hierarchical data.

    • Type: { baseCase: string, recursiveCase: string, alias: string }

Example

const query = selectSQL({
  table: 'orders',
  columns: { orders: ['order_id', 'status', 'customer_id'] },
  where: 'status = "shipped"',
  sort: { orders: { order_date: -1 } },
  limitSkip: { limit: 10, skip: 0 },
  aggregates: [
    { COUNT: 'order_id' },
    { SUM: 'total_amount' },
    { AVG: 'rating', alias: 'average_rating' }
  ],
  joins: [
    { type: 'INNER JOIN', table: 'customers', on: 'orders.customer_id = customers.customer_id' }
  ],
  groupBy: { orders: ['customer_id'] },
  having: 'COUNT(order_id) > 5'
});

console.log(query);
// Output:
// SELECT order_id, status, customer_id, COUNT(order_id) AS count, SUM(total_amount) AS summation, AVG(rating) AS average_rating 
// FROM orders
// INNER JOIN customers ON orders.customer_id = customers.customer_id
// WHERE status = "shipped"
// GROUP BY customer_id
// HAVING COUNT(order_id) > 5
// ORDER BY order_date DESC
// LIMIT 10 OFFSET 0;