sqlite-rx 1.0.0

Python SQLite Client and Server

sqlite_rx

Background

SQLite is a lightweight database written in C. The Python programming language has in-built support to interact with the database(locally) which is either stored on disk or in memory.

Introducing sqlite_rx - SQLite remote query execution

With sqlite_rx, clients should be able to communicate with an SQLiteServer in a fast, simple and secure manner and execute queries remotely.

Key Features

• Python Client and Server for SQLite database built using ZeroMQ as the transport layer and msgpack for serialization/deserialization.
• Supports authentication using ZeroMQ Authentication Protocol (ZAP)
• Supports encryption using CurveZMQ
• Allows the users to define a generic authorization policy during server startup

Install

pip install sqlite_rx

Supported OS

• Linux
• MacOS
• Windows

Supported Python Platforms

• CPython 3.6, 3.7, 3.8, 3.9
• PyPy3.6-7.1.1

Examples

Server

SQLiteServer runs in a single thread and follows an event-driven concurrency model (using tornado's event loop) which minimizes the cost of concurrent client connections.

import logging.config
from sqlite_rx import get_default_logger_settings
from sqlite_rx.server import SQLiteServer


def main():

    # database is a path-like object giving the pathname 
    # of the database file to be opened. 

    # You can use ":memory:" to open a database connection to a database 
    # that resides in RAM instead of on disk

    logging.config.dictConfig(get_default_logger_settings(logging.DEBUG))
    server = SQLiteServer(database=":memory:",
                          bind_address="tcp://127.0.0.1:5000")
    server.start()

if __name__ == '__main__':
    main()

Client

SQLiteClient is a thin client with a single method called execute

The execute method reacts to the following keyword arguments:

1. execute_many: True if you want to insert multiple rows with one execute call.

2. execute_script: True if you want to execute a script with multiple SQL commands.

3. request_timeout: Time in ms to wait for a response before retrying. Default is 2500 ms

4. retries: Number of times to retry before abandoning the request. Default is 5

Below are a few examples

Instantiate a client

import logging.config
from sqlite_rx.client import SQLiteClient
from sqlite_rx import get_default_logger_settings

# sqlite_rx comes with a default logger settings. 
# You could use as below.
logging.config.dictConfig(get_default_logger_settings(logging.DEBUG))


client = SQLiteClient(connect_address="tcp://127.0.0.1:5000")

CREATE TABLE statement

result = client.execute("CREATE TABLE stocks (date text, trans text, symbol text, qty real, price real)")
pprint(result)

OUTPUT

{'error': None, 
'items': []}

INSERT MANY rows

purchases = [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
             ('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
             ('2006-04-06', 'SELL', 'XOM', 500, 53.00),
             ('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
             ('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
             ('2006-04-06', 'SELL', 'XOM', 500, 53.00),
             ('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
             ('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
             ('2006-04-06', 'SELL', 'XOM', 500, 53.00),
             ('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
             ('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
             ('2006-04-06', 'SELL', 'XOM', 500, 53.00),
             ('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
             ('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
             ('2006-04-06', 'SELL', 'XOM', 500, 53.00),
             ('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
             ('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
             ('2006-04-06', 'SELL', 'XOM', 500, 53.00),
             ('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
             ('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
             ('2006-04-06', 'SELL', 'XOM', 500, 53.00),
             ('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
             ('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
             ('2006-04-06', 'SELL', 'XOM', 500, 53.00),
             ('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
             ('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
             ('2006-04-06', 'SELL', 'XOM', 500, 53.00)]

result = client.execute("INSERT INTO stocks VALUES (?,?,?,?,?)", 
                        *purchases, 
                        execute_many=True)
pprint(result)

OUTPUT

{'error': None, 
 'items': [], 
 'rowcount': 27}

SELECT with WHERE clause

args = ('IBM',)
result = client.execute("SELECT * FROM stocks WHERE symbol = ?", *args)
pprint(result)

OUTPUT

{'error': None,
 'items': [['2006-03-28', 'BUY', 'IBM', 1000.0, 45.0],
           ['2006-03-28', 'BUY', 'IBM', 1000.0, 45.0],
           ['2006-03-28', 'BUY', 'IBM', 1000.0, 45.0],
           ['2006-03-28', 'BUY', 'IBM', 1000.0, 45.0],
           ['2006-03-28', 'BUY', 'IBM', 1000.0, 45.0],
           ['2006-03-28', 'BUY', 'IBM', 1000.0, 45.0],
           ['2006-03-28', 'BUY', 'IBM', 1000.0, 45.0],
           ['2006-03-28', 'BUY', 'IBM', 1000.0, 45.0],
           ['2006-03-28', 'BUY', 'IBM', 1000.0, 45.0]],
 'lastrowid': 27}

Execute an SQL script

script = '''CREATE TABLE users(id INTEGER PRIMARY KEY, name TEXT, phone TEXT);
            CREATE TABLE accounts(id INTEGER PRIMARY KEY, description TEXT);

            INSERT INTO users(name, phone) VALUES ('John', '5557241'), 
             ('Adam', '5547874'), ('Jack', '5484522');'''

result = client.execute(script, execute_script=True)
pprint(result)

OUTPUT

{'error': None, 
 'items': [], 
 'lastrowid': 27}

Select the rows inserted using the above SQL script

result = client.execute("SELECT * FROM users")
pprint(result)

OUTPUT

{'error': None,
 'items': [[1, 'John', '5557241'],
           [2, 'Adam', '5547874'],
           [3, 'Jack', '5484522']],
 'lastrowid': 3}

DROP a Table

Note: In the default authorization setting, a client is not allowed to drop any table.

result = client.execute("DROP TABLE stocks")
pprint(result)

OUTPUT

{'error': {'message': 'sqlite3.DatabaseError: not authorized',
           'type': 'sqlite3.DatabaseError'},
 'items': []}

SELECT statement; Table not present

from pprint import pprint
result = client.execute("SELECT * FROM STUDENTS")
pprint(result)

OUTPUT

{'error': {'message': 'sqlite3.OperationalError: no such table: STUDENTS',
           'type': 'sqlite3.OperationalError'},
 'items': []}

Generic Default Authorization Policy

DEFAULT_AUTH_CONFIG = {
            sqlite3.SQLITE_OK: {
                sqlite3.SQLITE_CREATE_INDEX,
                sqlite3.SQLITE_CREATE_TABLE,
                sqlite3.SQLITE_CREATE_TEMP_INDEX,
                sqlite3.SQLITE_CREATE_TEMP_TABLE,
                sqlite3.SQLITE_CREATE_TEMP_TRIGGER,
                sqlite3.SQLITE_CREATE_TEMP_VIEW,
                sqlite3.SQLITE_CREATE_TRIGGER,
                sqlite3.SQLITE_CREATE_VIEW,
                sqlite3.SQLITE_INSERT,
                sqlite3.SQLITE_READ,
                sqlite3.SQLITE_SELECT,
                sqlite3.SQLITE_TRANSACTION,
                sqlite3.SQLITE_UPDATE,
                sqlite3.SQLITE_ATTACH,
                sqlite3.SQLITE_DETACH,
                sqlite3.SQLITE_ALTER_TABLE,
                sqlite3.SQLITE_REINDEX,
                sqlite3.SQLITE_ANALYZE,
                },

            sqlite3.SQLITE_DENY: {
                sqlite3.SQLITE_DELETE,
                sqlite3.SQLITE_DROP_INDEX,
                sqlite3.SQLITE_DROP_TABLE,
                sqlite3.SQLITE_DROP_TEMP_INDEX,
                sqlite3.SQLITE_DROP_TEMP_TABLE,
                sqlite3.SQLITE_DROP_TEMP_TRIGGER,
                sqlite3.SQLITE_DROP_TEMP_VIEW,
                sqlite3.SQLITE_DROP_TRIGGER,
                sqlite3.SQLITE_DROP_VIEW,
            },

            sqlite3.SQLITE_IGNORE: {
                sqlite3.SQLITE_PRAGMA
            }

}

You can define your own authorization policy in a python dictionary(as shown above) and pass it to the SQLiteServer class as auth_config parameter. It is recommended you do not override the SQLITE_PRAGMA action as the database starts in pragma journal_mode=wal mode

CLI

sqlite-server is a console script to start an SQLiteServer.

Usage: sqlite-server [OPTIONS]

Options:
  --log-level [CRITICAL|FATAL|ERROR|WARN|WARNING|INFO|DEBUG|NOTSET]
                                  Logging level  [default: INFO]
  --advertise-host TEXT           Host address on which to run the
                                  SQLiteServer  [default: 0.0.0.0]

  --port TEXT                     Port on which SQLiteServer will listen for
                                  connection requests  [default: 5000]

  --database TEXT                 Path like object giving the database name.
                                  You can use `:memory:` for an in-memory
                                  database  [default: :memory:]

  --zap / --no-zap                True, if you want to enable ZAP
                                  authentication  [default: False]

  --curvezmq / --no-curvezmq      True, if you want to enable CurveZMQ
                                  encryption  [default: False]

  --curve-dir TEXT                Curve Key directory
  --key-id TEXT                   Server key ID
  --help                          Show this message and exit.

All docker examples use this console script as an entrypoint

Secure Client and Server Setup

Please read the link for a detailed explanation on how to setup a secure client/server communication. This link also explains how to setup CurveZMQ encryption and ZAP authentication

Docker Examples

The following docker-compose examples using the docker image aosingh/sqlite_rx

sqlite-server CLI is used in all the docker examples

In-memory SQLite Database

version: "3"
services:
  sqlite_server:
    image: aosingh/sqlite_rx
    command: sqlite-server --log-level DEBUG
    ports:
    - 5000:5000

• Note that in the docker container the server listens on port 5000 so, do enable port forwarding on the host machine

On Disk SQLite Database

docker volume is used to persist the database file on the host's file system

version: "3"
services:

  sqlite_server:
    image: aosingh/sqlite_rx
    command: sqlite-server --log-level DEBUG --database /data/database.db
    ports:
      - 5000:5000
    volumes:
      - data:/data

volumes:
  data: {}

• Named docker volume data is mounted to /data location in the container
• sqlite-server CLI accepts --database option which is the database path in the container. Form is /data/<dbname>.db

SQLite Database server with CurveZMQ encryption

CurveZMQ is a protocol for secure messaging across the Internet that closely follows the CurveCP security handshake. curve-keygen is a script (packaged with sqlite_rx) which is modeled after ssh-keygen to generate public and private keys. Curve Key Generation uses an OpenSSH like directory: ~/.curve

We need public keys for both servers and clients. We differentiate this by running the curve-keygen script in either client or server mode.

Once the keys have been generated, we can enable CurveZMQ encryption in the following way

version: "3"
services:

  sqlite_server:
    image: aosingh/sqlite_rx
    command: sqlite-server --curvezmq --log-level DEBUG --database /data/database.db --key-id id_server_Abhisheks-MacBook-Pro.local_curve
    ports:
      - 5000:5000
    volumes:
      - data:/data
      - /Users/as/.curve:/root/.curve

volumes:
  data: {}

• sqlite-server CLI accepts --curvezmq boolean flag to enable encryption
• sqlite-server CLI accepts --key-id which is the server key id available at /root/.curve location
• /Users/as/.curve (on host machine) is mapped to /root/.curve in the docker container.

SQLite Database server with CurveZMQ encryption and ZAP authentication

ZeroMQ Authentication protocol

Setting --zap = True will restrict connections to clients whose public keys are in the /root/.curve/authorized_clients/ directory. Set this to False to allow any client with the server's public key to connect, without requiring the server to possess each client's public key.

version: "3"
services:

  sqlite_server:
    image: aosingh/sqlite_rx
    command: sqlite-server --zap --curvezmq --log-level DEBUG --database /data/database.db --key-id id_server_Abhisheks-MacBook-Pro.local_curve
    ports:
    - 5000:5000
    volumes:
    - data:/data
    - /Users/as/.curve:/root/.curve

volumes:
  data: {}