# zeeSQL, SQL and search by value for Redis. Fast, Simple and Reliable.

This is an introduction to zeeSQL, a Redis modules that brings SQL capabilities into Redis along with complex SQL search by value of Redis hashes.

This document is your entry point for the documentation and will guide you to what to read next.

## Quickstart

Once you start zeeSQL, you can interact with it using the standard `redis-cli`.

Below we are creating a database, create a new table, insert new rows, and query those rows back.

```
$ redis-cli
> ZEESQL.CREATE_DB DB
1) 1) "OK"
> ZEESQL.EXEC DB COMMAND "CREATE TABLE users(id STRING, score INT);"
1) 1) "DONE"
2) 1) (integer) 0
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "INSERT INTO users VALUES('jausten', 3), ('hugo', 5);"
1) 1) "DONE"
2) 1) (integer) 2
> ZEESQL.EXEC DB COMMAND "SELECT * FROM users;"
1) 1) "RESULT"
2) 1) "id"
   2) "score"
3) 1) "TEXT"
   2) "INT"
4) 1) "jausten"
   2) (integer) 3
5) 1) "hugo"
   2) (integer) 5
```

`zeeSQL` allows searching Redis hashes by values, making much simpler to express complex queries.

```
> ZEESQL.INDEX DB NEW PREFIX products:* TABLE products SCHEMA id INT price INT name STRING
OK
> HMSET products:123 id 123 price 2345 name "set of glasses"
OK
> HMSET products:471 id 471 price 3459 name "wall clock"
OK
> ZEESQL.EXEC DB COMMAND "SELECT name FROM products WHERE price > 2500;"
1) 1) "RESULT"
2) 1) "name"
3) 1) "TEXT"
4) 1) "wall clock"
```

If you think that `zeeSQL` can solve some of your problem, please keep reading for more context and details.

Or you can [read the Tutorial](/tutorial) to get a better overview on how `zeeSQL` works and what it can do for you.

## What is zeeSQL

`zeeSQL` is a Redis module. In version 4, Redis introduce this new features of modules.

A module, once loaded into Redis, provides new capabilities to Redis itself. Most of the time this means new commands that the user can access using the standard Redis interface.

Either by command line or by API access.

`zeeSQL` embeds SQLite into Redis.

This allows to create SQL databases that can be easily accessed via Redis. The main motivation for the project, back then, was to create a simple and easy to operate data layer. Instead of having, a DBMS, a cache layer and a queue, you can just use `zeeSQL` with Redis and have all the tools to run a rather big web service.

`zeeSQL` outgrow this first stage. Our users were to keep demanding tighter integration between the data stored in Redis and what was accessible over SQL. So, `zeeSQL` secondary indexes were born.

Secondary indexes allow getting data from Redis hashes using standard SQL. It is possible to look into all your keys and get only the one that have the eg. `score` field set to a number greater than 20.

Similarly, it is possible to do aggregation and filtering.

## How zeeSQL can help

`zeeSQL` can help whenever your infrastructure is too complex.

If you are having issue keeping your main database and its cache in sync, `zeeSQL` help.

If your Redis datamodel is too complex, `zeeSQL` can help.

If anytime you change a piece of information inside Redis you need to also modify dozen of other Redis keys, `zeeSQL` can simplify that.

If you need a fast SQL engine that works in memory, that you can integrate now in your infrastructure, `zeeSQL` will be fast.

## Getting the binary

The binary is distributed freely.

It is available on the website:

```
wget https://zeesql.com/releases/latest/zeesql.so -o $HOME/zeesql.so
```

The URL is stable and provides always the latest released version of `zeeSQL`.

More information on [how to get zeeSQL.](/how-to/get-zeesql)

## Loading the module

To use `zeeSQL` you need to load it into your Redis instances.

After you download the binary, and placed somewhere accessible, you can load the module in different way.

The first way is to pass the module as argument to Redis.

```
redis-server --loadmodule $HOME/zeesql.so
```

The second way is to configure Redis to start with the module. It is sufficient to add the following line to your `redis.conf` file.

```
loadmodule $HOME/zeesql.so
```

The final way is to load the module issuing a command against your Redis instance.

```
redis-cli MODULE LOAD $HOME/zeesql.so
```

Each of these three way, if successful, will load `zeeSQL` into Redis making it ready to use.

## Getting a license

In order to offer a great product, with great documentation and with support, `zeeSQL` must limits its capabilities for free users and let people pay a fair price for what we believe being good software.

Free users can use the product as much as they like and we will provide support to them without any issue. But `zeeSQL` will be limited in the amount of databases and secondary indexes that can be created.

Note how the lack of license does not limit the size of your dataset, but only the complexity tha `zeeSQL` manages for you.

