This the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

What is Genji?

Genji is a document-oriented, embedded, SQL database written in Go.

It combines the power of SQL with the versatility of documents to provide a maximum of flexibility with no compromise.

Here is a list of Genji’s main features:

  • Optional schemas: Genji tables are schemaless, but it is possible to add constraints on any field to ensure the coherence of data within a table.
  • Multiple Storage Engines: It is possible to store data on disk or in ram, but also to choose between B-Trees and LSM trees. Genji relies on BoltDB and Badger to manage data.
  • Transaction support: Read-only and read/write transactions are supported by default.
  • SQL and Documents: Genji mixes the best of both worlds by combining powerful SQL commands with JSON dot notation.
  • Easy to use, easy to learn: Genji was designed for simplicity in mind. It is really easy to insert and read documents of any shape.

Concepts

Genji’s main concepts are not new and semantics have been chosen to match as much as possible what is already existing in other databases:

Classic SQL databases Genji
Table Table
Row Document
Column Field
Schema Field constraints
  • Table: A collection of documents. Tables are schemaless by default and support optional
  • Document: A list of fields
  • Field: A key-value pair
  • Field constraint: A constraint validated against a field of every inserted or updated document

1 - Getting started

How to install and use Genji

Prerequisites

Genji requires at least Go 1.16.

Installation

To install the Genji database, run this command:

go get github.com/genjidb/genji

Golang API documentation

To learn how to embed Genji in your Go code, follow the instructions in the Go package documentation.

Try it out!

To try Genji without writing code, you can use the Genji command-line shell.

First, install it:

go get github.com/genjidb/genji/cmd/genji

To open an in-memory database, simply type:

genji

You can then enter your SQL queries directly in the shell.

It is also possible to create an on-disk database, using either BoltDB or Badger.

On-disk database using BoltDB

Run the genji command followed by the name of the database file of your choice.

genji my.db

On-disk database using Badger

Run the following command by replacing pathToDBDir by the directory of your choice.

genji --badger pathToDBDir

Next step

Once Genji is setup, follow the Genji SQL chapter to learn how to run queries.

2 - Essentials

How to create and manage database structures using Genji SQL syntax

2.1 - Table Basics

How to create and remove tables

Though Genji stores its data in tables, there is no concept of rows or columns. A Genji table is simply a collection of documents.

Each document is assigned to a primary key, which is a unique identifier.

The order in which documents are returned when reading a table is not guaranteed unless sorted explicitly.

Unlike relational databases, tables are schemaless, there is no need to specify a schema when creating one. This means that, by default, documents stored in a table can be completely different from one another. Optionally, it is possible to define constraints on a list of fields, to control their type, if they are required or not, if they can be null, etc. for every document of a table.

To create a table with no constraints, use the CREATE TABLE command.

CREATE TABLE teams;

This will create a table teams that can hold any document. An auto-incrementing primary key will be generated every time a document is inserted.

Creating a table with constraints uses a notation that is close to other relational databases.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER
    address.zipCode TEXT
)

This will create a table users with the following constraints:

  • All documents must have a non-empty id field, whose type can be converted to an integer. This field will be used as the primary key of the table and will be stored as an integer.
  • All documents must have a non-empty name field that can be converted to TEXT.
  • If a document has an age field, it will be converted to an integer.
  • If a document has an address field and its value is a document with a zipCode field, then its value will be converted to TEXT. Note that this constraint uses field references.

Unlike relational databases though, a document doesn’t have to contain only the fields described in the constraint list. A constraint only applies to its associated field.

CREATE TABLE will return an error if the table already exists.

To remove a table and all of its content, use the DROP TABLE command:

DROP TABLE users

This will remove the users table and all of its documents. If DROP TABLE is called on a non-existing table, it will return an error.

2.2 - Documents

Description of documents

Genji stores records as documents. A document is an object that contains pairs that associate a string field to a value of any type. Genji SQL represents documents as JSON objects, though they support far more types.

Here is a JSON representation of the structure of a document:

{
    field1: value1,
    field2: value2,
    field3: value3,
    ...
}

Example of a document using Genji SQL syntax:

{
    name: "Nintendo Switch",
    price: {
        base: 379.99,
        vat: 20,
        total: base + base * vat / 100
    },
    brand: "Nintendo",
    "top-selling-games": [
        "Mario Odyssey",
        "Zelda Breath of the Wild"
    ]
}

Each field name must be a string, but values can be of any type, including another document, an array or an expression.

Any JSON object is a valid document and can be inserted as-is.

Field names

Field names can be any string, with only one exception: they cannot be empty.

Paths

A path is a way to refer to fields of a document or elements of an array.

Given the following document:

{
    "name": "Foo",
    "address": {
        "city": "Lyon",
        "zipcode": "69001"
    },
    "friends": [
      {
        "name": "Bar",
        "address": {
            "city": "Paris",
            "zipcode": "75001"
        }
      },
        {
          "name": "Baz",
          "address": {
              "city": "Ajaccio",
              "zipcode": "20000"
          },
          "favorite game": "FF IX"
        }
    ]
}

Accessing a top-level field can be achieved by simply referring to its name.

Example: name will evaluate to "Foo".

To access a nested field, concatenate all the fields with the . character.

Examples: address.city will evaluate to "Lyon"

To access an element of an array, use the index of the element

Examples:

  • friends[0] will evaluate to {"name": "Bar","address": {"city":"Paris","zipcode": "75001"}}
  • friends[1].name will evaluate to "Baz"
  • friends[1]."favorite game" will evaluate to "ffix"

2.3 - Data Types

This chapter describes the list of data types

Genji provides a list of simple data types to store and manipulate data.

Name Description From To
BOOL Can be either true or false false true
INTEGER Signed integer which takes 1, 2, 4 or 8 bytes depending on the size of the stored number -9223372036854775808 9223372036854775807
DOUBLE 8 bytes decimal -1.7976931348623157e+308 1.7976931348623157e+308
BLOB Variable size BLOB of data
TEXT Variable size UTF-8 encoded string
ARRAY ARRAY of values of any type
DOCUMENT Object that contains pairs that associate a string field to a value of any type

