Getting Started>Installation

Prerequisites

Node.js: Ensure you have Node.js installed on your machine. You can check if it's installed by running:
```
node -v
```
If it's not installed, download it from nodejs.org.
npm: npm (Node Package Manager) is bundled with Node.js. You can check if it's installed by running:
```
npm -v
```

Step 1: Install AgileQuery

To install AgileQuery in your project, simply run the following command in your project directory:

npm install agile-query

Alternatively, you can use Yarn for installation:

yarn add agile-query

This will add AgileQuery to your node_modules and update your package.json accordingly.

Documentation for Developers

1. Importing the Library

After installing AgileQuery, you can import and use it in your JavaScript or TypeScript project:

For JavaScript

const agileQuery = require('agile-query');

For TypeScript

import agileQuery from 'agile-query';

2. Basic Usage Example:

const agileQuery = require('agile-query');

// Example usage for building a SELECT query
const query = agileQuery.selectSQL({
  table: 'orders',
  columns: ['order_id', 'status', 'customer_id'],
  where: 'status = "shipped"',
  sort: { order_date: -1 },
  limitSkip: { limit: 10, skip: 0 },
  aggregates: [
    { COUNT: 'order_id' },
    { SUM: 'total_amount' },
    { AVG: 'rating', alias: 'average_rating' }
  ]
});

console.log(query);

This would generate the following query:

SELECT order_id, status, customer_id, COUNT(order_id), SUM(total_amount), AVG(rating) AS average_rating
FROM orders
WHERE status = "shipped"
ORDER BY order_date DESC
LIMIT 10 OFFSET 0;

3. Function Breakdown

`selectSQL(config)`

This is the main function in AgileQuery for generating SQL queries. It supports a wide range of SQL features.

Parameters

config: An object containing the following properties:
- distinct: (boolean) Whether to apply DISTINCT to the query.
- table: (string) The name of the table from which to select data.
- columns: (array or object) An array of column names or an object with tables as keys and columns as values.
- sort: (object) An object for sorting. Example: { column_name: 1 } for ascending, -1 for descending.
- limitSkip: (object) An object for pagination. Example: { limit: 10, skip: 0 }.
- where: (string) The WHERE clause condition.
- having: (string) The HAVING clause, for filtering on aggregates.
- subQueries: (array) Array of subqueries to include in the select columns.
- joins: (array) An array of JOIN clauses to link other tables.
- recursiveCTE: (string) Recursive CTE (Common Table Expression) for hierarchical data.

Returns

A string containing the generated SQL query.

Example

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

console.log(query);

The output would be:

SELECT order_id, total_amount, COUNT(order_id), SUM(total_amount)
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id
WHERE status = "shipped"
ORDER BY order_date DESC
LIMIT 5 OFFSET 0;

4. Query Execution Example

You can use AgileQuery in combination with mysql2 or any other MySQL client to execute the generated query.

Example using `mysql2`

First, install mysql2 if you haven’t already:

npm install mysql2

Then, you can execute the query:

const mysql = require('mysql2');
const agileQuery = require('agile-query');

// Create a MySQL connection
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'my_database'
});

// Generate a SELECT query
const query = agileQuery.buildMySQLQuery({
  table: 'orders',
  columns: ['order_id', 'status'],
  where: 'status = "shipped"',
  sort: { order_date: -1 }
});

// Execute the query
connection.execute(query, (err, results) => {
  if (err) {
    console.error('Error executing query', err);
  } else {
    console.log('Query results:', results);
  }

  connection.end();
});

This will execute the generated query against your MySQL database and log the results.

5. Advanced Features:

Join Queries

You can easily add various types of joins:

const query = agileQuery.selectSQL({
  table: 'orders',
  columns: ['order_id', 'status'],
  joins: [
    { type: 'INNER JOIN', table: 'customers', on: 'orders.customer_id = customers.id' },
    { type: 'LEFT JOIN', table: 'products', on: 'orders.product_id = products.id' }
  ],
  where: 'status = "shipped"'
});

console.log(query);

This generates:

SELECT order_id, status
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id
LEFT JOIN products ON orders.product_id = products.id
WHERE status = "shipped";

Aggregates

Use aggregate functions such as COUNT, SUM, AVG, etc.

const query = agileQuery.selectSQL({
  table: 'orders',
  aggregates: [
    { COUNT: 'order_id' },
    { SUM: 'total_amount' },
    { AVG: 'rating' }
  ],
  where: 'status = "shipped"'
});

console.log(query);