INSERT INTO users(user_id,
                  age,
                  name)
VALUES ("f62255a8259f",
        30,
        Peter)

r.table("users").insert({
   "user_id": "f62255a8259f",
   "age": 30,
   "name": "Peter"
})

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(
    r.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(lambda 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(lambda 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(
    (r.row.has_fields("name"))
     & (r.row["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(
    (r.row["age"] >= 18)
    & (r.row["age"] <= 65))

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"] > 18,
        "yes",
        "no"
    )
})

SELECT *
  FROM posts
  WHERE EXISTS
    (SELECT * FROM users
     WHERE posts.author_id
         = users.id)

r.table("posts")
  .filter(lambda post:
    r.table("users")
      .filter( lambda user:
        user.id == post.author_id
      ).count() > 0
    )

UPDATE users
    SET age = 18
    WHERE age < 18

r.table("users").filter(
    r.row["age"] < 18
).update({
    "age": 18
})

UPDATE users
    SET age = age+1

r.table("users").update(
    { "age": r.row["age"]+1 }
)

DELETE FROM users

r.table("users").delete()

DELETE FROM users
WHERE age < 18

r.table("users")
    .filter( r.row["age"] < 18)
    .delete()

SELECT *
FROM posts
JOIN users
ON posts.user_id = users.id

r.table("posts").inner_join(
    r.table("users"),
    lambda 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"),
  lambda post, user:
    post["user_id"] == user["id"]
).map({
  "post_id": r.row["left"]["id"],
  "user_id": 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").outer_join(
    r.table("users"),
    lambda 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(lambda post:
  r.table("users")
    .get_all(post["id"],index="id")
    .do( lambda results:
      r.branch(
        results.count() == 0,
        [{"left": post}],
        results.map( lambda 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("users").outer_join(
    r.table("posts"),
    lambda user, post:
        post.user_id == user.id
).zip()

r.table("users").concat_map(lambda user:
  r.table("posts")
    .get_all(user["id"],index="id")
    .do( lambda results:
      r.branch(
        results.count() == 0,
        [{"left": user}],
        results.map( lambda post:
          {
            "left": user
            "right": post
          }
        )
      )
    )
).zip()

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']>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"] > 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"] > 1)

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")