The case of NULL

In Genji, NULL is treated as both a value and a type. It represents the absence of data, and is returned in various cases:

  • when selecting a field that doesn’t exists
  • when selecting a field whose value is NULL
  • as the result of the evaluation of an expression

Conversion

Whenever Genji needs to manipulate data of different types, depending on the situation it will rely on either:

  • explicit conversion: The source type and destination type are clearly identified. Ex: When inserting data to field with a constraint or when doing a CAST.
  • implicit conversion: Two values of different types need to be compared or used by an operator during the evaluation of an expression

Explicit conversion

Explicit conversion is used when we want to convert a value of a source type into a target type. However, Genji types are not all compatible with one another, and when a user tries to convert them, Genji returns an error. Here is a table describing type compatibility.

Source type Target type Converted Example
BOOL INTEGER yes, 1 if true, otherwise 0 CAST(true AS INTEGER) -> 1
BOOL TEXT yes, 'true' if true, otherwise 'false' CAST(true AS TEXT) -> 'true'
INTEGER BOOL yes, false if zero, otherwise true CAST(10 AS BOOL) -> true
INTEGER DOUBLE yes CAST(10 AS DOUBLE) -> 10.0
INTEGER TEXT yes CAST(10 AS TEXT) -> '10'
DOUBLE INTEGER yes, cuts off the decimal part CAST(10.5 AS DOUBLE) -> 10
DOUBLE TEXT yes CAST(10.5 AS DOUBLE) -> '10.5'
TEXT BOOL yes, if the content is a valid boolean CAST('true' AS BOOL) -> true
TEXT INTEGER yes, if the content is a valid integer CAST('10' AS INTEGER) -> 10
TEXT DOUBLE yes, if the content is a valid decimal number CAST('10.4' AS DOUBLE) -> 10.4
TEXT BLOB yes, if the content is a valid base64 value CAST('aGVsbG8K' AS BLOB) -> 'aGVsbG8K'
TEXT ARRAY yes, if the content is a valid json array CAST('[1, 2, 3]' AS ARRAY) -> [1, 2, 3]
TEXT DOCUMENT yes, if the content is a valid json object CAST('{"a": 1}' AS DOCUMENT) -> {"a": 1}
BLOB TEXT yes, the content will be encoded in base64
ARRAY TEXT yes, the content will be encoded as a json array CAST([1, 2, 3] AS TEXT) -> '[1, 2, 3]'
DOCUMENT TEXT yes, the content will be encoded as a json object CAST({a: 1} AS DOUBLE) -> '{"a": 1}'
NULL any type yes, NULL CAST(NULL AS DOUBLE) -> NULL

Implicit conversion

There is only one kind of implicit conversion: INTEGER to DOUBLE. This usually takes place during the evaluation of an expression involving INTEGER and DOUBLE values. No other conversion is applied unless it’s explicit.

2.4 - Expressions

How expression are evaluated, compared, etc.

Expressions are components that can be evaluated to a value.

Example:

1 + 1 # expression
-> 2  # result

An expression can be found in two forms:

  • unary: meaning it contains only one component
  • binary: meaning it contains two expressions, or operands, and one operator. i.e. <expr> <operator> <expr>

Example:

/* Unary expressions */
1
name
"foo"

/* Binary expressions */
age >= 18
1 AND 0

Here is a list of all supported expressions:

Diagram(
  Choice(
    0,
    Link("literal-value"),
    Link("parameter"),
    Link("field-path"),
    Sequence("NOT", Link("expr")),
    Sequence(Link("expr"), Link("binary-operator", "#operators"), Link("expr")),
    Sequence(
      Link("expr"),
      Optional("NOT"),
      Choice(
        0,
        Link("IN", "#comparison-operators"),
        Link("LIKE", "#comparison-operators")
      ),
      Link("expr")
    ),
    Sequence(
      Link("expr"),
      Link("IS", "#comparison-operators"),
      Optional("NOT"),
      Link("expr")
    ),
    Sequence(
      Link("expr"),
      Optional("NOT"),
      Link("BETWEEN", "#comparison-operators"),
      Link("expr"),
      "AND",
      Link("expr")
    ),
    Sequence(
      Link("function", "#functions"),
      "(",
      OneOrMore(Link("expr"), ","),
      ")"
    ),
    Sequence("CAST", "(", Link("expr"), "AS", "type-name", ")"),
    Sequence("NEXT", "VALUE", "FOR", "sequence-name"),
    Sequence("(", Link("expr"), ")")
  )
);

Literal value

Any literal evaluates to the closest compatible type.

Diagram(
  Choice(
    0,
    Link("integer-literal"),
    Link("number-literal"),
    Link("string-literal"),
    Link("blob-literal"),
    Link("bool-literal"),
    Link("array-literal"),
    Link("document-literal"),
    "NULL"
  )
);

Integer literal

Diagram(Sequence(Optional(Choice(0, "+", "-")), OneOrMore("digit")));

An integer literal is a sequence of characters that only contain digits. They may start with a + or - sign.

123456789
+100
-455

Integer literals are evaluated into the INTEGER type.

If an integer is bigger than the maximum value of a 64 bit integer or smaller than the minimum 64 bit integer value, it will be evaluated as a DOUBLE.

Number literal

Diagram(
  Sequence(
    Choice(
      0,
      Sequence(OneOrMore("digit")),
      Sequence(OneOrMore("digit"), ".", OneOrMore("digit")),
      Sequence(".", OneOrMore("digit"))
    ),
    Optional(
      Sequence(
        Choice(0, "e", "E"),
        Optional(Choice(0, "+", "-")),
        OneOrMore("digit")
      )
    )
  )
);

A number literal is a sequence of characters that contains three parts:

  • a sequence of digits
  • a decimal point (i.e. .)
  • a sequence of digits

They may start with a + or a - sign.

123.456
+3.14
-1.0

