Query

Query is a Rust server for your remote SQLite databases and a CLI to manage them.

Table Of Contents

• Run A Query Server
• CLI
  • Install
    • Use The Installer Scripts
    • Download The Binary
  • Commands
    • Settings
      • URL
      • Login
      • History
    • Shell
    • User
      • Create User
      • Delete User
      • List Users
      • Update User Password
      • Update User
    • User Token
      • Create User Token
      • Delete User Token
      • List User Tokens
      • Update User Token
    • Token
      • Create Token
      • Delete Token
      • List Tokens
      • Update Token
    • Migration
    • Branch
      • Create Branch
      • Delete Branch
      • List Branches
• APIs
  • Query Endpoint
    • POST
    • GET
  • User Endpoint
    • POST
    • PUT
    • PUT Password
    • DELETE
  • User Token Endpoint
    • POST
    • GET
    • PUT
    • DELETE
    • GET Value
    • POST Value
  • Token Endpoint
    • POST
    • GET
    • PUT
    • DELETE
    • GET Value
  • Migration Endpoint
    • POST
  • Branch Endpoint
    • POST
    • GET
    • DELETE

Run A Query Server

We recommend use Query with Fly (https://fly.io). It will help you to deploy your server in a few minutes and replicate your databases across the world.

If it is the first time using Fly, you can follow the Hands-on with Fly.io guide to install the CLI, sign up and sign in.

Once you have the Fly CLI installed, you have to rename the fly.toml.dist to fly.toml, and update it with your app name and the primary region running the following command:

fly launch

It is time to set the environment variables for your app. You can do it running the following commands:

Token secret:

fly secrets set QUERY_SERVER_TOKEN_SECRET=USE_AN_ALPHANUMERIC_HASH

Admin email:

fly secrets set QUERY_SERVER_ADMIN_EMAIL=USE_YOUR_EMAIL

Admin password:

fly secrets set QUERY_SERVER_ADMIN_PASSWORD=USE_A_SECURE_PASSWORD

We use LiteFS, a Fly addon that provides a simple way to replicate your SQLite databases in the cloud. To use LiteFS you need to configure consul. You can do it running the following commands:

fly consul attach

For the backups of your databases you have to create a LiteFS Cloud cluster in the LiteFS section of the fly.io dashboard. Take note of your auth token (you’ll need it later). LiteFS Cloud is optional, but highly recommended if your data is important to you!

fly secrets set LITEFS_CLOUD_TOKEN=YOUR_LITEFS_CLOUD_AUTH_TOKEN

Then you can deploy your app running:

fly deploy

Your app is currently running on a single machine. To ensure high availability, especially for production apps, Fly strongly recommend running at least 2 instances. You can scale up the number of machines using the fly machine clone command in the CLI. Please, have in mind that you can add that machine to an other region.

fly m clone

Or

fly m clone --select --region A_REGION

Example: fly m clone --select --region lhr (London)

To get a list of rigions you can run the following command:

fly platform regions

CLI

Install

Use The Installer Scripts

macOS and Linux (not NixOS, Alpine, or Asahi):

curl --proto '=https' --tlsv1.2 -LsSf https://github.com/gc-victor/query/releases/latest/download/query-installer.sh | sh

Windows PowerShell:

irm https://github.com/gc-victor/query/releases/latest/download/query-installer.ps1 | iex

Download The Binary

https://github.com/gc-victor/query/releases/latest

Commands

Following we will see the commands you can use with the CLI.

Settings

Lets start by adding the settings of your server.

query settings

It will ask you the following questions:

URL

• What is the server URL?

If you don't know it, you can run the following command to get it:

fly status

You will have to use the Hostname plus as a prefix the protocol https://.

Example: https://query-server.fly.dev

Where query-server.fly.dev is the Hostname.

Login

You need to log in to get the token to connect to your Query Server. The token will be saved in the .query/.token file.

• What is her email?

You have to use the same email you used to create the admin user.

• What is her password?

You have to use the same password you used to create the admin user.

History

• Do you want to save the history of your shell? (Y/n)

If you choose Y the history will be saved in the .query/.history file.

Shell

The shell command opens a SQLite shell to manage the remote database locally.

Usage:

query shell <DB_NAME>

It has the following commands:

• .quit - Exit the shell.
• .tables [?PATTERN?] - List names of tables matching a LIKE pattern.
• .schema [?TABLE?] - Show the CREATE statements. If TABLE specified, only show tables matching LIKE pattern TABLE.

It saves the command history in the .query/.history file.

User

The user command allows to manage the users of your Query Server, if you are admin. If you are not admin, you can only change your user password.

Usage:

query user <SUBCOMMAND>

It has the following subcommands:

• create - Create a new user.
• delete - Delete a user.
• list - List all the users.
• update - Update a user.
• password - Update your user password.
• help - Print this message or the help of the given subcommand(s).

Create User

It will create a new user.

Usage:

query user create

It will ask you for the following information:

• What is her email?
• What is her password?
• Is she an admin user? (Y/n)
• Is she an active user? (Y/n)

Delete User

It will delete a user.

Usage:

query user delete

It will ask you for the following information:

• What is her email?

List Users

It will show you a list of all the users.

Usage:

query user list

Update User Password

It will update your user password.

Usage:

query user password

It will ask you for the following information:

• What is your new password?

Update User

It will update a user.

Usage:

query user update

It will ask you for the following information:

• What is her email?
• What is her new email? (Optional)
• What is her new password? (Optional)
• Is she an admin user? (y/n) (Optional)
• Is she an active user? (y/n) (Optional)

User Token

The user token command allows to manage the user tokens of your Query Server, if you are admin.

Usage:

query user-token <SUBCOMMAND>

It has the following subcommands:

• create - Create a new user token.
• delete - Delete a user token.
• list - List all the user tokens.
• help - Print this message or the help of the given subcommand(s).

Create User Token

It will create a new user token.

Usage:

query user-token create

It will ask you for the following information:

• What is her email?
• Should have write permissions? (Y/n)
• What is the expiration date in milliseconds? (Optional)

Delete User Token

It will delete a user token.

Usage:

query user-token delete

It will ask you for the following information:

• What is her email?

List User Tokens

It will show you a list of all the user tokens.

Usage:

query user-token list

Update User Token

It will generate a new user token maintaining the current email.

Usage:

query user-token update

It will ask you for the following information:

• What is her email?
• Should have write permissions? (y/n) (Optional)
• What is the expiration date in milliseconds? (Optional)

Token

The token command allows to manage the tokens not related to a user of your Query Server, if you are admin.

Usage:

query token <SUBCOMMAND>

It has the following subcommands:

• create - Create a new token.
• delete - Delete a token.
• list - List all the tokens.
• update - Update a token.
• help - Print this message or the help of the given subcommand(s).

Create Token

It will create a new token.

Usage:

query token create

It will ask you for the following information:

• What is the name of the token?
• Should have write permissions? (Y/n)
• What is the expiration date in milliseconds? (Optional)

Delete Token

It will delete a token.

Usage:

query token delete

It will ask you for the following information:

• What is the name of the token?

List Tokens

It will show you a list of all the tokens.

Usage:

query token list

Update Token

It will generate a new token maintaining the current name.

Usage:

query token update

It will ask you for the following information:

• What is the name of the token?
• What is the new name of the token? (Optional)
• Should have write permissions? (y/n) (Optional)
• What is the expiration date in milliseconds? (Optional)

Migration

The migration command allows to manage the migrations of your Query Server, if you have write permissions.

Migration file:

• The migration file should be in the format of <version><name><type>.sql
• The version should be in the format of YYYYMMDD
• The name should be in the format of <name>_<description>
• The type should be up or down

Usage:

query migration <DB_NAME> <PATH>

Branch

The branch command allows to manage the branches of your Query Server, if you are admin.

Usage:

query branch <SUBCOMMAND>

It has the following subcommands:

• create - Create a new branch.
• delete - Delete a branch.
• list - List all the branches.
• help - Print this message or the help of the given subcommand(s).

Create Branch

It will create a new branch.

Usage:

query branch create

It will ask you for the following information:

• Which database would you like to use for creating a branch?
• What is the branch name?

Delete Branch

It will delete a branch.

Usage:

query branch delete

It will ask you for the following information:

• Which branch database would you like to delete?

List Branches

It will show you a list of all the branches.

Usage:

query branch list

APIs

Following we will see the API endpoints you can use with the Query Server.

Query Endpoint

The query endpoint allows to execute queries in the databases. Using the GET method, the query is executed in the database closest to the user's region, thanks to the LiteFS proxy. Using the POST method, the query is executed in the primary database.

POST

The query endpoint allows to execute a query in the primary database.

POST /query

Headers

Body

The params object should use kyes with the format ":AAA", "$AAA", or "@AAA" that serve as placeholders for values that are bound to the parameters at a later time.

Example:

{
  "db_name": "example.sql",
  "query": "SELECT * FROM example WHERE id = :id",
  "params": {
    ":id": 1
  }
}

In the case of the array, the values are bound to the parameters based on the order of the array.

Example:

{
  "db_name": "example.sql",
  "query": "SELECT * FROM example WHERE id = ?",
  "params": [1]
}

GET

By using the GET method, data can be retrieved with less latency from the database closest to the user's region, thanks to the LiteFS proxy.

GET /query?db_name=<DB_NAME>&query=<QUERY>&params=<PARAMS>

Headers

Query String

Example:

GET /query?db_name=example.sql&query=SELECT%20*%20FROM%20example%20WHERE%20id%20%3D%20%3F&params=%5B1%5D

User Endpoint

The user endpoint allows to manage the users of the Query Server.

POST

The user endpoint allows to create a new user.

POST /user

Headers

Body

Example:

{
  "email": "[email protected]",
  "password": "example",
  "admin": false,
  "active": true
}

PUT

The user endpoint allows to update a user.

PUT /user

Headers

Body

Example:

{
  "email": "[email protected]",
  "new_email": "[email protected]",
  "new_password": "example",
  "admin": false,
  "active": true
}

PUT Password

The user/password endpoint allows to update the password of a user. The user is inferred from the token.

PUT /user/password

Headers

Body

Example:

{
  "password": "example"
}

DELETE

The user endpoint allows to delete a user.

DELETE /user

Headers

Body

Example:

{
  "email": "[email protected]"
}

User Token Endpoint

The user token endpoint allows to manage the user tokens of the Query Server.

POST

The user token endpoint allows to create a new user token.

POST /user-token

Headers

Body

Example:

{
  "email": "[email protected]",
  "write": true,
  "expiration_date": 1632960000000
}

GET

The user token endpoint allows to get a list of all the user tokens.

GET /user-token

Headers

PUT

The user token endpoint allows to update a user token.

PUT /user-token

Headers

Body

Example:

{
  "email": "[email protected]",
  "write": true,
  "expiration_date": 1632960000000
}

DELETE

The user token endpoint allows to delete a user token.

DELETE /user-token

Headers

Body

Example:

{
  "email": "[email protected]"
}

GET Value

The user token endpoint allows to get the value of a user token having an access token.

GET /user-token/value?email=<EMAIL>

Headers

Query String

Example:

GET /user-token/[email protected]

POST Value

The user token endpoint allows to create a new user token without having an access token.

POST /user-token/value

Body

Example:

{
  "email": "[email protected]",
  "password": "example"
}

Token Endpoint

The token endpoint allows to manage the tokens not related to a user.

POST

The token endpoint allows to create a new token.

POST /token

Headers

Body

Example:

{
  "name": "example",
  "expiration_date": 1632960000000,
  "active": true,
  "write": true
}

GET

The token endpoint allows to get a list of all the tokens.

GET /token

Headers

PUT

The token endpoint allows to update a token.

PUT /token

Headers

Body

Example:

{
  "name": "example",
  "expiration_date": 1632960000000,
  "active": true,
  "write": true
}

DELETE

The token endpoint allows to delete a token.

DELETE /token

Headers

Body

GET Value

The token endpoint allows to get the value of a token.

GET /token/value?name=<NAME>

Headers

Query String

Example:

GET /token/value?name=example

Migration Endpoint

The migration endpoint allows to manage the migrations of the Query Server.

POST

The migration endpoint allows to execute a migration in the primary database.

POST /migration

Headers

Body

Example:

{
  "db_name": "example.sql",
  "query": "CREATE TABLE example (id INTEGER PRIMARY KEY, name TEXT NOT NULL)"
}

Branch Endpoint

The branch endpoint allows to manage the branches of the Query Server. A branch is a copy of the primary database.

POST

The branch endpoint allows to create a new branch.

POST /branch

Headers

Body

Example:

{
  "db_name": "example.sql",
  "branch_name": "dev"
}

The branches has this format: <db_name>.<branch_name>.branch.sql. For example, if the database name is example.sql and the branch name is dev, the branch will be example.dev.branch.sql. Notice that the extension is removed from the database name to be used as a prefix.

GET

The branch endpoint allows to get a list of all the branches.

GET /branch

Headers

To retrieve the list of branches, the system get the list of files in the database directory and filter the files with the extension .branch.sql.

DELETE

The branch endpoint allows to delete a branch.

DELETE /branch

Headers

Body

Example:

{
  "db_name": "example.dev.branch.sql"
}

The branches has this format: <db_name>.<branch_name>.branch.sql. For example, if the database name is example.sql and the branch name is dev, the branch will be example.dev.branch.sql. Notice that the extension is removed from the database name to be used as a prefix.

Only branches can be deleted, it means files with the extension .branch.sql. The primary databases cannot be deleted.