2020-11-23

Tarantool: In-memory SQL Hybrid Database

Tarantool is an awesome product that I've evaded for a while (because it's using Lua), but after a long time seeking a awesome free silver-bullet database (2016's benchmark here), I think only Tarantool fits the bill (so I need to learn about Lua 5.1 first since Tarantool uses LuaJIT that implements Lua 5.1 and some part of Lua 5.2). The benefits of using Tarantool are:
  • ACID Transaction, like standard RDBMS and Aerospike
  • Replication, very easy load balance, like Aerospike, Redis, etc
  • On-Disk Persistence, like Redis Enterprise, Aerospike, KeyDB Pro
  • Extension Modules, like Redis (RediGraph, RediSearch, RediSQL, etc)
  • Can use SQL, so no need to learn new language, unlike MongoDB, ArangoDB, or databases that only support partial/subset of SQL: ScyllaDB CQL and GridDB TQL, eg. no WHERE-IN.
  • Combining both in-memory and disk storage, like paid version of Redis, KeyDB
  • Can use Go :3 unlike GridDB that only have partial support
  • Extremely customizable using Lua, even on the server part
  • Less memory requirement when using vinyl engine (for large datasets that are larger than RAM) compared to in-memory solutions like Aerospike (64 byte per key requirement) or Redis swap,  sacrificing the I/O performance.
Some of the cons of using Tarantool is that you cannot prepend or drop fields/column, you can only append and the field should be nullable until ever rows have all values.
To install Tarantool on ubuntu (without Docker) you can use these commands:

curl -L https://tarantool.io/odVEwx/release/2.4/installer.sh | bash
curl -L https://tarantool.io/odVEwx/live/2.5/installer.sh | bash
sudo apt install tarantool
sudo systemctl enable tarantool
sudo systemctl start tarantool
netstat -antp | grep 3301

If it doesn't work or stuck just ctrl-C the curl script then install manually from these repo:

echo '
deb https://download.tarantool.org/odVEwx/tarantool/2.5/ubuntu/ focal main
deb-src https://download.tarantool.org/odVEwx/tarantool/2.5/ubuntu/ focal main
deb https://download.tarantool.org/tarantool/modules/ubuntu/ focal main
deb-src https://download.tarantool.org/tarantool/modules/ubuntu/ focal main
' | sudo tee -a /etc/apt/sources.list.d/tarantool_2_5.list
sudo apt update
sudo apt install tarantool

To connect to existing tarantool, we can use this command:

tarantoolctl connect 3301

-- create "table"
s = box.schema.space.create('tester')
-- s equal to box.space.tester

-- create "schema"
s:format({
  {name = 'id', type = 'unsigned'},
  {name = 'band_name', type = 'string'},
  {name = 'year', type = 'unsigned'}
})

-- create primary index
s:create_index('primary', {
  type = 'hash',
  parts = {'id'}
})

-- insert records
s:insert{1, 'Roxette', 1986}
s:insert{2, 'Scorpions', 2015}
s:insert{3, 'Ace of Base', 1993}

-- query using primary index
s:select{3}

-- create secondary index
s:create_index('secondary', {
  type = 'hash',
  parts = {'band_name'}
})

-- query using secondary index
s.index.secondary:select{'Scorpions'}

-- grant guest user to access anywhere
box.schema.user.grant('guest', 'read,write,execute', 'universe')

-- reset the admin password, only when listen, 
-- eg. from /etc/tarantool/instances.enabled/example.lua
box.schema.user.passwd('pass')

-- alter table 
box.space.tester:format({
  {name = 'id', type = 'unsigned'},
  {name = 'band_name', type = 'string'},
  {name = 'year', type = 'unsigned'},
  {name = 'rate', type = 'unsigned', is_nullable=true}})

-- update record id=1, 4th column to 5
box.space.tester:update(1, {{'=', 4, 5}})

-- update record id=2, 4th column to 4
box.space.tester:update(2, {{'=', 4, 4}})

-- search id>1
box.space.tester:select(1, {iterator = 'GT'})
-- can be LT, LE, EQ, REQ (reverse equal), GE, GT.
-- must have TREE index

Next, how to connect from golang? We need to install first go-tarantoll library:

go get -u -v github.com/tarantool/go-tarantool
# or: go get -u -v github.com/viciious/go-tarantool

Next let's create a go file:

package main
import (
    "log"
    "fmt"
    "github.com/tarantool/go-tarantool"
)
func main() {
    conn, err := tarantool.Connect("127.0.0.1:3301", tarantool.Opts{
        User: "guest",
        Pass: "",
    })
    if err != nil {
        log.Fatalf("Connection refused "+err.Error())
    }
    defer conn.Close()

    // insert
    resp, err := conn.Insert("tester", []interface{}{4, "ABBA", 1972})
    fmt.Printf("Insert: %#v %v\n",resp.Data,err)

    // select offset=0, limit=1
    resp, err = conn.Select("tester", "primary", 0, 1, tarantool.IterEq, []interface{}{4})
    fmt.Printf("Select: %#v %v\n",resp.Data,err)
    resp, err = conn.Select("tester","secondary", 0, 1, tarantool.IterEq, []interface{}{"ABBA"})
    fmt.Printf("Select: %#v %v\n",resp.Data,err)

    // update col 2 by 3
    resp, err = conn.Update("tester", "primary", []interface{}{4}, []interface{}{[]interface{}{"+", 2, 3}})
    fmt.Printf("Update: %#v %v\n",resp.Data,err)

    // replace
    resp, err = conn.Replace("tester", []interface{}{4, "New band", 2011})
    fmt.Printf("Replace: %#v %v\n",resp.Data,err)

    // upsert: update increment col 2 by 5, or insert if not exists
    // does not return data back
    resp, err = conn.Upsert("tester", []interface{}{4, "Another band", 2000}, []interface{}{[]interface{}{"+", 2, 5}})
    fmt.Printf("Upsert: %#v %v\n",resp.Data,err)

    // delete
    resp, err = conn.Delete("tester", "primary", []interface{}{4})
    fmt.Printf("Delete: %#v %v\n",resp.Data,err)

    // call directly, do not add () when calling
    resp, err = conn.Call("box.space.tester:count", []interface{}{})
    fmt.Printf("Call: %#v %v\n",resp.Data,err)

    // eval Lua 
    resp, err = conn.Eval("return 4 + 5", []interface{}{})
    fmt.Printf("Eval: %#v %v\n",resp.Data,err)
}

It would give an output something like this:

Insert: []interface {}{[]interface {}{0x4, "ABBA", 0x7b4}} <nil>
Select: []interface {}{[]interface {}{0x4, "ABBA", 0x7b4}} <nil>
Select: []interface {}{[]interface {}{0x4, "ABBA", 0x7b4}} <nil>
Update: []interface {}{[]interface {}{0x4, "ABBA", 0x7b7}} <nil>
Replace: []interface {}{[]interface {}{0x4, "New band", 0x7db}} <nil>
Upsert: []interface {}{} <nil>
Delete: []interface {}{[]interface {}{0x4, "New band", 0x7e0}} <nil>
Call: []interface {}{[]interface {}{0x3}} <nil>
Eval: []interface {}{0x9} <nil>

See more info here and APIs here. Next you can use cartridge to manage the cluster.
Each row/tuple in Tarantool stored as MsgPack and when displayed in console it uses YAML format. TREE is the default index in tarantool engine, memtx engine support few more: HASH, RTREE, BITSET. The TREE or HASH may only index certain types: integer, number, double, varbinary, boolean, uuid, scalar (null, bool, integer, double, decimal, string, varbinary). TREE or HASH or BITSET may only index string and unsigned. RTREE may only index array type. If we have multiple parts/columns on TREE index, we can do partial search starting from starting column of the index. When using string index, we may specify the collation (ordering): unicode or unicode_ci (case ignore, also ignore accented/diacritic alphabet). Sequence can be accessed through box.schema.sequence.create() with options (start, min, max, cycle, step, if_not_exists), we could call next method to get next value. Tarantool persist data in two modes: WAL and snapshot (can be forced using box.snapshot()). List of available operators for update/upsert:
  • + to add, - to subtract
  • & bitwise and, | bitwise or,  ^ bitwise xor
  • : string splice
  • ! insertion of a new field
  • # deletion
  • = assignment
This snippet shows how to update table and check it:

r, err := conn.Call(`box.schema.space.create`, []interface{}{TableName})
if err != nil {
  if err.Error() != `Space '`+TableName+`' already exists (0xa)` {
    log.Println(err)
    return
  }
}
fmt.Println(r.Tuples())
fmt.Println(`---------------------------------------------------`)
r, err = conn.Call(`box.space.`+TableName+`:format`, []interface{}{
  []map[string]interface{}{
    {`name`: `id`, `type`: `unsigned`},
    {`name`: `name`, `type`: `string`},
  },
})
if err != nil {
  log.Println(err)
  return
}
fmt.Println(r.Tuples())
r, err = conn.Call(`box.space.`+TableName+`:format`, []interface{}{})
if err != nil {
  log.Println(err)
  return
}
fmt.Println(r.Tuples())


func ExecSql(conn *tarantool.Connection, query string, parameters ...M.SX) map[interface{}]interface{} {
  params := A.X{query}
  for _, v := range parameters {
    params = append(params, v)
  }
  L.Describe(params)
  res, err := conn.Call(`box.execute`, params)
  if L.IsError(err) {
    L.Describe(`ERROR ExecSql !!! ` + err.Error())
    L.DescribeSql(query, parameters)
    L.Describe(err.Error())
    return map[interface{}]interface{}{`error`: err.Error()}
  }
  tup := res.Tuples()
  if len(tup) > 0 {
    if len(tup[0]) > 0 {
      if tup[0][0] != nil {
        kv, ok := tup[0][0].(map[interface{}]interface{})
        // row_count for UPDATE
        // metadata, rows for SELECT
        if ok {
          return kv
        }
      }
    }
  }
  // possible error
  if len(tup) > 1 {
    if len(tup[1]) > 0 {
      if tup[1][0] != nil {
        L.Describe(`ERROR ExecSql syntax: ` + X.ToS(tup[1][0]))
        L.Describe(query, parameters)
        L.Describe(tup[1][0])
        return map[interface{}]interface{}{`error`: tup[1][0]}
      }
    }
  }
  return map[interface{}]interface{}{}
}

func QuerySql(conn *tarantool.Connection, query string, callback func(row A.X), parameters ...M.SX) []interface{} {
  kv := ExecSql(conn, query, parameters...)
  rows, ok := kv[`rows`].([]interface{})
  if ok {
    for _, v := range rows {
      callback(v.([]interface{}))
    }
    return rows
  }
  return nil
}


The tarantool have some limitations:
  • no datetime (use unsigned if greater than 1970-01-01)
  • can only append column at the end, cannot delete column
  • cannot alter datatype, except if there’s no data yet
  • alter table when there’s data exists must have not_null=true flag 
Tarantool SQL limitations and gotchas:
  • table names or column names must be quoted or they will be automatically capitalized (and then error column/space=table not found)
  • concat just like postgresql (|| operator), but you must convert both operands to string first 
  • does not support right join (not needed anyway), can do natural join or left join, also support foreign key
  • no date/time data type/functions (tarantool problem)
  • NULLIF (tarantool), IFNULL (mysql), COALESCE (standard SQL) all works normally
  • cannot run alter table add column (must use APIs)
  • no information_schema
  • calling box.execute to execute SQL (supported since version 2.x), the result is on first row (row_count for UPDATE, metadata and rows for SELECT), the error is on second row.
I think that's it for now, to learn Lua (needed for stored procedure) you can use official documentation. For example project using Tarantool and Meilisearch, you can clone kmt1 dummy project.