Number literals are evaluated to the DOUBLE type.

String literal

Diagram(
  Choice(
    0,
    Sequence('"', ZeroOrMore("unicode-character"), '"'),
    Sequence("'", ZeroOrMore("unicode-character"), "'")
  )
);

A string literal is a sequence of utf-8 encoded characters surrounded by double or single quotes. They may contain any unicode character or escaped single or double quotes (i.e \' or \").

"l'école des fans"
'(╯ಠ_ಠ)╯︵ ┳━┳'
'foo \''

String literals are evaluated to the TEXT type.

Blob literal

Diagram(
  Choice(
    0,
    Sequence('"\\x', ZeroOrMore("hexadecimal-character"), '"'),
    Sequence("'\\x", ZeroOrMore("hexadecimal-character"), "'")
  )
);

A blob literal starts with \x followed by a list of hexadecimal characters which is then decoded into raw bytes by Genji.

Blob literals are evaluated to the BLOB type.

Bool literal

Diagram(Choice(0, "TRUE", "FALSE"));

A boolean literal is any sequence of character that is written as true or false, regardless of the case.

true
false
TRUE
FALSE
tRUe
FALse

Boolean literals are evaluated into the BOOL type.

Array literal

Diagram(
  Choice(
    0,
    Sequence("[", OneOrMore(Link("expr"), ","), "]"),
    Sequence("(", OneOrMore(Link("expr"), ","), ")")
  )
);

An array literal is any sequence of character that starts and ends with either:

  • [ and ]
  • ( and )

and that contains a coma-separated list of expressions.

[1.5, "hello", 1 > 10, [true, -10], {foo: "bar"}]

Array literals are evaluated into the ARRAY type.

Document literal

Diagram(
  "{",
  OneOrMore(
    Sequence(Choice(0, "identifier", "string"), ":", Link("expr")),
    ","
  ),
  "}"
);

A document is any sequence of character that starts and ends with { and } and that contains a list of pairs. Each pair associates an identifier with an expression, both separated by a colon. Each pair must be separated by a coma.

{
    foo: 1,
    bar: "hello",
    baz: true AND false,
    "long field": {
        a: 10
    }
}

In a document, the identifiers are referred to as fields. In the example above, the document has four top-level fields (foo, bar, baz and long field) and one nested field a.

Note that any JSON object is a valid document.

Document literals are evaluated into the DOCUMENT type.

Identifier

Diagram(
  Choice(
    0,
    OneOrMore(Choice(0, "ascii-letter", "digit", "_")),
    Sequence("`", OneOrMore("unicode-character"), "`")
  )
);

Identifiers are a sequence of characters that refer to table names, field names and index names.

Identifiers may be unquoted or surrounded by backquotes. Depending on that, different rules may apply.

Unquoted identifiers Identifiers surrounded by backquotes
Must begin with an uppercase or lowercase ASCII character or an underscore May contain any unicode character, other than the new line character (i.e. \n)
May contain only ASCII letters, digits and underscore May contain escaped " character (i.e. \")
foo
_foo_123_
`頂きます (*`▽´)_旦~~`
`foo \` bar`

Field path

Diagram(
  Sequence(
    "identifier",
    Optional(
      OneOrMore(
        Choice(
          0,
          Sequence(".", "identifier"),
          Sequence("[", "integer-literal", "]"),
          Sequence("[", "string-literal", "]")
        ),
        ","
      ),
      "skip"
    )
  )
);

A field path is any sequence of characters that contains one or more identifiers separated by dots or square brackets.

foo
foo.bar[10]
foo["long field"][0].bat.`other long field`

Depending on the context, a single identifier with no dot or square bracket will be parsed as an identifier or as a field path.

Field paths are evaluated into the value they refer to. They are used to select a value from a document. Their type will depend on the type of the value extracted from the document.

Given the following document:

{
    "recipes": 10,
    "cooking-time": {
        "eggs": [3, 6, 9]
    },
}

Here are examples on how field paths are evaluated:

recipes
-> 10

`cooking-time`
->  {
        "eggs": [
            3,
            6,
            9
        ]
    }

`cooking-time`.eggs[2]
-> 9

`cooking-time`.eggs[10]
-> NULL

Parameter

Diagram(Choice(0, "?", "$identifier"));

A parameters is an expressions used to represent a value passed when the query is evaluated. Genji supports two types of parameters:

  • Positional parameters: ?
  • Named parameters: $ followed by an identifier

Functions

Diagram(
  Sequence(
    "identifier",
    Optional(Sequence(".", "identifier"), "skip"),
    "(",
    ZeroOrMore(Link("expr"), ","),
    ")"
  )
);

A function name is an expression that represent a builtin function. It can either represent a global function or a function within a package.

count()
typeof("hello")
math.atan2(1.1, 1.1)

Operators

Diagram(
  Choice(
    0,
    "||",
    "*",
    "/",
    "%",
    "+",
    "-",
    "|",
    "&",
    "^",
    ">",
    ">=",
    "<",
    "<=",
    "=",
    "!=",
    "IS",
    "IN",
    "LIKE",
    "AND",
    "OR"
  )
);

Genji provides a list of operators that can be used to compute operations with expressions. Operators are binary expressions, meaning they always take exactly two operands. It is possible though to combine multiple operators to create an evaluation tree.

Logical operators

Logical operators are operators that return a boolean under certain conditions.

Name Description
AND Evaluates to true if both operands are truthy
OR Evaluates to true if either the left operand or the right are truthy

An expression is truthy if it evaluates to a non zero-value of its type.

Comparison operators

These operators are used to compare values and evaluate to a boolean.

