Hacker News Reader

Comment by siscia | Hacker News Reader

The modules is born just to fulfill one of our needs: we had several micro-services using redis and no DB at all, but then we find ourselves in the situation where we needed SQL capabilities, redis module where being announced and we give them a shoot.

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.

View on HN

Hello siscia. What's not clear to me if this is intended for production or if it's an example. I understand the general problem of proving SQLite with a network interface/API, and I guess, but I don't know, there are many solutions for that. If you are trying to solve the problem of SQLite not having a network interface by default, allowing the access from Redis via the Redis protocol, then the implementation is not optimal AFAIK because it does not use any threading but stops Redis while executing the queries in SQLite. Redis modules allow for non blocking commands doing the bulk of the work in threads, so I would suggest at least this approach if this is what you are doing. IMHO also there should be a more sensible conversion of data types from the SQL reply to the Redis protocol.

If Redis has to be stopped during SQL queries IMHO it's better to use another network layer with SQLite.

Ciao Salvatore,

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

Hey, glad to know you are going multi-threaded on that, since I believe it will be very easy. To start you could just use a thread per request (not just to start, probably even in the long run), immediately block the client via the module API to do this, fire the thread, wait for the thread to complete the work and accumulate the reply, and re-surrect the blocked client, feeding the reply. AFAIK this can work very well.

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.

I tried to spawn a new thread for each request and it was embarrassedly slow, maybe I was doing something wrong from my side, but I believe I will settle for a pool.

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

or with multiple insert in the same transaction

  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 executed

Just to throw my two cents into the mix. The idea of being able to _query_ a single key in a SQL-like fashion is interesting, but I can't think of a production implementation that would be useful enough.

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.

Having to write SQL statements as redis EXECs isn't elegant IMO.

> Just to throw my two cents into the mix.

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.

Reply on news.ycombinator.com