Simplifying the Management of Remote SQLite Databases

Introduction

With the growing popularity of SQLite usage in websites, particularly with LiteFS, there is an increasing demand to manage remote databases. Query aims to give you straightforward access to your remote databases.

What is Query?

Query is a Rust server that runs alongside your application and enhances its functionality without the application's knowledge. Query provides an API, CLI, and an authentication system that enables remote interaction with your SQLite databases.

The /query entry point of Query is both powerful and simple, utilizing a basic payload consisting of a database name, a query, and parameters to send requests to remote databases.

{
  "db_name": "kv.sql",
  "query": "INSERT INTO kv (key, value) VALUES (?, ?);",
  "params": ["1", 1]
}

It also provides functions (without sandboxing by default) like Edge Functions from Vercel or Deno subhosting or Cloudflare Workers, but with direct access to your databases. They are JavaScript functions executed in Rust using a runtime based on Deno. The functions are stored in a database and can be replicated through LiteFS, putting them closer to the user.

// get.index.js
export async function handleRequest(req) {
    const db = new Database("example.sql");

    const result = await db.query("SELECT * FROM example WHERE id = ?", [1]);

    return new Response(JSON.stringify({data: result}), {
      status: 200,
      headers: {
          "content-type": "application/json",
      },
  });
}

Query allows you to create branches, it duplicates a database to be utilized in development environments.

query branch <SUBCOMMAND>

Additionally, Query provides a basic migration system that manages your databases by sending a batch of queries.

query migration <DB_NAME> <PATH>

Benefits of using Query

The benefits of using Query are endless, as it provides a robust API on top of your databases.

With Query CLI you can safely manage your databases, from creating a branch to testing a migration before executing it in production to a function that receives a request from a webhook coming from a headless CMS and manipulates the data received before inserting it into the database.

Query Studio showcases the power of Query. It is an online SQLite editor for Query, allowing you to access databases using a UI built with Bun. Using the Query API, Query Studio facilitates the creation, reading, updating, and deletion of data from databases and column fields.

How to use Query

We recommend using Query with Fly. It will help you deploy your server in minutes and replicate your databases worldwide.

The Hands-on Fly guide covers CLI installation, sign-up, and sign-in for new Fly users.

There are two ways to use Query. You can either use it as a standalone service or use it with a proxy to connect to your app. You can access your databases directly from your application and Query using the Query Proxy. On the other hand, using Query as an isolated service allows you to access the databases through HTTP requests using the API.

Using the proxy instead of the isolated service offers the advantage of easier replication and scaling, as Query and your application share the same VM.

If you are an Epic Stack user or want to explore an example using it with Query, we have published a repository for you to review. You can find the example in the Epic Stack documentation.

There is an example on the Query repository for Bun users to test the proxy feature.

How to get involved?

Get involved with Query by visiting the GitHub Page to explore the codebase and discussions. Identify areas to contribute through existing issues, participate in discussions, and submit code enhancements via pull requests. Please spread the word to build a vibrant community around Query.

Conclusion

Query is an excellent tool to empower all your LiteFS projects. I strongly suggest exploring its features; you will realize its vast potential.

References:

Blog