Name Description
= Evaluates to true if operands are equal, otherwise returns false
!= Evaluates to true if operands are not equal, otherwise returns false
> Evaluates to true if the left-side expression is greater than the right-side expression, otherwise returns false
>= Evaluates to true if the left-side expression is greater than or equal to the right-side expression, otherwise returns false
< Evaluates to true if the left-side expression is less than the right-side expression, otherwise returns false
<= Evaluates to true if the left-side expression is less than or equal to the right-side expression, otherwise returns false
IN Evaluates to true if the left-side expression is equal to one of the values of the right-side array
NOT IN Evaluates to false if the left-side expression is equal to one of the values of the right-side array
IS Has the same behaviour as = except that it returns true if both operands are NULL
IS NOT Has the same behaviour as != except that it supports comparing with NULL
BETWEEN Evaluates to true if the left-side expression is between the two boundaries

Examples:

1 = 1
-> true

1 > 2.5
-> false

3 IN [1, 2, 3]
-> true

5 BETWEEN 2 AND 10
-> true

Conversion during comparison

Prior to comparison, an implicit conversion is operated for the operands to be of the same type. Not all types can be compared together. When two incompatible types are compared, the comparison always returns false, except if one of the operands is NULL, in that case it returns NULL.

Example:

1 > "hello"
-> false
1 < "hello"
-> false

The comparison follows a list of rules that are executed in order:

  • If one of the operands is NULL, return NULL.
  • If both operands are documents, use the Comparing documents rule
  • If both operands are arrays, use the Comparing arrays rule
  • If both operands are numbers (INTEGER or DOUBLE), cast the integer to DOUBLE then compare them together.
  • If both operands have the same type, compare them together.

In any other case, return false.

Comparing documents

The fields of each document are sorted, then they are compared one by one, until they are found not equal. The comparison is then determined by the result of the comparison between these two values. If both keys are equal, compare the values.

{a: 1, b: 2} = {b: 2, a: 1}
-> true

{} = {}
-> true

{a: 1, b: 3} > {a: 1, b: 2}
-> true

{a: 100} > {aa: 1}
-> false

Comparing arrays

Each elements of both arrays are compared one by one, index by index, until they are found not equal. The comparison is then determined by the result of the comparison between these two values.

[1, 2, 3] > [1, 1 + 1, 1]
-> true

Let’s break down the example above:

  1. Index 0: 1 and 1 are equal, the comparison continues
  2. Index 1: 2 and 1 + 1 are equal, the comparison continues
  3. Index 2: 3 is greater then 1, the comparison stops and the first array is considered greater than the second one

Two empty arrays are considered equal:

[] = []
-> true

The size of arrays doesn’t matter, unless all the elements of the smallest one are equal to the other one. In that case the biggest array is considered greater.

[3] > [1, 100000]
-> true

[1, 2] < [1, 2, 3]
-> true

Arithmetic operators

Name Description
+ Adding two values
- Substracting two values
* Multiplying two values
/ Dividing two values
% Find the remainder after division of one number by another
& Bitwise AND
| Bitwise OR
^ Bitwise XOR

Arithmetic operations are supported only for the following types:

  • INTEGER
  • DOUBLE

Note that INTEGER and DOUBLE types can be calculated together, in that case INTEGER values will be converted to DOUBLE prior the operation.

Any usage of these operators on incompatible types will return NULL.

3 + 3.5
-> 6.5

3 + '1'
-> NULL

The case of NULL

Any arithmetic operation with one of the operand being NULL returns NULL.

NULL + 1
-> NULL

5 * 10 - NULL
-> NULL

Division rules

The division obeys a few rules depending on the types of the operands:

  • Dividing two integers, always results in an integer
  • Dividing by zero, returns NULL

Return type and overflow

The type of the result of an operation doesn’t necessarily match the type of the operands.

  • The result of a DOUBLE operation will always return a DOUBLE
  • The result of an INTEGER operation will return an INTEGER, unless the return value is bigger than the maximum value of 64-bit integer. In that case, the return type will be a DOUBLE

Other operators

Name Description
|| Concatenation of two TEXT values

Evaluation tree and precedence

When parsed, an expression is turned into an evaluation tree so it is possible to combine operators to form complex expressions. The order in which these expressions are executed depends on the priority of the operator.

Here is the list of operators ordered by ascending precedence. Operators with higher precedence are executed before the ones with lower precedence

  • OR
  • AND
  • =, !=, <, <=, >, >=
  • +, -, |, ^
  • *, /, %, &
  • ||

Example:

3 + 4 * 2 > 10 AND 2 - 2 = false
-> true

This expression can be represented as the following tree:

.
└── AND
    ├── >
    │   ├── +
    │   │   ├── 3
    │   │   └── *
    │   │       ├── 4
    │   │       └── 2
    │   └── 10
    └── -
        ├── 2
        └── 2

The deepest branches will be executed first, recursively until reaching the root.

2.5 - Inserting Documents

How to use the INSERT statement to insert documents in a table

When a table is created, it contains no documents. The INSERT statement can be used to add one or more new documents to the table.

Inserting documents in tables with no field constraints

Consider a table created with the following statement:

CREATE TABLE users;

This table doesn’t have any constraint and thus can contain any kind of documents.

Let’s insert a document:

INSERT INTO users (name, age) VALUES ("Gon", 13);

Let’s break it down:

  • INSERT INTO users: tells Genji to run the statement on the users table
  • (name, age): lists the fields of the document we wish to create
  • VALUES ("Gon", 13): list the respective values of these fields in order

Here is the JSON representation of the document created by this statement:

{
  "name": "Gon",
  "age": 13
}

It is possible to create multiple documents in the same statement:

INSERT INTO users (name, age) VALUES ("Gon", 13), ("Kirua", 14);

This will create two documents in the users table:

{
    "name": "Gon",
    "age": 13
}
{
    "name": "Kirua",
    "age": 14
}

Until now, we created documents with the same shape, but nothing prevents us from inserting documents with different fields:

INSERT INTO users (name, address) VALUES ("Kurapika", {city: "York Shin City", "region": "Yorubian"});
INSERT INTO users (first_name, `last name`, skills) VALUES ("Zeno", 'Zoldik', ["Dragon Dive", "Dragon Head"] );

It is also possible to omit the list of fields and use a document literal:

INSERT INTO users VALUES {name: "Hisoka", "age": "unknown"}

