RediSQL – A Redis module that provides a functional SQL database
github.com
github.com
While this can be a good programming example for newcomers, I suggest to look at modules that are really implementing new DB paradigms on top of Redis. For instance this module builds a graph DB on top of Redis data types, with a query language and so forth:
On top of that, this looks like a simple proxy, so it's only making things less efficient.
OP, I'd suggest explaining the benefits better. Maybe I missed it but I don't really get this at all.
What you mean by efficiency ? Operation at the second? Sure it is less efficient than Postgres or of SQLite embed in your application.
However if you mean convenience and simplicity this solution may (arguably) be efficient enough.
This module sit between a "full" client-server DB, a redis instance (that doesn't provide SQL capabilities) and SQLite that is not client-server.
Being completely honest, how HN teach us, build a great technical product without showing it as soon as it provide just a tiny amount of value is not desirable.
The comment in this thread indicate that there is some interest and I got some valuable suggestions and feedback on what to develop next.
If Redis has to be stopped during SQL queries IMHO it's better to use another network layer with SQLite.
I claim that the module is alpha code.
There is a non-blocking implementation on the way. The problem that I am facing is the tradeoff between using a single thread or multiple threads, but I came to the conclusion that it depends on the use cases. (SQLite on write lock a whole table no a row at the time, so I am expecting a single lockless thread to be faster on write intensive workload and multiple thread be faster on read intensive).
What you suggest as conversion of data types from SQL reply to redis ? Right now a select return a nested array, the outer for the rows the inners for the columns.
Thanks
About the reply, returning a nested array as you are doing may actually be enough after all, even if the reply need to be parsed accordingly to the exact query. The alternative would be to annotate the reply with field names and things like that.
Another thing that probably could be improved: to provide direct support (by passing multiple arguments) to quote strings to pass to SQL, in order to allow the user to have an easy path to avoid security issues.
About the reply the only thing that I see manageable is to include the name of the columns as first row, I was considering the idea but I am not completely sold yet.
I was thinking to bind a particular statement to a key in order to provide the possibility to have standard queries between different clients
REDISQL.CREATE_STATEMENT insert_user "INSERT INTO users VALUES($1, $2, $3)"
> OK
REDISQL.EXEC_STATEMENT insert_user name, password, email
> OK
REDISQL.CREATE_TRANSACTION
> "REDISQL.TRANSACTION_12345"
REDISQL.EXEC_STATEMENT "REDISQL.TRANSACTION_12345" insert_user name1 password1 email1
> OK
REDISQL.EXEC_STATEMENT "REDISQL.TRANSACTION_12345" insert_user name2 password2 email2
> OK
REDISQL.EXEC_STATEMENT "REDISQL.TRANSACTION_12345" insert_user name3 password3 email3
> OK
REDISQL.COMMIT_TRANSACTION "REDISQL.TRANSACTION_12345"
> 3 # as the number of statement executedInstead, I'd look at using redis in front of something like MySQL so some operations go to redis and other right to the server.
Having to write SQL statements as redis EXECs isn't elegant IMO.
You have no idea how much those 2 cents are appreciated, really, I mean it.
> The idea of being able to _query_ a single key in a SQL-like fashion is interesting
Not sure I understood what you mean here. Would you mind to provide some kind of example or to explain it further?
> Instead, I'd look at using redis in front of something like MySQL so some operations go to redis and other right to the server.
Extremely interesting, how you discriminate though ? If it is going to be a "regular" cache you don't need a module, redis can already do it by itself. What use case you were think of?
> Having to write SQL statements as redis EXECs isn't elegant IMO.
I do completely agree, the API is going to get more structured for sure.
The idea was actually NOT to create an SQL like database, but just proxy ordinary redis commands via secondary index. So you select ids, and perform HGET or HGETALL on them, etc. And the same goes for indexing - you perform something like HMSET and "through" the index, and it performs the operation while indexing the hash automatically.
Also, indexes can be used as direct data types with no automation, you just insert ids and tuples of data into them, and you can query them and get ids back. https://github.com/RedisLabsModules/secondary
So would be very interesting to see benchmarks and comparisons with MySQL - I think right now we have more ways to implement it better.
upd: comment about proxying sqlite eliminated this hope.
Also I have introduced the module in a blog post here: http://redbeardlab.tech/2016/12/13/redisql
You are talking about something definitely feasible but that require a really carefully implementation.
For now, it is strictly for new SQL data tables.
However I must say that put all your keys inside the SQL table is not difficult.
SQLite does implement the LIKE operator.
From the module:
127.0.0.1:6379> REDISQL.EXEC "CREATE TABLE text_search(t TEXT);"
OK
127.0.0.1:6379> REDISQL.EXEC "INSERT INTO text_search VALUES('hello');"
OK
127.0.0.1:6379> REDISQL.EXEC "INSERT INTO text_search VALUES('banana');"
OK
127.0.0.1:6379> REDISQL.EXEC "INSERT INTO text_search VALUES('apple');"
OK
127.0.0.1:6379>
127.0.0.1:6379> REDISQL.EXEC "SELECT * FROM text_search WHERE t LIKE 'h_llo';"
1) 1) "hello"
127.0.0.1:6379> REDISQL.EXEC "SELECT * FROM text_search WHERE t LIKE '%anana';"
1) 1) "banana"
127.0.0.1:6379> REDISQL.EXEC "INSERT INTO text_search VALUES('anana');"
OK
127.0.0.1:6379> REDISQL.EXEC "SELECT * FROM text_search;"
1) 1) "hello"
2) 1) "banana"
3) 1) "apple"
4) 1) "anana"
127.0.0.1:6379> REDISQL.EXEC "SELECT * FROM text_search WHERE t LIKE 'a%';"
1) 1) "apple"
2) 1) "anana"What are your uses cases?
It is possible to have in SQLite the keys from redis, however it does require a level of complexity that must be justified.
redis 127.0.0.1:6379> set foo bar
OK
redis 127.0.0.1:6379> sql "select * from foo"
1) 1) "key"
2) "val"
2) 1) "1"
2) "bar"The blog mentions:
If you are writing microservices to avoid bothering the master of DB with your need ephemeral needs of structured database
I see this module perfect for small dataset of unimportant data.
I would understand this as an experiment (something like "ReDoom"), but since there will be a paid version, I'm trying to position the product technology.
A straight SQLite database doesn't quite give to you the same kind of possibility, imagine you have multiple machine that want to access the data.
It is a middle ground between "full" SQL databases and simpler solution.
It does use Redis for the client-server communication which gives reliability and is already well know in most companies.
My suggestion to use it for small, unimportant data is because there is still not persistency on disk, I am working on it right now.
Under this conditions there is still a false dichotomy between RediSQL on one side, and "complex RDBMS" on the other.
What about simple RDMSs like H2? Such solution would meet the requirements:
- network-accessible RDBMS
- no configuration
This project encourages merge-on-read which is exactly the problem k/v stores try to solve in another way (merge-on-write).
The problem with relational data stores is that once you have relations then you need consistency and then you need moving operations to the data store itself (stored procedures), and transactions, etc... and suddenly you need an army of certified DBAs and a $1+ million dollar budget on datastores alone.
It's not the right way to go. Feel free to have SQL but adding joins is a bad idea.
In k/v datastores, e.g: document oriented, you can just put all the data related to one entity on one record and that's it... and dealing with multi-key operations is discouraged rather than encouraged.
Why discouraged? because it's hard to load balance, creates the need for synchronization and locking at a broader level, adds complexity to the maintenance operations, and so on, so forth.
The SQL thing exists because dBase, one of the first database products, had it. And then every product that entered the market had to go with it to diminish friction.
Your discussion about what's "encouraged" or not is frankly irrelevant, as key/value stores are super easy compared to general relational databases and most RDBMSes are already quite good key/value stores (by contrast, most key/value stores make lousy RDBMSes). The simple fact of the matter is: if your key/value store doesn't support multikey read/write transactions, it has strictly less functionality than a database that does, regardless of how you use it, and if it does support multikey read/write transactions it'll have all the same performance issues you associate with RDBMSes (well, that's a slight oversimplification because it may provide a more limited API).
Thredis also added multi-threading to Redis. The code is here: https://github.com/grisha/thredis
https://groups.google.com/forum/#!topic/redis-db/TLfwGfldgUU
Using a redis module get you A LOT of thing from free and you really stand on the shoulder of giants.
Thredis author here - Redis did not support modules back then, had I done this today it prolly would have been a module (though it's been a while since I hacked on Redis and I don't know how modules work)
sure, I took a deep look at your project and really appreciate you open source it.
Do you have any feedback to share?
Cheers,
Simone
Definitely could use this for migration off SQLite to a solid DB.
For now it works in memory, after Saturday it will save data in a standard SQLite file.
Replication and SQL are fairly tricky beast together. What is your use case?
[1] http://www.philipotoole.com/replicating-sqlite-using-raft-co...
We want to replicate just to being sure that the data is safe? Then really you could simply stream to all the replicas what happens to the master.
You want to increase read performance? Then you first start caching result, then you start stream to slave and only make read from them.
You want to increase write performace? That is quite a bad beast. You start by making the single instance as fast as possible (96k insert per second [1]) then you pretty much hit the ceiling in my opinion.
You could partition the SQL tables, but then what happens to joins and select? They must go over the network (high latency), spot distributed deadlock (highly variable latency) and it start to become an huge complex project.
[1]: http://stackoverflow.com/questions/1711631/improve-insert-pe...
I could associate each DB to a redis key and stream all the UPDATES on each db.
At this point you could simply listen to the stream and apply those updates to a master DB.
What you think?
> Where does it save the data?
It doesn't
>> in-memory database.
> Does it support replication?
That's really, really unlikely, because you can't do that on the sqlite VFS layer.
> migration off SQLite to a solid DB.
This is SQLite (in redis).
The very first and naive solution would be to simply stream all your statements to the slaves.
You are sending data to the redis instance, that read your statement and pass it to the module, the module execute and finally reply.
And everything really depends on your network and load on redis itself.
However, to just give some number on my machine I do 1000 insert on 0.6 seconds, the longest insert took 0.01 second and overall I can claim a total of 1000 / 0.6 => 1600 insert per second. Consider that each insert is a isolated transaction.
The test I run is exactly the one you can find here: https://github.com/RedBeardLab/rediSQL/blob/381e3796ad31c231...
Of course, if you start to insert JOINS everything is slowing down.
Also please consider that, at the moment, it is a BLOCKING version, while executing the statements redis itself is waiting.
Finally I believe there are possibilities to improve this numbers.
To answer your other question, you have the option to either keep it all in memory (and lose on reboot) or commit the data to disk. In my testing I only saw a ~10% performance penalty for committed data to disk (granted we have extremely fast SSDs).