SQL to ReQL cheat sheet
SQL to ReQL cheat sheet
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.
SQL | ReQL |
---|---|
INSERT INTO users(user_id, age, name) VALUES ("f62255a8259f", 30, Peter) | r.table("users").insert({ userId: "f62255a8259f", age: 30, name: "Peter" }) |
SELECT
This is a list of queries for selecting data out of a database.
SQL | ReQL |
---|---|
SELECT * FROM users |
r.table("users") |
SELECT user_id, name FROM users |
r.table("users") .pluck("userId", "name") |
SELECT * FROM users WHERE name = "Peter" |
r.table("users").filter({ name: "Peter" }) If you have a secondary index built on the field r.table("users") .getAll("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( r.row("name").match("^P") ) |
SELECT * FROM users ORDER BY name ASC |
r.table("users").orderBy("name") |
SELECT * FROM users ORDER BY name DESC |
r.table("users").orderBy( r.desc("name") ) |
SELECT user_id FROM users WHERE name = "Peter" ORDER BY name DESC |
r.table("users").filter({ name: "Peter" }).orderBy( r.desc("name") ).pluck("userId") |
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( function (doc) { return r.expr(["Peter","John"]) .contains(doc("name")); } ) If you have a secondary index built on the field r.table("users") .getAll("Peter", "John", {index: "name"}) |
SELECT * FROM users WHERE name NOT IN ('Peter', 'John') |
r.table("users").filter( function (doc) { return 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( r.row.hasFields("name") .and(r.row("age").gt(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( r.row("age").ge(18) .and(r.row("age").le(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({ name: r.row("name"), is_adult: r.branch( r.row("age").gt(18), "yes", "no" ) }) |
SELECT * FROM posts WHERE EXISTS (SELECT * FROM users WHERE posts.author_id = users.id) |
r.table("posts") .filter(function (post) { return r.table("users") .filter(function (user) { return user("id").eq(post("authorId")) }).count().gt(0) }) |
UPDATE
This is a list of commands for updating data in the database.
SQL | ReQL |
---|---|
UPDATE users SET age = 18 WHERE age < 18 |
r.table("users").filter( r.row("age").lt(18) ).update({age: 18}) |
UPDATE users SET age = age+1 |
r.table("users").update( {age: r.row("age").add(1)} ) |
DELETE
This is a list of queries for deleting data from the database.
SQL | ReQL |
---|---|
DELETE FROM users |
r.table("users").delete() |
DELETE FROM users WHERE age < 18 |
r.table("users") .filter(r.row("age").lt(18)) .delete() |
JOINS
This is a list of queries for performing joins between multiple tables.
SQL | ReQL |
---|---|
SELECT * FROM posts JOIN users ON posts.user_id = users.id |
r.table("posts").innerJoin( r.table("users"), function (post, user) { return post("userId").eq(user("id")); }).zip() Note: 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 eqJoin. r.table("posts").eqJoin( "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").innerJoin( r.table("users"), function (post, user) { return post("userId").eq(user("id")); }).map({ postId: r.row("left")("id"), userId: r.row("right")("id"), name: r.row("right")("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").outerJoin( r.table("users"), function (post, user) { return post("userId").eq(user("id")); }).zip() Note: You can perform more efficient r.table("posts").concatMap( function (post) { return r.table("users") .getAll(post("id"), {index: id}) .do( function (result) { return r.branch( result.count().eq(0), [{left: post}], result.map(function (user) { return { 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("users").outerJoin( r.table("posts"), function (user, post) { return post("userId").eq(user("id")); } ).zip() r.table("users").concatMap( function (user) { return r.table("posts").getAll(user("id"), {index: "id"}).do( function (results) { return r.branch( results.count().eq(0), [{left: user}], results.map(function (post) { return {left: user, right: post}; }) ); } ); } ).zip() |
AGGREGATIONS
This is a list of queries for performing data aggregation.
SQL | ReQL |
---|---|
SELECT category FROM posts GROUP BY category |
r.table("posts").map( r.row("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(r.row('num_comments').gt(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(r.row("reduction").gt(7)) |
SELECT title, COUNT(title) FROM movies GROUP BY title HAVING COUNT(title) > 1 |
r.table("movies") .group("title") .count() .ungroup() .filter(r.row("reduction").gt(1)) |
TABLE and DATABASE manipulation
This is a list of queries for creating and dropping tables and databases.
SQL | ReQL |
---|---|
CREATE DATABASE my_database; |
r.dbCreate('my_database') |
DROP DATABASE my_database; |
r.dbDrop('my_database') |
CREATE TABLE users (id INT IDENTITY(1,1) PRIMARY KEY, name VARCHAR(50), age INT); |
r.tableCreate('users', {primaryKey: "id"}) Note: RethinkDB is a NoSQL database and does not enforce schemas. Note: The default primary key is |
TRUNCATE TABLE users; |
r.table("users").delete() |
DROP TABLE users; |
r.tableDrop("users") |
Read More
Browse the following resources to learn more about ReQL:
© RethinkDB contributors
Licensed under the Creative Commons Attribution-ShareAlike 3.0 Unported License.
https://rethinkdb.com/docs/sql-to-reql/javascript/