Note that in this example, the age field type is TEXT. It’s because field types don’t have to match those of the documents created previously, documents are independent and self-contained.

Inserting documents in tables with field constraints

Now, let’s consider having the following table:

CREATE TABLE users (
    id INTEGER PRIMARY KEY NOT NULL,
    name TEXT NOT NULL,
    age INTEGER
)

Only documents satisfying the following field constraints can be inserted in the users table:

  • the document must have a non-null id field. It must be convertible to an INTEGER. Since this field will be used as the primary key of the table, each id must be unique.
  • the document must have a non-null name field. It must be convertible to a TEXT.
  • the document may have an age field. If it exists, it must be convertible to an INTEGER.
  • the document may have any other fields.

The conversion compatible table can be found in the data types page.

To see how it works, let’s try inserting invalid documents:

/* totally different fields */
INSERT INTO users (first_name, alias) VALUES ('Genthru', 'The Bomber');

Error:

field “id” is required and must be not null

Let’s add an id:

INSERT INTO users (id, first_name, alias) VALUES (1, 'Genthru', 'The Bomber');

Error:

field “name” is required and must be not null

This time, it complains about the name field which is absent. Let’s change that:

INSERT INTO users (id, name, alias) VALUES (1, 'Genthru', 'The Bomber');

It works!

Since age doesn’t have a NOT NULL clause, it didn’t complain.

Also, the document contains an alias field and Genji didn’t complain. Field constraints only apply on the field they are associated with, they don’t care about the other ones. That’s what makes Genji different from “schemaful” databases, where the schema describes exactly the number of columns a row must always have.

Let’s add another one with an age field:

INSERT INTO users (id, name, age) VALUES (1, 'Biscuit', 57);

Error:

duplicate document

This time we used the same id as before. Since 1 is already used by Genthru, let’s pick another one:

INSERT INTO users (id, name, age) VALUES (1, 'Biscuit', 57);

It works!

Inserting documents from another table

It is also possible to insert documents selected from another table, using the INSERT ... SELECT notation.

-- create a new table to store the list of all of our users names
CREATE TABLE names(name TEXT);

-- select all non-null names and store them in that new table.
INSERT INTO names SELECT name FROM users WHERE name IS NOT NULL;

2.6 - Updating Documents

How to use the UPDATE statement to modify documents in a table

The UPDATE statement makes it possible to update one or more documents in a table.

Consider a table users with the following documents in it.

{
    "name": "Koruto",
    "age": 1
}
{
    "name": "Leol",
    "group": "Chimera-Ant",
    "age": 2
}
UPDATE users SET group = "Chimera Ant"

Let’s break it down:

  • UPDATE users runs the UPDATE statement on the users table
  • SET indicates the list of changes we want to perform
  • group = "Chimera Ant" sets the group field of the document to the value “Chimera Ant”

Without a WHERE clause, this statement will run on all the documents of the table. Here is the state of the table after running this command:

{
    "name": "Koruto",
    "group": "Chimera Ant"
}
{
    "name": "Leol",
    "group": "Chimera Ant"
}

The first document didn’t have a group field before. It’s because the SET clause actually sets fields in the document, regardless of their existence. This is a good way to add new fields to documents.

Since we can add or modify fields using the SET clause, it is also possible to delete fields using the UNSET clause:

UPDATE users UNSET age;

This will delete the age field from all the documents. If the field doesn’t exist it does nothing.

To update only a subset of documents, we can use the WHERE clause. In the following example, only the documents that satisfy the age = 2 condition will be updated.

UPDATE users SET group = "Chimera Ant" WHERE age = 2;

2.7 - Deleting Documents

How to use the DELETE statement to delete documents from a table

Documents can be deleted using the DELETE statement.

Let’s start with the simplest form:

DELETE FROM users;

This command deletes all the documents of the users table.

To delete only a few documents, use the WHERE clause:

DELETE FROM users WHERE age > 13;

For every document, the WHERE clause evaluates any expression that follows, here age > 13. If the result is truthy, the document gets deleted.

The DELETE statement doesn’t return an error if no document matches the WHERE clause, or if there aren’t any document in the table.

2.8 - Selecting Documents

How to use the SELECT statement to query documents in a table

Querying documents from a table can be achieved by using the SELECT statement.

In the Genji database, a query does two things:

  • it reads documents from a table
  • it uses the arguments of the query to transform, filter and project that data to create a result, which is a stream of documents.

This stream of documents can be consumed by the caller one by one, and each document will contain the fields the user chose.

Consider the following table:

CREATE TABLE users;
INSERT INTO users (name, age, nen, parents, abilities) VALUES
    ('Gon', 13, 'Enhancement', {'father': 'Ging Freecs'}, ['Jajanken']),
    (
        'Kirua', 14, 'Transmutation',
        {'father': 'Silva Zoldyck', 'mother': 'Kikyo Zoldyck'},
        ['Lighning Palm', 'Thunderbolt', 'Godspeed']
    );
INSERT INTO users (name, nen, abilities) VALUES
    ('Hisoka', 'Transmutation', ['Bungee Gum', 'Texture Surprise']);

Querying all the documents

Selecting all users goes like this:

SELECT * FROM users;
{
    "name": "Gon",
    "age": 13,
    "nen": "Enhancement",
    "parents": {
        "father": "Ging Freecs"
    },
    "abilities": ["Jajanken"]
}
{
    "name": "Kirua",
    "age": 14,
    "nen": "Transmutation",
    "parents": {
        "father": "Silva Zoldyck",
        "mother": "Kikyo Zoldyck"
    },
    "abilities": ["Lighning Palm", "Thunderbolt", "Godspeed"]
}
{
    "name": "Hisoka",
    "nen": "Transmutation",
    "abilities": ["Bungee Gum", "Texture Surprise"]
}

Let’s break it down:

  • SELECT: Run the SELECT command
  • *: This is the projection, it indicates how to build the documents returned by the result of the query. Here, we are using a special projection called the wildcard, which is a way to tell Genji to simply project all of the fields of each document.
  • FROM users: Indicates from which table we want to query the data.

