Deploying a SQLite database with ws4sqlite

ws4sqlite is a server-side application that creates a REST API for interacting with SQLite databases. It basically converts SQLite into a REST API that you can interact with using HTTP requests.

It makes it easy to use SQLite over a remote connection, for example, from a serverless function or directly from a client-side application.

Installing ws4sqlite

ws4sqlite is a Go application which you can download the binary directly from the GitHub releases page, or build from source.

Running ws4sqlite

Once you have downloaded or built ws4sqlite, you can run your server and create a new SQLite database.

❯ ./ws4sqlite --db selfhost.db

ws4sqlite v0.16.1, based on sqlite v3.45.2
- Serving database 'selfhost' from selfhost.db?_pragma=journal_mode(WAL)
  + No valid config file specified, using defaults
  + File not present, it will be created
  + Using WAL
- Web Service listening on 0.0.0.0:12321

With our server up and running, we can now start interacting with our SQLite database over the network.

Creating a table

To perform a SQL query or statement, all we need to do is send a POST request to the /<database> endpoint with the SQL statement in the body.

import requests

url = "http://localhost:12321/selfhost"
payload = {
    "transaction": [
        {
            "statement": """
                CREATE TABLE USERS (
                    ID INT PRIMARY KEY,
                    FIRST_NAME VARCHAR(50),
                    LAST_NAME VARCHAR(50)
                );
            """
        }
    ]
}

response = requests.post(url, json=payload)

On success, the response will be a 200 status code with the following JSON body:

{
  "results": [
    {
      "success": true,
      "rowsUpdated": 0
    }
  ]
}

Inserting data

Similarly, to insert data into our USERS table, we can send a POST request to the /<database> endpoint with the SQL statement in the body.

import requests

url = "http://localhost:12321/selfhost"
payload = {
    "transaction": [
        {
            "statement": """
                INSERT INTO USERS (ID, FIRST_NAME, LAST_NAME)
                VALUES (:id, :first_name, :last_name)
            """,
            "values": {
                "id": 1,
                "first_name": "John",
                "last_name": "Doe"
            }
        }
    ]
}

response = requests.post(url, json=payload)
print(response.json())

On success, the response will be a 200 status code with the following JSON body:

{
  "results": [
    {
      "success": true,
      "rowsUpdated": 1
    }
  ]
}

Querying data

Finally, to query data from our USERS table, we can send another POST request with a query transaction.

import requests

url = "http://localhost:12321/selfhost"
payload = {
    "transaction": {
      "query": {
        "statement": "SELECT * FROM USERS;"
      }
    }
}

response = requests.post(url, json=payload)
print(response.json())

On success, the response will be a 200 status code with the following JSON body:

{
  "results": [
    {
      "success": true,
      "resultHeaders": ["ID", "FIRST_NAME", "LAST_NAME"],
      "resultSet": [
        {
          "ID": 1,
          "FIRST_NAME": "John",
          "LAST_NAME": "Doe"
        }
      ]
    }
  ]
}

That’s it! We’ve created a SQLite database, created a table, inserted data, and queried data.

Authentication

A common use case for ws4sqlite is to expose a SQLite database over a remote connection. This opens up the ability to interact with the database from a remote client, such as a serverless function or a client-side application.

To make sure we’re not exposing our database to the public, we can use basic authentication. Luckily, ws4sqlite has built-in support for basic authentication. Let’s enable it and require authentication for all requests.

Defining users

To define users, we need to create a new config file, and then update it to define the users.

In ws4sqlite, we can define config files by creating a .yaml file with the same name as the database. For example, if we’re using the selfhost.db database, we can create a selfhost.yaml file.

auth:
  mode: HTTP
  byCredentials:
    - user: user1
      password: password

This will create a new user called user1 with the password password. Now, we can simply run our server again, and it will automatically use the new config file.

❯ ./ws4sqlite --db selfhost.db

ws4sqlite v0.16.1, based on sqlite v3.45.2
- Serving database 'selfhost' from selfhost.db?_pragma=journal_mode(WAL)
  + Parsed companion config file: selfhost.yaml
  + Using WAL
  + Authentication enabled, with 1 credential
- Web Service listening on 0.0.0.0:12321

Now, to run queries against our database, we will have to pass the username and password as basic authentication credentials.

import requests

url = "http://localhost:12321/selfhost"
payload = {
    "transaction": {
      "query": "SELECT * FROM USERS;"
    }
}

response = requests.post(url, json=payload, auth=("user1", "password"))
print(response.json())

On success, the response will be a 200 with the same JSON body as before. If we were to pass the wrong credentials, the response will be a 401 Unauthorized status code.

Hashing passwords

Additionally, ws4sqlite supports hashing passwords. This is a good practice to use when storing passwords instead of plain text.

To do this, we can simply replace the password field with hashedPassword in our config file and provide a SHA-256 hashed password.

❯ echo -n "password" | sha256sum

5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8  -

auth:
  mode: HTTP
  byCredentials:
    - user: user1
      password: password
    - user: user2
      hashedPassword: 5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8

Now, we can run our server again, and this time pass the hashed username and password as basic authentication credentials.

Backups

When running any database, it’s important to ensure that we have regular database backups in case of any issues. There are multiple ways to backup a SQLite database, and fortunately, ws4sqlite has built-in support for this.

To schedule a backup, we need to head back to our selfhost.yaml file, and schedule a backup task.

auth: // ...
scheduledTasks:
  - schedule: "1 * * * *" # Every hour
    doBackup: true
    backupTemplate: ./backups/dump_%s.db
    numFiles: 3

This will schedule a backup task to run every hour, store the backups in the ./backups directory, and keep the last 3 backups. Of course, you may adjust the schedule, backup template, and number of files to meet your needs.

Restoring from a backup

To restore from a backup, we can simply copy the backup file to the database file, and run our server again.

❯ ./ws4sqlite --db selfhost.db

Conclusion

ws4sqlite is a powerful tool that allows you to expose a SQLite database over a remote connection. It’s a good choice for running a SQLite database in a serverless environment, or for running a SQLite database in a client-side application.

You can find the source code for ws4sqlite on GitHub.