QueryBuilder

QueryBuilder: Architectural Implementation and Capabilities

QueryBuilder is a key component in the ORM system, providing flexibility in creating SQL queries using an object-oriented approach. Architecturally, it consists of several parts, each responsible for different aspects of query construction. This allows for creating, updating, deleting, and aggregating data from the database, as well as managing query structure and caching.

QueryBuilder uses various classes to create specific types of queries:

1. SelectQueryBuilder - responsible for creating SELECT queries, including column selection, join conditions, filtering, sorting, result limiting, etc.

2. InsertQueryBuilder - handles INSERT queries for adding new records to a table.

3. UpdateQueryBuilder - implements UPDATE queries for modifying existing records.

4. DeleteQueryBuilder - responsible for DELETE queries for removing records.

5. AggregateQueryBuilder - handles aggregate functions such as SUM, COUNT, GROUP BY, and HAVING.

6. QueryStructureBuilder - manages query structure, including UNION and creating VIEW.

The QueryBuilder class is initialized with necessary dependencies, such as DataSourceInterface, CacheInterface, LoggerInterface, and MonitoringInterface. This allows for integrating caching, logging, monitoring, and query execution into the database.

The methods select, insert, update, and delete use the corresponding subclasses to form SQL queries. The build method generates the final SQL query, replacing parameters with the appropriate format, such as $1, $2 for PostgreSQL. The execute method performs the query, with the option to include monitoring and caching.

Key features of QueryBuilder include creating complex SQL queries using methods that support various operations: selection (select), insertion (insert), updating (update), deletion (delete), aggregation (sum, count), and query structuring (from, union, createView). Additionally, QueryBuilder supports methods for adding conditions (where, having), sorting (orderBy), limiting the number of rows (limit), and join operations (innerJoin, leftJoin, rightJoin). All these methods return this, allowing them to be used in method chaining for convenient and consistent query building.

Method chaining allows calling multiple methods in sequence within a single line of code, enhancing the convenience and readability of the code. For example, a query can be constructed as follows:

const query = queryBuilder
  .select(['name', 'age'])
  .from('users')
  .where({ conditions: { age: { gt: 18 } } })
  .orderBy('name', 'ASC')
  .limit(10)
  .build();

This implementation allows users to create complex queries using simple and understandable method chains.

QueryBuilder also has built-in caching functionality, allowing the storage of query results to improve performance. When caching is enabled, data is stored using a generated key, avoiding repeated queries to the database.

As a result, QueryBuilder provides a powerful tool for creating and executing SQL queries, integrating various aspects of query management, including caching, monitoring, and logging, making it a versatile and effective component in the ORM system.

Here’s how my QueryBuilder is implemented in code:

https://github.com/MyroslavShymon/ORM/blob/main/src/orm/context/query-builder/query-builder.ts