Understanding projections

Now, let’s query only the name and age of each user:

SELECT name, age FROM users;
{
    "name": "Gon",
    "age": 13,
}
{
    "name": "Kirua",
    "age": 14,
}
{
    "name": "Hisoka",
    "age": null
}

The result contains three documents, all of them have a name and age fields.

A projection guarantees that all the documents returned by the query will contain the selected fields, even if the original documents don’t have that information. In our example, the Hisoka document doesn’t have an age field, so its projected value is null. The only exception is for the * wildcard, which projects all the fields of the original document.

Querying nested fields

Let’s determine who is the father of our users:

SELECT name, parents.father FROM users;
{
    "name": "Gon",
    "parents.father": "Ging Freecs"
}
{
    "name": "Kirua",
    "parents.father": "Silva Zoldyck"
}
{
    "name": "Hisoka",
    "parents.father": null
}

In this example, we used a field reference to select the parents.father field of our users.

Let’s add the information about the first ability they master:

SELECT name, parents.father, abilities[0] FROM users;
{
    "name": "Gon",
    "parents.father": "Ging Freecs",
    "abilities[0]": "Jajanken"
}
{
    "name": "Kirua",
    "parents.father": "Silva Zoldyck",
    "abilities[0]": "Lighning Palm"
}
{
    "name": "Hisoka",
    "parents.father": null,
    "abilities[0]": "Bungee Gum"
}

abilities[0] is a dot notation that indicates to select the element at index 0 of the abilities array.

Controlling the name of projected fields

The result of the query above contains fields named parents.father and abilities[0], which isn’t that great. Let’s rename them to more clean names:

SELECT name, parents.father AS father, abilities[0] AS main_skill FROM users;
{
    "name": "Gon",
    "father": "Ging Freecs",
    "main_skill": "Jajanken"
}
{
    "name": "Kirua",
    "father": "Silva Zoldyck",
    "main_skill": "Lighning Palm"
}
{
    "name": "Hisoka",
    "father": null,
    "main_skill": "Bungee Gum"
}

The AS clause allows creating aliases to rename projected fields.

Filter documents

Until now, we always performed our queries on every document of the table. Let’s only query those whose nen field is Transmutation.

SELECT name FROM users WHERE nen = 'Transmutation';
{
    "name": "Kirua"
}
{
    "name": "Hisoka"
}

This time, the result contains only two documents.

The WHERE clause allows filtering the documents returned. To do that, it evaluates an expression on every document:

  • if the result of the evaluation is truthy, the document is selected
  • if the result of the evaluation is falsy, the document is filtered out
SELECT name, age FROM users WHERE age < 14;
{
  "name": "Gon",
  "age": 13
}

In this example, only Gon satisfies the query:

  • Kirua’s age is greater 14 which is not < 14
  • Hisoka’s age is null, which is also not < 14

Filtering on values in nested objects

We can filter on values in nested arrays using the IN operator:

SELECT * FROM users WHERE 'Bungee Gum' IN abilities;
{
  "name": "Hisoka",
  "nen": "Transmutation",
  "abilities": ["Bungee Gum", "Texture Surprise"]
}

And values in nested documents using dot notation:

SELECT * FROM users WHERE parents.father = 'Silva Zoldyck';
{
  "name": "Kirua",
  "age": 14,
  "nen": "Transmutation",
  "parents": {
    "father": "Silva Zoldyck",
    "mother": "Kikyo Zoldyck"
  },
  "abilities": ["Lighning Palm", "Thunderbolt", "Godspeed"]
}

Ordering results

The order in which results are returned can be controlled, using the ORDER BY clause

SELECT name, age FROM users ORDER BY age;
{
    "name": "Hisoka",
    "age": null
}
{
    "name": "Gon",
    "age": 13
}
{
    "name": "Kirua",
    "age": 14
}

The order in which documents will appear depends on three factors:

  • the direction or the order
  • the type of the field used for ordering
  • the value of the field used for ordering

By default, the direction is ascending, from the smallest value to the highest.

When it comes to ordering, there is a hierarchy between types:

NULL < BOOLEAN < numbers < TEXT or BLOB

In the example above, the age field of Hisoka doesn’t exist, so it is treated as null, and then appears first in the result.

Then, Gon and Kirua have an age field which is an INTEGER, there are compared with each other and returned in ascending order.

The direction can be controlled by using ASC or DESC clauses.

SELECT name, age FROM users ORDER BY age ASC;
// returns the same results as above
SELECT name, age FROM users ORDER BY age DESC;
{
    "name": "Kirua",
    "age": 14
}
{
    "name": "Gon",
    "age": 13
}
{
    "name": "Hisoka",
    "age": null
}

Limiting and skipping results

The LIMIT clause is executed after WHERE and ORDER BY and allows controlling the number of final results.

SELECT name FROM users WHERE nen = 'Transmutation' ORDER BY age DESC LIMIT 1;
{
  "name": "Hisoka"
}

LIMIT must be followed by the number of maximum results. In this example, we limited the results to 1.

It is also possible to skip results, using the OFFSET clause. It is executed after the WHERE and ORDER BY clauses, but right before LIMIT.

SELECT name FROM users ORDER BY name LIMIT 2 OFFSET 1;
{
    "name": "Hisoka"
}
{
    "name": "Kirua"
}

Using functions

Projections can also use functions to add more power to the queries.

Select the primary key

Every document has a primary key, which is a unique value identifying it. When a document is inserted without an explicit primary key, an implicit one is created automatically. Implicit primary key don’t appear in the results though, even when using SELECT *. To select them, we can use the pk() function.

SELECT pk(), name FROM users;
{
    "name": "Gon",
    "pk()": 1
}
{
    "name": "Kirua",
    "pk()": 2
}
{
    "name": "Hisoka",
    "pk()": 3
}

2.9 - Using Indexes

How to manipulate indexes

Under certain conditions, indexes can be used to speed-up queries.

