Overview

Assemble database queries without having to know SQL with the Outerbase query builder.

The query builder is intended to read as closely to writing real SQL statements as possible. Understanding how SQL works and being able to transition from a query builder to using database tools and executing raw database queries can prove to be helpful for many use cases. Ideally developers, however new or veteran to SQL, can find value in the query builder and build confidence writing queries themselves.

What is a query builder?

A query builder allows you to construct an executable SQL statement by chaining together a series of conditions together. You can do this in the Outerbase SDK by calling pre-defined functions in a series, passing various parameters into them to help define what constraints should be expressed in the SQL output.

Tools like these can be very powerful, assisting to remove the requirement of knowing how database engines require the assembly of SQL statements and abstracting away many of the complexities that come with more complicated requests such as joining together multiple tables on various conditions.

How it works

As you can see in the example below, after you've instantiated a database connection you can use Typescript classes to compose a query that will be eventually executed against the database when the final chain the sequence .query() is hit. Each section of this chain is executed in the order listed.

// Create a connection to your database
const connection: OuterbaseConnection = new OuterbaseConnection(
    'YOUR_API_KEY'
)
const db = Outerbase(connection)

// Send a query request to the database
const { data, error } = db
    .selectFrom([{ table: 'users', columns: ['id'] }])
    .where(greaterThanOrEqualNumber('id', 100))
    .limit(10)
    .query()

For each line of the query builder the function being called has its own very specific job to store the metadata so it can eventually construct it into a complete SQL statement once all of the compositional aspects of the query builder have been completed.

At the end of the chain, generally there is a function that is called the does an action.

.query()

As the above example shows you can chain together commands and once you're satisified with all of the constraints you can place .query() at the end of the chain. This function must always be called at the end of the sequence.

This function outputs two variables you can tap into for information on what was returned:

• data = The data response from the database

• error = An error description string if an error occurred

.toString()

When you want to see what the constructed final SQL statement of a query builder chain would produce, instead of executing it with the .query() command noted above, you can instead use .toString() to print out a string instance of the SQL statement.

const sql = db
    .selectFrom([{ table: 'users', columns: ['id'] }])
    .where(greaterThanOrEqualNumber('id', 100))
    .limit(10)
    .toString()

Named vs Positional Parameters

When producing SQL queries, there are two types of parameterized queries that exist. These two approaches have slightly different ways of sending the data into the database provider to keep it sanitized, and putting the right parameter values in the right spots in the query.

Each connection type in the Outerbase SDK can define how their API or database engine takes query parameters – either named or positional.

Named Parameters

Named parameters take place of where a value should eventually be replaced in a SQL statement.

INSERT INTO table_name (id, name) VALUES (:id, :name)

The above statement does not pass the actual raw values intended to be used in the query, instead it replaces where the values should go with a named version of the property, such as :name.

Additional information gets passed in alongside the query so it knows what data should be replacing the named fields. That data object usually looks similar to below.

{
    "id": 1234,
    "name": "Space Cowboy"
}

Positional Parameters

Positional parameters take place of where a value should eventually be replaced in a SQL statement, but is very dependent on the order in which the placeholders are seen in the statement.

INSERT INTO table_name (id, name) VALUES (?, ?)

Unlike the named parameters, we just have ? characters in the places where our values should exist. Data for this gets passed in usually by way of an array and it just replaces it in order.

[
    1234,
    "Space Cowboy"
]