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

Return to the regular view of this page.

Reference

Complete documentation about Genji’s SQL syntax

1: ALTER TABLE
2: CREATE INDEX
3: CREATE SEQUENCE
4: Transactions
5: CREATE TABLE
6: DELETE
7: DROP INDEX
8: DROP SEQUENCE
9: DROP TABLE
10: EXPLAIN
11: INSERT
12: REINDEX
13: SELECT
14: UPDATE

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

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

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

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

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

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

8 - DROP SEQUENCE

Delete a sequence

Synopsis

DROP SEQUENCE statement

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

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

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

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(
        Optional(Sequence("(", OneOrMore("field-name", ","), ")"), "skip"),
        "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")
  )
);

12 - REINDEX

Reindex tables, indexes or everything

Synopsis

REINDEX statement

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

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")
    ),
    "*"
  )
);

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