In order to remove these limitations, it is possible to [buy a license](https://license.zeesql.com).

More information about the [pricing in the dedicate page](/pricing).


# How to

Set of short tutorial to get you started with zeeSQL as fast as possible!


# How to choose between QUERY and EXEC

zeeSQL comes with two commands that seem similar:

[`ZEESQL.QUERY`](/references#zeesql-query) and [`ZEESQL.EXEC`](/references#zeesql-exec).

These two commands have the same syntax, but different semantic.

[`ZEESQL.QUERY`](/references#zeesql-query) is for **read-only** operations like `SELECT`.

[`ZEESQL.EXEC`](/references#zeesql-exec) is for updating the status of the database using `DELETE` or `INSERT` or `UPDATE` statements. However, `ZEESQL.EXEC` supports also `SELECT`, mostly for keeping things simple for everybody.

Still, especially in a production environment, `ZEESQL.QUERY` should be preferred whenever you are executing a read-only operation.

Redis support AOF and primary/secondary replication. Every time a command that modifies the status of the database is invoked in Redis, the same command is sent to the AOF file (if enable) and to the replicas.

We know that `ZEESQL.QUERY` will never modify the status of the database, hence all the operations executed with it, are not replicated, nor to the AOF file, nor to the replicas.

This is not true for `ZEESQL.EXEC`. Since we don't know if the operation in `ZEESQL.EXEC` will modify or not the database, the command needs to be replicated. This implies that the replicas will repeat the command against their own internal status.

It is a waste of resources to send a `SELECT` with the `ZEESQL.EXEC` command because it will be replicated by your replicas and by the AOF file, while not changing the structure of the database.

`ZEESQL.QUERY`, being a read-only Redis command, can be executed also by the replicas.

If you have a very demanding application with a lot of load, you may have to use read-only replicas. To read-only replicas, you cannot send Redis command that might modify the internal status like `ZEESQL.EXEC`.

Against read-only replicas, you can only send read-only commands, like `ZEESQL.QUERY`.

While coding up your application it is a great idea to take the time and use `ZEESQL.QUERY` when possible instead of blindly using `ZEESQL.EXEC`.

`ZEESQL.QUERY` will raise an error when you try to use it with something that is not a read-only query, so you will catch possible misuses of `ZEESQL.QUERY` extremely fast.

```
127.0.0.1:6379> ZEESQL.CREATE_DB DB
1) 1) "OK"
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "CREATE TABLE foo(a INT, b INT);"
1) 1) "DONE"
2) 1) (integer) 0
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "INSERT INTO foo VALUES(1,2),(2,3);"
1) 1) "DONE"
2) 1) (integer) 2
127.0.0.1:6379> ZEESQL.QUERY DB COMMAND "INSERT INTO foo VALUES(1,2),(2,3);"
(error) Statement is not read only but it may modify the database, use `EXEC` instead.
127.0.0.1:6379> ZEESQL.QUERY DB COMMAND "SELECT * FROM foo;"
1) 1) "RESULT"
2) 1) "a"
   2) "b"
3) 1) "INT"
   2) "INT"
4) 1) (integer) 1
   2) (integer) 2
5) 1) (integer) 2
   2) (integer) 3
```

## About zeeSQL

zeeSQL is a Redis Module that provides SQL capabilities to Redis. It allows the creation and management of several SQL databases, each one independent from the other. Moreover, zeeSQL provides out-of-the-box [secondary indexes](/secondary-indexes) capabilities, allowing fast and easy search by value in Redis.


# know-what-secondary-indexes-are defined


# How to load zeeSQL in Redis

After you get the [zeeSQL module](/how-to/get-zeesql) you need to load it up in Redis.

If you are using zeeSQL from the docker image, this step is not necessary, since the image is already set up to work with zeeSQL out of the box.

If you are running zeeSQL from scratch or you are setting up your own infrastructure, then these steps are necessary.

All these steps assume that you have saved the zeeSQL module in `/home/user/zeeSQL.so`.

## Load from the command line

The simplest way to load the module in Redis is to pass the module as a flag when starting up the Redis instance.

You just need to provide the `--loadmodule` flag with the correct path.

For instance:

```
$ redis-server --loadmodule /home/user/zeeSQL.so
```

## Load from config

A more structured way to load the module is to use the default configuration file of Redis.

In the Redis configuration file, the `loadmodule` directive is available.

It is sufficient to use that directive passing the path of the module:

```
################################## MODULES #####################################

# Load modules at startup. If the server is not able to load modules
# it will abort. It is possible to use multiple loadmodule directives.
#

loadmodule /home/user/zeeSQL.so
```

## Load with Redis running

The last option is to issue the `MODULE LOAD` command at runtime against Redis.

```
127.0.0.1:6379> MODULE LOAD /home/user/zeeSQL.so
8:M 06 Mar 2021 14:33:04.923 * Module 'rediSQL' loaded from /home/user/zeeSQL.so
OK
```

This will load the module exactly like the two other systems.

## About zeeSQL

zeeSQL is a Redis Module that provides SQL capabilities to Redis. It allows the creation and management of several SQL databases, each one independent from the other. Moreover, zeeSQL provides out-of-the-box [secondary indexes](/secondary-indexes) capabilities, allowing fast and easy search by value in Redis.


# How to check if an index is used in zeeSQL and SQLite

This tutorial will cover both zeeSQL and SQLite. As you might know, zeeSQL is actually based on SQLite, so everything we say about zeeSQL can also be applied to raw SQLite.

## What are indexes

Indexes are secondary data-structures that sit next to the main data in your database.

They don't store the main data, but only a subset of them, usually a few columns, and the primary key.

The main point of indexes is that the data is always store sorted for the columns they are indexing.

For instance, if we create an index for the `score` of some users, in the index, the scores will be stored sorted, from the smallest to the largest (or vice-versa).

Storing the field sorted, allows a fast comparison for those fields. For instance, if we want to select all the users with a score greater than 20, we can look up in the index where the users with a score greater than 20 starts, and return all the users after that.

However, an index on the score, won't help if we are looking for all the users that played more than 15 games. For that query, you will need a different index.

As your use cases become complex, and the table larger, eventually a lot of indexes will be accumulated.

Then, for complex queries, it becomes very difficult to know what index, if any, is going to be used.

The SQL engine has its own algorithms and heuristic to figure out, what it thinks is the fastest way to execute a query.

Please note that while indexes help in retrieving information, they need to be maintained, which implies more load when the data are updated or added. There is a tradeoff between insertion speed, (no index, fastest insertion) and query speed (the more indexes they are, the more query will run optimally).

When no index can be used to speed up a query, SQLite will fallback in a full table scan, which means that it will look at every row in the table. For big tables, this implies a higher latency and slower results.

## Am I using some index?

SQLite, and so zeeSQL, comes with a handy command to check what the SQL engine is going to do, in other to retrieve the data.

`EXPLAIN QUERY PLAN $your_query` returns a human-readable representation of what the SQL engine is going to do to fetch the data.

An example will help clarify, suppose we have two tables, `foo` and `bar`.

```
127.0.0.1:6379> ZEESQL.CREATE_DB DB
1) 1) "OK"
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "create table foo(a int, b int, c int);"
1) 1) "DONE"
2) 1) (integer) 0
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "create table bar(x int, y int, z int);"
1) 1) "DONE"
2) 1) (integer) 0
```

At this point, we don't have any indexes, so any query will go through a full table scan.

```
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "EXPLAIN QUERY PLAN select a from foo where a = 3 and b = 4 and c = 5;" NO_HEADER
1) 1) "RESULT"
2) 1) (integer) 2
   2) (integer) 0
   3) (integer) 0
   4) "SCAN TABLE foo"
```

In this example, were are scanning the whole `foo` table.

Adding a simple index to the `foo` table will help SQLite in searching more efficiently the table.

```
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "CREATE INDEX foo_a on foo(a);" NO_HEADER
1) 1) "DONE"
2) 1) (integer) 0
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "EXPLAIN QUERY PLAN select a from foo where a = 3 and b = 4 and c = 5;" NO_HEADER
1) 1) "RESULT"
2) 1) (integer) 3
   2) (integer) 0
   3) (integer) 0
   4) "SEARCH TABLE foo USING INDEX foo_a (a=?)"
```

This works also when you are working with multiple tables.

```
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "EXPLAIN QUERY PLAN select a from foo, bar where a = 3 and b = 4 and c = 5 and c = z;" NO_HEADER
1) 1) "RESULT"
2) 1) (integer) 4
   2) (integer) 0
   3) (integer) 0
   4) "SCAN TABLE bar"
3) 1) (integer) 10
   2) (integer) 0
   3) (integer) 0
   4) "SEARCH TABLE foo USING INDEX foo_a (a=?)"
```

On `foo`, we have the index we created previously that simplifies the search, in `bar` we don't have any indexes, so we are forced to do a full table scan.

As soon as we add an index:

```
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "CREATE INDEX bar_z on bar(z);" NO_HEADER
1) 1) "DONE"
2) 1) (integer) 0
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "EXPLAIN QUERY PLAN select a from foo, bar where a = 3 and b = 4 and c = 5 and c = z;" NO_HEADER
1) 1) "RESULT"
2) 1) (integer) 4
   2) (integer) 0
   3) (integer) 0
   4) "SEARCH TABLE foo USING INDEX foo_a (a=?)"
3) 1) (integer) 13
   2) (integer) 0
   3) (integer) 0
   4) "SEARCH TABLE bar USING COVERING INDEX bar_z (z=?)"
```

Now searches on both tables are using an index.

One detail, on `bar` we are using a `COVERING INDEX` instead of a standard index.

A standard index makes it faster to look up the id of the rows that we are interested in, but then we still need to fetch those rows from the database. A covering index means that you are not going to fetch extra rows because all the data you care about are already stored in the index itself. Note that in the query above we don't ask for any columns from the `bar` table in the result set, but we only compare that one column of `foo` is equal to one of `bar`.

## Composite indexes

Indexes can be against a single column or against multiple columns.

The more the index is specific, the faster will be your query.

```
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "CREATE INDEX foo_b_c on foo(b, c);" NO_HEADER
1) 1) "DONE"
2) 1) (integer) 0
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "EXPLAIN QUERY PLAN select a from foo where a = 3 and b = 4 and c = 5;" NO_HEADER
1) 1) "RESULT"
2) 1) (integer) 3
   2) (integer) 0
   3) (integer) 0
   4) "SEARCH TABLE foo USING INDEX foo_b_c (b=? AND c=?)"
```

In this case, instead of using the index `foo_a` the SQLite engine prefers the one on both `b` and `c` columns.

It is important to be careful between `AND` and `OR` conditions.

```
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "EXPLAIN QUERY PLAN select a from foo where a = 3 OR b = 4 and c = 5;" NO_HEADER                                                     1) 1) "RESULT"
2) 1) (integer) 4
   2) (integer) 0
   3) (integer) 0
   4) "MULTI-INDEX OR"
3) 1) (integer) 10
   2) (integer) 4
   3) (integer) 0
   4) "SEARCH TABLE foo USING INDEX foo_a (a=?)"
4) 1) (integer) 21
   2) (integer) 4
   3) (integer) 0
   4) "SEARCH TABLE foo USING INDEX foo_b_c (b=? AND c=?)"
```

The query seems the same, but instead of an `AND` we have an `OR`, in this case, one more search is necessary.

If they were all `OR`, then a full table scan is almost guarantee, unless you don't have a separated index for each column in the table.

```
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "EXPLAIN QUERY PLAN select a from foo where a = 3 OR b = 4 OR c = 5;" NO_HEADER
1) 1) "RESULT"
2) 1) (integer) 2
   2) (integer) 0
   3) (integer) 0
   4) "SCAN TABLE foo"
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "CREATE INDEX foo_b on foo(b);" NO_HEADER
1) 1) "DONE"
2) 1) (integer) 0
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "CREATE INDEX foo_c on foo(c);" NO_HEADER
1) 1) "DONE"
2) 1) (integer) 0
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "EXPLAIN QUERY PLAN select a from foo where a = 3 OR b = 4 OR c = 5;" NO_HEADER
1) 1) "RESULT"
2) 1) (integer) 4
   2) (integer) 0
   3) (integer) 0
   4) "MULTI-INDEX OR"
3) 1) (integer) 10
   2) (integer) 4
   3) (integer) 0
   4) "SEARCH TABLE foo USING COVERING INDEX foo_a_b_c (a=?)"
4) 1) (integer) 20
   2) (integer) 4
   3) (integer) 0
   4) "SEARCH TABLE foo USING INDEX foo_b (b=?)"
5) 1) (integer) 30
   2) (integer) 4
   3) (integer) 0
   4) "SEARCH TABLE foo USING INDEX foo_c (c=?)"
```

## End

Indexes are fundamental for every database system, however, they might be confusing and not intuitive.

zeeSQL, and SQLite, provide a very simple way to check what indexes are going to be used for each of your queries, and when in doubt, it is a good idea to check it.

Do not add too many indexes, they slow down insertion. Moreover, in small datasets, a full table scan can be fast enough.

Always verify your assumption about indexes.

## About zeeSQL

zeeSQL is a Redis Module that provides SQL capabilities to Redis. It allows the creation and management of several SQL databases, each one independent from the other. Moreover, zeeSQL provides out-of-the-box [secondary indexes](/secondary-indexes) capabilities, allowing fast and easy search by value in Redis.


# know-what-secondary-indexes-are defined


# create-an-index


# create-a-view


# create-a-secondary-index


# How to create a trigger

Triggers are one way to keep a consistent state of your data.

They are not the only way, and somehow, they are looked upon, however they can be very powerful.

zeeSQL is based on SQLite, so all that we are saying, apply equally to both zeeSQL and SQLite itself.

When you modify your database, with an `UPDATE` or a `DELETE` or a `INSERT` triggers can be invoked and they can modify your databases.

To create a trigger, we need to define:

1. When to invoke it
2. What the trigger should do

A trigger can be invoked in response to either an UPDATE or a DELETE or an INSERT.

We can also specify if we want the trigger to be invoked before the action takes place, or just after.

The action that the trigger should do, is a simple SQL command, it can be an INSERT or an UPDATE or a DELETE.

Trigger are very useful to keep the database consistent with some view of the world that was not possible to express in the SQL schema, or to keep counters.

For instance, suppose we want to have a very quick way to know how many rows are in a table. We can either run a count(), or we can keep track of each row with a trigger.


# quickly-ingest-data


# How to copy a database

zeeSQL can manage multiple databases at the same time.

Sometimes, it can be convenient to copy the content of one database into another one.

Copying databases can help if you want to transfer a database backed by a disk into memory, or the other way, transferring an in-memory database to disk. Having databases on disk is make it simple to backup them, and reading databases from disk makes it simple to import databases from different sources. On the other hand, having databases in memory makes them extremely fast.

For some use cases, you may want to have a set of databases that all share the same structure. For instance, if each of your users is associated with a database, instead of creating all the tables needed every time new users signup, you could just clone a template database.

Copying databases can also help in spreading read load against an immutable database. Instead of reading from a single database, you can copy the database into few other databases and spread the load.

To copy a database, you can use the [`ZEESQL.COPY`](/references#zeesql-copy) database.

The command takes two databases, after the `FROM` and the `TO` flags. The database after the `FROM` flag is the source database. The one after the `TO` flag is the destination database.

The destination database is completely wiped out, and replaced by the content of the source database.

The source database is left intact.

```
127.0.0.1:6379> ZEESQL.CREATE_DB DB01
1) 1) "OK"
127.0.0.1:6379> ZEESQL.EXEC DB01 COMMAND "create table foo(a, b);"
2) 1) (integer) 0
127.0.0.1:6379> ZEESQL.EXEC DB01 COMMAND "create table bar(a, b);"
1) 1) "DONE"
2) 1) (integer) 0
127.0.0.1:6379> ZEESQL.EXEC DB01 COMMAND "insert into foo values(1,2),(2,3); insert into bar values(1,2),(2,3);"
1) 1) "DONE"
2) 1) (integer) 4
1) 1) "OK"
127.0.0.1:6379> ZEESQL.EXEC DB02 COMMAND "select * from foo, bar where foo.a = bar.a"
(error) no such table: foo
127.0.0.1:6379> ZEESQL.COPY FROM DB01 TO DB02
1) 1) "OK"
127.0.0.1:6379> ZEESQL.EXEC DB02 COMMAND "select * from foo, bar where foo.a = bar.a"
1) 1) "RESULT"
2) 1) "a"
   2) "b"
   3) "a"
   4) "b"
3) 1) "INT"
   2) "INT"
   3) "INT"
   4) "INT"
4) 1) (integer) 1
   2) (integer) 2
   3) (integer) 1
   4) (integer) 2
5) 1) (integer) 2
   2) (integer) 3
   3) (integer) 2
   4) (integer) 3
```

In the example, we create and populate two tables in the `DB01`.

Then we create the `DB02` and we try to immediately read from it, `zeeSQL` of course returns an error since the `DB02` database is empty.

After copying the content of the `DB01` database into the `DB02` database, the same query success.

## About zeeSQL

zeeSQL is a Redis Module that provides SQL capabilities to Redis. It allows the creation and management of several SQL databases, each one independent from the other. Moreover, zeeSQL provides out-of-the-box [secondary indexes](/secondary-indexes) capabilities, allowing fast and easy search by value in Redis.


# get-help


# work-with-dates


# using-full-text-search


# work-with-json


# How to create a new database in zeeSQL

To start using zeeSQL you need to create a database.

In zeeSQL you can have as many databases as necessary.

Each database you create in zeeSQL is an SQLite database, it can either be an in-memory database or a file-backed database.

Each database is associate with a Redis key.

Delete the key, and the database is deleted. If the database is backed by a file, then the file is closed, but it is not deleted.

To create a database, you can use the command [`ZEESQL.CREATE_DB`](/references#zeesql-create_db)

```
127.0.0.1:6379> ZEESQL.CREATE_DB DB
1) 1) "OK"
```

In this way a new in-memory database is created, it is associated with the `DB` key in Redis.

In-memory databases will always be created empty in zeeSQL.

To create a database-backed by a file, you need to pass the `PATH` flag.

```
127.0.0.1:6379> ZEESQL.CREATE_DB FILE_DB PATH file.sqlite
1) 1) "OK"
```

The `FILE_DB` database, will be associate with an SQLite database stored in disk, in the file `file.sqlite`.

Please note that databases stored in disk have different performances than the databases stored in memory. Usually slower.

## Importing an external database

Sometimes you may want to import some data from some other source.

zeeSQL allows you to load any SQLite database.

Suppose you already have an SQLite database that contains your users, and that you saved it in `users.sqlite`.

To import that database inside zeeSQL you only need to:

```
127.0.0.1:6379> ZEESQL.CREATE_DB USERS PATH users.sqlite
1) 1) "OK"
127.0.0.1:6379> ZEESQL.EXEC USERS COMMAND "select * from users"
1) 1) "RESULT"
2) 1) "id"
   2) "username"
   3) "score"
3) 1) "INT"
   2) "TEXT"
   3) "INT"
4) 1) (integer) 100
   2) "joh"
   3) (integer) 3
5) 1) (integer) 101
   2) "mary"
   3) (integer) 7
6) 1) (integer) 102
   2) "brand"
   3) (integer) 4
```

## About zeeSQL

zeeSQL is a Redis Module that provides SQL capabilities to Redis. It allows the creation and management of several SQL databases, each one independent from the other. Moreover, zeeSQL provides out-of-the-box [secondary indexes](/secondary-indexes) capabilities, allowing fast and easy search by value in Redis.


# How to create a new table in zeeSQL

After you have created a new database, the next likely action, will be to create a set of tables to store information.

zeeSQL uses SQLite under the hood, hence it can work with all the SQL queries that can be managed by SQLite.

Suppose you have created a new database with:

```
127.0.0.1:6379> ZEESQL.CREATE_DB DB
1) 1) "OK"
```

You can now create a new table executing a command against that database:

```
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "CREATE TABLE foo(col1 STRING, col2 INT, col3 STRING);"
1) 1) "DONE"
2) 1) (integer) 0
```

This command will create a table called `foo` with 3 columns. The first column is called `col1` and has type `STRING`, the second column is called `col2` and has type `INT` and the last column is called `col3` again of type `STRING`.

We know that everything went well because the result was "DONE". Moreover, zeeSQL is telling us that 0 rows were modified, this is correct since we only created a new table.

You can create an unlimited number of tables.

The [`CREATE TABLE`](https://sqlite.org/lang_createtable.html) documentation page of SQLite describes all the options and the syntax available to create a new table. All of them are available in `zeeSQL`.

## About zeeSQL

zeeSQL is a Redis Module that provides SQL capabilities to Redis. It allows the creation and management of several SQL databases, each one independent from the other. Moreover, zeeSQL provides out-of-the-box [secondary indexes](/secondary-indexes) capabilities, allowing fast and easy search by value in Redis.


# know-what-tables-are-defined


# know-what-databases-are-defined


# works-with-boolean


# How to get zeeSQL

There are different way to get `zeeSQL` each suitable for production and testing.

The best way for you dependes on your specific use case.

## Use the docker image

The simplest way to obtain `zeeSQL` is to use the standard docker image: `redbeardlab/zeesql`.

This particular image is based on the standard `Redis` image.

It starts `Redis` and automatically loads `zeeSQL` for you.

As soon as the image start, `zeeSQL` is loaded and ready to be used.

This can be used with `docker`, `podman`, but also in `kubernetes`.

If you are using docker, make sure to expose the port `6379` for `Redis`.

**Example**

```
docker run -d --name zeesql -p 6379:6379 --rm redbeardlab/zeesql
```

## Getting the binary

The second option is to get the `zeeSQL` binary.

The binary is rather small, \~10MB, and it can be downloaded from:

```
https://zeesql.com/releases/latest/zeesql.so
```

A simple way to get the binary is:

```
wget https://zeesql.com/releases/latest/zeesql.so
```

The binary can be distributed in whichever way you like, so you can push it in your private docker image, or send it to customers.

After you got the binary, you need to [load it in Redis.](/how-to/load-zeesql-into-redis)

## Using the license

If you are using either the docker image or the binary, zeeSQL will be run with limitations.

To avoid those limitations, you need a [software license](/pricing).

## About zeeSQL

zeeSQL is a Redis Module that provides SQL capabilities to Redis. It allows the creation and management of several SQL databases, each one independent from the other. Moreover, zeeSQL provides out-of-the-box [secondary indexes](/secondary-indexes) capabilities, allowing fast and easy search by value in Redis.


# How to get JSON output

By default zeeSQL returns nested arrays.

zeeSQL can also returns the exact same information as JSON output.

JSON output may be preferable since it is usually easier to parse and all languages offer full support for it.

The [`ZEESQL.EXEC`](/references#zeesql-exec) and [`ZEESQL.QUERY`](/references#zeesql-query) commands support the [`JSON`](/references#json-flag) flag, which instructs them to return JSON as output.

## Examples

The first example is about a command that does not return any rows but only `DONE`.

```
127.0.0.1:6379> ZEESQL.CREATE_DB DB
1) 1) "OK"
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "CREATE TABLE foo(col1 STRING, col2 INT, col3 STRING);"
1) 1) "DONE"
2) 1) (integer) 0
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "CREATE TABLE bar(col1 STRING, col2 INT, col3 STRING);" JSON
"{\"result\":\"done\",\"modified_rows\":0}"
```

Adding the `JSON` flags returns the exact same result but in JSON format.

```
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "INSERT INTO foo VALUES('AAA', 2, 'BBB'),('CCC', 3, 'DDD');"
1) 1) "DONE"
2) 1) (integer) 2
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "INSERT INTO bar VALUES('AAA', 2, 'BBB'),('CCC', 3, 'DDD');" JSON
"{\"result\":\"done\",\"modified_rows\":2}"
```

Another example is when the command returns some rows.

```
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "SELECT * FROM foo;"
1) 1) "RESULT"
2) 1) "col1"
   2) "col2"
   3) "col3"
3) 1) "TEXT"
   2) "INT"
   3) "TEXT"
4) 1) "AAA"
   2) (integer) 2
   3) "BBB"
5) 1) "CCC"
   2) (integer) 3
   3) "DDD"
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "SELECT * FROM bar;" JSON
"{\"rows\":[{\"col1\":\"AAA\",\"col2\":2,\"col3\":\"BBB\"},{\"col1\":\"CCC\",\"col2\":3,\"col3\":\"DDD\"}],\"number_of_rows\":2,\"columns\":{\"col1\":\"TEXT\",\"col2\":\"INT\",\"col3\":\"TEXT\"}}"
```

The returned JSON is formatted for saving bytes on the network, not for readability.

However, the result looks like this:

```
{
  "rows": [
    {
      "col1": "AAA",
      "col2": 2,
      "col3": "BBB"
    },
    {
      "col1": "CCC",
      "col2": 3,
      "col3": "DDD"
    }
  ],
  "number_of_rows": 2,
  "columns": {
    "col1": "TEXT",
    "col2": "INT",
    "col3": "TEXT"
  }
}
```

The `rows` key contains the actual result set, each row is an object with a key the name of the column and with value the actual value of the row.

Then there is the `number_of_rows` key, an integer that describes how many rows are returned in this result set.

The `columns` field maps the name of the column to their type.

Overall returning JSON from zeeSQL is very simple, just add the `JSON` flag to your command and you are done.

## About zeeSQL

zeeSQL is a Redis Module that provides SQL capabilities to Redis. It allows the creation and management of several SQL databases, each one independent from the other. Moreover, zeeSQL provides out-of-the-box [secondary indexes](/secondary-indexes) capabilities, allowing fast and easy search by value in Redis.


# add-multiple-rows


# blog


# node


# Using RediSQL with Node.js

This tutorial will help you to get start to use RediSQL with Node.js

In this tutorial we will scrape the content of Hacker News using their [API documented here](https://github.com/HackerNews/API).

To communicate with Redis we will use the popular [node\_redis](https://github.com/NodeRedis/node_redis) library wich is the most suitable to communicate with Redis Modules and RediSQL. Other libraries can be used as well, but some more work will be necessary.

To follow this tutorial you will need a modern (> v4.0) instance of Redis running RediSQL. You can obtain RediSQL from [our shop](https://payhip.com/b/Ri4d) or from the [github releases](https://github.com/RedBeardLab/rediSQL/releases).

To load RediSQL is sufficient to pass it as argument to the redis-server: `./redis-server --loadmodule /path/to/redisql.so`

The whole code show in this example is reachable [here.](https://github.com/RedBeardLab/rediSQL/blob/master/doc/docs/blog/node/hn.js)

## Dependencies

The first step is always to load the dependencies, for this little script we will need just 3 dependencies:

1. `util` for formatting strings and promisify functions
2. `axios` for making HTTP requests
3. `redis` for actually talking with RediSQL

```javascript
const util = require('util');
const axios = require('axios');
const redis = require('redis');
```

## Connect to Redis and make it asycn/await ready.

RediSQL works on a normal Redis instance, hence we need to create a connection to our Redis.

In this tutorial we are going to use the async/await syntax that is not natively supported by `node_redis` but that it can easily added using `promisify`.

We first create a new connection to Redis, just one that is enough, and then we promisify the `send_command` method in order to give us the nice async/await syntax.

```javascript
const client = redis.createClient();
const send_command = util.promisify(client.send_command).bind(client);
```

## Polling HN API to get the newest items

The API of HN provides an endpoint to retrieve the ID of the latest element added to the website. The ID are auto-incremental, hence if the ID = `n` exists, it means that also the ID = `n - 1` exists as well.

However is necessary a little of cautions, indeed, is possible that some items are not yet available if they exists, in such case they return the string `null` instead of a JSON object. We take care of this with a simple loop and an `if`.

```javascript
const getMaxItem = async () => {
        let response = await axios.get("https://hacker-news.firebaseio.com/v0/maxitem.json");
        return response.data;
};

const getItem = async id => {
        let url = util.format("https://hacker-news.firebaseio.com/v0/item/%s.json", id);
        while (true) {
                let response = await axios.get(url);
                let data = response.data;
                if (data != "null") {
                        return data;
                }
        }
};
```

## The RediSQL structure

Now that we have removed all the boilerplate let's get down to business. We need to:

1. Create our database in RediSQL using the `REDISQL.CREATE_DB` command.
2. Create the table that will contains our data using the `REDISQL.EXEC` command.
3. Create the statement to actually insert the data into the database using the `REDISQL.CREATE_STATEMENT` command. 

We will store in our table the `id` of the item we are adding, the author of the item, when the item was posted on HN and finally the whole item as a JSON structure.

The third step is not strictly needed, but it provide defense against SQL-injections, is more performant, and it is just a cleaner way to do it. The alternative would be to just use `REDISQL.EXEC` every time we want to add a new row to the database.

All those steps are done in the `setUp` functions.

```javascript
const setUp = async () => {
        await send_command("REDISQL.CREATE_DB", ["HN"]).catch( err => console.log(err) );
        table = "CREATE TABLE IF NOT EXISTS hn(id integer primary key, author text, time int, item text);"
        await send_command("REDISQL.EXEC", ['HN', table]).catch( err => console.log(err) );
        stmt = "INSERT INTO hn VALUES(" + 
        "json_extract(json(?1),'$.id')," +
        "json_extract(json(?1),'$.by')," +
        "json_extract(json(?1),'$.time')," +
        "json(?1));";
        await send_command("REDISQL.CREATE_STATEMENT", ['HN', 'insert_item', stmt])
                .catch( err => console.log(err) );
};
```

Note how nicely the `JSON1` modules help us. It extracts for us the `id` of the item we want to add, the author of the item (the `by` field) and the time when the item was created. Without it we would be force to do that operation ourselves and pass more parameters to the statement.

## Insert data into the database

Finally let's make a simple function that will gets an item from HN and stores it into our database executing the statement we just created.

```javascript
const storeItem = async id => {
        let item = await getItem(id);
        console.log(item);
        await send_command("REDISQL.EXEC_STATEMENT", ['HN', 'insert_item', JSON.stringify(item)])
                .catch( err => console.log(err) );
}
```

## Running it

Now that we have all our piece in order we can just combine them together.

We will start by creating the database, table and statements.

Then a simple infinite loop will simply keep pooling the API. As soon as it detects new items available, it download them, and store those into the database.

We also include a small throttling mechanism to avoid hammering the API, which is not needed using the `sleep` function.

```javascript
const sleep = (waitTimeInMs) => new Promise(resolve => setTimeout(resolve, waitTimeInMs));

(async () => {
        setUp();
        let maxItem = await getMaxItem();
        while (true) {
                let newMaxItem = await getMaxItem();
                for (; maxItem < newMaxItem; maxItem += 1) {
                        storeItem(maxItem);
                }
                await sleep(5 * 1000);
        }
})()
```

## Concluding

In this small example (less than 60 lines) we show how simple and easy is to get started with RediSQL. We just set up the database and table once, along with the statement and that's it. A much quicker set up, especially for testing, than using classical databases as `Postgres` or `MySQL`.

Indeed is sufficient to set up the database following always the same steps:

1. Create the database with `REDISQL.CREATE_DB $db_name`
2. Create the schema inside your database `REDISQL.EXEC $db_name "CREATE TABLE ... etc"` and, if you want, the statements: `REDISSQL.CREATE_STATEMENT $db_name $statement_name "INSERT INTO ... etc"`
3. Start inserting data, with or without a statement: `REDISQL.EXEC_STATEMENT` or simply `REDISQL.EXEC`

Finally to query back the data is possible to use:

* queries `REDISQL.QUERY $db_name "SELECT * FROM ..."`,
* statements `REDISQL.CREATE_STATEMENT $db_name $query_name "SELECT * FROM ... WHERE foo = ?1"` and the `REDISQL.QUERY_STATEMENTS $db_name $query_name "paramenters"`
* simple exec `REDISQL.EXEC $db_name "SELECT * FROM ... etc"`

Feel free to explore our [references documentation](https://github.com/RedBeardLab/zeeSQL-doc/tree/a2b67b9d686c211c772ba26ddf89e17f8df9c4ab/docs/references/README.md) to understand better what capabilities RediSQL provides you.

The complete code of this example is [available here.](https://github.com/RedBeardLab/rediSQL/blob/master/doc/docs/blog/node/hn.js)

If you wish to see a similar tutorial for a different language, [open an issue on github.](https://github.com/RedBeardLab/rediSQL/issues/new)


# JSON on Redis via RediSQL, SQL steroids for Redis

### RediSQL, Redis on SQL steroids.

RediSQL is a redis module that embeds SQLite to provide full SQL capabilities to redis.

The fastest introduction to RediSQL is [our homepage](https://github.com/RedBeardLab/zeeSQL-doc/tree/70a762a22db224251819ed5ce9119aa9d87adf58/README.md)

tl;dr; We build a **JSON as a Service** in less than 500 lines of javascript + RediSQL, you can check out the [whole source file here.](https://github.com/RedBeardLab/JaaS/blob/master/index.js)

## JSON as a Service in 500 lines of code + RediSQL

While building web services is common to have the need to store some un-structured or semi-structured data (aka **JSON**) somewhere.

Unfortunately, it is not always so easy.

If you are using a SQL database, think about postgres, you need to add a column or even a table to your database, you need to decide how to encode the data and to be sure that your drivers work correctly with this new type.

If you are already using some kind of NoSQL database you may be a little luckier, but nevertheless, you should be sure that your database support all the operation you may need on JSON and still you need your team on-board to add a new collection/fields/column to your actual database.

## A faster solution

A faster solution could be to use RediSQL exploiting the [JSON1](https://www.sqlite.org/json1.html) extension.

SQLite provides several interesting extensions and one of our favourites is JSON1 that allow an efficient and fast manipulation of JSON data, all inside a full SQL engine.

We include this extension by default in RediSQL, so, if you are using RediSQL, you already have all the necessary function.

## Desiderata

In this example we assume that you are sharing your JSON data store between different part of the system, hence you need some form of hierarchy.

We will create JSON object that will have names and then each object will live inside a namespace, the couple `(namespace, name)` will be unique while name could be repeated inside different namespaces.

Then we will like to have a simple RediSQL interface like this one:

```
127.0.0.1:6379> REDISQL.EXEC_STATEMENT DB create_namespace noises
1) DONE
2) (integer) 1
127.0.0.1:6379> REDISQL.EXEC_STATEMENT DB upsert_object noises animals '{"cat": "meeow", "dog": "woof", "goldfish": "..."}'
1) DONE
2) (integer) 1
127.0.0.1:6379> REDISQL.EXEC_STATEMENT DB upsert_object noises humans '{"extrovert": "blablabla", "introverse": "bla", "programmer": "tap tap tap"}'
1) DONE
2) (integer) 1
127.0.0.1:6379> REDISQL.EXEC_STATEMENT DB extract noises humans $.extrovert
1) 1) "blablabla"
127.0.0.1:6379> REDISQL.EXEC_STATEMENT DB extract noises animals $.dog
1) 1) "woof"
127.0.0.1:6379>
```

Of course, we would also like to navigate complex JSONs and to add fields and values at will.

```
127.0.0.1:6379> REDISQL.EXEC_STATEMENT DB create_namespace foo
1) DONE
2) (integer) 1
127.0.0.1:6379> REDISQL.EXEC_STATEMENT DB upsert_object foo bar '{"a": {"quite": ["", {"complex": {"json": [1, 2, "object"]}}, ""]}}'
1) DONE
2) (integer) 1
127.0.0.1:6379> REDISQL.EXEC_STATEMENT DB extract foo bar $.a.quite[1].complex.json[2]
1) 1) "object"
127.0.0.1:6379> REDISQL.EXEC_STATEMENT DB set foo bar $.a.quite[1].complex.json[3] '["even", "more", "complex"]'
1) DONE
2) (integer) 1
127.0.0.1:6379> REDISQL.EXEC_STATEMENT DB extract foo bar $.a.quite[1].complex.json[3] 
1) 1) "[\"even\",\"more\",\"complex\"]"
127.0.0.1:6379> REDISQL.EXEC_STATEMENT DB extract foo bar $.a.quite[1].complex.json[3][0]
1) 1) "even"
127.0.0.1:6379> REDISQL.EXEC_STATEMENT DB extract foo bar $.a.quite[1].complex.json[3][1]
1) 1) "more"
127.0.0.1:6379> REDISQL.EXEC_STATEMENT DB extract foo bar $.a.quite[1].complex.json[3][2]
1) 1) "complex"
```

In this specific example we are only showing JSON, but keep in mind that the JSON field can be stored alongside the regular SQL fields as an extra field to hold any kind of unstructured data.

Also please note how these APIs are quite pleasant to work with, they seem almost native to redis and thanks to redis-module we have the possibility to simply create more powerful commands.

## Implementation

Now that we know what we are trying to achieve let's proceed to the implementation that you will see is quite simple.

Usually is a good idea to start from the data structure, and in our simple, but powerful, example, we need only a single table:

```sql
CREATE TABLE IF NOT EXISTS namespace ( 
    namespace TEXT PRIMARY KEY 
); 

CREATE TABLE IF NOT EXISTS json_data (
    namespace STRING,
    object_name STRING,
    data JSON,
    PRIMARY KEY (namespace, object_name),
    FOREIGN KEY(namespace) REFERENCES namespace(namespace) ON UPDATE CASCADE ON DELETE CASCADE
);
```

We could have done everything with just `json_data` and without `namespace`, but that would force to have the namespace that always contains at least a single object and everything would become more complex.

Now that we have our data structure we can proceed with the procedures that I have show you above:

```sql
-- create_namespace
INSERT INTO namespace VALUES(?1);

-- upsert_object
INSERT OR REPLACE 
        INTO json_data (namespace, object_name, data)
        VALUES (?1, ?2, json(?3))

-- get_object
SELECT data 
        FROM json_data 
        WHERE namespace = ?1 AND
        object_name = ?2;

-- extract
SELECT json_extract(data, ?3) 
        FROM json_data 
        WHERE namespace = ?1 AND 
        object_name = ?2;

-- set
UPDATE json_data 
        SET data = json_set(data, ?3, json(?4))
        WHERE namespace = ?1 AND 
        object_name = ?2 AND
        data != json_set(data, ?3, json(?4)); -- if no modification, don't change the object
```

In order to actually create those table here is an example on RediSQL that is more difficult to read but simpler to just copy and paste into the redis-cli.

```
127.0.0.1:6379> REDISQL.CREATE_DB DB
OK
127.0.0.1:6379> REDISQL.EXEC DB "CREATE TABLE IF NOT EXISTS namespace (namespace TEXT PRIMARY KEY);" 
1) DONE
2) (integer) 0
127.0.0.1:6379> REDISQL.EXEC DB "CREATE TABLE IF NOT EXISTS json_data (namespace STRING, object_name STRING, data JSON, PRIMARY KEY (namespace, object_name));"
1) DONE
2) (integer) 0
127.0.0.1:6379> REDISQL.CREATE_STATEMENT DB create_namespace "INSERT INTO namespace VALUES(?1);"
OK
127.0.0.1:6379> REDISQL.CREATE_STATEMENT DB upsert_object "INSERT OR REPLACE INTO json_data (namespace, object_name, data) VALUES (?1, ?2, json(?3))"
OK
127.0.0.1:6379> REDISQL.CREATE_STATEMENT DB get_object "SELECT data FROM json_data WHERE namespace = ?1 AND object_name = ?2;"
OK
127.0.0.1:6379> REDISQL.CREATE_STATEMENT DB extract "SELECT json_extract(data, ?3) FROM json_data WHERE namespace = ?1 AND object_name = ?2;"
OK
127.0.0.1:6379> REDISQL.CREATE_STATEMENT DB set "UPDATE json_data SET data = json_set(data, ?3, json(?4)) WHERE namespace = ?1 AND object_name = ?2 AND data != json_set(data, ?3, json(?4));"
OK
```

Of course, there are a lot more commands in JSON1 API to use and explore, so I will simply [leave you the reference](https://www.sqlite.org/json1.html).

I also prepare a simple node application which exposes this exact same interface via REST API, it is a single, \~500 LOC, file that you can find [here](https://github.com/RedBeardLab/JaaS/blob/master/index.js)

Feel free to use the node application as a blueprint for your next project.

## Recap

In this brief tutorial, we have shown how quickly and easily is possible to build a fairly complex JSON store using Redis and RediSQL.

Of course, similar structures, procedure and ideas can be used inside bigger structures and data table yielding powerful primitives for your application capable of sustain quite reasonable load without incurring in any extra operational cost.

## Question?

Of course, if you have any question on RediSQL either open a public issue or write me, siscia, a private email.

Cheers,

;)


# golang


# Using RediSQL with Go(lang)

This tutorial will help you to get start to use RediSQL with Go(lang).

In this tutorial we will scrape the content of Hacker News using their [API documented here](https://github.com/HackerNews/API).

To communicate with Redis we will use the popular [radix](https://github.com/mediocregopher/radix) library wich is the most suitable to communicate with Redis Modules and RediSQL. Other libraries can be used as well, but some more work will be necessary.

To follow this tutorial you will need a modern (> v4.0) instance of Redis running RediSQL. You can obtain RediSQL from [our shop](https://payhip.com/b/Ri4d) or from the [github releases](https://github.com/RedBeardLab/rediSQL/releases).

To load RediSQL is sufficient to pass it as argument to the redis-server: `./redis-server --loadmodule /path/to/redisql.so`

The whole code show in this example is reachable [here](https://github.com/RedBeardLab/rediSQL/blob/master/doc/docs/blog/golang/main.go)

## Creating a Redis Pooled Connection

Using `radix` is quite simple to use a pooled connection to Redis, indeed `radix` provide the `NewPool` function that creates a pool of connection to Redis. Then it is possible to use that pool as a client and have the library allocate a client for us.

```go
redis, err := radix.NewPool("tcp", "localhost:6379", 10)
if err != nil {
    fmt.Println(err)
        return
}
```

## Setting up the database

In order to work with RediSQL is necessary to do a small setup. The first step is always to create a database, then we create the tables inside the databases and finally the different statements if they are necessary.

It is always a good idea to use statements instead of building query by hand, but it is not mandatory. The use of statements eliminate the risk of SQL injection and it is more performant, since the query is parsed only once and not every time it get executed.

In our case we create a simple database that we will call `HN`.

```go
r.Do(radix.Cmd(nil, "REDISQL.CREATE_DB", "HN"))
```

Then we create a single table `hn` inside the `HN` database.

```go
table := "CREATE TABLE IF NOT EXISTS hn(id integer primary key, author text, time int, item text);"
r.Do(radix.Cmd(nil, "REDISQL.EXEC", "HN", table))
```

The table will contains the `id` of each item we are getting from HN, along with the author of the item, when the item was posted and finally the last field will contains the whole item as a `json` string.

The last step is to create a statement to easily insert the data inside our table.

```go
stmt := `INSERT INTO hn VALUES(
    json_extract(json(?1),'$.id'), 
    json_extract(json(?1),'$.by'), 
    json_extract(json(?1),'$.time'), 
    json(?1));`
r.Do(radix.Cmd(nil, "REDISQL.CREATE_STATEMENT", "HN", "insert_item", stmt))
```

The statement is a little complex. It exploit the [JSON1](https://www.sqlite.org/json1.html) sqlite extension to extract the necessary fields from a JSON string. In particular we extract the `id`, the `by` (author) and the `time` fields.

After that those fields are extracted from the JSON string we store all of them into the database along with the whole item.

## The loop

After the database is ready to accept data, we start to poll the HN API in order to fetch the newest item. Each item is then pushed into the `itemIds` channel that is later consumed.

```go
itemIds := make(chan int, 10)

go func() {
    oldMaxItemId := getMaxItem()
    for {
        newMaxItemId := getMaxItem()
        for ; oldMaxItemId < newMaxItemId; oldMaxItemId++ {
            itemIds <- oldMaxItemId
        }
        time.Sleep(5 * time.Second)
    }
}()
```

The API provide an endpoint that show the biggest element in HN at the moment, it is a simple auto-incremental id that we can fetch using the `getMaxItem()` function implemented as:

```go
func getMaxItem() int {
    resp, _ := http.Get("https://hacker-news.firebaseio.com/v0/maxitem.json")
    defer resp.Body.Close()
    body, _ := ioutil.ReadAll(resp.Body)
    n, _ := strconv.Atoi(string(body))
    return n
}
```

Finally the main loop iterate through the `itemIds` channel. For each new item we use again the HN API to get the content of the items and then we store it into RediSQL.

```go
for itemId := range itemIds {
    go func() {
        item := getItem(itemId)
        err := redis.Do(radix.Cmd(nil, "REDISQL.EXEC_STATEMENT", "HN", "insert_item", item))
        if err != nil {
            fmt.Println(err)
        }
    }()
}
```

The `getitem()` functions implement a trivial error recovery strategy. Indeed, after some trial and error, was clear that, sometimes, the element `n` is not ready yet even if the element `n+1` was published as `maxitem` and the API returns the simple string "null", if that is the case we simply repeat the call.

```go
func getItem(id int) string {
    for {
        url := fmt.Sprintf("https://hacker-news.firebaseio.com/v0/item/%d.json", id)
        resp, _ := http.Get(url)
        defer resp.Body.Close()
        body, _ := ioutil.ReadAll(resp.Body)
        result := string(body)
        if result != "null" {
            return result
        }
    }
}
```

## Concluding

This small example (less than 80 lines) show how simple is to quickly get value from RediSQL. Indeed is sufficient to set up the database following always the same steps:

1. Create the database with `REDISQL.CREATE_DB $db_name`
2. Create the schema inside your database `REDISQL.EXEC $db_name "CREATE TABLE ... etc"` and, if you want, the statements: `REDISSQL.CREATE_STATEMENT $db_name $statement_name "INSERT INTO ... etc"`
3. Start inserting data, with or without a statement: `REDISQL.EXEC_STATEMENT` or simply `REDISQL.EXEC`

Finally to query back the data is possible to use:

* queries `REDISQL.QUERY $db_name "SELECT * FROM ..."`,
* statements `REDISQL.CREATE_STATEMENT $db_name $query_name "SELECT * FROM ... WHERE foo = ?1"` and the `REDISQL.QUERY_STATEMENTS $db_name $query_name "paramenters"`
* simple exec `REDISQL.EXEC $db_name "SELECT * FROM ... etc"`

Feel free to explore our [references documentation](https://github.com/RedBeardLab/zeeSQL-doc/tree/a2b67b9d686c211c772ba26ddf89e17f8df9c4ab/docs/references/README.md) to understand better what capabilities RediSQL provides you.

The complete code of this example is [available here.](https://github.com/RedBeardLab/rediSQL/blob/master/doc/docs/blog/golang/main.go)

If you wish to see a similar tutorial for a different language, [open an issue on github.](https://github.com/RedBeardLab/rediSQL/issues/new)


# Doubling the performances of RediSQL, SQL steroids for Redis.

### RediSQL provides SQL steroids for Redis

RediSQL is a redis module that embeds SQLite to provide full SQL capabilities to redis.

The fastest introduction to RediSQL is [our homepage](https://github.com/RedBeardLab/zeeSQL-doc/tree/a2b67b9d686c211c772ba26ddf89e17f8df9c4ab/README.md)

tl;dr; We double the performance of RediSQL switching to a zero-copy use of the input arguments.

## Background

During the development of RediSQL we always kept in mind performance, but only up to a degree.

As Donal Knuth says "premature optimization is the root of all evil", but he also added, "Yet we should not pass up our opportunities in that critical 3%."

For us not pass up opportunities was more about using the correct data structure and algorithm when and where they make sense.

Overall the performances were quite good, on my old personal machine I could get 30k/inserts per second in a very simple table (few numeric value and small texts).

This number is really small compared to Redis that can `SET` keys up to 100k/set per second, but we are also doing more work and we have always considered this figures good enough, at least as long as nobody complains.

## The complains

*Fortunately* somebody [complains about the insertion rates](https://github.com/RedBeardLab/rediSQL/issues/29#issuecomment-382199622).

His requirements were a little different from the assumption that we had when testing the performance. He needed to insert a lot of data \~100kB in each row. And to do it fast.

First tests were not so good, showing just 2000 insertions per second. Definitely not good.

## Looking for the causes

Rust, the system language in which RediSQL is built, is peculiar in the management of memory.

The type system must be sure that every reference that you are using is valid and that you will never deference stuff out of your memory space.

Of course, this gives you a lot of safety but introduces quite a bit of complexity.

Fortunately, there are ways to manage this complexity: is possible to trade complexity for performances or, the other way around, performances for complexity.

High complexity with high performance means that you are using references (pointers) everywhere it is possible, so you never copy memory around if it is not necessary, you pass a pointer to that memory location and the type system assure you that it is safe to deference such pointer.

Low complexity with low performance implies to copy areas of memory multiple times so that the type system is always happy. Instead of using a pointer to a piece of memory I will just copy everything I need and pass it around functions.

Since the product was new, it made sense for us to get as low complexity as possible in such a way that it is possible to move faster understanding what our clients need and what we want to build.

Until now.

## The problem

The biggest problem we identified was that we were copying all the input parameter, so when a client was sending as a request like `REDISQL.EXEC_STATEMENT DB insert 1 2 3` we were copying 6 (insert) + 3 (1, 2, 3) = 9 bytes of memory.

(Redis string are a little particular since they don't have a null '\0' terminator and carry along their size.)

Nobody will complain about copying just \~10 bytes of memory, especially using slab allocator it is still some work but it is quite fast to do.

However, when we start to ingest 100k bytes this will really impact negatively the performance.

## Need for Speed

We could get away with just copying and freeing few bytes, especially using jemalloc, however as soon as the payload start increasing in size copying it every time started to impact negatively the performance of the module.

Was time to buy some performances paying our share price in complexity.

The process was a little complex because the data lived outside the Rust code, inside Redis itself. It was necessary to use `unsafe` code that in normal Rust you can avoid, but finally, we were able to see the Redis string as a slice (array) of chars and get a reference to it to pass around.

## Result

Before to release the code I obviously tested it. I used a c4.8xlarge from AWS.

That machine before the patch could ingest 4000 request / second each of \~100k bytes. After the patch, it was able to get 8000 requests/second.

Finally, the latency was pretty much the same with the 99.9 percentile at \~50 milliseconds.

Xin, the gentleman who brought the issues to our attention, went even further setting several parameters available in `redis-benchmark`, notably the number of pipelined requests, and he reached 17000 requests per second.

Being conservative we can claim that our works made redisql twice (from 4000 req/s to 8000 req/s) as fast at ingesting big amount of data.

It is definitely necessary more testing to see the impact on different workloads, smaller payload. Fortunately, our preliminary results seem quite good.

## Wrapping up

We showed how was possible to get a lot of performance out of Rust and RediSQL switching to a zero copy approach.

We also showed that our solution is very efficient, being able to ingest 800 MB / s of data without any sort of tuning.

## Looking for beta tester

We are looking also for tester for the PRO version of RediSQL, if you would like to test the PRO module without paying anything this is your occasion. You can either open an issues on github or write me (siscia) directly (email on github).


# zeeSQL now runs on SQLite 3.35

SQLite is the SQL workhorse and engine that powers zeeSQL.

SQLite was chosen since it is fast, reliable, simple to operate, widely available, widely known in the tech community, and very well maintained.

The latest release of SQLite (3.35) is being exciting. SQLite 3.35 introduces three really interesting features. [The release notes are available on the SQLite website.](https://sqlite.org/releaselog/3_35_0.html)

zeeSQL version 1.0.1 includes SQLite 3.35 with all the improvements listed above.

zeeSQL version 1.0.1 is available as docker container [(redbeardlab/zeesql:1.0.1)](https://hub.docker.com/layers/redbeardlab/zeesql/1.0.1/images/sha256-6a1aafcb6d1285355af0c75737aa4920a4365cb03f31b1ea3f1160135079e807?context=explore) and as [direct download (https://zeesql.com/releases/v1.0.1/zeesql.so)](https://zeesql.com/releases/v1.0.1/zeesql.so)

## SQLite with RETURNING

RETURNING is a novel clause for `INSERT`, `UPDATE` and, `DELETE` statements.

It appears after the main statement and specifies a set of columns, or expressions, to return after the insertion, update or delete of rows.

The RETURNING clauses returns a row, for each row that the main statement insert, update or deletes.

In the case of zeeSQL, the RETURNING clauses can be invaluable.

Suppose you are letting zeeSQL generate random IDs for your columns, without the RETURNING clauses, you have no way to know what ID has been generated. The only way would be to query back those rows. With the `RETURNING` clauses it is possible to insert those rows and having zeeSQL returns the random ID generated.

```
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "create table random_id(id STRING DEFAULT (hex(randomblob(16))), name STRING, score INT);"
1) 1) "DONE"
2) 1) (integer) 0
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "insert into random_id(name, score) values('john', 3),('mary', 4) RETURNING id"
1) 1) "RESULT"
2) 1) "id"
3) 1) "TEXT"
4) 1) "A3400CE9270D6F7AFAE3FA1F09DD5798"
5) 1) "0FC0FA5557C3F873A80F6949EF01AEA0"
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "select * from random_id;"
1) 1) "RESULT"
2) 1) "id"
   2) "name"
   3) "score"
3) 1) "TEXT"
   2) "TEXT"
   3) "INT"
4) 1) "A3400CE9270D6F7AFAE3FA1F09DD5798"
   2) "john"
   3) (integer) 3
5) 1) "0FC0FA5557C3F873A80F6949EF01AEA0"
   2) "mary"
   3) (integer) 4
```

The RETURNING clause it is maybe more useful for UPDATEs and DELETEs.

Suppose you are deleting different some old values based on a timestamp, maybe a cache, you might be interested in knowing which element you removed. Either you run two queries, the first to get all the rows you are going to delete and the second to delete them. Or you use the RETURNING clauses, to delete the rows and get them at the same time.

```
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "create table cache(key STRING, value STRING, TTL INT);"
1) 1) "DONE"
2) 1) (integer) 0
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "insert into cache(key, value, TTL) VALUES('a', 'aaa', 3),('b', 'bbb', 4),('c', 'ccc', 2), ('d', 'ddd', 0);"
1) 1) "DONE"
2) 1) (integer) 4
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "DELETE FROM cache WHERE TTL < 3 RETURNING key, value;"
1) 1) "RESULT"
2) 1) "key"
   2) "value"
3) 1) "TEXT"
   2) "TEXT"
4) 1) "c"
   2) "ccc"
5) 1) "d"
   2) "ddd"
```

These are just a tiny sample of what is possible with the RETURNING clauses, your application can benefit from it a lot.

Also, the RETURNING clauses, break a small hidden assumption in zeeSQL.

Now also statements that are not `READ ONLY` can return rows. This means that we have no constraints anymore of the statements and command that can use the [`INTO $stream`](/references#into-stream) clauses in zeeSQL.

More information about the RETURNING clauses is available on the [main SQLite page.](https://sqlite.org/lang_returning.html)

## SQLite with math functions

Another great addition to SQLite from the 3.35 release is about math functions.

The 3.35 release added a lot of math functions. The whole list is available on the main [SQLite documentation.](https://sqlite.org/lang_mathfunc.html)

All these functions are now available in zeeSQL.

zeeSQL is compiled with the `SQLITE_ENABLE_MATH_FUNCTIONS` compile-time option enable.

## SQLite with DROP column

Another great feature of this release is the DROP column.

Before this release, to drop a column from a table, it was necessary to copy the whole table in a temporary table without the column to delete. Then drop the original table.

With the 3.35 release, all this can be done with a simpler command which is an important quality of life improvement.

## Conclusion

The first minor release of zeeSQL updates the SQLite engine to one of the most feature-rich SQLite update ever.

The latest zeeSQL version (1.0.1) works with all the licenses already purchased. It is available as docker container [(redbeardlab/zeesql:1.0.1)](https://hub.docker.com/layers/redbeardlab/zeesql/1.0.1/images/sha256-6a1aafcb6d1285355af0c75737aa4920a4365cb03f31b1ea3f1160135079e807?context=explore) and as [direct download (https://zeesql.com/releases/v1.0.1/zeesql.so)](https://zeesql.com/releases/v1.0.1/zeesql.so)


# Query Redis on two attributes

Somebody [asked on StackOverflow how to query Redis for two attributes](https://stackoverflow.com/questions/66310700/what-is-the-right-data-structure-to-use-in-redis-to-query-based-on-two-attribute/66555255#66555255).

The use case is rather simple, the developer is storing a set of physical events, each of them has a start time and a finish time.

We would like to know all the events that are in progress at any given time.

The solution in raw Redis would involve creating two sorted sets, insert in one of those sets the events that start after the input time, insert in the other set all the events that end after the input time, and finally take the intersection of the two sets. The two sorted sets are not necessary anymore after we obtain our results, so they can be deleted.

This solution works, but it forces the developers to express the logic and all the steps necessary to obtain your results. Using procedural programming (expressing the steps necessary to obtain a result, like in this case) is more error-prone and complex than using declarative programming (expressing the result that you want, no how-to obtain it).

With [zeeSQL, SQL and search by value for Redis](https://zeesql.com) we can obtain the same result procedurally, without thinking or taking care of temporary sorted sets in Redis.

In this case, we are going to use a zeeSQL secondary index.

## The data format

Building on top of the StackOverflow question, we might expect that each event has a unique id, a start time, an end time, and maybe the number of participants.

We will express the two times as Unix timestamps.

```
127.0.0.1:6379> HMSET event:100 start_time 1615225944 end_time 1615657944 participants 30
OK
127.0.0.1:6379> HMSET event:101 start_time 1615225844 end_time 1615658944 participants 67
OK
127.0.0.1:6379> HMSET event:102 start_time 1615744344 end_time 1616003544 participants 52
OK
```

Now is around \~1615402492 so only `event:100` and `event:101` are ongoing now.

With zeeSQL the very first step would be to create a new database where to store the data, we can create a database and called it `EventsDB`.

```
127.0.0.1:6379> ZEESQL.CREATE_DB EventsDB
1) 1) "OK"
```

After we have created the database, we can add a zeeSQL secondary index to it. zeeSQL will automatically add all the keys to the newly created secondary index.

```
127.0.0.1:6379> ZEESQL.INDEX EventsDB NEW PREFIX event:* TABLE events SCHEMA start_time INT end_time INT participants INT
OK
```

With this command, we have created a new zeeSQL secondary index.

The secondary index is associated with the SQL table `events` and stores all the Redis Hashes that start with the prefix `event:`.

The index has 3 columns plus one.

The first column is the primary key and stores the Redis key of the hashes, in this case, it will store the string `event:100`, `event:101`, and `event:102`.

The other columns are the `start_time`, the `end_time`, and the `participants` column. Each one is an integer column and will store the respective fields of the Redis Hash.

As soon as the index is created, zeeSQL automatically populates it with the Redis Hash already in Redis.

We can immediately visualize the data.

```
127.0.0.1:6379> ZEESQL.EXEC EventsDB COMMAND "SELECT * FROM events"
1) 1) "RESULT"
2) 1) "key"
   2) "start_time"
   3) "end_time"
   4) "participants"
3) 1) "TEXT"
   2) "INT"
   3) "INT"
   4) "INT"
4) 1) "event:102"
   2) (integer) 1615744344
   3) (integer) 1616003544
   4) (integer) 52
5) 1) "event:101"
   2) (integer) 1615225844
   3) (integer) 1615658944
   4) (integer) 67
6) 1) "event:100"
   2) (integer) 1615225944
   3) (integer) 1615657944
   4) (integer) 30
```

Now we can try to answer the original question.

What are the events that are on-going now, or at any given time?

```
127.0.0.1:6379> ZEESQL.EXEC EventsDB COMMAND "SELECT key FROM events WHERE start_time < ?1 AND end_time > ?1" ARGS 1615402492
1) 1) "RESULT"
2) 1) "key"
3) 1) "TEXT"
4) 1) "event:101"
5) 1) "event:100"
```

Simple.

No other data structure to manage, just a simple SQL query to write.

## Use the standard SQLite time functions

You might not know what time it is now, in this case, you can use the standard SQLite functions.

```
127.0.0.1:6379> ZEESQL.EXEC EventsDB COMMAND "SELECT key FROM events WHERE start_time < strftime('%s', 'now') AND end_time > strftime('%s', 'now')"
1) 1) "RESULT"
2) 1) "key"
3) 1) "TEXT"
4) 1) "event:101"
5) 1) "event:100"
```

Or maybe you need to answer more complex queries, like the original one.

We might want to know all the events that are on-going at the moment and will finish in one week.

We don't have any of them in our Redis instance, but we can add one.

```
127.0.0.1:6379> HMSET event:103 id 103 start_time 1615225944 end_time 1616176492 participants 22
OK
127.0.0.1:6379> ZEESQL.EXEC EventsDB COMMAND "SELECT count(*) FROM events"
1) 1) "RESULT"
2) 1) "count(*)"
3) 1) "INT"
4) 1) (integer) 4
```

zeeSQL automatically adds the new event to the secondary index.

And now we can express our more complex query:

```
127.0.0.1:6379> ZEESQL.EXEC EventsDB COMMAND "SELECT key FROM events WHERE start_time < strftime('%s', 'now') AND end_time > strftime('%s', 'now', '+7 day')"
1) 1) "RESULT"
2) 1) "key"
3) 1) "TEXT"
4) 1) "event:103"
```

## Using all SQL

At this point, is useful to clarify that in zeeSQL you can use all the SQL provided by SQLite.

For instance, you may want to know how many people are registered for all the events that are ongoing right now.

```
127.0.0.1:6379> ZEESQL.EXEC EventsDB COMMAND "SELECT key, participants FROM events WHERE start_time < ?1 AND end_time > ?1" ARGS 1615402492
1) 1) "RESULT"
2) 1) "key"
   2) "participants"
3) 1) "TEXT"
   2) "INT"
4) 1) "event:101"
   2) (integer) 67
5) 1) "event:100"
   2) (integer) 30
6) 1) "event:103"
   2) (integer) 22
127.0.0.1:6379> ZEESQL.EXEC EventsDB COMMAND "SELECT SUM(participants) FROM events WHERE start_time < ?1 AND end_time > ?1" ARGS 1615402492
1) 1) "RESULT"
2) 1) "SUM(participants)"
3) 1) "INT"
4) 1) (integer) 119
```

## Getting data as JSON

The last interesting step that I would like to make you aware, it is how to return data as JSON.

Working with Redis nested arrays is not always easy, and sometimes JSON is more convenient to deserialize.

In zeeSQL, you only need to add the `JSON` flag, and your result will be returned as a JSON object.

```
root@96ad2f8bdfe5:/data# redis-cli ZEESQL.EXEC EventsDB COMMAND "SELECT key, participants FROM events WHERE start_time < ?1 AND end_time > ?1" JSON ARGS 1615402492 | jq
{
  "rows": [
    {
      "key": "event:101",
      "participants": 67
    },
    {
      "key": "event:100",
      "participants": 30
    },
    {
      "key": "event:103",
      "participants": 22
    }
  ],
  "number_of_rows": 3,
  "columns": {
    "key": "TEXT",
    "participants": "INT"
  }
}
```

Here we pipe the result in `jq` for easier visualization.

## Conclusions

zeeSQL solves a lot of problems when working with Redis.

In this particular case simplify the querying of data using a zeeSQL secondary index. This avoided the need to keeping separated indexes in Redis that can become cumbersome and error-prone.

This particular use case could have been solved without any Redis data structure, but simply using zeeSQL as the main datastore.

All the standard SQL commands are available like `SELECT`, `INSERT`, `UPDATE`, and `DELETE`.

zeeSQL by default works in-memory, like Redis, and it is blazing fast. Moreover, being completely integrated with Redis, zeeSQL supports AOF and RDB persistency.


# RediSQL for analytics

RediSQL is a module for Redis that embed a completely functional SQLite database.

RediSQL enables new paradigm where is possible to have several smaller decentralized databases instead of a single giant one.

In this blog post, we are going to explore how RediSQL can be used for storing analytics data.

Redis is always been used for storing fast data and so it is an extremely interesting software for analytics solution.

We are now going to describe the problem, explore some data structures that may help and finally sketch a possible solution using RediSQL.

At the end of the article, there is actual python code that you can run.

## Problem

Suppose you are interested in following the user around your website, and you will like to know what buttons they click, what events they trigger, what form the focus on and so on and so forth.

All these events are quite simple to catch using javascript and client-side code, but then you need to store them in your database to analyze them further and extract new information and value for your business.

However, you would prefer to avoid to put too much pressure on your main database that is already busy storing all the essential information for the business.

## Data Structure

One of the advantages of using SQL is the possibility to use and declare the shape of your data.

For this specific problem, our data are quite simple. We want to store a user identifier (it may be its alias, nickname, ID in the main database or even something else), the IP address of the user, the timestamp when the event was triggered and finally the event itself.

We are going to represent the identifier, the IP address and the timestamp as strings. Yes, unfortunately, SQLite does not provide a time type, to use a string is quite a reasonable choice, another one could be to use integers and to save the timestamp as Unix epoch of the event.

### Events

Representing the events may be a little complex and it really depends on your use case. Suppose you are just listening to specific events like "Sales", "Register", "Login" or "Submit form" you could simply store them as strings.

However you can be a little more sophisticated as well, and associate to every Sales some other data like "amount", "shipping cost" or "total elements sold" or again improve the "Submit form" with information about the web page, like the URL of the page or if it was the A or the B version of your A/B test. And so on and so forth.

### JSON or Tables

If your events are quite static and you already know what you are going to store the best approach is to use tables.

An idea could be to use this representation for the table `Events`:

```sql
| event_id | user_id | ip_address | timestamp |
|----------|---------|------------|-----------|
```

And then different tables for each type of events, like:

`Sales`:

```sql
| event_id | amount  | shipping_cost | total_elements_sold |
|----------|---------|---------------|---------------------|
```

`Submits`:

```sql
| event_id | url_page | A/B_version |
|----------|----------|-------------|
```

Where `event_id` is a Primary key to the table `Events` and a Foreign key on the table `Sales` and `Submits`.

This approach works really well, the shape of your data will be always known and it will be fast, however, you actually need to know what you are saving in your DB and change the structure of the table is quite complex.

A different approach will be to store directly JSON in your table.

The new schema will be only a single table, `Events`:

```sql
| event_id | user_id | ip_address | timestamp | data |
|----------|---------|------------|-----------|------|
```

The column data will be of type `text` and it will store anything you want if encoded in JSON.

Of course, it is also possible to run any kind of computation on the JSON data including filters and selection.

Using JSON you gain a lot of flexibility but you are not sure anymore of the shape of your data and if you are not careful it may cause some headaches.

## Solution Sketch

In this section we are going to get through a possible implementation of the above solution, we are going to use the JSON variant since I believe that not everybody knew that SQLite could handle JSON so well.

I am assuming you already know how to get a Redis instance and how to load a module into it, if not make sure to check out [the readme of the project](https://github.com/RedBeardLab/rediSQL#getting-start)

We are going to automate as much as possible in this tutorial, in this way your analytic script will just run.

The very first thing to do is to get a working connection to your Redis instance, any Redis binding should make this process quite simple, here an example in python.

```python
import redis
r = redis.StrictRedis(host='localhost', port=6379, db=0)
```

Now that you have established a connection the next step is to create a RediSQL database, RediSQL can manage multiple, completely independent databases, each associated with a Redis key, for this simple example we are going to use only one database that, with a lot of fantasy, `DB`.

```python
ok = r.execute_command("REDISQL.CREATE_DB", "DB")
assert ok == "OK"
```

Now that we have created our database we can go ahead and create the table that will contain our data. We are going to create the table if and only if it does not exists yet.

```python
done = r.execute_command("REDISQL.EXEC", "DB", 
                         """CREATE TABLE
                            IF NOT EXISTS 
                            Events(
                                event_id INTEGER PRIMARY KEY,
                                user_id STRING,
                                ip_address STRING,
                                timestamp STRING,
                                data JSON
                            );""")
assert done == ["DONE", 0]
```

Setting the type of `event_id` as `INTEGER PRIMARY KEY` is synonymous with `ROWID` which is an autoincrement fields that do not need to be set during insert.

At this point, the only thing left to do is to listen for events in your code and write them into the database.

The simplest, and insecure, way to write the data is to use the `EXEC` function like so:

```python
# import datetime
user_id = "user_1234"
ip_address = "a.simple.ip.address"
now = datetime.datetime.now()
data = {"type" : "sales", "total": 1999, "shipping_address" : "..."}
statement = """INSERT INTO Events (user_id, ip_address, timestamp, data) 
               VALUES(\"{}\", \"{}\", \"{}\", \"{}\")""" \
               .format(user_id, ip_address, now, data)
done = r.execute_command("REDISQL.EXEC", "DB", statement)
assert done == ["DONE", 1]
```

As you may have guessed already the return value of `REDISQL.EXEC` is a list of two elements, the string `DONE` and the integer representing the number of rows modified (inserted, deleted or updated).

However, this way of inserting data into the database is not optimal, especially if the same operation will be performed several times. And also because it is vulnerable to SQL injections attacks.

The better and safer way to do this kind of operation is to define **statements**.

```python
ok = r.execute_command("REDISQL.CREATE_STATEMENT", "DB", "insert_event",
                       """INSERT INTO Events 
                          (user_id, ip_address, timestamp, data) 
                          VALUES(?1, ?2, ?3, ?4)""")
assert ok == "OK"
```

Once a statement is defined you can execute it using the following commands.

```python
# import datetime
user_id = "user_1234"
ip_address = "a.simple.ip.address"
now = datetime.datetime.now()
data = {"type" : "sales", "total": 1999, "shipping_address" : "..."}
done = r.execute_command("REDISQL.EXEC_STATEMENT", "DB", "insert_event", user_id, ip_address, now, data)
assert done == ["DONE", 1]
```

The use of statements brings some benefits.

* It reduces code deduplication in your code base
* It puts a name on a particular procedure, decoupling the implementation and the goal
* It allows different microservices to invoke the always the exact same procedure
* It is faster to execute

### Use the JSON1 SQLite module

Now that we have covered how to execute SQL against RediSQL let me quickly introduce you to the JSON1 syntax provide by SQLite.

The ones that follow are plain SQL statements that you can execute `REDISQL.EXEC` against the database or that you can embed into a statement.

The most interesting function provide is `json_extract`.

```sql
SELECT user_id, json_extract(data, '$.total')
FROM Events
WHERE json_extract(data, '$.type') = "sales";
```

This query will look inside the field `type` of the JSON stored into the columns `data` if this fields contains the string "sales" it will return the user who bought something and total of the sale.

`json_extract` works also on array using a simple syntax: `$.array[2]` (eg. extract the third element of the array)

## Move the data

Running the above script will be extremely fast, I am talking about 10ks inserts per second fast.

However, it is so fast for a variety of reason but maybe the most important is that it keeps all the data in memory and does not write them on disk.

This can be just fine for some application (think about storing data that become useless in few days time) or it can be a big issue for some other use case, luckily there is a very simple solution.

The simplest thing to do when you decide to dump the data in your persistent storage is just to query them all and push them, in batch, to your persistent system. Moving the data in all together will allow having an extremely high throughput and it will take a fraction of the time than if you moved just a row at the time.

A quite simple practice is to simply dump all the content of your database in a CSV file and then let your RDBMS load it.

This operation is quite simple and it can be done like so.

```python
# get the data
values = r.execute_command("REDISQL.EXEC", "DB", "SELECT * FROM Events;")
# iterate throught the list writing on file
with open('csv_file', 'w') as csv_file:
    # write the csv header
    csv_file.write("event_id,user_id,ip_address,timestamp,data\n")
    for row in values:
        # create a single string with all the fields separated by a comma
        elements = ",".join(row) + "\n"
        # write the result on the csv_file
        csv_file.write(elements)
```

Now you can use tools like PostgreSQL COPY to load all the data into your database.

This solution is not perfect and in a distributed setting with several concurrent workers it may result in some data duplication, however, we are getting ahead of ourselves and this topic will go far ahead of the scope of this post.

## Recap

In this blog post, we explored how to write a quite sophisticated analytics infrastructure using nothing more than RediSQL.

Adding this tool to your existing infrastructure should be quite simple and painless while it provides a simple way to do powerful things in a fast and reliable way.

Is worth to remember that RediSQL already provide RDB persistency so you already have some interesting level of safeness embed into this architecture.


# Copying RediSQL databases

One undervalued capability of RediSQL redise in the \[REDISQL.COPY]\[copy] command.

As you know, RediSQL databases comes in two different shapes, memory-backends database and file-backend databases.

The memory-backend databases operate only in RAM, all the database is stored in memory and (excluding RDB and AOF files) they disapear when the redis instance is shutted down. The file-backend databases store all their content in a standard file, so when the redis instance is shuted down, the file will still be in the filesystem. The file-backend databases can store an huge amount of data since all the data is stored on disk and not on memory, the trade-off is clearly performance.

RediSQL databases support both RDB and AOF files, so the data stored maintain all the persistency guarantees of Redis.

File-backend databases provide an easy way to get data in and out of RediSQL.

## Creating a file-backend database

The command `REDISQL.CREATE_DB $DB_NAME [$path]` take the optional argument $path. If a path is provided, RediSQL try to open an SQLite database from the specified path. If the file does not exists, it creates a new database. If the file is a SQLite database, the database, with all its data, is loaded into RediSQL. If the file is not a SQLite database, a simple error is raised.

This behaviour provide a simple yet very effective way to load data into RediSQL. It is possible to create an SQLite database that already contains all the necessary data, then, we pass this database to the `REDISQL.CREATE_DB` command as path argument. This will create a file-backend database with already all your data loaded.

The fastest and most efficient way to load tons of files inside RediSQL.

The trade-off is clear, the database just created will have the performance of a file-backend database.

## Copying databases

If is it necessary to have faster performance, than a file-backend database, the solution is to copy the database into a memory-backend database. The copy of a database is rather efficient, since it works in memory batches, copying pages of memory at the time and it is not a naive row by row copy.

The `REDISQL.COPY $source $destination` takes as argument a `$source` database and a `$destination` database, creating a copy of the `$source` database into the `$destination` database. The `$destination` database is overwritten and its content is lost.

Overall, the procedure to load a database into RediSQL would be similar to:

```
# create the SQLite database in /home/ubuntu/input.sqlite
redis> REDISQL.CREATE_DB input /home/ubuntu/input.sqlite
redis> REDISQL.CREATE_DB fast_production
redis> REDISQL.COPY input fast_production
```

We first create two database, `input` and `fast_production`. The `input` database is a file-backend database using our original SQLite database while the `fast_production` is a memory-backend database. Then we copy the content of `input` into `fast_production`.

Now we can query the `fast_production` database that will have all the data in the original `/home/ubuntu/input.sqlite` database.

## Going the other way

While this procedure is convenient to get data inside RediSQL, it can be used also to get the data out of it, for backup reason or for shipping the data to some analytic pipeline.

Again, the procedure is rather simple, we create two databases: `production` and `copy`. `production` is a memory-backend database used during normal operatios, `copy` is a file-backend database used for backup. Then we copy the database from `production` into `copy`.

```
# create the SQLite database in /home/ubuntu/input.sqlite
redis> REDISQL.CREATE_DB production
redis> REDISQL.CREATE_DB copy /home/ubuntu/copy.sqlite
redis> REDISQL.COPY production copy
```

The last step can be to delete the `copy` database, the file will stay intact in the filesystem, but the database won't use any more resorces from Redis. The database can be deleted using the standard `DEL` command, `DEL copy`.

## Recap

In this article we explore a simple way to use the `REDISQL.COPY` command.

The `REDISQL.COPY` command has also different uses, it can be used to create a stale copy of a database to distribute some traffic. Or it can be used to create a complex structure in a "template" database and quickly replicate the template for different users.

\[copy]:


# Release 0.9.0 of RediSQL, SQL steroids for Redis

### RediSQL, Redis on SQL steroids.

RediSQL is a Redis module that provides full SQL capabilities to Redis, it is the simplest and fastest way to get an SQL database up and running, without incurring in difficult operational issues and it can scale quite well with your business.

The fastest introduction to RediSQL is [our homepage](https://redisql.com)

**tl;dr** This release introduce one simple new command `REDISQL.STATISTICS`. The new command returns the amount of time each command is been called and how many of those calls were successfully and how many returned errors. The command does not introduce noticeable slowdowns.

This release is the smallest, however it provide the foundation for the next major releases.

## Motivation

The infrastucture behind the `REDISQL.STATISTICS` commands is needed for the next major release of RediSQL.

Moreover it provides an useful tool for the administrator of the instance allowing them to spot inefficiencies.

## How to use

Just invoke the command without any arguments to get an array of all the counters, extra arguments are ignored for the moment.

After using RediSQL for few commands, the output of `REDISQL.STATISTICS` could be the following.

```
127.0.0.1:6379> REDISQL.STATISTICS
 1) 1) "CREATE_DB"
    2) (integer) 1
 2) 1) "CREATE_DB OK"
    2) (integer) 1
 3) 1) "CREATE_DB ERR"
    2) (integer) 0
 4) 1) "EXEC"
    2) (integer) 4
 5) 1) "EXEC OK"
    2) (integer) 4
 6) 1) "EXEC ERR"
    2) (integer) 0
 7) 1) "QUERY"
    2) (integer) 0
 8) 1) "QUERY OK"
    2) (integer) 0
 9) 1) "QUERY ERR"
    2) (integer) 0
10) 1) "QUERY.INTO"
    2) (integer) 0
11) 1) "QUERY.INTO OK"
    2) (integer) 0
12) 1) "QUERY.INTO ERR"
    2) (integer) 0
13) 1) "CREATE_STATEMENT"
    2) (integer) 3
14) 1) "CREATE_STATEMENT OK"
    2) (integer) 1
15) 1) "CREATE_STATEMENT ERR"
    2) (integer) 2
16) 1) "EXEC_STATEMENT"
    2) (integer) 2
17) 1) "EXEC_STATEMENT OK"
    2) (integer) 2
18) 1) "EXEC_STATEMENT ERR"
    2) (integer) 0
19) 1) "UPDATE_STATEMENT"
    2) (integer) 2
20) 1) "UPDATE_STATEMENT OK"
    2) (integer) 1
21) 1) "UPDATE_STATEMENT ERR"
    2) (integer) 1
22) 1) "DELETE_STATEMENT"
    2) (integer) 0
23) 1) "DELETE_STATEMENT OK"
    2) (integer) 0
24) 1) "DELETE_STATEMENT ERR"
    2) (integer) 0
25) 1) "QUERY_STATEMENT"
    2) (integer) 0
26) 1) "QUERY_STATEMENT OK"
    2) (integer) 0
27) 1) "QUERY_STATEMENT ERR"
    2) (integer) 0
28) 1) "QUERY_STATEMENT.INTO"
    2) (integer) 0
29) 1) "QUERY_STATEMENT.INTO OK"
    2) (integer) 0
30) 1) "QUERY_STATEMENT.INTO ERR"
    2) (integer) 0
31) 1) "COPY"
    2) (integer) 0
32) 1) "COPY OK"
    2) (integer) 0
33) 1) "COPY ERR"
    2) (integer) 0
```

The `CERATE_DB` line means that the `REDISQL.CREATE_DB` command is been invoked once. The `CREATE_DB OK` lines means that the command succeeded once.

Let's analyze the `CREATE_STATEMENT` lines as well.

```
13) 1) "CREATE_STATEMENT"
    2) (integer) 3
```

This line says that the command is been invoked 3 times.

```
14) 1) "CREATE_STATEMENT OK"
    2) (integer) 1
```

The next line specify that the commands completed successfully 1 time out of 3.

```
15) 1) "CREATE_STATEMENT ERR"
    2) (integer) 2
```

The last line confirms that out of the 3 times we invoked the command, 2 of them failed for some reason.

Of course the math need to check out and the sum of successful and erroneous runs should match with the number of invocation.

## Implementation

This command is implemented with atomic counters, they are fast and provide a simple and easy way to manage concurrent access.

We careful tested the performance to make sure that the slowdown introduces by the counter is not noticeable.


# Release 0.8.0 of RediSQL, SQL steroids for Redis

### RediSQL, Redis on SQL steroids.

RediSQL is a Redis module that provides full SQL capabilities to Redis, it is the simplest and fastest way to get an SQL database up and running, without incurring in difficult operational issues and it can scale quite well with your business.

The fastest introduction to RediSQL is [our homepage](https://redisql.com)

**tl;dr** This release introduce two new commands [`REDISQL.QUERY.INTO[.NOW]`](/references#redisqlqueryinto) and [`REDISQL.QUERY_STATEMENT.INTO[.NOW]`](/references#redisqlquery_statementinto). The new commands behave similary to `REDISQL.QUERY` and `REDISQL.QUERY_STATEMENT` but they [`XADD`](https://redis.io/commands/xadd) the results to a [Redis Stream](https://redis.io/topics/streams-intro) passed as first argument.

## Motivation

Being able to write the result of a query into a stream opens several possibilities. First off all allow to easily cache the result of expensive queries. Then, it separate the creation of a result with its consuption which is a very important step forward especially for big results.

Indeed, while the computation of a query is not done by the main redis thread but it is off-load to another thread to allow redis to keep serving the client. Returning the result must be done in the main Redis thread. Hence a long result can take a lot of time to be returned to the client and in that time Redis cannot serve other requests. Writing the result into a stream make it much more efficient use of the main Redis thread time.

Moreover, on the other side of the network, a small consumer might not expect a big result and could be overlwhelmed by the size.

In standard databases this problem is usually solved using cursors, however Redis itself does not provide this facility. Redis provide lists, but they are simply flat list and can store only strings, it would be complex to create the cursors on top of them.

The streams however are a better fit. While also them can store only strings, they store them into entries, which are small key-values objects. Each entry represent a row of our result set. Where we encode the column name and type into the key, and we use the value field to store the actual value of the column.

An example will be easier to follow.

## How to use

An example of `REDISQL.QUERY.INTO` is the following:

```
REDISQL.QUERY.INTO result_stream DB "SELECT foo, bar FROM baz WHERE n > 42"
```

The command will execute the query `SELECT foo, bar FROM baz WHERE n > 42` agains the database `DB` and it will `XADD` each row of the result to the stream `result_stream`.

If the result is empty, the command will return `["DONE", 0]` to the Redis client.

If the result is not empty, the command will return, to the Redis client, the name of the stream used (hence `result_stream` in this example) along with the first ID added and the last ID added and the size of the cursor (the number of entries added to the stream.)

In the following example we start by creating a database, then we create a new table `foo` in the database, and we store 4 rows into the table.

Then we use the new `REDISQL.QUERY.NOW` command to store the result of the query `SELECT * FROM foo` agains the database `DB` in the stream `{DB}:all_foo`.

```
127.0.0.1:6379> REDISQL.CREATE_DB DB
OK
127.0.0.1:6379> REDISQL.EXEC DB "CREATE TABLE foo(a int, b int);"
1) DONE
2) (integer) 0
127.0.0.1:6379> REDISQL.EXEC DB "INSERT INTO foo(a) VALUES(1)"
1) DONE
2) (integer) 1
127.0.0.1:6379> REDISQL.EXEC DB "INSERT INTO foo VALUES(3, 4)"
1) DONE
2) (integer) 1
127.0.0.1:6379> REDISQL.EXEC DB "INSERT INTO foo VALUES(5, 6)"
1) DONE
2) (integer) 1
127.0.0.1:6379> REDISQL.EXEC DB "INSERT INTO foo VALUES(10, 19)"
1) DONE
2) (integer) 1
127.0.0.1:6379> REDISQL.QUERY.INTO {DB}:all_foo DB "SELECT * FROM foo"
1) 1) "{DB}:all_foo"
   2) "1549811093979-0"
   3) "1549811093979-3"
   4) (integer) 4
127.0.0.1:6379> XRANGE {DB}:all_foo - +
1) 1) "1549811093979-0"
   2) 1) "int:a"
      2) "1"
      3) "null:b"
      4) "(null)"
2) 1) "1549811093979-1"
   2) 1) "int:a"
      2) "3"
      3) "int:b"
      4) "4"
3) 1) "1549811093979-2"
   2) 1) "int:a"
      2) "5"
      3) "int:b"
      4) "6"
4) 1) "1549811093979-3"
   2) 1) "int:a"
      2) "10"
      3) "int:b"
      4) "19"
```

The first thing to notice is that the stream entity contains both the type of the column and it's name as well. The format is `$column_type:$column_name`. This is necessary because stream support only strings.

In the example above the string `int:a` means that, for this row, the column `a` is of type `int`. Usually the type of a column is constant, however, it may be null, in that case it would be something like: `null:b`.

Another interesting thing to notice is the name of the stream used which can look peculiar. Indeed it is the same name of the database `DB`, between curly braces `{DB}` and then a useful identifier `{DB}:all_foo`. The name of the stream can be any name, so is not important to use this schema, however this schema is useful if you use redis cluster.

Indeed, both keys, the target stream `{DB}:all_foo` and the source database `DB`, need to be on the same redis cluster node. Since redis use the part of the key between curly bracket to decide in which node a key should resize, this schema allow us to make sure that this invariant is always respected.

Moreover this schema is also quite nice, allowing with a glance to know what stream refer to what database. But again, it is not necessary at all.


# Release 0.7.0 of RediSQL, SQL steroids for Redis

#### RediSQL, Redis on SQL steroids.

RediSQL is a Redis module that provides full SQL capabilities to Redis, it is the simplest and fastest way to get an SQL database up and running, without incurring in difficult operational issues and it can scale quite well with your business.

The fastest introduction to RediSQL is [our homepage](https://redisql.com)

**tl;dr** This release introduce a new commands [`REDISQL.COPY`](/references#redisqlcopy) that copy the content from a source database into a destination database.

## Motivations

Since from the very first release and the very first user, we have been asked a lot about the possibility to copy SQLite database into disk or into memory.

It is definitely an useful feature, suppose to already have the database and you simply want to make it available to some of your services.

We wait a bit before to incorporate on RediSQL such capabilities, mostly because we weren’t sure about the API to offer.

Finally we decide to pull the trigger and we implemented a new command [`REDISQL.COPY`](/references#redisqlcopy).

The [`REDISQL.COPY`](/references#redisqlcopy) command takes two parameters as input, a `source` database and a `destination` database and it overwrite the content of the `source` database into the `destination` database.

It is important to understand that the content of the destination database is completely lost after a `REDISQL.COPY`

The [`REDISQL.COPY`](/references#redisqlcopy) command takes as input two databases, both of them must be created using the `REDISQL.CREATE_DB` command. This API allows several use cases that are quite interesting.

1. Make a backup/copy of your database
2. Split load to multiple threads
3. Move a database from a in-memory database to a disk-based database
4. Move a database from  a disk-based database to a in-memory database

### Few Examples

I will show briefly some examples of those use cases.

Making a backup/copy

Backups are already provided by the internal of Redis itself, all the database will be copied into the RDB files. However you may be interested in having just a copy of your database, so that you can archive it in a different way, or just explore it offline.

Suppose you have your database `DB` running with some table and some data:

> REDISQL.CREATE\_DB DB OK REDISQL.EXEC DB "CREATE TABLE foo( ... )" DONE 0L REDISQL.EXEC DB "INSERT INTO foo VALUES ( ... )" DONE 1L

Now you will like to transfer that same database into a file, so that you can archive it or analyze it.

The first step would be to create another database backed by a file.

> REDISQL.CREATE\_DB BACKUP "/home/foo/backup.sqlite" OK

In this way we have created a new, empty database that is backed by a file.

You will see the small file `home/foo/backup.sqlite`

At this point you just need to make a copy of it.

> REDISQL.COPY DB BACKUP OK

Now the file `/home/foo/backup.sqlite` will contains all the data that were originally on the `DB` database.

### Load a database

Now, suppose that the data you want to serve via RediSQL are already inside a SQLite database, or suppose that you are recovering from a previous backup. However you would like to have the database in memory, since we know the load will be quite high.

Assuming your database is stored into `/home/foo/recover.sqlite` we start by loading it, and then move it into an in-memory database, and finally we can also delete the database we used for recovering.

> REDISQL.CREATE\_DB TO\_RECOVER "/home/foo/recover.sqlite" OK REDISQL.CREATE\_DB DB OK REDISQL.COPY TO\_RECOVER DB OK DEL TO\_RECOVER OK

At this point we have only one database `DB` that is an in-memory one and we have used the `TO_RECOVER` database to load the recovering file.

### Spread load

Another quite interesting use case is about load spreading.

Suppose to have a read-only database `DB1` that makes quite complex and long queries, if that start to be a problem we could spread the load into two identical databases.

> REDISQL.CREATE\_DB DB2 OK REDISQL.COPY DB1 DB2 OK

Now we have the same dataset in two different database, each one of them with its own thread of execution. This will allow us to round robin between the two databases and achieve smaller latencies.

## End

With this post we showed the newest features of RediSQL.

The product start to be quite stable, more performance test will come in the next release but we don’t plan to touch the API.

If we don’t change the API the next release will be the v1.0.0

As always you can find all the public releases on the [github page](https://github.com/RedBeardLab/rediSQL/releases/tag/v0.5.0), you can openly access the same public release on the [open page of our shop](https://plasso.com/s/epp4GbsJdp-redisql/) or you can buy the complete PRO package [signing up in the shop](https://plasso.com/s/epp4GbsJdp-redisql/signup/).

Remember that signing up for the PRO product also provide you free support from us, the creator of the project, so that we can point you to the right direction and suggest the best use cases for our product.


# JSON on Redis via RediSQL, SQL steroids for Redis


# Release 0.6.0 of RediSQL, SQL steroids for Redis

### RediSQL, Redis on SQL steroids.

RediSQL is a Redis module that provides full SQL capabilities to Redis, it is the simplest and fastest way to get an SQL database up and running, without incurring in difficult operational issues and it can scale quite well with your business.

The fastest introduction to RediSQL is [our homepage](https://redisql.com)

**tl;dr** This release does not introduce new commands, but it provides a SQLite virtual table implementation that allows making SQL queries against Redis Hashes. The release is important because set the foundation to write more complex commands or SQLite functions. Possible ideas could be SQLite functions that append to a list or to a stream, these functions could be used inside triggers to generate an event log of all the operation that happened to a particular table.

## Virtual Table

Inside RediSQL is now possible to use the virtual table: [REDISQL\_TABLES\_BRUTE\_HASH](/references#redisql_tables_brute_hash).

This virtual table allows to only query Redis hashes that follow a common structure.

The understood structure is:

```
HSET $tableName:$id $col1 $val1 $col2 $val2 ... $colN $valN
```

Where the `$col`s are constant in the hashes and, of course, the `$val`s change from row to row.

In order to create a [REDISQL\_TABLES\_BRUTE\_HASH](/references#redisql_tables_brute_hash) the syntax is the following:

```
CREATE VIRTUAL TABLE funny_cats USING REDISQL_TABLES_BRUTE_HASH($tableName, $col1, $col2, ..., $colN);
```

Please note that the first parameter of the virtual table is not, as we could expect, the first column of the table, but is the name of hashes that we want to use as table, of course without specifying any `$id`.

Also note that is pointless to provide a type to the columns since Redis does store only strings inside the hashes, hence you will get only strings from the virtual table as well.

What you can do to get numbers, integer or floats, is to exploit the [`CAST`](https://www.sqlite.org/lang_expr.html#castexpr) capabilities of SQLite.

You can find examples of this feature in [the documentation.](/references#redisql_tables_brute_hash)

Let me make clear that this virtual table does **not** implements updates, inserts or deletes, at the moment you can only query this type of virtual tables.

The implementation of update and inserts and deletes should not pose significant challenges.

## Importance of this release

This release is extremely important for architectural reasons inside the module itself.

In order to implement the above virtual table was necessary to keep a pointer to an internal structure of Redis that actually allow calling any Redis command from inside a module.

Including this pointer into the RediSQL structures make possible to call arbitrary Redis commands.

This opens the gate to quite interesting features, as an example, imagine to be able to call `LPUSH` or `XADD` inside a trigger.

This will allow to log every operation you are doing against your dataset. You could replay them later in a different instance of RediSQL or maybe also against a different database.

You could write all you operation very fast in memory using RediSQL and when you have enough of them write them to disk against PostgreSQL, MySQL or any other database.

## End

As always you can find all the public releases on the [github page](https://github.com/RedBeardLab/rediSQL/releases/tag/v0.5.0), you can openly access the same public release on the [open page of our shop](https://plasso.com/s/epp4GbsJdp-redisql/) or you can buy the complete PRO package [signing up in the shop](https://plasso.com/s/epp4GbsJdp-redisql/signup/).

Remember that signing up for the PRO product also provide you free support from us, the creator of the project, so that we can point you to the right direction and suggest the best use cases for our product.


# python


# using-redisql-with-python

## Using RediSQL with Python

This tutorial will help you to get start to use RediSQL with Python3.

In this tutorial we will scrape the content of Hacker News using their [API documented here](https://github.com/HackerNews/API).

We will use async python with [`asyncio`](https://docs.python.org/3/library/asyncio.html) to manage the event loop, [`aiohttp`](https://github.com/aio-libs/aiohttp/) to retrieve data from a public API and [`aioredis`](https://github.com/aio-libs/aioredis) to communicate with Redis.

To follow this tutorial you will need a modern (> v4.0) instance of Redis running RediSQL. You can obtain RediSQL from [our shop](https://payhip.com/b/Ri4d) or from the [github releases](https://github.com/RedBeardLab/rediSQL/releases).

To load RediSQL is sufficient to pass it as argument to the redis-server: `./redis-server --loadmodule /path/to/redisql.so`

The whole code show in this example is reachable [here](https://github.com/RedBeardLab/rediSQL/blob/master/doc/docs/blog/python/src/simple.py) while we also created a [more sophisticate example](https://github.com/RedBeardLab/rediSQL/blob/master/doc/docs/blog/python/src/main.py) that stress much more the infrastructure to show that the bottle neck is not RediSQL but python and the network.

### RediSQL and aioredis

Most Redis library implements methods to call the standard Redis command like `SET` or `GET` or `RPOP` and aioredis is not an exception. This is generally a problem for Redis modules like RediSQL that instead defined their own commands. Fortunately most libraries usually expose also a lower level method that is used to implement most of the other Redis command. For what concern `aioredis` the lower level method that we can use is `.execute` that is implemented for both [single connection](https://aioredis.readthedocs.io/en/v1.2.0/api_reference.html#aioredis.RedisConnection.execute) and for a [pool of connections](https://aioredis.readthedocs.io/en/v1.2.0/api_reference.html#aioredis.ConnectionsPool.execute).

Indeed is possible to implement all the other high level command using the low level `.execute` method.

### RediSQL and redis

While in this article we will talk about `aioredis`, another, not asynchronous library for using Redis with python is [`redis` library](https://pypi.org/project/redis/).

In the `redis` library, the low level method is `.execute_command` and not `.execute` as for `aioredis`, other than this difference everything will apply just the same.

### Creating a Redis connection

The very first thing to do is to connect to Redis, in our case we use a connection pool that has the same interface of a simple connection but is backed by a pool of different connections.

Creating the pool can be done like so:

```python
loop = asyncio.get_event_loop()
conn_co = aioredis.create_pool('redis://localhost', minsize=10, maxsize=300, loop = loop)
redis_co = asyncio.gather(*[conn_co])
redis = loop.run_until_complete(redis_co)
redis = redis[0]
```

and now the variable `redis` refer to a `aioredis` pool.

When we will need a new connection, the pool will either give us an idle connection or open a new connection to Redis and give us the new one.

### Setting up RediSQL

Now that we have a pool of connections before to get the data into RediSQL we need to set up RediSQL. The first step is to create a database in RediSQL, this can be done easily with a call like

```python
await redis.execute("REDISQL.CREATE_DB", "HN")
```

this call will create a new RediSQL database and it will call it `HN`. If the key `HN` already exists the call will return an error.

The next step is to create the structure to hold our data. In our case we will stick to something simple, a single table where we store the identifier of each item (comment or story) from HN, the author of such item, when the item was created and finally we will store the whole item as json structure in a text field.

```python
query = """CREATE TABLE IF NOT EXISTS hn(id integer primary key, author text, time int, item text);"""
await redis.execute("REDISQL.EXEC", "HN", query)
```

Finally, since we storing data from the open internet inside our database, is wise to create an SQL statement to execute when doing an insert. The advantage of the statement is that is safe from SQL injections and is usually faster than re-compile the same query each time.

To create a statement we can proceed as following:

```python
statement = "INSERT INTO hn VALUES(?1, ?2, ?3, json(?4));"
await redis.execute("REDISQL.CREATE_STATEMENT", "HN", "insert_item", statement)
```

The last command create a new statement in the `HN` database and associate it with the string `insert_item` so that we can refer to it later.

Also note the use of the `json(?4)` function, this is a function provided by the [JSON1 module](https://www.sqlite.org/json1.html) of SQLite and exposed by RediSQL that allow fast and efficient manipulation of json object. Using the JSON1 module is possible to have a lot of flexibility even inside a rigid SQL schema.

Is usually wise to wrap those command into a `try: except:` block. Hence the final function will look like this:

```python
async def set_up(redis):
    try:
        await redis.execute("REDISQL.CREATE_DB", "HN")
    except Exception as e:
        print(e)

    query = """CREATE TABLE IF NOT EXISTS hn(id integer primary key, author text, time int, item text);"""
    try:
        await redis.execute("REDISQL.EXEC", "HN", query)
    except Exception as e:
        print(e)

    statement = "INSERT INTO hn VALUES(?1, ?2, ?3, json(?4));"
    try:
        await redis.execute("REDISQL.CREATE_STATEMENT", "HN", "insert_item", statement)
    except Exception as e:
        print(e)
```

### Running the loop

Now that we have set up our environment we can go on and start to listen for new items posted on HN.

The API provides a simple endpoint [`maxitem.json`](https://hacker-news.firebaseio.com/v0/maxitem.json) that returns the id of the latest item posted on HN. When the loop start we get maxitem and we store it into Redis. Then, when the maxitem get updated we download each of the items between the `old maxitem` and the `new maxitem`.

We repeat the loop forever with a sleep to avoid hammering the API endpoint.

```python
async def main(redis, http):
    max_item = await get_max_item(http)
    await redis.execute("SET", "max-item", max_item)
    old_max_item = max_item
    while True:
        # we download the new maxitem
        max_item = await get_max_item(http)
        # if the new maxitem is actually bigger than the old one
        if max_item > old_max_item:
            # for each new item...
            for i in range(old_max_item, max_item):
                # we start a new Task that store the item in our database
                store = store_item(http, redis, str(i))
                asyncio.ensure_future(store)
            await redis.execute("SET", "max-item", max_item)
            old_max_item = max_item
        asyncio.sleep(1)
```

### Storing the data into RediSQL

The last interesting bit is about the `store_item` function that is the one that download the item from the API and store it into RediSQL.

```python
async def store_item(http, redis, item_id):
    item = await get_hn_item(http, item_id)
    await store_on_db(redis, item)

async def get_hn_item(http, item_id):
    while True:
        async with http.get(get_item_url(item_id)) as item:
            if 200 <= item.status < 300:
                data = await item.text()
                item = json.loads(data)
                if item:
                    return item

# In this function we store the item into the RediSQL database
async def store_on_db(redis, item):
    try:
        # execute the statement passing the necessary parameters
        await redis.execute("REDISQL.EXEC_STATEMENT", "HN", "insert_item", 
                item["id"], item.get("by", "_no_author_"), item["time"], json.dumps(item))
    except Exception as e:
        print(e)
        print(item["id"])
```

Downloading the item from the API is a simple HTTP GET request, then we simply check if it returns a successful status code and that it actually returns valid json.

Finally to store the element into RediSQL we execute the statement that we have create before during the set up phase. Indeed we are executing the command `REDISQL.EXEC_STATEMENT HN insert_item $item_id $item_author $item_time $item`. This command will find inside the database `HN` the statement `insert_item` that we have previously defined as `INSERT INTO hn VALUES(?1, ?2, ?3, json(?4));`. Now the item id will be substituted to `?1`, the item author will substitute `?2`, the creation time of the item will take the place of `?3` and the whole json string of the item will substitute `?4`, finally the statement is executed agains RediSQL and its result returned.

If everything went right, we have just added our first row to the database using async python.

## Concluding

In this tutorial we took a rather simple problem and we use it to show how to use RediSQL with async python.

We started by setting up the database, we show how to create a database and tables inside it. Then we also show how to create statements to avoid SQL injections attack and improve the efficiency of repeated queries.

Then we obtain the data from the Hacker News API and we show how to insert the data into RediSQL using the statement that we have just created.

Hopefully this tutorial will be helpful and sufficient to get started, but if you have any question feel free to get in touch or to open an issue.

If you wish to see a similar tutorial for a different language, [open an issue on github.](https://github.com/RedBeardLab/rediSQL/issues/new)


# Release 0.5.0 of RediSQL, SQL steroids for Redis

### RediSQL, Redis on SQL steroids.

RediSQL is a Redis module that provides full SQL capabilities to Redis, it is the simplest and fastest way to get an SQL database up and running, without incurring in difficult operational issues and it can scale quite well with your business.

The fastest introduction to RediSQL is [our homepage](https://redisql.com)

**tl;dr;** This release does not introduce any new features but it does improve the performances significantly. Moreover, we are releasing for multiple platforms, notably for ARMv7 (Rasberry PI), for CentOS7 (Linux AMI on AWS) and of course for Linux generic. Finally, we introduce also the TRIAL executable which can be freely downloaded and used, it provides all the functionality of the PRO version but it is limited for evaluation purposes, after \~2 hours it will shut down itself.

## Performance Improvement \~20%

We are registering an improvement in performance of roughly 20% with a similar load.

Of course, performance inside an SQL database varies a lot depending on the query you are executing.

In our evaluation, we are focusing on a simple query that inserts or updates values in the database.

We decided to focus on `insert`s because is the simplest write operation, hence it cannot be distributed to different instances, and the performance of the single instance will limit the performance of the overall system.

However, `insert` operations, need to allocate new memory, the allocator used by SQLite is very efficient but we still wanted to see what would happen without the need of allocation, hence we tested also `update`s

We are seeing an improvement of roughly the 20% in `insert`/`upsert` throughput.

The increase in throughput is driven by the switch to a zero-copy architecture.

In Rust, the language in which RediSQL is written is usual to start dealing with lifetime issues simply by copying memory, this, of course, comes with a penalty in performances.

However, as long as this performance penalty is not an issue is better to just leave as it is and work on the more important parts of the project. For one of our user it was a problem and so we decide to fix it by bringing performance improvements to all. [More about this story here.](/blog/performances)

## Releases

Rust produce in general static linked objects, so everything you need is already inside your object and you do not depends on any external library that must be installed in your host system.

This has several advantages, as long as the architecture of your host is the correct one your executable will most likely run.

There is an exception to that, `libc` given its ubiquity and size is compiled dynamically, so your object will need it to be present in your host machine. Which usually is not an issue.

Unfortunately is some machine `libc` is present in an older version that the one we are expecting, so the module will not be able to run.

Very old systems have this issues as well as CentOS 7 and the Linux AMI on AWS.

Unfortunately, cross-compile for a different version of glibc is not as simple as it may seem, but we finally manage :)

## Trial

Finally, we decide to provide open access to the PRO version for evaluation purposes.

Hence we created a third release that is called TRIAL.

The releases are exactly the same as the PRO one, except that it shuts itself down after \~2 hours.

It is supposed to let you test the PRO version before to commit to buy it, still, you have 14 days of money back guarantee if you don't like the product.

Clearly, the TRIAL version is not supposed to be used in production.

## End

As always you can find all the public releases on the [github page](https://github.com/RedBeardLab/rediSQL/releases/tag/v0.5.0), you can openly access the same public release on the [open page of our shop](https://plasso.com/s/epp4GbsJdp-redisql/) or you can buy the complete PRO package [signing up in the shop](https://plasso.com/s/epp4GbsJdp-redisql/signup/).

Remember that signing up for the PRO product also provide you free support from us, the creator of the project, so that we can point you to the right direction and suggest the best use cases for our product.


# References

This document explains all the API that zeeSQL provide to the users.

`zeeSQL` is a Redis module, the command illustrate below are added **on top** of all the existing Redis commands. Using `zeeSQL` means using Redis at the same time. You can use any Redis client, in any programming language, even the `redis-cli`, to send the `zeeSQL` commands.

This document refers to the latest API, but `zeeSQL` commits to backward-compatible API.

For each command, it exposes first the name and then the syntax, and finally a brief explanation of what is going on inside the code.

Where is possible, it provides also an estimate of the complexity but since we are talking about databases not all queries have the same time and spatial complexity.

Finally, if it is appropriate the document also provides several references to external material that the interested reader can use to understand better the dynamics of every command.

## ZEESQL.CREATE\_DB

```
ZEESQL.CREATE_DB db_key [PATH path]
```

This command creates a new DB and associates it with the key.

The path argument is optional and, if provided is the file that SQLite will use. It can be an existing SQLite file or it can be a not existing file.

If the file exists and if it is a regular SQLite file that database will be used. If the file does not exist a new file will be created.

If the path is not provided it will open an in-memory database. Not providing a path is equivalent to provide the special string `:memory:` as the path argument.

After opening the database it inserts metadata into it and then starts a thread loop.

**Complexity**: O(1), it means constant, it does not necessarily mean *fast*. However, is fast enough for any use case facing human users (eg create a new database for every user logging into a website.)

**Examples**:

```
127.0.0.1:6379> ZEESQL.CREATE_DB DB 
1) 1) "OK"
```

This command created an in-memory database. Persistency is managed by Redis with AOF or RDB following the setting of your Redis instance.

```
127.0.0.1:6379> ZEESQL.CREATE_DB on_disk_db PATH /tmp/foo.sqlite
1) 1) "OK"
```

This command created a database that uses a file as storage support, in this case `/tmp/foo.sqlite`.

If the file does not exist, it is created.

If the file is already an SQLite database, it gets used immediately with all the data already loaded.

If the file is not an SQLite database, an error is raised.

**See also**:

1. [SQLite `sqlite3_open_v2`](https://sqlite.org/c3ref/open.html)

## DEL

```
DEL db_key [key ...]
```

This command is a generic command from Redis.

It eliminates keys from Redis itself, as well if the key is a RediSQL database create with [`ZEESQL.CREATE_DB`](/references#redisqlcreate_db) it will eliminate the SQLite database, stop the thread loop and clean up everything left.

If the database is backed by a file the file will be closed, but it won't be deleted.

**Complexity**: DEL is O(N) on the number of keys, if you are only eliminating the key associated with the SQLite database will be constant, O(1).

**Examples**:

```
127.0.0.1:6379> ZEESQL.CREATE_DB DB 
1) 1) "OK"
127.0.0.1:6379> DEL DB
(integer) 1
```

**See also**:

1. [SQLite `sqlite3_close`](https://sqlite.org/c3ref/close.html)
2. [Redis `DEL`](https://redis.io/commands/del)

## ZEESQL.EXEC

```
ZEESQL.EXEC db_key 
    ( (COMMAND "command") | (STATEMENT statement) ) 
    [NOW] 
    [READ_ONLY] 
    [INTO stream] 
    [NO_HEADER] 
    [JSON] 
    [ARGS arg1 arg2 ... argn]
```

The EXEC command is the main command of zeeSQL. It allows interaction with the database stored in the `db_key`.

It takes as input the database `db_key`, either a `COMMAND` or a `STATEMENT`, an optional series of flags, and an optional variadic number of arguments.

You need to supply **EITHER** a command (using the `COMMAND` flag) or a statement (using the `STATEMENT` flag), but you need one of them.

### Command vs Statement

The command is a valid SQL string. The command SQL string can contain arguments in the form `?n`. Those arguments will be matched to the one provided at the end of the command. The first argument is bound against `?1`, NOT against `?0`. Arguments that are not provided will be bound to `NULL`.

The statement can be created with the `ZEESQL.STATEMENT` command. The arguments follow the same logic of the COMMAND variants.

**Examples**:

```
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "create table foo(a INT, b string);"
1) 1) "DONE"
2) 1) (integer) 0
```

In this example, we create a new table.

```
127.0.0.1:6379> ZEESQL.STATEMENT DB NEW insert "insert into foo values(?1, ?2);"
1) 1) "OK"
127.0.0.1:6379> ZEESQL.EXEC DB STATEMENT insert ARGS 1 one
1) 1) "DONE"
2) 1) (integer) 1
127.0.0.1:6379> ZEESQL.EXEC DB STATEMENT insert ARGS 2 two
1) 1) "DONE"
2) 1) (integer) 1
```

Then we create a new STATEMENT to insert values, and we execute the statement twice.

```
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "select * from foo;"
1) 1) "RESULT"
2) 1) "a"
   2) "b"
3) 1) "INT"
   2) "TEXT"
4) 1) (integer) 1
   2) "one"
5) 1) (integer) 2
   2) "two"
```

We queried the values just inserted in the table executing another command.

### NOW flag

By default `zeeSQL` offload the SQL computation to a secondary thread. This free the main Redis thread and keep the Redis instance reactive.

The `NOW` flags force `zeeSQL` to run the SQL computation in the main Redis thread.

### READ\_ONLY flag

The `READ_ONLY` flags communicate to `zeeSQL` that the execution will not modify the database.

If the execution might modify the database, and the `READ_ONLY` flag is passed, `zeeSQL` will return an error.

Passing the `READ_ONLY` flags allow `zeeSQL` to not replicate the command. Possibly saving computing resources of the replicas.

**Examples**:

```
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "select * from foo where a > ?1;" READ_ONLY ARGS 1
1) 1) "RESULT"
2) 1) "a"
   2) "b"
3) 1) "INT"
   2) "TEXT"
4) 1) (integer) 2
   2) "two"
```

Here we correctly used the `READ_ONLY` flag to communicate with `zeeSQL` that the command will not modify the database.

`zeeSQL` correctly executes the query.

```
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "insert into foo values(3, 'three')" READ_ONLY
(error) Statement is not read only but it may modify the database, use `EXEC` instead.
```

In this other case, we ask `zeeSQL` to modify the database while using the `READ_ONLY` flag.

`zeeSQL` correctly refuses to modify the database and returns an error.

### NO\_HEADER flag

By default `zeeSQL` returns information about the result set. It returns the name of the columns and their type.

This information might not be useful nor desirable.

With the `NO_HEADER` flag, only the result itself is returned.

**Examples**:

```
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "select a as number_as_integer, b as number_as_string from foo;"
1) 1) "RESULT"
2) 1) "number_as_integer"
   2) "number_as_string"
3) 1) "INT"
   2) "TEXT"
4) 1) (integer) 1
   2) "one"
5) 1) (integer) 2
   2) "two"
```

This is the default result from `zeeSQL`. It reports the name of the columns (in this case `number_as_integer` and `number_as_string` and their type `INT` and `TEXT`).

If we are not intereted in such information:

```
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "select a as number_as_integer, b as number_as_string from foo;" NO_HEADER
1) 1) "RESULT"
2) 1) (integer) 1
   2) "one"
3) 1) (integer) 2
   2) "two"
```

the `NO_HEADER` flags will omit it for us.

### JSON flag

By default `zeeSQL` returns its result as an array of array. This makes parsing the result a little complex in some programming languages.

The JSON flags instruct `zeeSQL` to return a single JSON string as result.

**Examples**:

```
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "select a as number_as_integer, b as number_as_string  from foo;" JSON
"{\"rows\":[{\"number_as_integer\":1,\"number_as_string\":\"one\"},{\"number_as_integer\":2,\"number_as_string\":\"two\"}],\"number_of_rows\":2,\"columns\":{\"number_as_integer\":\"INT\",\"number_as_strin
g\":\"TEXT\"}}"
```

The JSON is valid JSON even if compressed:

```
$ redis-cli ZEESQL.EXEC DB COMMAND "select a as number_as_integer, b as number_as_string  from foo;" JSON | jq
{
  "rows": [
    {
      "number_as_integer": 1,
      "number_as_string": "one"
    },
    {
      "number_as_integer": 2,
      "number_as_string": "two"
    }
  ],
  "number_of_rows": 2,
  "columns": {
    "number_as_integer": "INT",
    "number_as_string": "TEXT"
  }
}
```

Of course, this can be combined with the `NO_HEADER` flag.

```
$ redis-cli ZEESQL.EXEC DB COMMAND "select a as number_as_integer, b as number_as_string  from foo;" JSON NO_HEADER | jq
{
  "rows": [
    {
      "number_as_integer": 1,
      "number_as_string": "one"
    },
    {
      "number_as_integer": 2,
      "number_as_string": "two"
    }
  ]
}
```

### INTO stream

`zeeSQL` can push the result of a computation in a Redis Stream.

This is desirable if you want to:

1. consume the result at a later time,
2. or cache the result,&#x20;
3. or if the result is rather big and you don't want to send all of it over the network.

The `INTO stream` option will inform `zeeSQL` to push the result of the computation into the Redis STREAM called `stream`.

The `INTO stream` option is available only if the query is marked as `READ_ONLY`.

The command executes [`XADD`](https://redis.io/commands/xadd) to the stream.

If the stream does not exist a new one is created.

If the stream already exists the rows are simply appended.

The command itself is eager, hence it computes the whole result, append it into the stream, and then it returns. Once the command returns, the whole result set is already in the Redis stream.

The return value of the command depends on the result of the query:

If the result of the query is empty, it simply returns `["DONE", 0]`.

If at least one row is returned by the query the command returns:

1.the name of the stream where it appended the resulting rows, which is always the one passed as input 2. the first ID added to the stream 3. the last ID added to the stream 4. and the total number of entries added to the stream.

Using a standard Redis Stream all the standard consideration applies.

1. The stream is not deleted by zeeSQL, hence it can be used for caching, on the other hand too many streams will use memory.
2. The stream uses a standard Redis key, in a cluster environment you should be sure that the database that is executing the query and the stream that will accommodate the results are on the same cluster node.&#x20;

This can be accomplished easily by forcing the stream name to hash to the same cluster node of the database, it is sufficient to use a `stream_name` composed as such `{db_key}:what:ever:here`. Redis will hash only the part between the `{` and `}` to compute the cluster node. 3. The result can be consumed using the standard [Redis streams commands](https://redis.io/commands#stream), two good starting points are [`XREAD`](https://redis.io/commands/xread) and [`XRANGE`](https://redis.io/commands/xrange).

The key of the stream elements are the tuple `(type, column name)` separated by a colon `:`.

**Examples**:

```
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "select * from foo;" INTO foo_stream
(error) Asked a STREAM, but the query is not `READ_ONLY` (flag not set), this is not supported.
```

At first, we tried to push the result of a query that is not marked as `READ_ONLY` and this correctly fails.

```
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "select * from foo where a > 100;" READ_ONLY INTO foo_stream
1) 1) "DONE"
2) 1) (integer) 0
```

Above we execute a query that returns an empty result.

`zeeSQL` simply communicates to us that the result is empty.

```
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "select * from foo;" READ_ONLY INTO foo_stream                                                                                       1) 1) "RESULT"
2) 1) "foo_stream"
   2) "1612797707753-0"
   3) "1612797707753-1"
   4) (integer) 2
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "select * from foo;" READ_ONLY INTO foo_stream JSON
"{\"rows\":[{\"stream\":\"foo_stream\",\"first_id\":\"1612797722505-0\",\"last_id\":\"1612797722505-1\",\"size\":2}]}"
```

Then we push into the Redis stream `foo_stream` the result of the query `select * from foo`.

We do it twice, once using the standard return and the second time using the JSON return.

We pushed twice, a query that returned two results, so we expect 4 elements in the stream.

```
127.0.0.1:6379> XLEN foo_stream
(integer) 4
```

We can read the elements from the stream using the standard Redis stream interface. In this case, we are going to read only the first two elements.

```
127.0.0.1:6379> XRANGE foo_stream - + COUNT 2
1) 1) "1612797707753-0"
   2) 1) "int:a"
      2) "1"
      3) "text:b"
      4) "one"
2) 1) "1612797707753-1"
   2) 1) "int:a"
      2) "2"
      3) "text:b"
      4) "two"
```

**Complexity**: Besides the complexity of the query, the `INTO stream` options add the complexity of adding each row to the Redis stream, which is `O(n)` where `n` is the amount of row returned by the query.

**See also**:

1. [Redis Streams Intro](https://redis.io/topics/streams-intro)
2. [Redis Streams Commands](https://redis.io/commands#stream)
3. [`XADD`](https://redis.io/commands/xadd)
4. [`XREAD`](https://redis.io/commands/xread)
5. [`XRANGE`](https://redis.io/commands/xrange)

### ARGS arguments

The ARGS arguments are used to pass arguments to the statement or command.

They are variadic, and you can pass as many as you need.

In the SQL, the first argument will be bound to `?1`, the second to `?2`, and so on. Please note that the first argument is NOT bound to `?0`.

If an argument is not bound, for instance, you pass a query with `?4` but only provide 3 arguments, then, that argument is bound to `NULL`.

Redis works using a text protocol, all the arguments are encoded as text, hence the module is forced to use the procedure `sqlite3_bind_text`, however, SQLite is smart enough to recognize numbers and treat them correctly. Numbers will be treated as numbers and text will be treated as text.

**See also**:

1. [SQLite `sqlite3_prepare_v2`](https://sqlite.org/c3ref/prepare.html)
2. [SQLite `statement` aka `sqlite3_stmt`](https://sqlite.org/c3ref/stmt.html)
3. [SQLite `sqlite3_step`](https://sqlite.org/c3ref/step.html)
4. [SQLite `PRAGMA`s](https://sqlite.org/pragma.html)
5. [Redis Blocking Command](https://redis.io/topics/modules-blocking-ops)

## ZEESQL.QUERY

```
ZEESQL.QUERY db_key 
    ( (COMMAND "command") | (STATEMENT statement) ) 
    [NOW] 
    [INTO stream] 
    [NO_HEADER] 
    [JSON] 
    [ARGS arg1 arg2 ... argn]
```

This command behaves similarly to [`ZEESQL.EXEC`](/references#redisqlexec) but it imposes an additional constraint on the statement it executes.

It only executes the statement if it is a read-only operation, otherwise, it returns an error.

A read-only operation is defined by the result of calling [`sqlite3_stmt_readonly`](https://www.sqlite.org/c3ref/stmt_readonly.html) on the compiled statement.

The statement is executed if and only if [`sqlite3_stmt_readonly`](https://www.sqlite.org/c3ref/stmt_readonly.html) returns true.

This command is exactly like `ZEESQL.EXEC ... READ_ONLY` however it can be executed against Redis replicas.

**Complexity**: Similar to [`ZEESQL.EXEC`](/references#redisqlexec), however, if a statement is not read-only it is aborted immediately and it does return an appropriate error.

**See also**:

1. [SQLite `sqlite3_prepare_v2`](https://sqlite.org/c3ref/prepare.html)
2. [SQLite `statement` aka `sqlite3_stmt`](https://sqlite.org/c3ref/stmt.html)
3. [SQLite `sqlite3_step`](https://sqlite.org/c3ref/step.html)
4. [SQLite `PRAGMA`s](https://sqlite.org/pragma.html)
5. [Redis Blocking Command](https://redis.io/topics/modules-blocking-ops)&#x20;
6. [`ZEESQL.EXEC`](/references#redisqlexec)
7. [SQLite `sqlite3_stmt_readonly`](https://www.sqlite.org/c3ref/stmt_readonly.html)
8. [`ZEESQL.QUERY_STATEMENT`](/references#redisqlquery_statement)&#x20;

## ZEESQL.STATEMENT

```
ZEESQL.STATEMENT db_key
    (
        (NEW stmt "query" [CAN_UPDATE]) | 
        (DELETE stmt) | 
        (UPDATE stmt "query" [CAN_CREATE]) |
        (SHOW stmt) |
        LIST
    )
    [NOW]
```

This command manages `zeeSQL` statements.

A statement is a pre-compiled SQL query, if you are going to execute your query over and over again, it is a good idea to make it into a statement. Under the hood it is a [sqlite statement](https://sqlite.org/c3ref/stmt.html).

Statements can be used in the `ZEESQL.EXEC db_key STATEMENT stmt` command and in the `ZEESQL.QUERY db_key STATEMENT stmt` command.

Five different actions can be invoked with the STATEMENT command.

1. Create a new statement with the `NEW` option.
2. Delete a statement with the `DELETE` option.
3. Update a statement with the `UPDATE` option.
4. Show the SQL behind a statement with the `SHOW` option.
5. List all the statements with the `LIST` option.

The `STATEMENT` command includes the `NOW` flag. The `NOW` flag forces `zeeSQL` to execute the action in the main Redis thread. In standard operations mode, it should not be used.

### NEW

The `NEW` option takes as input the name to associate with the statement and an SQL query to compile.

The command compiles the SQL query into a pre-compiled statement, and associate it with the name.

The `CAN_UPDATE` flag to the `NEW` command, instruct `zeeSQL` to behave as an `UPDATE` if the statement name is already allocated to an old statement. Otherwise, without the `CAN_UPDATE` flag, if the statement name is already used by a different statement, the command fails with an error.

### DELETE

The `DELETE` option deletes a statement.

### UPDATE

The `UPDATE` option updates a statement, associating the old name with the statement compiled from the SQL query.

If the name does not exists, `UPDATE` fails, unless the `CAN_CREATE` flag is provided. In such a case `UPDATE` behave like `NEW`.

### SHOW

The `SHOW` option returns the SQL query behind one statement.

### LIST

The `LIST` option returns all the statements and their SQL queries.

Both `SHOW` and `LIST` will report:

1. The name of the statement
2. The SQL query associate with the statement
3. The number of parameters the statement expects
4. If the statement is read only or not

**Complexity**: Operation on the statements happens in constant time O(1). Listing the statements happens in O(n) with `n` number of statements present in the database.

**Examples**:

At first we create a new statement,

```
127.0.0.1:6379> ZEESQL.STATEMENT DB NEW select_1 "SELECT 1;"
1) 1) "OK"
```

We can then list, the statement:

```
127.0.0.1:6379> ZEESQL.STATEMENT DB LIST
1) 1) "RESULT"
2) 1) "identifier"
   2) "SQL"
   3) "parameters_count"
   4) "read_only"
3) 1) "TEXT"
   2) "TEXT"
   3) "INT"
   4) "INT"
4) 1) "select_1"
   2) "SELECT 1;"
   3) (integer) 0
   4) (integer) 1
```

The statement can be updated, but we need to use the `UPDATE` command or the `CAN_UPDATE` flag.

```
127.0.0.1:6379> ZEESQL.STATEMENT DB NEW select_1 "SELECT '1';"
(error) The statement is already present in the database, try with UPDATE_STATEMENT
127.0.0.1:6379> ZEESQL.STATEMENT DB NEW select_1 "SELECT '1';" CAN_UPDATE
1) 1) "OK"
127.0.0.1:6379> ZEESQL.STATEMENT DB UPDATE select_1 "SELECT '1';"
1) 1) "OK"
```

We change `select_1` to return a string and not an integer.

```
127.0.0.1:6379> ZEESQL.STATEMENT DB UPDATE select_plus_one "SELECT ?1 + 1;" CAN_CREATE
1) 1) "OK"
```

We create another statement, this time we create the statement with the `UPDATE` command and the `CAN_CREATE` flag.

```
127.0.0.1:6379> ZEESQL.STATEMENT DB LIST
1) 1) "RESULT"
2) 1) "identifier"
   2) "SQL"
   3) "parameters_count"
   4) "read_only"
3) 1) "TEXT"
   2) "TEXT"
   3) "INT"
   4) "INT"
4) 1) "select_1"
   2) "SELECT '1';"
   3) (integer) 0
   4) (integer) 1
5) 1) "select_plus_one"
   2) "SELECT ?1 + 1;"
   3) (integer) 1
   4) (integer) 1
127.0.0.1:6379> ZEESQL.EXEC DB STATEMENT select_plus_one NO_HEADER ARGS 5
1) 1) "RESULT"
2) 1) (integer) 6
```

**See also**:

1. [SQLite `sqlite3_prepare_v2`](https://sqlite.org/c3ref/prepare.html)
2. [SQLite `statement` aka `sqlite3_stmt`](https://sqlite.org/c3ref/stmt.html)
3. [SQLite bindings, `sqlite3_bind_text`](https://sqlite.org/c3ref/bind_blob.html)
4. [Redis Blocking Command](https://redis.io/topics/modules-blocking-ops)

## ZEESQL.COPY

```
ZEESQL.COPY 
    FROM db_key_source 
    TO db_key_destination 
    [NOW]
```

The command copies the source database into the destination database.

The content of the destination databases is completely ignored and lost.

It is not important if the databases are stored in memory or backed by disk, the `COPY` command will work nevertheless.

This command is useful to:

1. Create backups of databases
2. Load data from slow, disk-based, databases into a fast in-memory one
3. To persist data from an in-memory database into a disk-based database
4. Initialize a database with a predefined status

Usually, the destination database is an empty database just created, while the source one is a database where we have been working for a while.

This command use the [backup API](https://www.sqlite.org/backup.html) of sqlite.

**Complexity**: The complexity is linear on the number of pages (dimension) of the source database, beware it can be "slow" if the source database is big, during the copy the `source` database is busy and it cannot serve other queries.

**Example**:

```
127.0.0.1:6379> ZEESQL.CREATE_DB DB01
1) 1) "OK"
127.0.0.1:6379> ZEESQL.EXEC DB01 COMMAND "create table foo(a, b);"
1) 1) "DONE"
2) 1) (integer) 0
127.0.0.1:6379> ZEESQL.EXEC DB01 COMMAND "insert into foo values(1,2),(3,4);"
1) 1) "DONE"
2) 1) (integer) 2
127.0.0.1:6379> ZEESQL.CREATE_DB DB02
1) 1) "OK"
127.0.0.1:6379> ZEESQL.EXEC DB02 COMMAND "select * from foo"
(error) no such table: foo
127.0.0.1:6379> ZEESQL.COPY FROM DB01 TO DB02
1) 1) "OK"
127.0.0.1:6379> ZEESQL.EXEC DB02 COMMAND "select * from foo"
1) 1) "RESULT"
2) 1) "a"
   2) "b"
3) 1) "INT"
   2) "INT"
4) 1) (integer) 1
   2) (integer) 2
5) 1) (integer) 3
   2) (integer) 4
```

In the example we create a database, we create a table, and then we pushed few rows into the table.

We then create another database, but this time we didn't create the table, neither pushed any row.

As expected, trying to query the second database returned an error.

After copying the content of the first database into the second, the second database has become a perfect copy of the first one.

**See also**:

1. [Backup API](https://www.sqlite.org/backup.html)

## ZEESQL.INDEX

The index command accepts 3 different options:

1. NEW
2. LIST
3. DELETE

We will illustrate them separately.

### ZEESQL.INDEX NEW

```
ZEESQL.INDEX db_key 
    NEW
    TABLE table_name
    [PREFIX prefix]
    SCHEMA column_name column_type [column_name column_type ...]
```

Creates a new secondary index table for the Redis hashes.

The secondary index will refer to the table `table_name` and will use the columns indicated in the `SCHEMA` parameter.

`column_type` can be whatever is accepted by SQLite as column name, suggestions are `TEXT`, `INT`, `FLOAT` or `BLOB`.

If a prefix is provided, only the Redis hashes that start with that prefix are indexed in the table. If the prefix is omitted, the `*` prefix (catch-all) is assumed.

If the table does not exists when the index is created, `zeeSQL` creates it.

If the table already exists, `zeeSQL` takes no action. However, if the table exists but contains the wrong columns, `zeeSQL` may find it impossible to insert the hashes into the secondary index.

It is possible to create multiple indexes, with the same table, same schema, but different prefix.

`zeeSQL` store in the secondary index table, the main Redis hash key, as primary key.

The table created by `zeeSQL` behaves like any other table, so it can be queried, modified, and indexed, using the standard `ZEESQL.EXEC` interface.

No steps are taken to avoid manual deletions or updates of the secondary index table by the user.

Secondary indexes are univocally identified by the combination of the table in which they write and the prefix.

**Example**:

In this example, we can see how to create a secondary index, and how the secondary index automatically keeps the values between Redis and zeeSQL in synchronism.

```
127.0.0.1:6379> ZEESQL.INDEX DB NEW TABLE users prefix user:* SCHEMA username STRING score INT
OK
127.0.0.1:6379> HMSET user:1001 username aaa score 0
OK
127.0.0.1:6379> HMSET user:1002 username bbb score 0
OK
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "select * from users"
1) 1) "RESULT"
2) 1) "key"
   2) "username"
   3) "score"
3) 1) "TEXT"
   2) "TEXT"
   3) "INT"
4) 1) "user:1001"
   2) "aaa"
   3) (integer) 0
5) 1) "user:1002"
   2) "bbb"
   3) (integer) 0
127.0.0.1:6379> HINCRBY user:1002 score 3
(integer) 3
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "select * from users"
1) 1) "RESULT"
2) 1) "key"
   2) "username"
   3) "score"
3) 1) "TEXT"
   2) "TEXT"
   3) "INT"
4) 1) "user:1001"
   2) "aaa"
   3) (integer) 0
5) 1) "user:1002"
   2) "bbb"
   3) (integer) 3
```

### ZEESQL.INDEX LIST

The list option shows the active secondary index.

The secondary indexes are identified by the table in which they write and by the prefix they use to filter the keys.

**Example**:

```
127.0.0.1:6379> ZEESQL.INDEX DB LIST
1) 1) "RESULT"
2) 1) "users"
   2) "user:*"
127.0.0.1:6379> ZEESQL.INDEX DB NEW TABLE games prefix games:* SCHEMA first_player STRING second_player STRING score_player_1 INT score_player_2 INT
OK
127.0.0.1:6379> ZEESQL.INDEX DB LIST
1) 1) "RESULT"
2) 1) "users"
   2) "user:*"
3) 1) "games"
   2) "games:*"
```

### ZEESQL.INDEX DELETE

```
ZEESQL.INDEX db_key 
    DELETE
    TABLE table_name
    [PREFIX prefix]
```

The `DELETE` option removes a secondary index.

Hashes that match the prefix are not inserted anymore in the table after the index is removed.

The `DELETE` option takes as input the table and the prefix that identifies the secondary index to remove.

If the prefix is omitted the `*` (catch-all) prefix is assumed.

**Example**:

```
127.0.0.1:6379> ZEESQL.INDEX DB NEW TABLE users PREFIX user:* SCHEMA username STRING score INT
OK
127.0.0.1:6379> HMSET user:1001 username first_user score 12
OK
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "select * from users" NO_HEADER
1) 1) "RESULT"
2) 1) "user:1001"
   2) "first_user"
   3) (integer) 12
127.0.0.1:6379> ZEESQL.INDEX DB DELETE TABLE users PREFIX user:*
1) 1) "DONE"
2) 1) (integer) 1
127.0.0.1:6379> HMSET user:1002 username second_user score 5
OK
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "select * from users" NO_HEADER
1) 1) "RESULT"
2) 1) "user:1001"
   2) "first_user"
   3) (integer) 12
```

In the example, we first create an index.

Then we add a Redis hash that matches the secondary index prefix, so it is added to the secondary index table.

Then, delete the index.

And we can confirm that new users are not added any more to the secondary index table.

## ZEESQL.LICENSE

```
ZEESQL.LICENSE
    ( SET "license" ) | SHOW
```

The `SET` option will set the license to use for `zeeSQL`.

The license is first checked against our backend server, and if the license is correct, it will return `OK` otherwise it will return an error.

It is required an internet connection to set the license.

The `SHOW` option will show the license that is actually in use in your `zeeSQL` process.


# zeeSQL commits to backward compatibility

zeeSQL commits to backward compatibility.

Software written to run on zeeSQL version `n` should be able to run, unchanged on any version greater than `n`.

Unfortunately, committing so strictly to backward compatibilities, also means that the APIs that `zeeSQL` exposes are almost immutable, this is not sustainable.

Hence we offer a very reasonable middle ground.

## Namespacing the command

Each command of `zeeSQL` is namespaced also with its major version.

Now, `zeeSQL` is at version `1`, hence all the commands available in `ZEESQL`, like `ZEESQL.CREATE_DB` or `ZEESQL.EXEC`, are also available in `ZEESQL.V1`. In the specific case, `ZEESQL.CRATE_DB` is available also as `ZEESQL.V1.CREATE_DB` and `ZESQL.EXEC` is also available as `ZEESQL.V1.EXEC`. This, of course, holds for every single command exposed by `zeeSQL`.

When `zeeSQL` version `2` will be released, all the commands in `ZEESQL.V1` will stay exactly the same. The code won't change, and they will map one to one to the commands in version `1`.

However, when version `2` of `zeeSQL` will be available, all the commands without the version namespace, `ZEESQL.CREATE_DB` (for instance) will be the same as `ZEESQL.V2.CREATE_DB` not of `ZEESQL.V1.CREATE_DB`.

This schema allows us to move forward with the APIs, fixing mistakes, but will also provide stable backward compatibility guarantees for our users.

## Which version of the command to use, with or without version namespace?

What specific command version you should use in your project depends.

If you are not so interested in updates and new features, and the project might become legacy in your organization, in this case, it would be better to use the commands with the namespace. In this way, you can be sure that your code will always work and that you can update `zeeSQL` for performance or security reasons.

If you are experimenting, are interested in more features, or are betting a lot on `zeeSQL` tracking the version without the namespace is a good idea as well. This might involve more work in the future, but it will also give access to more features.

## Conclusions

Overall, we hope that this commitment to backward compatibility, show how much we care about our users and that our priorities are about selling good software that simplifies our users' life.

Not committing to backward compatibility would have been a short-term choice to quickly get more revenues at the cost of losing the trust of the community.


# zeeSQL, a solid product for busy developer

zeeSQL is not an open source project. I tried really hard to make RediSQL sustainable while being completely open source. Unfortunately I failed.

The product was stolen and used against its own license.

Between the choice of abbandoning the project and monetize it in a not open-source way, I decided to try to monetize it.

## Advatanges

Being a closed source project, tht people pay real money for it, comes with a series of advantages.

Having the main developer of the project keep working on it, enhancing, and writing documentation for it, is invaluable. It is much more likely that the documentation will be of high quality. There is a clear incentive in having a well documented and easy to use project.

(This is not always the case for projects that sell supports, nor it is always the case for project that sell computing resources.)

Also, the possibilities that the project get abbandoned in the foreseable futures are extremely slim. It is a source of income for the author who has all the incentives to keep expading it.

## Disadvantages

The main disadvanates of having the source code of the project not open, is that it is impossible to modify the source code yourself.

During the years of work with RediSQL, very few people tryied to submit valuable Pull Request, and I don't really think that this would change with zeeSQL.

Another disadvantage of a closed source project is that it won't be possible for your organization to run the project indipendently.

This is not a problem, as long as RedBeardLab is around and keep working on zeeSQL, but it could be a real problem in the next 5 or 10 years.

To avoid this problem, we pledge to release the source code of zeeSQL as soon as we are unable to support it anymore.


# zeeSQL and secondary indexes, how to search Redis key by value

Redis, at his heart, is a key-value store. It makes it simple, easy, and fast to search for values given their keys.

Often time, however, is necessary to look for keys that values respect some properties.

For instance, find all the keys for which their value is greater than 5. Or the keys that have a value set to a specific string like "admin".

The standard solution for this problem in Redis is to keep a set of secondary indexes. To keep everything in sync, those secondary indexes need to be updated along with the primary keys. Keeping the indexes in sync is an activity that the developer who uses Redis need to take care of, and it brings a sizable increment in complexity.

zeeSQL aims to solve this problem.

## Redis Hashes

Redis provides different data structures, one of them is Redis Hashes.

Redis Hashes map between string fields and string values. From the Redis documentation:

```
HMSET user:1000 username antirez password P1pp0 age 34
HGETALL user:1000
HSET user:1000 password 12345
HGETALL user:1000
```

In this case to the Redis key `user:1000` we associate a Redis Hash.

The Redis Hash has 3 fields, `username` with value `antirez`, `password` with value `P1pp0`, and `age` with value 34.

Redis Hashes are the only data structure that can be indexed using zeeSQL.

At the moment, zeeSQL we can index only Redis Hashes.

## Creating the Index

Secondary indexes in zeeSQL are standard SQL tables.

The user needs to provide the schema of the table, and zeeSQL will automatically keep the table in sync with the Redis Hashes.

The user can also provide a prefix so that only some Redis Hashes, the ones that match the prefix, are stored in the secondary index.

A secondary table can be created using the [`ZEESQL.INDEX` command](/references#zeesql-index-new).

```
> ZEESQL.INDEX DB NEW TABLE $table_name [PREFIX prefix] SCHEMA column_name column_type [column_name column_type]
```

This command will perform two actions.

At first, it will try to create a table called `$table_name`. If the table already exists, this step is skipped. If the table does not exists, the new table is created with the columns indicated after the `SCHEMA` keyword.

After the table is created, we register a callback.

The callback, listen to all the events that happen to the keys that start with the prefix, if the prefix is omitted, the callback listen to events for all the keys.

The callback is invoked passing as arguments the type of event and against which key the event was fired. From there, the callback has all the information it needs to keep the secondary index table in sync.

Every time that one key, matching the prefix is modified, zeeSQL updates the secondary index table.

## Secondary index table structure

The structure of the secondary index table is very simple.

There is a primary key, which is always a string, which value is the Redis key itself. In the Redis Hash above, the primary key will be the value `user:1000`

Next to the primary key, there are all the columns defined in the schema, with their respective types.

## It is JUST A TABLE

The secondary index table is a standard SQLite table.

There is nothing special about it, besides being managed by zeeSQL itself and not by the user.

You can, of course, query it, in whichever way you find more appropriate.

However, you could also modify it, even though it is strongly discouraged.

Being a standard SQLite table, it is possible to define indexes also on your secondary index table. This will allow even faster lookups.

Moreover, it is also possible to define triggers.

## Fire and forget

The commands to modify the secondary index tables, are fire and forget.

The works seamlessly on a standard table without constraints. However, if you start to add constraints and triggers to the secondary index table, it will be your responsibility to keep the database in a consistent state.

Unfortunately, zeeSQL cannot provide any feedback, if an update or insertion failed.

The use cases when this could be a problem, are extremely advanced.

## An Example

In our Redis we can store users for a simple online game. Of those users we store a simple ID, the name, and the score.

We want to search for all the user who scores is greater than 5.

We start by creating a zeeSQL database.

```
> ZEESQL.CREATE_DB DB
1) 1) "OK"
```

Now we can start populating our users.

```
127.0.0.1:6379> HMSET user:100 id 100 name foo score 3
OK
127.0.0.1:6379> HMSET user:103 id 103 name bar score 5
OK
127.0.0.1:6379> HMSET user:105 id 105 name baz score 4
```

We have created 3 users, each with its own name, id, and score.

At this point, we can create a secondary index.

```
127.0.0.1:6379> ZEESQL.INDEX DB NEW PREFIX user:* TABLE users SCHEMA id INT name STRING score INT
OK
```

Now we have created the secondary index table.

We can visualize what table was created by querying the `sqlite_master` special table.

```
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "select * from sqlite_master;"
1) 1) "RESULT"
2) 1) "type"
   2) "name"
   3) "tbl_name"
   4) "rootpage"
   5) "sql"
3) 1) "TEXT"
   2) "TEXT"
   3) "TEXT"
   4) "INT"
   5) "TEXT"
5) 1) "table"
   2) "users"
   3) "users"
   4) (integer) 3
   5) "CREATE TABLE users(key PRIMARY KEY, id INT, name STRING, score INT)"
```

Exactly what we would expect, the `key` column as primary key, where we will store the key of the Redis Hash, and then the schema we asked for.

Since the secondary index was created after some Redis Hashes were already inside Redis, the table is already populated.

```
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "select * from users;"
1) 1) "RESULT"
2) 1) "key"
   2) "id"
   3) "name"
   4) "score"
3) 1) "TEXT"
   2) "INT"
   3) "TEXT"
   4) "INT"
4) 1) "user:105"
   2) (integer) 105
   3) "baz"
   4) (integer) 4
5) 1) "user:103"
   2) (integer) 103
   3) "bar"
   4) (integer) 5
6) 1) "user:100"
   2) (integer) 100
   3) "foo"
   4) (integer) 3
```

If now we add a new user, the new user will be automatically added to the secondary index table.

```
127.0.0.1:6379> HMSET user:109 id 109 name joe score 2
OK
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "select * from users;"
1) 1) "RESULT"
2) 1) "key"
   2) "id"
   3) "name"
   4) "score"
3) 1) "TEXT"
   2) "INT"
   3) "TEXT"
   4) "INT"
4) 1) "user:105"
   2) (integer) 105
   3) "baz"
   4) (integer) 4
5) 1) "user:103"
   2) (integer) 103
   3) "bar"
   4) (integer) 5
6) 1) "user:100"
   2) (integer) 100
   3) "foo"
   4) (integer) 3
7) 1) "user:109"
   2) (integer) 109
   3) "joe"
   4) (integer) 2
```

Similarly, if a user is updated, the table will reflect the new status of the Redis Hash.

```
127.0.0.1:6379> HSET user:109 score 5
(integer) 0
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "select * from users where id = 109;"
1) 1) "RESULT"
2) 1) "key"
   2) "id"
   3) "name"
   4) "score"
3) 1) "TEXT"
   2) "INT"
   3) "TEXT"
   4) "INT"
4) 1) "user:109"
   2) (integer) 109
   3) "joe"
   4) (integer) 5
```

Similarly, a Redis Hash deleted, will be removed from the secondary index table.

```
127.0.0.1:6379> DEL user:105 user:103
(integer) 2
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "select * from users;"
1) 1) "RESULT"
2) 1) "key"
   2) "id"
   3) "name"
   4) "score"
3) 1) "TEXT"
   2) "INT"
   3) "TEXT"
   4) "INT"
4) 1) "user:100"
   2) (integer) 100
   3) "foo"
   4) (integer) 3
5) 1) "user:109"
   2) (integer) 109
   3) "joe"
   4) (integer) 5
```

## More advanced example

The first example was very straightforward. But we can use zeeSQL for something more.

For instance, maybe we want to give a rank to our users.

Users with a score between 0 and 5 will be "novice" and users with a score above it will be expert.

An easy way to achieve this would be to create a view on top of the `users` tables.

```
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "create view ranked_users AS select id, name, score, case  when score < 5 then 'novice' else 'expert' end as rank from users;"
1) 1) "DONE"
2) 1) (integer) 0
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "select * from ranked_users;"
1) 1) "RESULT"
2) 1) "id"
   2) "name"
   3) "score"
   4) "rank"
3) 1) "INT"
   2) "TEXT"
   3) "INT"
   4) "TEXT"
4) 1) (integer) 100
   2) "foo"
   3) (integer) 3
   4) "novice"
5) 1) (integer) 109
   2) "joe"
   3) (integer) 5
   4) "expert"
```

If the user with ID 100, gains a few more points, he will become an expert as well.

Using views on top of secondary indexes, we only need to care about the user score, not about the rank. Using plain Redis we would need to keep track also of the rank ourselves.

```
127.0.0.1:6379> HSET user:100 score 7
(integer) 0
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "select * from ranked_users;"
1) 1) "RESULT"
2) 1) "id"
   2) "name"
   3) "score"
   4) "rank"
3) 1) "INT"
   2) "TEXT"
   3) "INT"
   4) "TEXT"
4) 1) (integer) 100
   2) "foo"
   3) (integer) 7
   4) "expert"
5) 1) (integer) 109
   2) "joe"
   3) (integer) 5
   4) "expert"
```

If our game gains a lot of users some queries could become slow.

On top of the secondary index table, it is possible to add SQLite indexes.

For instance, we might want to know how many users have a particular score. If there are a lot of users, this query might be slow.

```
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "explain query plan select * from users where score = 3;"
1) 1) "RESULT"
2) 1) "id"
   2) "parent"
   3) "notused"
   4) "detail"
3) 1) "INT"
   2) "INT"
   3) "INT"
   4) "TEXT"
4) 1) (integer) 2
   2) (integer) 0
   3) (integer) 0
   4) "SCAN TABLE users"
```

This query uses a full table scan.

We can do better defining an index:

```
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "create index user_rank on users(score);"
1) 1) "DONE"
2) 1) (integer) 0
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "explain query plan select * from users where score = 3;"
1) 1) "RESULT"
2) 1) "id"
   2) "parent"
   3) "notused"
   4) "detail"
3) 1) "INT"
   2) "INT"
   3) "INT"
   4) "TEXT"
4) 1) (integer) 3
   2) (integer) 0
   3) (integer) 0
   4) "SEARCH TABLE users USING INDEX user_rank (score=?)"
```

## Conclusion

In this article, we show how to use secondary indexes in zeeSQL.

They are very powerful and useful when you are simplifying your queries against Redis Hashes. Moreover, they allow you to think only about the main data, it is the query engine that finds the best way to query your data for you.

The important takeaway from this article should be that the table creates as zeeSQL secondary indexes are just standard tables. As such, those tables can be manipulated in whichever way the application developer finds more opportune.


# Tutorial

### Setting up the environment

This tutorial will walk you through the most important commands of `zeeSQL` and how to use them.

The simplest way to follow this tutorial is to launch an instance of `zeeSQL` using docker.

```
docker run --name zeesql --rm -d redbeardlab/zeesql
```

The next thing you will need to follow the tutorial is the `redis-cli`.

You can connect to the same docker container and start the `redis-cli` with:

```
docker exec -it zeesql redis-cli
```

At this point you are inside the `redis-cli`, ready to interact with `Redis` and `zeeSQL`.

### Creating a database in zeeSQL

The very first step when working with `zeeSQL` it is to create a new database.

The database can be created with a single command.

```
127.0.0.1:6379> ZEESQL.CREATE_DB DB
1) 1) "OK"
```

This command will create a new database, and it will associate it with the Redis key `DB`.

Any time we want to interact with this database, we will pass `DB` to the `zeeSQL` commands.

It is possible to create more than one database, you can create as many as you like, since they are very lightweight.

### Sending commands to the database

After creating a database, we want to interact with it. It is possible to interact with the database sending it commands.

```
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND 'select 1;' NO_HEADER
1) 1) "RESULT"
2) 1) (integer) 1
```

We have successfully sent our first command to `zeeSQL` and get our first `RESULT`, the integer 1.

### Modify the database structure

A database without tables is not very useful. We will now create a table to store information about users.

In the table we want to store the username, its score and the user email. The username and the email will be text fields, while the score will be an integer field.

```
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND 'CREATE TABLE users(username TEXT, score INT, email TEXT);'
1) 1) "DONE"
2) 1) (integer) 0
```

The operation was successfully and 0 rows have been modified.

However, we now have a table where we can store information about the user.

### Add data to the database

We can now start to add users to our table.

```
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND 'INSERT INTO users VALUES("jsmith", 3, "jon.smith@gmail.com");'
1) 1) "DONE"
2) 1) (integer) 1
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND 'INSERT INTO users VALUES("EvelineInArgentina", 3, "eve.frank@yahoo.com");'
1) 1) "DONE"
2) 1) (integer) 1
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND 'INSERT INTO users VALUES("DuffyAlone", 12, "mr.duffy@proton.com");'
1) 1) "DONE"
2) 1) (integer) 1
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND 'INSERT INTO users VALUES("far", 6, "farrington@nv.ru");'
1) 1) "DONE"
2) 1) (integer) 1
```

Each insert was successful and each one added one more row to the database.

### Query the database

After having added data to the database, we want to query those data back.

We can ask for the score of the user `jsmith`

```
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND 'SELECT score FROM users WHERE username = "jsmith"'
1) 1) "RESULT"
2) 1) "score"
3) 1) "INT"
4) 1) (integer) 3
```

In this case the score is an integer, and it is of value 3.

Or to know what users have a score greater than 5

```
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND 'SELECT username FROM users WHERE score > 5'
1) 1) "RESULT"
2) 1) "username"
3) 1) "TEXT"
4) 1) "DuffyAlone"
5) 1) "far"
```

In this other case the usernames are of type TEXT and are: `DuffyAlone` and `far`.

Since we are not modifying the database, instead of EXECUTING a command with `EXEC` we can just QUERY.

```
127.0.0.1:6379> ZEESQL.QUERY DB COMMAND 'SELECT username FROM users WHERE score > 5'
1) 1) "RESULT"
2) 1) "username"
3) 1) "TEXT"
4) 1) "DuffyAlone"
5) 1) "far"
```

`QUERY` does not work when trying to modify the database.

```
127.0.0.1:6379> ZEESQL.QUERY DB COMMAND 'INSERT INTO users VALUES("far", 6, "farrington@nv.ru");'
(error) Statement is not read only but it may modify the database, use `EXEC_STATEMENT` instead.
```

### Modify the data

Our users, keep using our platform, are increasing their score. We can increase the score with an update.

```
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND 'UPDATE users SET score = score + 1 WHERE username = "jsmith";'
1) 1) "DONE"
2) 1) (integer) 1
127.0.0.1:6379> ZEESQL.QUERY DB COMMAND 'SELECT score FROM users WHERE username = "jsmith"'
1) 1) "RESULT"
2) 1) "score"
3) 1) "INT"
4) 1) (integer) 4
```

In this example, we first increase the score of the user `jsmith` of one, and then we query the same score.

### Delete the data

Eventually our user will leave the platform, in this case we can delete them.

```
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND 'DELETE FROM users WHERE username = "far";'
1) 1) "DONE"
2) 1) (integer) 1
```

### Use arguments for your queries

Up to now, we send only SQL queries that contains all the parameters. It is also possible to send a query with placeholders followed by arguments. This is useful to avoid SQL injections attacks and to avoid string constructions at runtime.

In our example we can increment the score of a player by a specific amount.

```
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND 'UPDATE users SET score = score + ?2 WHERE username = ?1;' ARGS jsmith 3
1) 1) "DONE"
2) 1) (integer) 1
127.0.0.1:6379> ZEESQL.QUERY DB COMMAND 'SELECT score FROM users WHERE username = ?1' ARGS jsmith
1) 1) "RESULT"
2) 1) "score"
3) 1) "INT"
4) 1) (integer) 7
```

The first argument is `?1` (not `?0`) and the second argument is `?2`.

## Using secondary indexes (or search by values) in Redis

Now that we have understood how to deal with standard zeeSQL databases and how to query them, we can move forward.

Now we will introduce zeeSQL secondary indexes, or how to search and project Redis `hash` data.

In Redis, it is common to store values as hash. Each hash is univocally identify by a key, and it has one of more field. Each field has a value associated with.

Redis already provide fast access to elements by their key. But it is not possible to search keys from their values.

With zeeSQL we can solve this problem, but automatically push the hashes keys and values to a specific table.

We will work with a simple telemetric system. We have different sensors, each sensor send a timestamp, a temperature value and a humidity value.

### Saving the data into Redis

As first step let's see how we model the data in raw Redis, using Redis Hashes.

```
127.0.0.1:6379> HMSET sensor:001:1612733809 timestamp 1612733809 sensor 1 temperature 23 humidity 56
OK
127.0.0.1:6379> HMSET sensor:001:1612633809 timestamp 1612633809 sensor 1 temperature 18 humidity 21
OK
127.0.0.1:6379> HMSET sensor:001:1612633819 timestamp 1612633819 sensor 1 temperature 20 humidity 23
OK
127.0.0.1:6379> HMSET sensor:002:1612733809 timestamp 1612733809 sensor 2 temperature 32 humidity 11
OK
127.0.0.1:6379> HMSET sensor:002:1612633809 timestamp 1612633809 sensor 2 temperature 21 humidity 16
OK
127.0.0.1:6379> HMSET sensor:002:1612633819 timestamp 1612633819 sensor 2 temperature 23 humidity 12
OK
```

The key is in the form `sensor:$sendor_id:$timestamp` and the other fields contains the telemetries' data.

### Creating an index

We now want to store the information in the sensor in an SQL table, for easier access.

```
127.0.0.1:6379> ZEESQL.INDEX DB NEW TABLE sensors PREFIX sensor:* SCHEMA timestamp INT sensor INT temperature INT humidity INT
OK
```

The command creates a new secondary index associate with the table `sensors`. The index will be concerned only for the hashes which key start with the prefix `sensor:` (`*` being a catch-all). The schema used by the index will have 4 rows, each of them will be an INTEGER, and the name of those columns are respectively `timestamp`. `sensor`, `temperature` and `humidity`.

The creation of an index, imply the creation of the table in the database. If the table already exists, it is assumed to contain the correct columns.

### Reading data from the index

As soon as the index is created, the Redis keys space is scanned and the hashes are added to the table. Which means that we can immediately query the table.

```
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND 'select * from sensors;'
1) 1) "RESULT"
2) 1) "key"
   2) "timestamp"
   3) "sensor"
   4) "temperature"
   5) "humidity"
3) 1) "TEXT"
   2) "INT"
   3) "INT"
   4) "INT"
   5) "INT"
4) 1) "sensor:001:1612633809"
   2) (integer) 1612633809
   3) (integer) 1
   4) (integer) 18
   5) (integer) 21
5) 1) "sensor:002:1612733809"
   2) (integer) 1612733809
   3) (integer) 2
   4) (integer) 32
   5) (integer) 11
6) 1) "sensor:001:1612733809"
   2) (integer) 1612733809
   3) (integer) 1
   4) (integer) 23
   5) (integer) 56
7) 1) "sensor:002:1612633819"
   2) (integer) 1612633819
   3) (integer) 2
   4) (integer) 23
   5) (integer) 12
8) 1) "sensor:002:1612633809"
   2) (integer) 1612633809
   3) (integer) 2
   4) (integer) 21
   5) (integer) 16
9) 1) "sensor:001:1612633819"
   2) (integer) 1612633819
   3) (integer) 1
   4) (integer) 20
   5) (integer) 23
```

### Modify the table of the index

It is possible to add, remove and update values to the index table manually. Do not do that, the data between Redis and zeeSQL will go out of sync.

### Adding hashes

The index continuously listens to the HASH commands of Redis and keeps the table in sync.

```
127.0.0.1:6379> HMSET sensor:003:1612633819 timestamp 1612633819 sensor 3 temperature 50 humidity 8
OK
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND 'select * from sensors where sensor = 3;'
1) 1) "RESULT"
2) 1) "key"
   2) "timestamp"
   3) "sensor"
   4) "temperature"
   5) "humidity"
3) 1) "TEXT"
   2) "INT"
   3) "INT"
   4) "INT"
   5) "INT"
4) 1) "sensor:003:1612633819"
   2) (integer) 1612633819
   3) (integer) 3
   4) (integer) 50
   5) (integer) 8
```

In this example we added the sensor with ID 3, after the secondary index was already in place.

### Removing hashes

Similarly, if a hash is deleted, the correct row is deleted from the table.

```
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND 'select key from sensors where sensor = 2;'
1) 1) "RESULT"
2) 1) "key"
3) 1) "TEXT"
4) 1) "sensor:002:1612733809"
5) 1) "sensor:002:1612633819"
6) 1) "sensor:002:1612633809"
127.0.0.1:6379> DEL sensor:002:1612633809
(integer) 1
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND 'select key from sensors where sensor = 2;'
1) 1) "RESULT"
2) 1) "key"
3) 1) "TEXT"
4) 1) "sensor:002:1612733809"
5) 1) "sensor:002:1612633819"
```

As you can see we delete a hash, and the correct row was deleted also from the table.

### Updating hashes

As with deletion, updating a hash is also reflected on the index table.

```
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND 'select key, temperature from sensors where sensor = 3;'
1) 1) "RESULT"
2) 1) "key"
   2) "temperature"
3) 1) "TEXT"
   2) "INT"
4) 1) "sensor:003:1612633819"
   2) (integer) 50
127.0.0.1:6379> HINCRBY sensor:003:1612633819 temperature 33
(integer) 83
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND 'select key, temperature from sensors where sensor = 3;'
1) 1) "RESULT"
2) 1) "key"
   2) "temperature"
3) 1) "TEXT"
   2) "INT"
4) 1) "sensor:003:1612633819"
   2) (integer) 83
```

In this case we update a hash, and also the table was updated.

### Querying the secondary index table

The secondary index table, it is just a plain SQL table. zeeSQL does not add any index on the table. User is free to add the SQL indexes it desired to the secondary index table.

Without any index, each query will go through a full table scan.

## End

I hope that this tutorial was helpful :)

If you have any question, feel free to contact me <simone@redbeardlab.com> or on github: [RedBeardLab/zeeSQL-doc](https://github.com/RedBeardLab/zeeSQL-doc)


# Pricing for zeeSQL

zeeSQL is **not** a product free to run.

However, it comes with a **generous free plan**, so that small and medium applications can work without paying anything.

This document explains how the pricing for `zeeSQL` works.

It starts explaining how we charge for `zeeSQL` and then what is included in the free plan.

## How zeeSQL charges users

zeeSQL uses a credit system linked to a license key.

In `zeeSQL` there are two main concepts, `database`s and `secondary indexes`.

Running either one (database or one secondary index) costs 1 credit every hour.

One credit costs 0,01€

We devise this pricing schema to scale on the **complexity** that zeeSQL is managing not on the size of your datasets.

There is an allowance of **2160 free credit each month** for each license.

## zeeSQL free plan

zeeSQL reports its usage (number of credit used) only if the user provided a license key.

It is not necessary to provide a license key to use `zeeSQL`.

However, without a license key, it is not possible to spend more than 3 credits each hour. This is enforced by avoiding creating a new database or a new index if you are already using 3 credits for an hour.

To spend more than 3 credits for an hour, you need to input a valid license key.

The 2160 free credit, are there to allow users to input a valid license while still benefit from the free plan.

```
3 credits each hours * 24 hours a day * 30 days a months = 2160 credit / month.
```

Which is exactly the amount of free credit available.

This will allow to test the system and do maintenance operations.

The 2160 credit/month can be spent also together during a single hour. This allows testing the product with multiple databases and secondary indexes.

## [Getting a license](https://license.zeesql.com)

You can obtain a license from [this website](https://license.zeesql.com).

The license can be shared between multiple `zeeSQL` processes.

## Example

These examples show how much you will be charged for using `zeeSQL`.

They all assume steady-state operation where databases and secondary indexes are not generate or deleted.

| Number of databases | Numbre of indexes | Credit usage | Cost per hour | Cost per month |
| ------------------- | ----------------- | ------------ | ------------- | -------------- |
| 1                   | 0                 | 1            | FREE          | FREE           |
| 3                   | 0                 | 3            | FREE          | FREE           |
| 4                   | 0                 | 4            | 0.01          | 7.20           |
| 5                   | 0                 | 5            | 0.02          | 14.40          |
| 1                   | 2                 | 3            | FREE          | FREE           |
| 1                   | 3                 | 4            | 0.01          | 7.20           |
| 2                   | 3                 | 5            | 0.02          | 14.40          |
| 2                   | 4                 | 6            | 0.03          | 21.60          |

## Code examples

These code examples show what happens when the user tries to create more than 3 databases without one valid license key.

```
127.0.0.1:6379> ZEESQL.CREATE_DB DB_1
1) 1) "OK"
127.0.0.1:6379> ZEESQL.CREATE_DB DB_2
1) 1) "OK"
127.0.0.1:6379> ZEESQL.CREATE_DB DB_3
1) 1) "OK"
127.0.0.1:6379> ZEESQL.CREATE_DB DB_4
(error) Not enough credit in your license, please upgrade.
127.0.0.1:6379> ZEESQL.LICENSE SET $one_valid_license
OK
127.0.0.1:6379> ZEESQL.CREATE_DB DB_4
1) 1) "OK"
```

`zeeSQL` returns a simple error and does not allows the user to create more databases.

Similarly with secondary indexes.

```
127.0.0.1:6379> ZEESQL.CREATE_DB DB_1
1) 1) "OK"
127.0.0.1:6379> ZEESQL.INDEX DB_1 NEW TABLE users PREFIX users:* SCHEMA id INT name STRING
OK
127.0.0.1:6379> ZEESQL.INDEX DB_1 NEW TABLE games PREFIX games:* SCHEMA id INT game_name STRING player INT
OK
127.0.0.1:6379> ZEESQL.INDEX DB_1 NEW TABLE credit PREFIX credit:* SCHEMA user_id INT available_credits INT
(error) Not enough credit in your license, please upgrade.
127.0.0.1:6379> ZEESQL.LICENSE SET $one_valid_license
OK
127.0.0.1:6379> ZEESQL.INDEX DB_1 NEW TABLE credit PREFIX credit:* SCHEMA user_id INT available_credits INT
OK
```

## Pathological cases

Please [get in touch](mailto:simone@redbeardlab.com) if your application is a pathological case.

For instance, if you design your application to have one database for each user.

## Air gapped instances

`zeeSQL` needs to communicate to a specific hostname to communicate how many credits each instance is using.

If your system is without external connectivity, please [get in touch](mailto:simone@redbeardlab.com).

We can provide builds of `zeeSQL` that don't need to report back their usage.


# Why you should migrate from RediSQL to zeeSQL

zeeSQL is the successor of RediSQL. zeeSQL is RediSQL V2.

It is based on largely the same codebase but improved in several ways with novel features.

## Secondary indexes, or search by value

Secondaries indexes allow searching Redis keys by value. If you are interested in searching Redis keys by value or manipulate Redis hashes without maintains secondary data structures, secondary indexes are a perfect way to do it.

Secondary indexes have been introduced in RediSQL V2, also know as zeeSQL.

Secondary indexes themselves are a more than valid motivation to switch to zeeSQL instead of keeping using RediSQL.

## Better API

The APIs of zeeSQL are just better.

They are more flexible, they allow extensions, and they provide more information to the user.

The zeeSQL APIs were improved keeping what worked well with RediSQL and fixing what could have been improved.

### Return type and column name

The new APIs of zeeSQL, by default, return both the column name and the column type.

This allows writing wrappers or client library that automatically creates the correct type reading from Redis.

In the example below, you can see what RediSQL returned, versus what zeeSQL returns.

```
127.0.0.1:6379> REDISQL.V1.CREATE_DB DB
OK
127.0.0.1:6379> REDISQL.V1.EXEC DB "create table foo(a, b, c);"
1) DONE
2) (integer) 0
127.0.0.1:6379> REDISQL.V1.EXEC DB "insert into foo values(1 , 'aaa', 2)"
1) DONE
2) (integer) 1
127.0.0.1:6379> REDISQL.V1.EXEC DB "select * from foo;"
1) 1) (integer) 1
   2) "aaa"
   3) (integer) 2
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "select * from foo;"
1) 1) "RESULT"
2) 1) "a"
   2) "b"
   3) "c"
3) 1) "INT"
   2) "TEXT"
   3) "INT"
4) 1) (integer) 1
   2) "aaa"
   3) (integer) 2
```

The result from zeeSQL is more structured. It contains the columns' name, then their type, and finally the rows.

The result from RediSQL returns directly the rows without providing information about the column name and their type.

### Structured returns

Values from zeeSQL and RediSQL can either be:

1. OK
2. DONE
3. Some result set

RediSQL returns either:

1. A simple string with the value "OK"
2. An array with first string the "DONE" string
3. A nested array, that has as first elements of the first subarray, the string "RESULT"

This is clearly suboptimal and it was fixed in zeeSQL.

zeeSQL returns always a nested array.

The first element, of the first subarray, identify the type of the result. It can be either the string "OK", or the string "DONE" or the string "RESULT".

But zeeSQL returns **always** a nested array.

Let's compare the same workflow with RediSQL and with zeeSQL.

```
127.0.0.1:6379> REDISQL.V1.CREATE_DB DB
OK
127.0.0.1:6379> REDISQL.V1.EXEC DB "create table foo(a, b, c);"
1) DONE
2) (integer) 0
127.0.0.1:6379> REDISQL.V1.EXEC DB "insert into foo values(1 , 'aaa', 2)"
1) DONE
2) (integer) 1
127.0.0.1:6379> REDISQL.V1.EXEC DB "select * from foo;"
1) 1) (integer) 1
   2) "aaa"
   3) (integer) 2
```

In this example, RediSQL returned three different types of results.

```
127.0.0.1:6379> ZEESQL.CREATE_DB DB1
1) 1) "OK"
127.0.0.1:6379> ZEESQL.EXEC DB1 COMMAND "create table foo(a);"
1) 1) "DONE"
2) 1) (integer) 0
127.0.0.1:6379> ZEESQL.EXEC DB1 COMMAND "insert into foo values(1),(2);"
1) 1) "DONE"
2) 1) (integer) 2
127.0.0.1:6379> ZEESQL.EXEC DB1 COMMAND "select * from foo;"
1) 1) "RESULT"
2) 1) "a"
3) 1) "INT"
4) 1) (integer) 1
5) 1) (integer) 2
```

In this other example, zeeSQL returned always a single type of result, a nested array.

### The JSON flag

The [JSON flag](/references#json-flag) is a new feature of zeeSQL, not available in RediSQL.

Instead of returning a nested subarray, that in some programming language are difficult to manage, we can return a single JSON string that represents the same result set.

This makes working with zeeSQL much simpler in both dynamic and statically typed languages.

The string returned in a valid JSON string.

```
127.0.0.1:6379> ZEESQL.CREATE_DB DB1
1) 1) "OK"
127.0.0.1:6379> ZEESQL.EXEC DB1 COMMAND "create table foo(a);" JSON
"{\"result\":\"done\",\"modified_rows\":0}"
127.0.0.1:6379> ZEESQL.EXEC DB1 COMMAND "insert into foo values(1),(2);" JSON
"{\"result\":\"done\",\"modified_rows\":2}"
127.0.0.1:6379> ZEESQL.EXEC DB1 COMMAND "select * from foo;" JSON
"{\"rows\":[{\"a\":1},{\"a\":2}],\"number_of_rows\":2,\"columns\":{\"a\":\"INT\"}}"
```

### Arguments to the EXEC and QUERY command

In RediSQL there is no way to pass arguments to simple queries.

You can either submit a full query, or create a statement, and submit the statement with arguments.

[In zeeSQL is possible to submit queries with arguments.](/references#args-arguments) Especially if the arguments are coming from the users you should always submit them as arguments.

In RediSQL you had no choice, but to create a statement, and bind the arguments. In zeeSQL you can bind them to a simple query, without the need of creating a statement.

```
127.0.0.1:6379> ZEESQL.CREATE_DB DB
1) 1) "OK"
127.0.0.1:6379> ZEESQL.EXEC DB1 COMMAND "create table foo(a);"
1) 1) "DONE"
2) 1) (integer) 0
127.0.0.1:6379> ZEESQL.EXEC DB1 COMMAND "insert into foo values(?1 + 3),(?2 + ?1);" ARGS 5 2
1) 1) "DONE"
2) 1) (integer) 2
127.0.0.1:6379> ZEESQL.EXEC DB1 COMMAND "select * from foo;"
1) 1) "RESULT"
2) 1) "a"
3) 1) "INT"
4) 1) (integer) 8
5) 1) (integer) 7
```

## Maintainance

zeeSQL is maintained, RediSQL is not maintained anymore.

Code updates, security fixes, and SQLite engine upgrades will happen only in zeeSQL.

No changes at all are expected to RediSQL.

## Backward compatibility

Besides all these upgrades, zeeSQL maintains backward compatibility with RediSQL.

All the RediSQL commands are prefixed by the `REDISQL.` string.

In zeeSQL you can find exactly the same commands, with exactly the same semantic, but a different prefix: `REDISQL.V1.`.

So if in RediSQL you used the `REDISQL.EXEC` command, with zeeSQL you can use the exact same command with `REDISQL.V1.EXEC`.


# FAQs

## Common answer and mistake quickly solved

### ERR - Error the key is empty

You try to execute a command against a database like

```
REDISQL.EXEC DB-EXAMPLE "SELECT 1;"
```

and RediSQL returns the error: `ERR - Error the key is empty`.

Most likely the dabatase `DB-EXAMPLE` does not exists. To fix the problem you can simply create first the database with

```
REDISQL.CREATE_DB DB-EXAMPLE
```

During development is quite convenient to just delete everything from RediSQL, so it may happens that you encounter this error. A possible solution is to always invoke the `REDISQL.CREATE_DB` command, if the database is not there, it will be created, if the database is already there an error will be raise. As long as you are in a development environment just ignore the error.

### READONLY You can't write against a read only replica.

Redis and RediSQL supports **replication**. You can have the same database in different redis instance, on different processes and potentially on different machine. This means that you can read data from different instances in parallel, greatly improving reading performances. However you cannot write in parallel to different instances, otherwise we wouldn't know what data is "real". You can write only to the master instance.

By policy the `REDISQL.EXEC` command allow you to read and write and (due to Redis limitation) you cannot use this command on replicas. The `REDISQL.EXEC` command works only on the master node. This is true even if the query that you are trying to execute is an very simple read only query like `SELECT 1;`, you cannot `REDISQL.EXEC` against a replica node.

In order to read from the replicas, you can use the `REDISQL.QUERY` family of commands. This command is allowed to only read data, without modifying the database, hence you can use it also in the replica instances. Moreover it is a good idea to use it also against the master instance whenever is possible.

If you try to execute the `REDISQL.EXEC` command against a replica you will get the error `READONLY You can't write against a read only replica`. To query the replicas use the `REDISQL.QUERY` command.


# Motivation

I build `zeeSQL` to remove operational and software complexity from most small to medium applications.

## Remove complexity from small and medium application

Even simple apps are becoming increasingly complex.

Applications today usually have at least 3 moving parts.

1. Some persistent layer like a database
2. A fast ephemeral storage
3. Some sort of queue

Modern database solutions like Postres can usually manage almost everything, but they are never really quite enough.

You are not going to cache your data in Postgres, you are going to use Redis or Memcache. Similarly for session information.

Moreover, databases are hard and complex to operate. Complex, even before to create all the modern container orchestration infrastructure.

Redis is another very good candidate to be a single solution for the data need of small or medium applications, it works very well as fast ephemeral storage and as a queue system. Moreover, it has great persistence capabilities. However, besides simple use cases, it is hard to use as the only database.

Most applications need some form of complex data query and filtering.

`zeeSQL` is born to address this small niche.

It provides a fast, simple, and easy to operate SQL engine that is embedded in Redis. Adding more capabilities on top of the Redis features. It inherits all the persistency guarantees of Redis, and it is perfect to use as a persistency layer in small to medium applications.

Using `zeeSQL` it is possible to use a single, easy to maintain, and easy to operate external process for all the application data needs.

Then working in-memory by default, `zeeSQL` turns out to be very fast. And I try my very best to keep performance as high as possible.

## Simplify Redis

The first version of `zeeSQL` kept completely separated the data belongings to `zeeSQL` and the data from Redis.

`zeeSQL` was not able to query and see data from Redis.

Then users start to ask how to query data that are stored in Redis.

People wanted to use the SQL capabilities of `zeeSQL` to query data in Redis. It makes sense, the technology, and the code were ready for this use case, and it improves the use cases of `zeeSQL`.

The latest version of `zeeSQL` can now integrate with Redis hashes allow people to search Redis data by value.

This is really another big simplification for developers and Redis users.

Without `zeeSQL`, if you needed to search value in Redis hash, you had two choices.

1. Get all the data from Redis to your code, and then implement search, filter, and aggregation by hand.
2. Maintains separated data structure in Redis to quickly identify the elements you are interested in.

Of course, neither choice is optimal.

Fetching all the data from Redis is a slow operation, that keeps the Redis process busy, increments the tail latency, and saturates the bandwidth. Also implementing search, filtering, and aggregation in code is a slow and error-prone process. It would be much better if Redis could return directly only the data we are interested in.

Keeping separated data structures is very cumbersome and error-prone. Whenever you update a Redis hash value, you need also to update all the other data structures, otherwise, you will keep a wrong view of your data. And these updates need to be done on insertion, deletions, and when you modify the data. Moreover, it is not flexible when new business requirements come along. It would be much better if Redis could keep track itself of the data and figure out by itself how to query them.

With `zeeSQL` all of this is now possible.

Values from Redis hashes are pushed into a standard SQL table, and from there they can be queried.

This is a superior model to search Redis by value. It keeps the best of the two alternatives overcoming both limitations.

It allows users to specify what data they want, and `zeeSQL` finds the best way to provide only those information. Without the need to maintains any separated data structures.

## Simplify, simplify, simplify

The motivation for creating `zeeSQL` is to simplify developers' life.

First, it simplified operations at the data level, offering a single solution for small to medium applications to solve all their data needs.

Then `zeeSQL` simplifies Redis operations, solving how to search Redis by value and not only by key.

If you would like to learn more about `zeeSQL`, please visit the [README](/) or try to [follow the tutorial](/tutorial). You can also check out the [command references.](/references)

If you got more questions you can contact me at <simone@redbeardlab.com>