In my recent article, I explained how we used SQLite Cloud and SQLite’s full-text search extension to implement our documentation search. I noted that the performance of our solution was remarkable, with the total round-trip taking just 35ms.
While this performance was exciting, I was not totally satisfied with our solution. This is because we had to deploy and host an intermediary Go web server to proxy requests. Basically, we end up developing an entire backend just to support that functionality. This added unnecessary complexity and maintenance for the team.
Thankfully, our latest feature release introduces Edge Functions to SQLite Cloud! With edge functions, we can rewrite our application to remove the extraneous backend, simplifying our stack and increasing maintainability.
Rewriting the Application with Edge Functions
Edge functions in SQLite Cloud run directly on the node that stores your data, for the fastest performance possible. They run in a separate process from your database process, to protect your data from an edge function mishap. They are also isolated and secure by design, and executed in the Bun.js runtime environment.
Edge functions can be written in JavaScript, TypeScript, SQL and Python (experimental). For this demonstration, we will write logic in pure SQL.
We started by creating a new doc-search project with three nodes:
We then created a read-only user with read-only access to the documentation.sqlite database. The next step was to create a new APIKEY named doc-search based on the read-only user.
The final step of our refactor is to encapsulate the logic and functionality from our Go server into a SQLite Cloud edge function, so when user start typing in the search field a query is sent to the edge function url as a GET parameter, the code performed on server-side looks like:
SELECT url, replace(snippet(documentation, -1, '<b>', '</b>', '...', 10), x'0A', ' ') as 'snippet' FROM documentation WHERE content MATCH concat($query, '*') ORDER BY rank LIMIT 256;
The above SQL code runs against the documentation.sqlite database and documentation table, previously created by running:
CREATE VIRTUAL TABLE documentation USING fts5 (url, content);
To speed up the execution of the query we decided to disable linearizable reads inside the cluster, because our docs are updated infrequently while reads occur very often, so eventual consistency is acceptable. Linearizable reads guarantee that any read reflects the most recent write, which is crucial for applications requiring strict consistency, but reads may introduce additional latency because they often require communication with the leader or synchronization with the latest committed state.
The final result looks like:
Note that you can debug any edge function from within our UI by using the Test button and passing your test values in the Query or Body section:
Finally, we removed the Go backend. When you implement a real-time search engine, the result must change every time the letter changes. It's important for the latency to be under 100ms - any longer, and the user will detect the delay! In our new solution, our latency was consistently about 40 ms without any additional optimization and using the smallest AWS instance we can rent.
Documentation search component and GitHub action
We were so happy with the results, we decided to write a GitHub action and headless component for you to drop into your existing documentation project. You can test it with your own edge function url here!
It costs around $45 to serve 100k searches per month on Algolia (with their “Grow” plan). With SQLite Cloud, our smallest paid instance can offer similar performance around the globe for just $5 a month (a ~90% savings).
What’s next?
Edge functions enable users to integrate third-party services and edge data processing on top of their SQLite database. Our edge functions have acceptable performance, but we are working on an improvement to our architecture to push their performance even further. We’re excited to see what our users build with this new feature!
If you’d like to learn more, you can read our documentation, or sign up for a free account.
Until next time. Ciao!
