A SQL query builder can be a lifesaver when you need to write a single query that works across different databases. It can be tough to create queries that are compatible with the syntax and features of each database. But a query builder makes it easy by abstracting away these differences, so you can write queries in a unified way.
One of the biggest advantages of using a query builder is that it reduces the likelihood of errors. With its API for constructing queries using valid SQL syntax, you don't have to worry about getting the syntax right or making mistakes in your queries.
Another great thing about query builders is that they help improve code readability and maintainability. By encapsulating the complexity of constructing SQL statements behind an API, developers can focus on writing high-level code that expresses their intent more clearly. Plus, changes to the underlying database schema or structure can be accommodated more easily since they only require updates to the query builder's implementation, rather than every place where raw SQL statements are used.
You've probably seen this functionality in many Content Management Systems (CMS) or frameworks that support multiple databases.
And here's where it gets really interesting: we can use the power of Proxy to implement a SQL query builder that allows us to construct SQL queries in a more intuitive and readable way. In this post, we'll learn how to use JavaScript Proxy to implement a simple SQL query builder.
Designing the API of a query builder
The API of a query builder is designed to be user-friendly, allowing users to create complex SQL queries without needing to be experts in SQL syntax.
Usually, a query builder's API includes a range of methods that correspond to different parts of an SQL statement. For instance, there may be methods for selecting specific columns from a table, filtering rows based on certain conditions, grouping rows based on specific columns, and sorting the results.
These methods are often chainable, which means that users can call them one after another in a fluent interface style. This makes it possible to build complex queries by chaining together multiple method calls.
Here is an example of how the API might look:
const builder = new QueryBuilder('users');
const query = builder
.select.id
.select.name
.select.email
.where.id(1)
.where.email('"example@example.com"')
.orderBy.id('DESC')
.orderBy.email('ASC')
.build();
The code example above shows how to use a query builder to make an SQL SELECT statement. The query chooses the id, name, and email columns from a table named users. It then applies two filters to the results, returning only the rows where the id is equal to 1 and the email is equal to 'example@example.com'. Finally, it sorts the results by the id column in descending order, and then by the email column in ascending order.
This sample code can generate the following query:
SELECT id, name, email
FROM users
WHERE id = 1 AND email = "example@example.com"
ORDER BY id DESC, email ASC
In addition to the essential ways of building queries, some query builders offer convenient methods for everyday tasks, such as inserting or updating records.
The ultimate objective of a query builder's API is to simplify the process of constructing valid SQL queries for users. This means that users don't have to deal with the complexities of the underlying syntax.
Initializing the query builder
Let's begin building our SQL query by creating a new instance of the QueryBuilder class with the new keyword. You can also pass in the name of the table you want to query as an optional argument in the constructor function.
class QueryBuilder {
constructor(table) {
this._table = table;
this._select = [];
this._where = [];
this._orderBy = [];
}
}
The QueryBuilder class has three properties that are crucial for building SQL queries: _select, _where, and _orderBy.
The _select property is an array that holds the names of the columns we want to select from the table. We can add values to this array by using the select method, which accepts one or more arguments that represent column names.
The _where property is an array that stores the conditions for filtering rows in the table. Each condition has a column name, an operator, and a value. We can add values to this array by using the where method, which takes two arguments: a column name and a value.
The _orderBy property is an array that determines the sorting criteria for ordering rows in the table. Each criterion consists of a column name and a direction (either ASC or DESC). We can add values to this array by using the orderBy method, which takes two arguments: a column name and a direction.
By keeping these values in separate arrays, we can create complex SQL statements by combining them in different ways. For example, we can add multiple columns to the SELECT statement, multiple conditions to the WHERE clause, or multiple sorting criteria to the ORDER BY clause.
Selecting columns
To select columns in SQL, we can use the select property of the QueryBuilder instance. We can use Proxy to intercept the property access and add the selected columns to an internal array.
class QueryBuilder {
get select() {
return new Proxy({}, {
get: (target, property) => {
this._select.push(property);
return this;
},
});
}
}
The QueryBuilder class has a helpful method called select that allows users to choose which columns from a table they want to work with. When you call select, it returns a Proxy object that keeps track of which columns you want to use. This is done by pushing the column names into an internal _select array.
For example, if you call select with the arguments id, name, and email:
const builder = new QueryBuilder('users');
const result = builder
.select.id
.select.name
.select.email
Then the code below will be executed:
this._select.push('id');
this._select.push('name');
this._select.push('email');
We're going to add some column names to the _select array: id, name, and email. These columns will later be included in our SELECT statement when we call the build() method of our QueryBuilder instance.
Using Proxy objects makes it easier to construct SQL queries with a more intuitive and readable API. By chaining multiple calls to the select method, users can easily specify which columns they want to include in their query.
Filtering rows
To filter specific rows in our SQL query, we can utilize the where property of the QueryBuilder instance. By using Proxy, we can intercept the property access and add the filter condition to an internal array.
class QueryBuilder {
get where() {
return new Proxy({}, {
get: (target, property) => {
return (value) => {
this._where.push(`${property} = ${value}`);
return this;
};
},
});
}
}
We have a new tool in our query arsenal: the where property. It allows us to easily filter rows and get exactly what we need.
const builder = new QueryBuilder('users');
const result = builder
.select.id
.select.name
.select.email
.where.id(1)
.where.email('"example@example.com"');
console.log(result);
// QueryBuilder {
// _select: [ 'id', 'name', 'email' ],
// _where: [ 'id = 1', 'email = "example@example.com"' ],
// ...
// }
Ordering rows
To sort the rows in our SQL query, we can utilize the orderBy property of the QueryBuilder instance. With the help of Proxy, we can intercept the property access and add the ordering condition to an internal array. This makes it easy to specify the order in which we want our data to be returned.
class QueryBuilder {
get orderBy() {
return new Proxy({}, {
get: (target, property) => {
return (direction) => {
this._orderBy.push(`${property} ${direction}`);
return this;
};
},
});
}
}
We can now use the orderBy feature to sort rows in our query.
const builder = new QueryBuilder('users');
const result = builder
.select.id
.select.name
.select.email
.orderBy.id('DESC')
.orderBy.email('ASC');
console.log(result);
// QueryBuilder {
// _select: [ 'id', 'name', 'email' ],
// _orderBy: [ 'id DESC', 'email ASC' ],
// ...
// }
Creating the SQL query
To create the final SQL query, we can simply add a build method to the QueryBuilder class. This method will combine the selected columns, filter conditions, and ordering conditions. With this, we can easily build a comprehensive SQL query to get the data we need.
class QueryBuilder {
build() {
const select = this._select.join(', ');
const where = this._where.length ? `WHERE ${this._where.join(' AND ')}` : '';
const orderBy = this._orderBy.length ? `ORDER BY ${this._orderBy.join(', ')}` : '';
return `SELECT ${select} FROM ${this._table} ${where} ${orderBy}`;
}
}
We're all set to get the final SQL query using the build method.
const builder = new QueryBuilder('users');
const query = builder
.select.id
.select.name
.select.email
.where.id(1)
.where.email('"example@example.com"')
.orderBy.id('DESC')
.orderBy.email('ASC')
.build();
console.log(query);
// SELECT id, name, email
// FROM users
// WHERE id = 1 AND email = "example@example.com"
// ORDER BY id DESC, email ASC
And that's a wrap! By using JavaScript Proxy, we've successfully created a SQL query builder that simplifies the process of constructing SQL queries, making it more intuitive and easier to read.
Conclusion
To sum it up, a SQL query builder can be a really useful tool for developers who need to create complex SQL queries in a simple and easy way. By handling the details of SQL syntax and providing an easy-to-use API for building queries, query builders can help reduce errors, improve code readability and maintainability, and make it easier to work with multiple databases.
Although we've only just scratched the surface of what's possible with JavaScript Proxy-based query builders, we hope this post has given you a glimpse of their power and flexibility. Whether you're building a web application or working on a data-heavy project, incorporating a query builder into your workflow could save you time and help you write cleaner, more maintainable code.
If you found this series helpful, please consider giving the repository a star on GitHub or sharing the post on your favorite social networks 😍. Your support would mean a lot to me!
If you want more helpful content like this, feel free to follow me:
Top comments (0)