From SQL to Redisearch: Mapping SQL commands to Redisearch

The Problem

Let me tell you a story. In the past, my blog used MySQL with a Node.js API. To maintain it, we had to use a 2GB RAM server. The monthly maintenance cost was $10 - $12. It might not be a huge amount, but it would accumulate if we had to continuously maintain it. Not to mention the other bills we had to pay every month.

This became a big issue that forced me to evaluate whether there was a way to run the blog on the lowest server configuration, reducing the cost by half. I had heard of Redis before, but using Redis to replace SQL was inconvenient due to some limitations in storage and query compared to SQL, which was already very good and familiar.

During my research, I stumbled upon Redisearch, which turned out to be a suitable tool for this situation. In addition to its storage capacity and query speed, Redisearch also provided advanced search capabilities. Search was a feature I prioritized because I wanted to provide a good search experience for users, allowing them to quickly find what they need without wasting too much time.

After spending some time with Redisearch, I have summarized some experiences for those who are considering "using Redisearch as SQL" or in other words, mapping SQL queries to Redisearch.

Relationship Mapping

A Table is an essential component in SQL, where Columns define the data types for storage. In Redis, there is a data type similar to a Table called HASH. When data is stored in HASH, with the magic touch of Redisearch, we can have the ability to query data equivalent to SQL.

A HASH structure consists of a hash_name, containing key-value pairs.

For example, data stored in HASH would look like this:

> HSET articles:1 id 1 title "What is Node.js" url "what-is-nodejs" createdAt 1671032684

You can learn more about the HASH data type at Redis hashes.

Since HASHes in Redis are simply discrete data and not contained in any set like a table in SQL, Redisearch creates Index Schemas to gather HASHes into a structure similar to a table.

Common Queries

In this example, I created a table named articles consisting of id, title, url, and createdAt, corresponding to the information of the article ID, title, URL, and creation time.

Data Types

In SQL, columns can have various data types such as char, varchar, int, float... depending on the storage purpose of the column. In Redisearch, a "column" only includes the following types: Text, Numeric, Tag, Geo.

Creating a Table

SQL:

CREATE TABLE articles (
    id int PRIMARY KEY,  
    title varchar,  
    url varchar,  
    createdAt date
);

Redisearch:

> FT.CREATE articles ON HASH PREFIX 1 articles: SCHEMA id NUMERIC title TEXT url TEXT createdAt NUMERIC

The HASHes created follow the articles: rule and are automatically added to the articles Index Schema.

Altering a Table

SQL:

ALTER TABLE articles
ADD deletedAt date;

Redisearch:

> FT.ALTER articles SCHEMA ADD deletedAt NUMERIC

Note: In SQL, the ALTER command can be used to add/modify/delete columns, but in Redisearch, you can only add a Schema. If you want to modify or delete, you have to DROP the index schema and recreate it.

INSERT

SQL:

INSERT INTO article (id, title, url, createdAt)
VALUES (1, 'What is Node.js', 'what-is-nodejs', 1671032684);

Redisearch:

> HSET articles:1 id 1 title "What is Node.js" url "what-is-nodejs" createdAt 1671032684

SELECT

SQL:

SELECT * FROM articles LIMIT 10 OFFSET 0;

Redisearch:

> FT.SEARCH articles * LIMIT 0 10

For more details on how basic SQL query syntax is mapped to Redisearch, you can refer to Mapping common SQL predicates to RediSearch.

UPDATE

SQL:

UPDATE articles SET title = 'What is Node.js?' WHERE id = 1;

Redisearch:

> HSET articles:1 title "What is Node.js?"

GROUP BY

Suppose we have another table named react with three columns client_id, react, and url to store user reviews by url of an article. Now, you want to retrieve the total number of each react by URL:

SQL:

SELECT react, COUNT(*) AS count FROM react WHERE url = 'what-is-nodejs' GROUP BY react;

Redisearch:

> FT.AGGREGATE articles "@url:what-is-nodejs" GROUPBY 1 @react REDUCE count 0 AS count

The syntax is quite different from SQL, but Redisearch does support the AGGREGATE query. You can learn more about the syntax at FT.AGGREGATE.

Conclusion

In this article, I have summarized some common SQL commands that can be mapped to Redisearch. Through this article, I would like to provide readers with a different perspective on whether Redisearch can solve problems similar to SQL. If you are interested, you can read more in my article What is Redisearch? Estacks is using Redisearch as a database!.