Dec 27 2012, Gregory Trubetskoy

What is Thredis?

Thredis is Redis + SQL + Threads. Or perhaps it's pure lunacy
resulting from some mad winter hacking mixed with eggnog. Or perhaps
it's the first hybrid SQL/NoSQL server. You be the judge.

Thredis embeds an in-memory SQLite database within Redis to enable a
sophisticated level of SQL (joins, sub-selects, etc, all supported),
as well as introduces multi-threaded processing to take advantage of
SMP systems.

Thredis is Work In Progress - currently available at
This is ALPHA quality code, tests and docs have not been written

Now the FUN part, best shown by example:

I'm assuming you're familiar with Redis and its CLI. If not, you
should probably spend some time on first. I'm also assuming 
you're no stranger to SQL.

Thredis adds a new command 'SQL' which takes one argument, a
string of SQL:

redis> sql "select 'hello from sql' as greeting"
1) 1) "greeting"
2) 1) "hello from sql"

We can create a virtual table using the redis module. The name of
the table maps to a Redis object (which doesn't have to exist).

redis> sql "create virtual table foo using redis"

Let's create a simple string object.

redis> set foo bar

And let's see if we can use SQL to read it:

redis> sql "select * from foo"
1) 1) "key"
   2) "val"
2) 1) "1"
   2) "bar"

Et voila!

Notice how a SELECT from a redis-mapped table always has two columns,
key and val. val is always the value, and key is sometimes just a
sequential number (as above) or the actual key or the score (see below).

Let's get rid of the string object. Notice that this doesn't break the
virtual table. When the correposnding Redis object does not exist,
the table will simply appear empty.

redis> del foo
(integer) 1

How about a list?

redis> lpush foo a b c
(integer) 3
redis> sql "select * from foo"
1) 1) "key"
   2) "val"
2) 1) "1"
   2) "c"
3) 1) "2"
   2) "b"
4) 1) "3"
   2) "a"

Or a hash?

redis> hmset foo_hash a b c d e f
redis> sql "select * from foo_hash"
(error) ERR SQL error: no such table: foo_hash 

Oh, we forgot to create the virtual table so that SQLite can see the
Redis object, let's do that:

redis> sql "create virtual table hash using redis (foo_hash)"

Notice how this time we named the table 'hash' and gave the name of
the Redis object as an argument at the end.

redis> sql "select * from hash"
1) 1) "key"
   2) "val"
2) 1) "a"
   2) "b"
3) 1) "c"
   2) "d"
4) 1) "e"
   2) "f"

Let's try this with a sorted set?

redis> zadd foo_zset 1 a 2 b 3 c
(integer) 3
redis> sql "create virtual table zset using redis (foo_zset)"
redis> sql "select * from zset"
1) 1) "key"
   2) "val"
2) 1) "1.0"
   2) "a"
3) 1) "2.0"
   2) "b"
4) 1) "3.0"
   2) "c"

Now let's try joining a hash with a sorted set:

redis> sql "select * from zset join hash on zset.val = hash.val"
1) 1) "key"
   2) "val"
   3) "key"
   4) "val"
2) 1) "2.0"
   2) "b"
   3) "a"
   4) "b"

What else can you do with it?

redis> sql "select max(key) from zset"
1) 1) "max(key)"
2) 1) "3.0"

redis> sql "select sum(key) from zset"
1) 1) "sum(key)"
2) 1) "6.0"

We can also create a real SQLite table (it's all in-memory and very fast):

redis> sql "create table bar (one int, two text)"
redis> sql "insert into bar values (101, 'blah')"
redis> sql "insert into bar values (202, 'bleh')"

This table is NOT a redis object, it exists in SQLite memory space:

redis> debug object bar
(error) ERR no such key

Copy this table into a hash, like so (here we're introducing the
redis() SQL function, which allows executing Redis commands from
within SQL):

redis> sql "select redis('hset', 'bar_copy', one, two) from bar"
1) 1) "redis('hset', 'bar_copy', one, two)"
2) 1) "1"
3) 1) "1"

Just to make sure it worked:

redis> hgetall bar_copy
1) "101"
2) "blah"
3) "202"
4) "bleh"

And let's not forget you can also use Lua in conjunction with this.

eval "return'sql', 'select * from foo')" 0
1) 1) "key"
   2) "val"
2) 1) "1.0"
   2) "a"
3) 1) "2.0"
   2) "b"
4) 1) "3.0"
   2) "c"

Triggers are supported as well. Let's say we have a SQL table called
blah which we created like this:

redis> sql "create table blah (one int, two text);"

And we want to count all updates of column text in a Redis key called
'blah_update_count' using a trigger. We create the trigger like so:
redis> sql "CREATE TRIGGER blah_update_count UPDATE OF two ON blah BEGIN SELECT redis('incr', 'blah_update_count'); END"

What happens if we insert a record into blah?
redis> sql "insert into blah values (2012, 'new year')"
redis> get blah_update_count

Nothing. This is because that was an INSERT, not an UPDATE. Let's try

redis> sql "update blah set two = 'old year' where one = 2012"
redis> get blah_update_count

Hopefully by now you're getting the idea.

Your SQL data will get saved for you automatically in the background
according to your Redis SAVE policy and loaded on startup. It is saved
in a separate file by default called dump.sqlite, and it is simply a
SQLite database, which can be opened using the sqlite3
command. Accessing your Redis virtual tables from a sqlite3 command
line will give you errors, of course.

You can also force a save manually:

redis> sqlsave

What do threads have to do with any of this? Thredis started out as
simply threaded Redis. The idea of SQLite integration came later. Why
and how threads are used is explained in README-THREDIS.