Simplifying the Management of Remote SQLite Databases
Tags: rust, sqlite, litefs, javascript
Published: 2023-11-27 09:49:38

## Introduction

With the growing popularity of [SQLite](https://www.sqlite.org/index.html) usage in websites, particularly with [LiteFS](https://fly.io/docs/litefs/), there is an increasing demand to manage remote databases. [Query](https://github.com/gc-victor/query) aims to give you straightforward access to your remote databases.

![Kent C. Dodds Tweet about this article](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3rbzit9z9il2lqon0k4k.png)

## What is Query?

[Query](https://github.com/gc-victor/query) is a Rust server that runs alongside your application and enhances its functionality without the application's knowledge. Query provides an [API](https://github.com/gc-victor/query/tree/main?tab=readme-ov-file#apis), [CLI](https://github.com/gc-victor/query/tree/main?tab=readme-ov-file#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.

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

It also provides [functions](https://github.com/gc-victor/query/tree/main?tab=readme-ov-file#function) (without sandboxing by default) like [Edge Functions from Vercel](https://vercel.com/features/edge-functions) or [Deno subhosting](https://deno.com/subhosting) or [Cloudflare Workers](https://workers.cloudflare.com), but with direct access to your databases. They are JavaScript functions executed in Rust using a runtime based on [Deno](https://deno.com). The functions are stored in a database and can be replicated through LiteFS, putting them closer to the user.

```javascript
// 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](https://github.com/gc-victor/query/tree/main?tab=readme-ov-file#branch), it  duplicates a database to be utilized in development environments.

```sh
query branch <SUBCOMMAND>
```
Additionally, Query provides a basic [migration](https://github.com/gc-victor/query/tree/main?tab=readme-ov-file#migration) system that manages your databases by sending a batch of queries.

```sh
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](https://github.com/gc-victor/query/tree/main?tab=readme-ov-file#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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bwpc6w9b9pt3p4j3hrsb.png)


[Query Studio](https://github.com/gc-victor/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](https://bun.sh). 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](https://fly.io). It will help you deploy your server in minutes and replicate your databases worldwide.

The [Hands-on Fly guide](https://fly.io/docs/hands-on/) 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](https://github.com/gc-victor/query#query-server-with-proxy). On the other hand, using [Query as an isolated service](https://github.com/gc-victor/query#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](https://www.epicweb.dev/epic-stack) user or want to explore an example using it with Query, we have published a [repository](https://github.com/gc-victor/epic-stack-with-query) for you to review. You can find the example in the [Epic Stack documentation](https://github.com/epicweb-dev/epic-stack/blob/main/docs/examples.md).

There is an [example](https://github.com/gc-victor/query/tree/main/examples/proxy) on the Query repository for [Bun](https://bun.sh) users to test the proxy feature.

## How to get involved?

Get involved with Query by visiting the [GitHub Page](https://github.com/gc-victor/query) 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:
- https://github.com/gc-victor/query
- https://github.com/gc-victor/query-studio
- https://github.com/gc-victor/epic-stack-with-query
- https://www.sqlite.org/index.html
- https://fly.io/docs/hands-on/
- https://fly.io/docs/litefs/
- https://www.epicweb.dev/epic-stack
- https://bun.sh
- https://deno.com