Indexes are created using the CREATE INDEX statement.

CREATE INDEX idx_nen ON users(nen);
CREATE INDEX idx_address_city ON users(address.city);
CREATE INDEX `main skill index` ON users(skills[0]);

Every index must have a name and must indicate on which table and field they operate. Note that is it possible to index nested fields or array values as well.

Once an index is created, every document inserted afterward will be indexed. Creating an index doesn’t reindex existing documents.

To index older documents, use the REINDEX statement.

/* Reindex all the indexes of all tables */
REINDEX;
/* Reindex all the indexes of a given table */
REINDEX users;
/* Reindex a given index, if a table with the same name doesn't exist */
REINDEX idx_nen;

To make sure all documents of a table have a unique value for a given field, use the CREATE INDEX statement:

CREATE UNIQUE INDEX idx_email ON users(email);

A unique index ensures that the indexed fields do not store duplicate values. Note that NULL values will have the same constraints, meaning that only one document who doesn’t contain the indexed field, or whose field is equal to NULL will be able to be inserted.

To delete indexes, use the DROP INDEX statement

DROP INDEX idx_address_city;

3 - Reference

Complete documentation about Genji’s SQL syntax

3.1 - ALTER TABLE

Manage tables and table constraints

Synopsis

ALTER TABLE statement

Diagram(
  Stack(
    Sequence("ALTER", "TABLE", "table-name"),
    Choice(
      0,
      Sequence("RENAME", "TO", "new-table-name"),
      Sequence("ADD", "FIELD", Link("field-definition"))
    )
  )
);

Field definition

Diagram(
  Sequence(Link("field-path", "/docs/essentials/expressions/#field-path")),
  OptionalSequence("type-name", OneOrMore(Link("field-constraint")))
);

Field constraint

Diagram(
  Choice(
    0,
    Sequence("PRIMARY", "KEY"),
    Sequence("UNIQUE"),
    Sequence("NOT", "NULL"),
    Sequence(
      "DEFAULT",
      Choice(
        0,
        Sequence("(", Link("expr", "/docs/essentials/expressions"), ")"),
        Sequence(Link("expr", "/docs/essentials/expressions"))
      )
    )
  )
);

3.2 - CREATE INDEX

Define a new index

Synopsis

CREATE INDEX statement

Diagram(
  Stack(
    Sequence("CREATE", Optional("UNIQUE", "skip"), "INDEX"),
    Choice(
      0,
      Optional("index-name"),
      Sequence("IF", "NOT", "EXISTS", "index-name")
    ),
    Sequence(
      "ON",
      "table-name",
      "(",
      OneOrMore(
        Link("field-path", "/docs/essentials/expressions/#field-path"),
        ","
      ),
      ")"
    )
  )
);

3.3 - CREATE SEQUENCE

Define a new sequence

Synopsis

CREATE SEQUENCE statement

Diagram(
  Stack(
    Sequence(
      "CREATE",
      "SEQUENCE",
      Optional(Sequence("IF", "NOT", "EXISTS"), "skip"),
      "sequence-name"
    ),
    Optional(
      MultipleChoice(
        0,
        "any",
        Sequence("AS", "type-name"),
        Sequence("INCREMENT", Optional("BY", "skip"), "integer"),
        Sequence(
          "NO",
          Choice(
            0,
            Sequence("MINVALUE"),
            Sequence("MAXVALUE"),
            Sequence("CYCLE")
          )
        ),
        Sequence("MINVALUE", "integer"),
        Sequence("MAXVALUE", "integer"),
        Sequence("START", Optional("WITH", "skip"), "integer"),
        Sequence("CACHE", "integer"),
        Sequence("CYCLE")
      ),
      "skip"
    )
  )
);

3.4 - Transactions

Manage transactions

Synopsis

BEGIN TRANSACTION statement

Diagram(
  Sequence(
    "BEGIN",
    Optional("TRANSACTION", "skip"),
    Optional(
      Choice(0, Sequence("READ", "ONLY"), Sequence("READ", "WRITE")),
      "skip"
    )
  )
);

ROLLBACK TRANSACTION statement

Diagram(Sequence("ROLLBACK", Optional("TRANSACTION", "skip")));

COMMIT TRANSACTION statement

Diagram(Sequence("COMMIT", Optional("TRANSACTION", "skip")));

3.5 - CREATE TABLE

Define a new table

Synopsis

CREATE TABLE statement

Diagram(
  Stack(
    Sequence(
      "CREATE",
      "TABLE",
      Optional(Sequence("IF", "NOT", "EXISTS"), "skip"),
      Sequence(Link("table-name"))
    ),
    Choice(
      0,
      Sequence(
        OneOrMore(Link("field-definition"), ","),
        Optional(
          Sequence(",", OneOrMore(Link("table-constraint"), ",")),
          "skip"
        )
      ),
      Sequence(OneOrMore(Link("table-constraint"), ","))
    )
  )
);

Parameters

IF NOT EXISTS

Do not throw an error if a table with the same name already exists.

table-name

The name of the table to be created.

Field definition

Diagram(
  Link("field-path", "/docs/essentials/expressions/#field-path"),
  OptionalSequence(
    Link("type-name", "/docs/essentials/data-types"),
    OneOrMore(Link("field-constraint"))
  )
);
table-name

The name of the table to be created.

Field constraint

Diagram(
  Choice(
    0,
    Sequence("PRIMARY", "KEY"),
    Sequence("UNIQUE"),
    Sequence("NOT", "NULL"),
    Sequence(
      "DEFAULT",
      Choice(
        0,
        Sequence("(", Link("expr", "/docs/essentials/expressions"), ")"),
        Sequence(Link("expr", "/docs/essentials/expressions"))
      )
    ),
    Sequence("CHECK", "(", Link("expr", "/docs/essentials/expressions"), ")")
  )
);

Table constraint

