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

Related tags

Database query
Overview

Query

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

Table Of Contents

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
Name Type Description Required
Authorization string The bearer token to connect to the server. true
Body
Name Type Description Required
db_name string The database to use. true
query string The query to execute. true
params object | array The params to use in the query. false

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
Name Type Description Required
Authorization string The bearer token to connect to the server. true
Query String
Name Type Format Description Required
db_name string - The database to use. true
query string URL Encoded The SELECT query. true
params object | array URL Encoded The params to use in the query. false

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
Name Type Description Required
Authorization string The bearer token to connect to the server. true
Body
Name Type Description Default Required
email string The email of the user. - true
password string The password of the user. - true
admin boolean If the user is admin. false false
active boolean If the user is active. true false

Example:

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

PUT

The user endpoint allows to update a user.

PUT /user
Headers
Name Type Description Required
Authorization string The bearer token to connect to the server. true
Body
Name Type Description Default Required
email string The email of the user. - true
new_email string The new email of the user. - false
new_password string The new password of the user. - false
admin boolean If the user is admin. false false
active boolean If the user is active. true false

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
Name Type Description Required
Authorization string The bearer token to connect to the server. true
Body
Name Type Description Required
password string The password of the user. true

Example:

{
  "password": "example"
}

DELETE

The user endpoint allows to delete a user.

DELETE /user
Headers
Name Type Description Required
Authorization string The bearer token to connect to the server. true
Body
Name Type Description Required
email string The email of the user. true

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
Name Type Description Required
Authorization string The bearer token to connect to the server. true
Body
Name Type Description Default Required
email string The email of the user. - true
write boolean If the token has write permissions. true false
expiration_date number The expiration date in milliseconds. = updated_at false

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
Name Type Description Required
Authorization string The bearer token to connect to the server. true

PUT

The user token endpoint allows to update a user token.

PUT /user-token
Headers
Name Type Description Required
Authorization string The bearer token to connect to the server. true
Body
Name Type Description Default Required
email string The email of the user. - true
write boolean If the token has write permissions. false false
expiration_date number The expiration date in milliseconds. = updated_at false

Example:

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

DELETE

The user token endpoint allows to delete a user token.

DELETE /user-token
Headers
Name Type Description Required
Authorization string The bearer token to connect to the server. true
Body
Name Type Description Required
email string The email of the user. true

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
Name Type Description Required
Authorization string The bearer token to connect to the server. true
Query String
Name Type Description Required
email string The email of the user. true

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
Name Type Description Required
email string The email of the user. true
password string The password of the user. true

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
Name Type Description Required
Authorization string The bearer token to connect to the server. true
Body
Name Type Description Default Required
name string The name of the token. - true
expiration_date number The expiration date in milliseconds. = updated_at false
active boolean If the token is active true false
write boolean If the token has write permissions. true false

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
Name Type Description Required
Authorization string The bearer token to connect to the server. true

PUT

The token endpoint allows to update a token.

PUT /token
Headers
Name Type Description Required
Authorization string The bearer token to connect to the server. true
Body
Name Type Description Default Required
name string The name of the token. - true
expiration_date number The expiration date in milliseconds. = updated_at false
active boolean If the token is active true false
write boolean If the token has write permissions. true false

Example:

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

DELETE

The token endpoint allows to delete a token.

DELETE /token
Headers
Name Type Description Required
Authorization string The bearer token to connect to the server. true
Body
Name Type Description Required
name string The name of the token. true

GET Value

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

GET /token/value?name=<NAME>
Headers
Name Type Description Required
Authorization string The bearer token to connect to the server. true
Query String
Name Type Description Required
name string The name of the token. true

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
Name Type Description Required
Authorization string The bearer token to connect to the server. true
Body
Name Type Description Required
db_name string The database to use. true
query string The query to execute. true

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
Name Type Description Required
Authorization string The bearer token to connect to the server. true
Body
Name Type Description Required
db_name string The database to use. true
branch_name string The name of the branch. true

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
Name Type Description Required
Authorization string The bearer token to connect to the server. true

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
Name Type Description Required
Authorization string The bearer token to connect to the server. true
Body
Name Type Description Required
db_name string The database to delete. true

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.

You might also like...
This project provides a Rust-based solution for migrating MSSQL databases to MySQL.

MSSQL to MySQL Database Migration A Rust project to migrate MSSQL databases to MySQL, including table structures, column data types, and table data ro

Sled - the champagne of beta embedded databases
Sled - the champagne of beta embedded databases

key value buy a coffee for us to convert into databases documentation chat about databases with us sled - it's all downhill from here!!! An embedded d

Document your SQLite tables and columns with in-line comments
Document your SQLite tables and columns with in-line comments

sqlite-docs A SQLite extension, CLI, and library for documentating SQLite tables, columns, and extensions. Warning sqlite-docs is still young and not

