Tarantool Usage: Stored Procedures


    Translation of an article from Zone. The original .

    I want to share my experience in creating applications for Tarantool, and today we’ll talk about installing this DBMS, storing data and accessing it, as well as recording stored procedures.

    Tarantool is a NoSQL / NewSQL database that stores data in RAM, but can use the disk and maintain consistency using a carefully designed mechanism called write-ahead log (WAL). Tarantool also boasts a built-in LuaJIT-compiler (JIT - just-in-time), which allows you to execute Lua-code.

    First steps

    We will look at creating a Tarantool application that implements an API for registering and authenticating users. Its features:

    • Registration and authentication by mail in three stages: creating an account, confirming registration and setting a password.
    • Registration using social network credentials (FB, Google+, VKontakte, etc.).
    • Password recovery.

    For an example of a stored procedure for Tarantool, we will turn to the first stage, more precisely, to obtain a registration confirmation code. You can go to the GitHub repository and perform all the actions as the story progresses.

    Install Tarantool

    The network has detailed installation instructions for various operating systems. For example, to install Tarantool under Ubuntu, paste in the console and run this script:

    curl http://download.tarantool.org/tarantool/1.9/gpgkey | sudo apt-key add -
    release=`lsb_release -c -s`
    sudo apt-get -y install apt-transport-https
    sudo rm -f /etc/apt/sources.list.d/*tarantool*.list
    sudo tee /etc/apt/sources.list.d/tarantool_1_9.list <<- EOF
    deb http://download.tarantool.org/tarantool/1.9/ubuntu/ $release main
    deb-src http://download.tarantool.org/tarantool/1.9/ubuntu/ $release main
    sudo apt-get update
    sudo apt-get -y install tarantool

    We verify the success of the installation by entering tarantooland logging into the interactive administrator console.

    $ tarantool
    version 1.9.0-4-g195d446
    type 'help' for interactive help

    Here you can already try programming in Lua. If you are not familiar with this language, then here is a short guide to get you started:http://tylerneylon.com/a/learn-lua .

    Registration by mail

    Now we will write our first script to create a space in which all users will be stored. It is similar to a table in a relational database. The data itself is stored in tuples (arrays containing records). Each space must have one primary index and may have several secondary indexes. Indexes can be defined by one or more fields. Here is the space diagram of our authentication service:

    We use two types of indices: HASHand TREE. The index HASHallows you to search for tuples using the full match of the primary key, which must be unique. The index TREEsupports non-unique keys, allows you to search by the beginning of a composite index and organize the sorting of keys, since their values ​​are ordered inside the index.

    The space sessioncontains a special key ( session_secret) used to sign session cookies. Storing session keys allows you to log out users on the server side if necessary. Also, the session has an optional space reference social. This is necessary to check the sessions of those users who are logged in with the credentials of social networks (we check the validity of the stored OAuth 2 token).

    We are writing an application

    Before you start writing the application itself, let's take a look at the structure of the project:

    ├── authman
    │ ├── model
    │ │ ├── password.lua
    │ │ ├── password_token.lua
    │ │ ├── session.lua
    │ │ ├── social.lua
    │ │ └── user.lua
    │ ├── utils
    │ │ ├── http.lua
    │ │ └── utils.lua
    │ ├── db.lua
    │ ├── error.lua
    │ ├── init.lua
    │ ├── response.lua
    │ └── validator.lua
    └── test
     ├── case
     │ ├── auth.lua
     │ └── registration.lua
     ├── authman.test.lua
     └── config.lua

    The paths defined in the variable package.pathare used to import Lua packages. In our case, packages are imported relative to the current directory tarantool-authman. But if necessary, the import paths can easily be expanded:

    -- Prepending a new path with the highest priority
    package.path = “/some/other/path/?.lua;” .. package.path

    Before creating the first space, let's put all the necessary constants in separate models. You need to give a name to each space and index. It is also necessary to determine the order of the fields in the tuple. For example, the model looks like this authman/model/user.lua:

    -- Our package is a Lua table
    local user = {}
    -- The package has the only function — model — that returns a table
    -- with the model’s fields and methods
    -- The function receives configuration in the form of a Lua table
    function user.model(config)
      local model = {}
      -- Space and index names
      model.SPACE_NAME = ‘auth_user’
      model.PRIMARY_INDEX = ‘primary’
      model.EMAIL_INDEX = ‘email_index’
      -- Assigning numbers to tuple fields
      -- Note thatLua uses one-based indexing!
      model.ID = 1
      model.EMAIL = 2
      model.TYPE = 3
      model.IS_ACTIVE = 4
      -- User types: registered via email or with social network
      -- credentials
      model.COMMON_TYPE = 1
      model.SOCIAL_TYPE = 2
      return model
    -- Returning the package
    return user

    When processing users, we need two indexes: unique by ID and non-unique by mail address. When two different users are registered with the credentials of social networks, they can indicate the same addresses or not at all. And for users registering in the usual way, the application will check the uniqueness of the email addresses.

    The package authman/db.luacontains a method for creating spaces:

    local db = {}
    -- Importing the package and calling the model function
    -- The config parameter is assigned a nil (empty) value
    local user = require(‘authman.model.user’).model()
    -- The db package’s method for creating spaces and indexes
    function db.create_database()
      local user_space = box.schema.space.create(user.SPACE_NAME, {
                  if_not_exists = true
      user_space:create_index(user.PRIMARY_INDEX, {
                  type = ‘hash’,
                  parts = {user.ID, ‘string’},
                  if_not_exists = true
      user_space:create_index(user.EMAIL_INDEX, {
                  type = ‘tree’,
                  unique = false,
                  parts = {user.EMAIL, ‘string’, user.TYPE, ‘unsigned’},
                  if_not_exists = true
    return db

    The UUID will act as the user ID, and for the search with a complete match we will use the index HASH. The mail search index will consist of two parts: ( user.EMAIL, ‘string’) - user mail address, ( user.TYPE, ‘unsigned’) - user type. Let me remind you that types are defined a little earlier in the model. A composite index allows you to search not only by all fields, but also by the first part of the index. So we can only search by mail address (without user type).

    We will enter the admin console and use the package authman/db.lua.

    $ tarantool
    version 1.9.0-4-g195d446
    type ‘help’ for interactive help
    tarantool> db = require(‘authman.db’)
    tarantool> box.cfg({})
    tarantool> db.create_database()

    Great, we just created the first space. Do not forget: before calling box.schema.space.create, you must box.cfgconfigure and start the server using the method . Now you can perform some simple actions inside the created space:

    -- Creating users
    tarantool> box.space.auth_user:insert({‘user_id_1’, ‘example_1@mail.ru’, 1})
     — -
    - [‘user_id_1’, ‘example_1@mail.ru’, 1]
    tarantool> box.space.auth_user:insert({‘user_id_2’, ‘example_2@mail.ru’, 1})
     — -
    - [‘user_id_2’, ‘example_2@mail.ru’, 1]
    -- Getting a Lua table (array) with all the users
    tarantool> box.space.auth_user:select()
     — -
    - — [‘user_id_2’, ‘example_2@mail.ru’, 1]
     — [‘user_id_1’, ‘example_1@mail.ru’, 1]
    -- Getting a user by the primary key
    tarantool> box.space.auth_user:get({‘user_id_1’})
     — -
    - [‘user_id_1’, ‘example_1@mail.ru’, 1]
    -- Getting a user by the composite key
    tarantool> box.space.auth_user.index.email_index:select({‘example_2@mail.ru’, 1})
     — -
    - — [‘user_id_2’, ‘example_2@mail.ru’, 1]
    -- Changing the data in the second field
    tarantool> box.space.auth_user:update(‘user_id_1’, {{‘=’, 2, ‘new_email@mail.ru’}, })
     — -
    - [‘user_id_1’, ‘new_email@mail.ru’, 1]

    Unique indexes do not allow non-unique values ​​to be entered. If you want to create records that may already be in space, use the operation upsert(update / insert). A complete list of available methods is provided in the official documentation .

    Let's expand the user model with the ability to register users:

    function model.get_space()
        return box.space[model.SPACE_NAME]
      function model.get_by_email(email, type)
        if validator.not_empty_string(email) then
          return model.get_space().index[model.EMAIL_INDEX]:select({email, type})[1]
      -- Creating a user
      -- Fields that are not part of the unique index are not mandatory
      function model.create(user_tuple)
        local user_id = uuid.str()
        local email = validator.string(user_tuple[model.EMAIL]) and
                      user_tuple[model.EMAIL] or ‘’
        return model.get_space():insert{
      -- Generating a confirmation code sent via email and used for
      -- account activation
      -- Usually, this code is embedded into a link as a GET parameter
      -- activation_secret — one of the configurable parameters when
      -- initializing the application
      function model.generate_activation_code(user_id)
        return digest.md5_hex(string.format(‘%s.%s’,
                      config.activation_secret, user_id))

    The code below uses two standard Tarantool packages - uuidand digest- and one created by the user - validator. But first you need to import them:

    -- standard Tarantool packages
    local digest = require(‘digest’)
    local uuid = require(‘uuid’)
    -- Our application’s package (handles data validation)
    local validator = require(‘authman.validator’)

    When defining variables, we use an operator localthat limits their scope to the current block. If you don’t do this, the variables will be global, and we need to avoid this because of possible name conflicts.

    Now create the main package authman/init.luain which all API methods will be stored:

    local auth = {}
    local response = require(‘authman.response’)
    local error = require(‘authman.error’)
    local validator = require(‘authman.validator’)
    local db = require(‘authman.db’)
    local utils = require(‘authman.utils.utils’)
    -- The package returns the only function — api — that configures and
    -- returns the application
    function auth.api(config)
      local api = {}
      -- The validator package contains checks for various value types
      -- This package sets the default values as well
      config = validator.config(config)
      -- Importing the models for working with data
      local user = require(‘authman.model.user’).model(config)
      -- Creating a space
      -- The api method creates a non-active user with a specified email
      -- address
      function api.registration(email)
        -- Preprocessing the email address — making it all lowercase
        email = utils.lower(email)
        if not validator.email(email) then
          return response.error(error.INVALID_PARAMS)
        -- Checking if a user already exists with a given email
        -- address
        local user_tuple = user.get_by_email(email, user.COMMON_TYPE)
        if user_tuple ~= nil then
          if user_tuple[user.IS_ACTIVE] then
            return response.error(error.USER_ALREADY_EXISTS)
            local code = user.generate_activation_code(user_tuple[user.ID])
            return response.ok(code)
        -- Writing data to the space
        user_tuple = user.create({
          [user.EMAIL] = email,
          [user.TYPE] = user.COMMON_TYPE,
          [user.IS_ACTIVE] = false,
        local code = user.generate_activation_code(user_tuple[user.ID])
        return response.ok(code)
      return api
    return auth

    Excellent! Now users can create accounts.

    tarantool> auth = require(‘authman’).api(config)
    -- Using the api to get a registration confirmation code
    tarantool> ok, code = auth.registration(‘example@mail.ru’)
    -- This code needs to be sent to a user’s email address so that they
    -- can activate their account
    tarantool> code

    To be continued

    Also popular now: