Today, we are announcing a few new features for our open-source StarbaseDB that help improve developer workflows by bringing the reality of safely being able to execute SQL from the frontend to your hosted database. That means developers no longer have to contribute code to their API repository, merge, deploy, and find out they missed another piece of data they needed to, only to have to repeat that cycle again. Now you can write SQL statements in your frontend code and securely execute them against your database!

A quick preview of the features we are announcing today include:

• Allowlist – a set of queries you have explicitly granted permission to run

• Row Level Security – add table policies to be certain users only see results they have access to

• Authentication – using JWT to add context payloads accessible to your SQL

• Query Cache – for external data sources, cache read statements near the user for faster reads

Each feature above likely warrants its own blog post to delve (kidding, kidding) dive deeper into the inner-workings of each of them and the benefits they grant. For this post, though, we’ll keep it high level and talk about what the orchestration of these features means for developers.

How It Works

As it stands today your database is accessed via your hosted backend API. This allows a user to submit a request from the client → reach your API to verify they are allowed to access the data → fetch the data from your database → model the response → return that information to the user. The process is cumbersome and requires a lot of effort particularly for developers who are trying to work in high velocity environments.

To make writing SQL from the frontend possible we needed to be sure that:

1. Only authorized users could reach the databases

2. Users could only see their data

3. Users could not manipulate the system to get data they should not be able to

As you see in the diagram above a user makes a request and it reaches the first PoP (point of presence) as it begins its sequence of hops, skips and U-Turns about the internet. Because of Cloudflare’s vast global network there is a high chance your initial PoP has access to Cloudflare Workers. In addition to that, Workers offer a 0ms cold start time which means we can execute logic at no additional latency penalty (aside from the time it takes to execute the code itself) so we can quickly offer a response to a user either by rejecting their request (blocking access to the database) or serving cached data from the edge (relieving our database).

StarbaseDB now offers Allowlist as a feature which means you can specify a list of queries that are approved to continue through to the database for query execution. If your query does not match any entry on the list then an error response is quickly returned back to the client.

Authentication can be handled in one of three ways, all of which require passing a value in through the Authentication header of your network request.

1. Pass in the ADMIN_AUTHORIZATION_TOKEN value from your Wrangler TOML file and you get full access to your database. This is great for providing to database UI tools to browse your tables without restriction.

2. Pass in the CLIENT_AUTHORIZATION_TOKEN value from your Wrangler TOML file and you get access to the database but are restricted by the rules in place – for example, Allowlist (if enabled).

3. Pass in a valid JWT token from your authentication provider (e.g. Clerk). Today StarbaseDB supports RS256 algo tokens and a JWKS endpoint to verify the token is signed.

Row level security (RLS) allows us to utilize #3 from the above Authentication methods to use the JWT token to identify a user by their ID and begin applying policies to your tables. For example, if we have a todos table in our database with a column for user_id and another for the text of the todo item we have added – we only want our users requests to return with data where the sub value of their JWT token matches the user_id column in the database. This enables users to now run SELECT * FROM todos; and only receive the results associated to that user_id value.

Query Caching

In addition to what we discussed above, we now also allow you to cache queries from external data sources through StarbaseDB into the internal data source – or Durable Object. Perhaps you have a Postgres database with a complex query that takes a few seconds to complete but you do not want your users always waiting for this operation to complete to get a response. Now you can specify your query request with the following headers and we will automatically cache the response at the edge for you.

X-Starbase-Source: external
X-Starbase-Cache: true

By including the cache header it tells your instance to respond with any cached response to the user. If no cache value exists then it will execute the query and then store it in the internal data source for 60 seconds by default. Any request in that 60 second window will continue to get the cached value and once that expires the cache will be updated with the latest value.

Any cached request will return fast, our tests show usually around ~50ms roundtrip time in total. It doesn’t matter how long the original request might have taken or the complexity of the execution and aggregation of that request, the response itself is held in our Durable Object and no additional logic needs to run to prepare it for the user response.

There are a few restrictions to how this works today that we will continue to improve upon in the coming weeks that are worth note:

1. The maximum size per response we can cache is 1MiB in size due to RPC restrictions

2. Only READ/SELECT operations will be cached

3. Requests with dynamic query parameters at the moment do not get cached

Many optimizations to come but we are excited to offer database query caching for any database that StarbaseDB supports which includes the list below whether by supplying the database connection in your wrangler.toml or an Outerbase API key for the designated database type.

• Postgres

• MySQL

• Mongo

• D1

• SQL Server*

• MotherDuck*

• ClickHouse*

• Snowflake*

• BigQuery*

• Redshift*

• Turso

Try It Today

Below you will find an example repository with Clerk. To start using the new features today deploy a latest instance of StarbaseDB and toggle on the ENABLE_ALLOWLIST and ENABLE_RLS features via your wrangler.toml file. You can find more instructions in the Github example to get you started.

We believe in making all interactions with data as easy and secure as possible and this is a big step in that direction. The features mentioned today are all in Early Access and we would love to hear your feedback to continue building better developer experiences.

StarbaseDB: https://github.com/outerbase/starbasedb

Github Example: https://github.com/outerbase/frontend-sql-clerk-example