Simple and handy btrfs snapshoting tool. Supports unattended snapshots, tracking, restoring, automatic cleanup and more. Backed with SQLite.
Simple and handy btrfs snapshoting tool. Supports unattended snapshots, tracking, restoring, automatic cleanup and more. Backed with SQLite.

Description Simple and handy btrfs snapshoting tool. Supports unattended snapshots, tracking, restoring, automatic cleanup and more. Backed with SQLit

🧰 The Rust SQL Toolkit. An async, pure Rust SQL crate featuring compile-time checked queries without a DSL. Supports PostgreSQL, MySQL, SQLite, and MSSQL.

SQLx 🧰 The Rust SQL Toolkit Install | Usage | Docs Built with ❤️ by The LaunchBadge team SQLx is an async, pure Rust† SQL crate featuring compile-tim

🐸Slippi DB ingests Slippi replays and puts the data into a SQLite database for easier parsing.
🐸Slippi DB ingests Slippi replays and puts the data into a SQLite database for easier parsing.

The primary goal of this project is to make it easier to analyze large amounts of Slippi data. Its end goal is to create something similar to Ballchasing.com but for Melee.

A tool for automated migrations for PostgreSQL, SQLite and MySQL.

Models Models is an implementation for a SQL migration management tool. It supports PostgreSQL, MySQL, and SQLite. Quick Start install the CLI by runn

A SQLite extension for quickly generating random numbers, booleans, characters, and blobs

sqlite-fastrandom A SQLite extension for quickly generating random numbers, booleans, characters, and blobs. Not cryptographically secure. Based on sq

Ergonomic bindings to SQLite for Rust

Rusqlite Rusqlite is an ergonomic wrapper for using SQLite from Rust. It attempts to expose an interface similar to rust-postgres. use rusqlite::{para

Releases(v0.1.0)
Owner
Víctor García
Víctor García
Grsql is a great tool to allow you set up your remote sqlite database as service and CRUD(create/read/update/delete) it using gRPC.

Grsql is a great tool to allow you set up your remote sqlite database as service and CRUD (create/ read/ update/ delete) it using gRPC. Why Create Thi

Bruce Yuan 33 Dec 16, 2022
rust_arango enables you to connect with ArangoDB server, access to database, execute AQL query, manage ArangoDB in an easy and intuitive way, both async and plain synchronous code with any HTTP ecosystem you love.

rust_arango enables you to connect with ArangoDB server, access to database, execute AQL query, manage ArangoDB in an easy and intuitive way, both async and plain synchronous code with any HTTP ecosystem you love.

Foretag 3 Mar 24, 2022
Mycelite is a SQLite extension that allows you to synchronize changes from one instance of SQLite to another.

Mycelite What is Mycelite? Mycelite is a SQLite extension that allows you to synchronize changes from one instance of SQLite to another. Currently, it

Mycelial 16 Jan 2, 2023
XLite - query Excel (.xlsx, .xls) and Open Document spreadsheets (.ods) as SQLite virtual tables

XLite - query Excel (.xlsx, .xls) and Open Document spreadsheets (.ods) as SQLite virtual tables XLite is a SQLite extension written in Rust. The main

Sergey Khabibullin 1.1k Dec 28, 2022
webmention-receiver - a simple program that receives webmentions, records them to a SQLite database

webmention-receiver is a simple program that receives webmentions, records them to a SQLite database, and allows viewing them via a webpage or RSS feed. It has no conception of an "account", and by default will accept webmentions for any domain. It is possible to configure it to only accept webmentions for a specific set of domains, if you'd prefer.

Wesley Aptekar-Cassels 11 Sep 3, 2022
CLI tool to work with Sled key-value databases.

sledtool CLI tool to work with Sled key-value databases. $ sledtool --help Usage: sledtool <dbpath> <command> [<args>] CLI tool to work with Sled da

Vitaly Shukela 27 Sep 26, 2022
Replibyte - a powerful tool to seed your databases

Seed Your Development Database With Real Data ⚡️ Replibyte is a powerful tool to seed your databases with real data and other cool features ?? Feature

Qovery 3.4k Jan 9, 2023
Engula empowers engineers to build reliable and cost-effective databases.

Engula is a storage engine that empowers engineers to build reliable and cost-effective databases with less effort and more confidence. Engula is in t

Engula 706 Jan 1, 2023
A Rust-based comment server using SQLite and an intuitive REST API.

soudan A Rust-based comment server using SQLite and an intuitive REST API. Soudan is built with simplicity and static sites in mind. CLI usage See sou

Elnu 0 Dec 19, 2022
Optimistic multi-version concurrency control (MVCC) for main memory databases, written in Rust.

MVCC for Rust This is a work-in-progress the Hekaton optimistic multiversion concurrency control library in Rust. The aim of the project is to provide

Pekka Enberg 32 Apr 20, 2023