From SQL to Redisearch: Mapping SQL commands to Redisearch

From SQL to Redisearch: Mapping SQL commands to Redisearch

Daily short news for you
  • Previously, there was a mention of openai/codex - a type of agent that runs conveniently in the Terminal from OpenAI, especially since it is open source and they have now added support for other providers instead of just using the chatgpt model as before.

    Recently, Anthropic also introduced Claude Code which is quite similar to Codex, except it is not open source and you are required to use their API. Since I don't have money to experiment, I've only heard that people in the programming community praise it a lot, and it might even be better than Cursor. On the flip side, there's the risk of burning a hole in your wallet at any moment 😨

    » Read more
  • For a long time, I have been thinking about how to increase brand presence, as well as users for the blog. After much contemplation, it seems the only way is to share on social media or hope they seek it out, until...

    Wearing this shirt means no more worries about traffic jams, the more crowded it gets, the more fun it is because hundreds of eyes are watching 🤓

    (It really works, you know 🤭)

    » Read more
  • A cycle of developing many projects is quite interesting. Summarized in 3 steps: See something complex -> Simplify it -> Add features until it becomes complex again... -> Back to a new loop.

    Why is that? Let me give you 2 examples to illustrate.

    Markdown was created with the aim of producing a plain text format that is "easy to write, easy to read, and easy to convert into something like HTML." At that time, no one had the patience to sit and write while also adding formatting for how the text displayed on the web. Yet now, people are "stuffing" or creating variations based on markdown to add so many new formats that… they can’t even remember all the syntax.

    React is also an example. Since the time of PHP, there has been a desire to create something that clearly separates the user interface from the core logic processing of applications into two distinct parts for better readability and writing. The result is that UI/UX libraries have developed very robustly, providing excellent user interaction, while the application logic resides on a separate server. The duo of Front-end and Back-end emerged from this, with the indispensable REST API waiter. Yet now, React doesn’t look much different from PHP, leading to Vue, Svelte... all converging back to a single point.

    However, the loop is not bad; on the contrary, this loop is more about evolution than "regression." Sometimes, it creates something good from something old, and people rely on that goodness to continue the loop. In other words, it’s about distilling the essence little by little 😁

    » Read more

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!.

Premium
Hello

The secret stack of Blog

As a developer, are you curious about the technology secrets or the technical debts of this blog? All secrets will be revealed in the article below. What are you waiting for, click now!

As a developer, are you curious about the technology secrets or the technical debts of this blog? All secrets will be revealed in the article below. What are you waiting for, click now!

View all

Subscribe to receive new article notifications

or
* The summary newsletter is sent every 1-2 weeks, cancel anytime.

Comments (0)

Leave a comment...