Today in celebration of the launch week announcement from Outerbase we’re releasing a blog post that digs into a part of StarbaseDB that makes it more expansive than ever with feature capabilities. If you haven’t yet seen the Outerbase announcement then check it out here. As of today the Outerbase platform is allowing any database connected through it to be powered by a companion StarbaseDB instance to help supercharge existing databases by providing features such as REST API’s, web sockets, and more…. well, since they are being accessed by a StarbaseDB after all!
But this blog post is for us to dig into query hooks. What are they? When do they happen? What can they make possible for us?
Pre-Query Hooks
Databases have two primary aspects to them, compute and storage. Storage is either on disk or in memory to be accessed by a process, while compute manages a whole slew of other pieces to the puzzle. In the case of StarbaseDB we have two functions in our code that serve as “hooks”, or a point in the process where we can inject custom functionality in the compute cycle.
Before accessing the data in storage our pre-query hook will execute an array of functions declared by our instance. By default it is an empty array. As an example to how it might work though, imagine we want to verify that the SQL query the user is attempting to execute is allowed by a static array of queries we have marked as "allowed”. Each pre-query hook function will receive the SQL statement and its parameters to execute logic against and return back one of two things:
Valid SQL statement, perhaps altered from what it received initially
Throwable error to return immediately back to the user with
In the case where we receive valid SQL then our system will continue running through any remaining pre-query hook functions and assuming none of those throw an error it will only then be passed on to execute the final SQL statement against the database. Note that any number of these functions can return a different SQL statement than what was passed in!
async function beforeQuery(sql: string, params?: any[], dataSource?: DataSource, env?: Env): Promise<{ sql: string, params?: any[] }> {
// Do your operations here
sql = await env?.RLS.applyRLS(sql, dataSource?.source === Source.internal ? 'sqlite' : env?.EXTERNAL_DB_TYPE)
return {
sql,
params
}
}
Examples:
Authorize the user has permissions to execute the query
Manipulate the query to return only specific rows for the current user
Post-Query Hooks
After the pre-query hooks have potentially validated and/or transformed the incoming SQL statement, and then the database has executed that SQL statement to return results, then comes the post-query hooks. These functions each receive both the SQL statement the database executed as well as the results the database is intending to respond back to the caller with.
At this point in the workflow is where functions can transform, cleanse or do whatever other tasks deemed necessary before the data leaves the server as a response. Imagine a use case where we may allow a SQL statement to be ran to get user data but we never want the column for their Social Security Number to be returned to any caller under any circumstance. We may have a post-query hook function that intends to filter that column from the response altogether.
async function afterQuery(sql: string, result: any, isRaw: boolean, dataSource?: DataSource, env?: Env): Promise<any> {
// Do your operations here
result = await env?.DATA_MASKING.maskQueryResult(sql, result);
return result;
}
Examples:
Remove data from the response that the user should not have access to
Inject additional data that the caller may need to complete their task
Imagining Possibilities
We are building StarbaseDB in the way that we are to allow for easy and reusable community contributions for all databases across the planet. Previously to accomplish some of these tasks you would need to spin up your own backend server, handle database connection pooling interactions, add in these logical pieces yourself or through an NPM package, and then manage the deployment process on your preferred cloud offering. What we are encouraging is to simply attach your database to StarbaseDB (or use the built-in SQLite offering) and get all of this out of the box with zero-hassle.
Before you run any SQL query against your database, execute any amount of code that may be necessary for your use cases to ensure safest data access whether it be with SQL allow-list checking, rate limiting, row level security SQL WHERE clause manipulation or more.
After your query has returned with results from the database then verify the right data is going to the right user. Remove any data that may be deemed unnecessary for their privileges, mask or redact data to hide private information, or inject additional data perhaps from other data stores to enrich the response.
Conclusion
This is just the beginning. We believe in an offering that brings the best of all features from any database provider in an easy to deploy tool in front of any data source. You can define your own custom functions and inject them in our two functions available in the operations.ts file.
With this you can essentially make the compute aspect of any database bend to your will with the help of Cloudflare’s expansive globally distributed network. With almost any request you’re likely to not incur much if any additionally latency between the caller and the data source as Cloudflare Workers work at the edge!
async function beforeQuery(sql: string, params?: any[], dataSource?: DataSource, env?: Env): Promise<{ sql: string, params?: any[] }> {
return { sql, params }
}
async function afterQuery(sql: string, result: any, isRaw: boolean, dataSource?: DataSource, env?: Env): Promise<any> {
return result;
}
Join the Adventure
We’re working on building an open-source database offering with building blocks we talk about above and more. Our goal is to help make database interactions easier, faster, and more accessible to every developer on the planet. Follow us, star us, and check out Outerbase below!
Twitter: https://twitter.com/BraydenWilmoth
Github Repo: github.com/Brayden/starbasedb
Outerbase: https://outerbase.com