reqlite

README

reqlite

reqlite makes it possible to query data in Redis with SQL. Queries are executed client-side with SQLite (not on the redis server). This project is distributed as a SQLite run-time loadable extension and (soon) as a standalone binary (CLI).

This project is experimental for the time being. It's made possible by a great library for building SQLite extensions in go.

The JSON1 extension is also included by default as a convenience.

Use Cases

What can or should I use this for? This project is pretty experimental and part of that is exploring use-cases to understand what's possible and interesting!

A common situation is a task queue in Redis. If you're using a LIST as a queue holding JSON objects, reqlite + the SQLite json1 extension could be used to issue basic "slicing and dicing" queries against your task queue.

-- what are the most common tasks currently in the queue?
SELECT count(*), json_extract(value, '$.task') as task
FROM LRANGE('my-queue', 0, 100)
GROUP BY task
ORDER BY count(*) DESC

In general, Redis is fairly accessible from many programming languages, and any query using reqlite could probably be implemented in a language of your choice using a Redis client. However, sometimes declarative SQL can be a better choice to express what you're looking for, and that's where this project may be most useful. Since reqlite is distributed as a run-time loadable SQLite extension, it can be loaded into a language using a SQLite driver as well, which would allow you to mix SQL and the "host" language to access data in Redis.

Getting Started

To build a run-time loadable extension, run make in the root of the source tree. The reqlite.so file should be in .build/reqlite.so, which you can use immediately in a SQLite shell:

sqlite3
sqlite> .load .build/reqlite.so
sqlite> SELECT * FROM LRANGE('some-key', 0, 10);

Connecting to Redis

Currently, the Redis connection can only be set via the following env variables:

ENV	Default	Description
REQLITE_NET	tcp	Network type - either tcp or udp
REQLITE_ADDR	localhost:6379	Network address of the redis instance
REQLITE_USER	(none)	Redis username
REQLITE_PASS	(none)	Redis password

TODO - Implement another mechanism (SQLite UDFs?) for setting up the connection information.

Commands

Currently, only read operations are targeted to be implemented as SQLite scalar functions or table-valued functions. In the examples below, you'll see how a SQLite scalar or table-valued function maps to a corresponding Redis command, based on the response type. Note that there won't always be an exact correspondence, and currently not all Redis commands are targeted to be implemented (read-only for now).

SELECT * FROM some_table_valued_function('param', 1, 2) -- function that returns a table
SELECT some_scalar_function('param', 1, 2) -- function that returns a scalar value

Available functions are listed below. For a full list of Redis commands and corresponding SQLite functions, see here.

LRANGE

SELECT * FROM LRANGE('some-key', 0, 10)

HGETALL

SELECT * FROM HGETALL('myhash')

DUMP

SELECT DUMP('some-key');

### BITCOUNT

```sql
SELECT BITCOUNT('some-key')
SELECT BITCOUNT('some-key', 1, 1)

BITPOS

SELECT BITPOS('some-key', 0)
SELECT BITPOS('some-key', 1, 2)

CLIENT GETNAME

SELECT CLIENT_GETNAME()

CLIENT ID

SELECT CLIENT_ID()

CLIENT LIST

SELECT CLIENT_LIST()

CONFIG GET

SELECT * FROM CONFIG_GET('*max-*-entries*')
SELECT * FROM CONFIG_GET -- equivalent to CONFIG GET *

CLUSTER COUNT-FAILURE-REPORTS

SELECT CLUSTER_COUNT_FAILURE_REPORTS('some-node-id')

CLUSTER_COUNTKEYSINSLOT

SELECT CLUSTER_COUNTKEYSINSLOT('some-key-id')

DBSIZE

SELECT DBSIZE()

GET

SELECT GET('some-key')

ECHO

SELECT ECHO('some-string')

LLEN

SELECT LLEN('some-list')

RedisJSON (link)

JSON_GET

SELECT JSON_GET('my-json-key')
SELECT JSON_GET('my-json-key', 'some.path[2]')

JSON_MGET

SELECT * FROM JSON_MGET('some.path', 'key1,key2,key3')