SQL to ReQL cheat sheet

Data Modeling Illustration

 

Terminology

SQL and RethinkDB share very similar terminology. Below is a table of terms and concepts in the two systems.

SQL RethinkDB
database database
table table
row document
column field
table joins table joins
primary key primary key (by default id)
index index

INSERT

This is a list of queries for inserting data into a database.

SQLReQL
 
INSERT INTO users(user_id,
                  age,
                  name)
VALUES ("f62255a8259f",
        30,
        Peter)
 
r.table("users").insert({
   :user_id => "f62255a8259f",
   :age => 30,
   :name => "Peter"
})

SELECT

This is a list of queries for selecting data out of a database.

SQLReQL
 
SELECT * FROM users
 
r.table("users")
SELECT user_id, name FROM users
 
r.table("users")
 .pluck("user_id", "name")
SELECT * FROM users
WHERE name = "Peter"
 
r.table("users").filter({
    :name => "Peter"
})

If you have a secondary index built on the field name, you can run a more efficient query:

r.table("users")
    .get_all("Peter", :index => "name")
SELECT * FROM users
WHERE name = "Peter"
AND age = 30
 
r.table("users").filter({
    :name => "Peter",
    :age => 30
})
SELECT * FROM users
WHERE name LIKE "P%"
 
r.table("users").filter{ |row|
    row['name'].match("^P")
}
SELECT * FROM users
ORDER BY name ASC
 
r.table("users").order_by("name")
SELECT * FROM users
ORDER BY name DESC
 
r.table("users").order_by(
    r.desc("name")
)
SELECT user_id FROM users
WHERE name = "Peter"
ORDER BY name DESC
 
r.table("users").filter({
    :name => "Peter"
}).order_by(
    r.desc("name")
).pluck("user_id")
SELECT * FROM users LIMIT 5 SKIP 10
 
r.table("users").skip(10).limit(5)
SELECT * FROM users
WHERE name IN ('Peter', 'John')
 
r.table("users").filter{ |doc|
    r.expr(["Peter", "John"])
        .contains(doc["name"])
}

If you have a secondary index built on the field name, you can run a more efficient query:

r.table("users")
    .get_all("Peter", "John",
        :index => "name")
SELECT * FROM users
WHERE name NOT IN ('Peter', 'John')
 
r.table("users").filter{ |doc|
    r.expr(["Peter", "John"])
        .contains(doc["name"])
        .not()
}
SELECT COUNT(*) FROM users
 
r.table("users").count()
SELECT COUNT(name) FROM users
WHERE age > 18
 
r.table("users").filter{ |doc|
    (doc.has_fields("name") & doc["age"] > 18)
}.count()
SELECT AVG("age")
    FROM users
 
r.table("users")
 .avg("age")
SELECT MAX("age")
    FROM users
 
r.table("users")["age"]
 .max()
SELECT DISTINCT(name) FROM users
 
r.table("users").pluck("name").distinct()
SELECT *
    FROM users
    WHERE age BETWEEN 18 AND 65;
 
r.table("users").filter{ |doc|
    (doc["age"] >= 18) & (doc["age"] <= 65)
}
If you have a secondary index built on the field age, you can run a more efficient query: 
r.table("users")
    .between(18, 65, :index => "age")
SELECT name, 'is_adult' = CASE
    WHEN age>18 THEN 'yes'
    ELSE 'no'
    END
FROM users
 
r.table("users").map{ |user|
    {
        :name => user["name"],
        :is_adult => r.branch(
            user["age"] > 18
            "yes",
            "no"
        )
    }
}
SELECT *
  FROM posts
  WHERE EXISTS
    (SELECT * FROM users
     WHERE posts.author_id
         = users.id)
 
r.table("posts")
  .filter{ |post|
    r.table("users")
      .filter{ |user|
        user.id == post.author_id
      }.count() > 0
    }

UPDATE

This is a list of commands for updating data in the database.

SQLReQL
 
UPDATE users
    SET age = 18
    WHERE age < 18
 
r.table("users").filter{ |doc|
    doc["age"] < 18
}.update({
    :age => 18
})
UPDATE users
    SET age = age+1
 
r.table("users").update{ |doc|
    { :age => doc["age"]+1 }
}

DELETE

This is a list of queries for deleting data from the database.

SQLReQL
 
DELETE FROM users
 
r.table("users").delete()
DELETE FROM users
WHERE age < 18
 
r.table("users").filter{ |doc|
    doc["age"] < 18
}.delete()