Diagram(
  Choice(
    0,
    Sequence(
      "PRIMARY",
      "KEY",
      "(",
      Link("field-path", "/docs/essentials/expressions/#field-path"),
      ")"
    ),
    Sequence(
      "UNIQUE",
      "KEY",
      "(",
      Link("field-path", "/docs/essentials/expressions/#field-path"),
      ")"
    ),
    Sequence("CHECK", "(", Link("expr", "/docs/essentials/expressions"), ")")
  )
);

3.6 - DELETE

Delete documents

Synopsis

DELETE statement

Diagram(
  Stack(
    Sequence("DELETE", "FROM", "table-name"),
    Optional(
      Sequence("WHERE", Link("expr", "/docs/essentials/expressions")),
      "skip"
    ),
    Optional(
      Sequence(
        "ORDER",
        "BY",
        Link("expr", "/docs/essentials/expressions"),
        Optional(Choice(0, "ASC", "DESC"), "skip")
      ),
      "skip"
    ),
    Optional(
      Sequence("LIMIT", Link("expr", "/docs/essentials/expressions")),
      "skip"
    ),
    Optional(
      Sequence("OFFSET", Link("expr", "/docs/essentials/expressions")),
      "skip"
    )
  )
);

3.7 - DROP INDEX

Delete an index and all of its content

Synopsis

DROP INDEX statement

Diagram(
  Sequence(
    "DROP",
    "INDEX",
    Optional(Sequence("IF", "EXISTS"), "skip"),
    "index-name"
  )
);

3.8 - DROP SEQUENCE

Delete a sequence

Synopsis

DROP SEQUENCE statement

Diagram(
  Sequence(
    "DROP",
    "SEQUENCE",
    Optional(Sequence("IF", "EXISTS"), "skip"),
    "sequence-name"
  )
);

3.9 - DROP TABLE

Delete a table and all of its content

Synopsis

DROP TABLE statement

Diagram(
  Sequence(
    "DROP",
    "TABLE",
    Optional(Sequence("IF", "EXISTS"), "skip"),
    "table-name"
  )
);

3.10 - EXPLAIN

Generate query plan

Synopsis

EXPLAIN statement

Diagram(
  Sequence(
    "EXPLAIN",
    Choice(
      0,
      Link("select-stmt", "../select#select-stmt"),
      Link("insert-stmt", "../insert#insert-stmt"),
      Link("update-stmt", "../update#update-stmt"),
      Link("delete-stmt", "../delete#delete-stmt")
    )
  )
);

3.11 - INSERT

Insert documents

Synopsis

INSERT statement

Diagram(
  Stack(
    Sequence("INSERT", "INTO", "table-name"),
    Choice(0, Link("values-clause"), Link("select-stmt", "select#select-stmt")),
    Optional(Link("conflict-clause"), "skip"),
    Optional(Link("returning-clause"), "skip")
  )
);

VALUES clause

Diagram(
  Stack(
    Choice(
      0,
      Sequence(
        "(",
        OneOrMore("field-name", ","),
        ")",
        "VALUES",
        OneOrMore(
          Sequence(
            "(",
            OneOrMore(Link("expr", "/docs/essentials/expressions"), ","),
            ")"
          ),
          ","
        )
      ),
      Sequence(
        "VALUES",
        OneOrMore(
          Link(
            "document-literal",
            "/docs/essentials/expressions#document-literal"
          ),
          ","
        )
      )
    )
  )
);

Conflict clause

Diagram(
  Sequence(
    "ON",
    "CONFLICT",
    Choice(
      0,
      "IGNORE",
      "REPLACE",
      Sequence("DO", "NOTHING"),
      Sequence("DO", "REPLACE")
    )
  )
);

RETURNING clause

Diagram(
  Sequence(
    "RETURNING",
    Link("expr", "/docs/essentials/expressions"),
    Optional(Sequence("AS", "alias"), "skip")
  )
);

3.12 - REINDEX

Reindex tables, indexes or everything

Synopsis

REINDEX statement

Diagram(
  Sequence("REINDEX", Optional(Choice(0, "table-name", "index-name"), "skip"))
);

3.13 - SELECT

Query the database

Synopsis

SELECT statement

Diagram(
  Stack(
    OneOrMore(
      Group(
        Stack(
          Sequence(
            "SELECT",
            Optional("DISTINCT", "skip"),
            OneOrMore(Link("result-field"), ",")
          ),
          Optional(Sequence("FROM", Link("table-name")), "skip"),
          Optional(
            Sequence("WHERE", Link("expr", "/docs/essentials/expressions")),
            "skip"
          ),
          Optional(
            Sequence(
              "GROUP",
              "BY",
              Link("expr", "/docs/essentials/expressions")
            ),
            "skip"
          )
        ),
        "select-core"
      ),
      Choice(0, "UNION", Sequence("UNION", "ALL"))
    ),
    Optional(
      Sequence(
        "ORDER",
        "BY",
        Link("expr", "/docs/essentials/expressions"),
        Optional(Choice(0, "ASC", "DESC"), "skip")
      ),
      "skip"
    ),
    Optional(
      Sequence("LIMIT", Link("expr", "/docs/essentials/expressions")),
      "skip"
    ),
    Optional(
      Sequence("OFFSET", Link("expr", "/docs/essentials/expressions")),
      "skip"
    )
  )
);

Result field

Diagram(
  Choice(
    0,
    Sequence(
      Link("expr", "/docs/essentials/expressions"),
      Optional(Sequence("AS", "field-alias"), "skip")
    ),
    "*"
  )
);

3.14 - UPDATE

Update documents

Synopsis

UPDATE statement

Diagram(
  Stack(
    Sequence("UPDATE", "table-name"),
    Choice(
      0,
      Sequence(
        "SET",
        OneOrMore(
          Sequence(
            Link("field-path", "/docs/essentials/expressions/#field-path"),
            "=",
            Link("expr", "/docs/essentials/expressions")
          ),
          ","
        )
      ),
      Sequence("UNSET", OneOrMore("top-level-field", ","))
    ),
    Optional(
      Sequence("WHERE", Link("expr", "/docs/essentials/expressions")),
      "skip"
    )
  )
);