# SQL Reference
# Data types
|INTEGER||Signed 64-bit integer value. Usually referred to as ||-|
|BOOLEAN||A boolean value, either ||-|
|VARCHAR||UTF8-encoded text||Maximum number of bytes in the UTF-8 encoded representation of the string|
|BLOB||sequence of bytes||Maximum number of bytes in the sequence|
# Size constraints
Size constraint is specified with a
[MAX_SIZE] suffix on the type,
BLOB represents a sequence of up to 16 bytes.
# NULL values
NULL values in immudb are not unique - two
NULL values are considered equal on comparisons.
# Creating tables
Common examples of
CREATE TABLE statements are presented below.
CREATE TABLE IF NOT EXISTS customers ( id INTEGER, customer_name VARCHAR, email VARCHAR, address VARCHAR, city VARCHAR, ip VARCHAR, country VARCHAR, age INTEGER, active BOOLEAN, PRIMARY KEY (id) ); CREATE TABLE IF NOT EXISTS products ( id INTEGER, product VARCHAR NOT NULL, price VARCHAR NOT NULL, PRIMARY KEY (id) ); CREATE TABLE IF NOT EXISTS orders ( id INTEGER AUTO_INCREMENT, customerid INTEGER, productid INTEGER, PRIMARY KEY id ); CREATE TABLE customer_review( customerid INTEGER, productid INTEGER, review VARCHAR, PRIMARY KEY (customerid, productid) );
IF NOT EXISTS
With this clause the
CREATE TABLE statement will not fail if a table with same name already exists.
Note: If the table already exists, it is not compared against the provided table definition neither it is updated to match it.
Columns marked as not null can not have a null value assigned.
Every table in immudb must have a primary key. Primary key can use at least 1 and up to 8 columns.
Columns used in a primary key can not have
NULL values assigned,
even if those columns are not explicitly marked as
Primary key creates an implicit unique index on all contained columns.
PRIMARY KEY can be marked as
immudb will automatically set a unique value of this column for new rows.
When inserting data into a table with an
the value for such primary key must be omitted.
When updating data in such table with
the value for such primary key is obligatory
UPSERT statement can only update existing rows.
The type of an
AUTO_INCREMENT column must be
Internally immudb will assign sequentially increasing values for new rows
ensuring this value is unique within a single table.
# Foreign keys
Explicit support for relations to foreign tables is not currently supported in immudb.
It is possible however to create ordinary columns containing foreign key values that can be used in
Application logic is responsible for ensuring data consistency and foreign key constraints.
SELECT * FROM orders INNER JOIN customers ON customers.id = orders.customerid INNER JOIN products ON products.id = orders.productid;
immudb indexes can be used for a quick search of rows with columns having specific values.
Certain operations such as ordering values with
ORDER BY clause
require columns to be indexed.
CREATE INDEX ON customers(customer_name); CREATE INDEX ON customers(country, ip); CREATE INDEX IF NOT EXISTS ON customers(active); CREATE UNIQUE INDEX ON customers(email);
Index can only be added to an empty table.
Index do not have explicit name and is referenced by the ordered list of indexed columns.
# Column value constraints
VARCHAR type must have a size limit set on them.
The maximum allowed value size for one indexed column is 256 bytes.
Values for indexed columns can not be
even if the column is not explicitly marked as
# Unique indexes
Index can be marked as unique with extra
Unique index will prevent insertion of new data into the table
that would violate uniqueness of indexed columns within the table.
# Multi-column indexes
Index can be set on up to 8 columns. The order of columns is important when doing range scans, iterating over such index will first sort by the value of the first column, then by the second and so on.
Note: Large indexes will increase the storage requirement and will reduce the performance of data insertion. Iterating using small indexes will also be faster than with the large ones.
IF NOT EXISTS
With this clause the
CREATE INDEX statement will not fail if an index with same type and list of columns already exists.
This includes a use case where the table is not empty which can be used to simplify database schema initialization.
Note: If the index already exists, it is not compared against the provided index definition neither it is updated to match it.
# Inserting or updating data
immudb supports standard
INSERT sql statement.
It can be used to add one or multiple values within the same transaction.
INSERT INTO customers ( id, customer_name, email, address, city, ip, country, age, active ) VALUES ( 1, 'Isidro Behnen', 'email@example.com', 'firstname.lastname@example.org', 'Arvika', '127.0.0.15', 'SE', 24, true ); INSERT INTO products (id, product, price) VALUES ( 1, 'Juice - V8, Tomato', '$4.04' ), ( 2, 'Milk', '$3.24' ); INSERT INTO orders (customerid, productid) VALUES (1, 1), (1, 2); INSERT INTO customer_review (customerid, productid, review) VALUES (1, 1, 'Nice Juice!');
UPSERT is an operation with a syntax similar to
the difference between those two is that
UPSERT either creates a new or replaces an existing row.
A new row is created if an entry with the same primary key does not yet exist in the table,
otherwise the current row is replaced with the new one.
UPSERT can only be performed on tables without secondary indexes
(other than the one used for the primary key).
This constraint will be removed in the future.
If a table contains an
AUTO_INCREMENT primary key,
the value for that key must be provided
UPSERT operation will only update the existing row.
UPSERT INTO products (id, product, price) VALUES ( 2, 'Milk', '$3.17' ), ( 3, 'Grapes - Red', '$5.03' ); UPSERT INTO orders (id, customerid, productid) VALUES (1, 1, 3);
# Selecting all columns
All columns from all joined tables can be queried with
SELECT * statement.
SELECT * FROM products;
# Selecting specific columns
SELECT id, customer_name, ip FROM customers;
# Filtering entries
SELECT id, customer_name, email FROM customers WHERE country = 'SE' AND city = 'Arvika';
# Ordering by column value
SELECT id, customer_name FROM customers ORDER BY customer_name ASC;
Currently only one column can be used in the
ORDER BY clause.
The order may be either ascending (
ASC suffix, default) or descending (
Ordering rows by a value of a column requires a matching index on that column.
immudb supports standard SQL
INNER JOIN syntax.
INNER join type is optional.
SELECT * FROM orders INNER JOIN customers ON orders.customerid = customers.id; SELECT * FROM orders JOIN customers ON orders.customerid = customers.id WHERE orders.productid = 2; SELECT * FROM orders JOIN customers ON customers.id = orders.customerid JOIN products ON products.id = orders.productid;
immudb supports the
Unlike in other SQL engines though, the pattern use a regexp syntax
supported by the regexp library in the go language (opens new window).
NOT prefix negates the value of the
SELECT product FROM products WHERE product LIKE '(J.*ce|Red)'; SELECT product FROM products WHERE product NOT LIKE '(J.*ce|Red)'; SELECT id, product FROM products WHERE (id > 0 AND NOT products.id >= 10) AND (products.product LIKE 'J');
immudb has a basic supports for the
NOT prefix negates the value of the
Note: Currently the list for the
IN operator can not be
calculated using a sub-query.
SELECT product FROM products WHERE product IN ('Milk', 'Grapes - Red'); SELECT product FROM products WHERE product NOT IN ('Milk', 'Grapes - Red'); SELECT id, product FROM products WHERE (id > 0 AND NOT products.id >= 10) AND (product IN ('Milk', 'Grapes - Red'));
# Column and table aliasing
SELECT c.id, c.customer_name AS name, active FROM customers AS c WHERE c.id <= 3 AND c.active = true; SELECT c.id, c.customer_name AS name, active FROM customers c WHERE c.id <= 3 AND c.active = true;
Table name aliasing is necessary when using more than one join with the same table.
Available aggregation functions:
SELECT COUNT() AS c, SUM(age), MIN(age), MAX(age), AVG(age) FROM customers;
# Grouping results with
Results can be grouped by a value of a single column.
That column must also be used in a matching
ORDER BY clause.
SELECT COUNT() as customer_count, country FROM customers GROUP BY country ORDER BY country;
# Filtering grouped results with
SELECT active, COUNT() as c, MIN(age), MAX(age) FROM customers GROUP BY active HAVING COUNT() > 0 ORDER BY active DESC;
The table in the
JOIN clauses can be replaced with a sub-query.
SELECT * FROM ( SELECT id, customer_name FROM customers WHERE age < 30 ) INNER JOIN customer_review ON customer_review.customerid = customers.id; SELECT * FROM ( SELECT id, customer_name FROM customers WHERE age < 30 ) AS c INNER JOIN ( SELECT * FROM customer_review ) AS r ON r.customerid = c.id;
Note: the context of a sub-query does not propagate outside,
e.g. it is not possible to reference a table from a sub-query
WHERE clause outside of the sub-query.
# Basic transactions
Multiple insert and upsert statements can be issued within a single transaction.
Note: The example below won't work in the current version of the immudb webconsole.
The easiest way to tested it is with the
./immuclient exec "..." shell command
(make sure to use an escaped
\$ value to avoid cutting out part of the price).
BEGIN TRANSACTION UPSERT INTO products (id, price, product) VALUES (4, '$5.76', 'Bread'); INSERT INTO orders(productid, customerid) VALUES(4, 1); COMMIT;
# Time travel
Time travel allows reading data from SQL as if it was in some previous state. The state is indicated by transaction id.
A historical version of a table can be used in
BEFORE TX clause:
SELECT id, product, price FROM products BEFORE TX 13; SELECT id, product, price FROM products BEFORE TX 13 WHERE id = 2;