Build a SQL query builder

Written byPhuoc Nguyen

Created

15 Jan, 2024

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:

sql

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 post 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!

Discover the power of a revocable Proxy←

Simplify document cookies management→

Questions? 🙋

Do you have any questions about front-end development? If so, feel free to create a new issue on GitHub using the button below. I'm happy to help with any topic you'd like to learn more about, even beyond what's covered in this post.

While I have a long list of upcoming topics, I'm always eager to prioritize your questions and ideas for future content. Let's learn and grow together! Sharing knowledge is the best way to elevate ourselves 🥷.

Ask me questions

Newsletter 🔔

If you're into front-end technologies and you want to see more of the content I'm creating, then you might want to consider subscribing to my newsletter.

By subscribing, you'll be the first to know about new articles, products, and exclusive promotions.

Don't worry, I won't spam you. And if you ever change your mind, you can unsubscribe at any time.

Phước Nguyễn