JOINS

This is a list of queries for performing joins between multiple tables.

SQLReQL
 
SELECT *
FROM posts
JOIN users
ON posts.user_id = users.id
 
r.table("posts").inner_join(
    r.table("users")
) { |post, user|
    post["user_id"] == user["id"]
}.zip()

Note: zip() will merge the user in the post, overwriting fields in case of conflict.

If you have an index (primary key or secondary index) built on the field of the right table, you can perform a more efficient join with eq_join.

r.table("posts").eq_join("id",
    r.table("users"),
    :index => "id"
).zip()
SELECT posts.id AS post_id,
       user.name,
       users.id AS user_id
    FROM posts
    JOIN users
        ON posts.user_id = users.id

SELECT posts.id AS post_id,
       user.name,
       users.id AS user_id
    FROM posts
    INNER JOIN users
        ON posts.user_id = users.id
 
r.table("posts").inner_join(
  r.table("users")
) { |post, user|
    post["user_id"] == user["id"]
}.map { |post, user|
  :post_id => post["id"],
  :user_id => user["id"],
  :name => user["name"]
}
SELECT *
    FROM posts
    RIGHT JOIN users
        ON posts.user_id = users.id

SELECT *
    FROM posts
    RIGHT OUTER JOIN users
        ON posts.user_id = users.id
 
r.table("posts").outer_join(
    r.table("users")
) { |post, user|
        post["user_id"] == user["id"]
}.zip()

Note: You can perform more efficient OUTER JOIN operations with the concat_map command.

r.table("posts").concat_map{ |post|
  r.table("users").get_all(
    post["id"], :index => "id"
  ).do{ |results| r.branch(
    results.count() == 0,
    [{:left => post}],
    results.map { |user|
      {:left => post, :right => user}
    }
  )}
}.zip()
SELECT *
    FROM posts
    LEFT JOIN users
        ON posts.user_id = users.id

SELECT *
    FROM posts
    LEFT OUTER JOIN users
        ON posts.user_id = users.id
 
r.table("posts").outer_join(
    r.table("users")
) { |user, post|
        post["user_id"] == user["id"]
}.zip()
r.table("posts").concat_map{ |post|
  r.table("users").get_all(
    post["id"], :index => "id"
  ).do{ |results| r.branch(
    results.count() == 0,
    [{:left => user}],
    results.map { |post|
      {:left => user, :right => post}
    }
  )}
}.zip()

AGGREGATIONS

This is a list of queries for performing data aggregation.

SQLReQL
 
SELECT category
    FROM posts
    GROUP BY category
 
r.table("posts").map{ |doc|
    doc["category"]
}.distinct()
SELECT category,
       SUM('num_comments')
    FROM posts
    GROUP BY category
 
r.table('posts')
 .group('category')
 .sum('num_comments')
SELECT category,
       status,
       SUM('num_comments')
    FROM posts
    GROUP BY category, status
 
r.table("posts")
 .group('category', 'status')
 .sum('num_comments')
SELECT category,
       SUM(num_comments)
    FROM posts
    WHERE num_comments > 7
    GROUP BY category
 
r.table("posts").filter{ |doc|
    doc['num_comments'] > 7
}.group('category')
 .sum('num_comments')
SELECT category,
       SUM(num_comments)
    FROM posts
    GROUP BY category
    HAVING num_comments > 7
 
r.table("posts")
 .group('category')
 .sum('num_comments')
 .ungroup()
 .filter{ |doc|
   doc["reduction"] > 7
  }
SELECT title,
        COUNT(title)
    FROM movies
    GROUP BY title
    HAVING COUNT(title) > 1
 
r.table("movies")
 .group("title")
 .count()
 .ungroup()
 .filter{ |doc|
   doc["reduction"] > 1
  }

TABLE and DATABASE manipulation

This is a list of queries for creating and dropping tables and databases.

SQLReQL
 
CREATE DATABASE my_database;
 
r.db_create('my_database')
DROP DATABASE my_database;
 
r.db_drop('my_database')
CREATE TABLE users
    (id INT IDENTITY(1,1) PRIMARY KEY,
    name VARCHAR(50),
    age INT);
 
r.table_create('users',
    :primary_key => "id")

Note: RethinkDB is a NoSQL database and does not enforce schemas.

Note: The default primary key is id

TRUNCATE TABLE users;
 
r.table("users").delete()
DROP TABLE users;
 
r.table_drop("users")

Read More

Browse the following resources to learn more about ReQL: