Module net.box

The net.box module contains connectors to remote database systems. One variant is for connecting to MySQL or MariaDB or PostgreSQL (see SQL DBMS modules reference). The other variant, which is discussed in this section, is for connecting to Tarantool server instances via a network.

Examples on GitHub: sample_db, net_box

The tutorial shows how to use net.box to connect to a remote Tarantool instance, perform CRUD operations, and execute stored procedures. For more information about the net.box module API, check net_box-module.

This section describes the configuration of a sample database that allows remote connections:

 
credentials:  users:    sampleuser:      password: '123456'      privileges:      - permissions: [ read, write ]        spaces: [ bands ]      - permissions: [ execute ]        functions: [ get_bands_older_than ]groups:  group001:    replicasets:      replicaset001:        instances:          instance001:            iproto:              listen:              - uri: '127.0.0.1:3301'app:  file: 'myapp.lua'

• The configuration contains one instance that listens for incoming requests on the 127.0.0.1:3301 address.
• sampleuser has privileges to select and modify data in the bands space and execute the get_bands_older_than stored function. This user can be used to connect to the instance remotely.
• myapp.lua defines the data model and a stored function.

The myapp.lua file looks as follows:

 
-- Create a space --box.schema.space.create('bands')-- Specify field names and types --box.space.bands:format({    { name = 'id', type = 'unsigned' },    { name = 'band_name', type = 'string' },    { name = 'year', type = 'unsigned' }})-- Create indexes --box.space.bands:create_index('primary', { parts = { 'id' } })box.space.bands:create_index('band', { parts = { 'band_name' } })box.space.bands:create_index('year_band', { parts = { { 'year' }, { 'band_name' } } })-- Create a stored function --box.schema.func.create('get_bands_older_than', {    body = [[    function(year)        return box.space.bands.index.year_band:select({ year }, { iterator = 'LT', limit = 10 })    end    ]]})

You can find the full example on GitHub: sample_db.

To try out net.box requests in the interactive console, start the sample_db application using tt start:

 
$ tt start sample_db

Then, use the tt run -i command to start an interactive console:

 
$ tt run -iTarantool 3.0.0-entrypoint-1144-geaff238d9type 'help' for interactive helptarantool>

In the console, you can create a net.box connection and try out data operations.

To load the net.box module, use the require() directive:

To create a connection, pass a database URI to the net_box.connect() method:

 
conn = net_box.connect('sampleuser:123456@127.0.0.1:3301')--[[---...]]

connection:ping() can be used to check the connection status:

 
conn:ping()--[[---- true...]]

Then, you can get a space object and perform CRUD operations on it using conn.space.<space_name>.

In the example below, four tuples are inserted into the bands space:

 
conn.space.bands:insert({ 1, 'Roxette', 1986 })--[[---- - [1, 'Roxette', 1986]...]]conn.space.bands:insert({ 2, 'Scorpions', 1965 })--[[---- [2, 'Scorpions', 1965]...]]conn.space.bands:insert({ 3, 'Ace of Base', 1987 })--[[---- [3, 'Ace of Base', 1987]...]]conn.space.bands:insert({ 4, 'The Beatles', 1960 })--[[---- [4, 'The Beatles', 1960]...]]

The example below shows how to get a tuple by the specified primary key value:

 
conn.space.bands:select({ 1 })--[[---- - [1, 'Roxette', 1986]...]]

You can also get a tuple by the value of the specified index as follows:

 
conn.space.bands.index.band:select({ 'The Beatles' })--[[---- - [4, 'The Beatles', 1960]...]]

space_object.update() updates a tuple identified by the primary key. This method accepts a full key and an operation to execute:

 
conn.space.bands:update({ 2 }, { { '=', 'band_name', 'Pink Floyd' } })--[[---- [2, 'Pink Floyd', 1965]...]]

space_object.upsert() updates an existing tuple or inserts a new one. In the example below, a new tuple is inserted:

 
conn.space.bands:upsert({ 5, 'The Rolling Stones', 1962 }, { { '=', 'band_name', 'The Doors' } })--[[---...]]

In this example, space_object.replace() is used to delete the existing tuple and insert a new one:

 
conn.space.bands:replace({ 1, 'Queen', 1970 })--[[---- [1, 'Queen', 1970]...]]

The space_object.delete() call in the example below deletes a tuple whose primary key value is 5:

 
conn.space.bands:delete({ 5 })--[[---- [5, 'The Rolling Stones', 1962]...]]

To execute a stored procedure, use the connection:call() method:

 
conn:call('get_bands_older_than', { 1966 })-- ----- - [[2, 'Pink Floyd', 1965], [4, 'The Beatles', 1960]]-- ...

The connection:close() method can be used to close the connection when it is no longer needed:

 
conn:close()--[[---...]]

You can call the following methods:

• require('net.box') – to get a net.box object (named net_box for examples in this section)
• net_box.connect() – to connect and get a connection object (named conn for examples in this section)
• other net.box() routines, passing conn:, to execute requests on the remote database system
• conn:close – to disconnect

All net.box methods are fiber-safe, that is, it is safe to share and use the same connection object across multiple concurrent fibers. In fact that is perhaps the best programming practice with Tarantool. When multiple fibers use the same connection, all requests are pipelined through the same network socket, but each fiber gets back a correct response. Reducing the number of active sockets lowers the overhead of system calls and increases the overall server performance. However for some cases a single connection is not enough – for example, when it is necessary to prioritize requests or to use different authentication IDs.

Most net.box methods accept the last {options} argument, which can be:

• {timeout=...}. For example, a method whose last argument is {timeout=1.5} will stop after 1.5 seconds on the local node, although this does not guarantee that execution will stop on the remote server node.
• {buffer=...}. For an example, see the buffer module.
• {is_async=...}. For example, a method whose last argument is {is_async=true} will not wait for the result of a request. See the is_async description.
• {on_push=... on_push_ctx=...}. For receiving out-of-band messages. See the box.session.push() description.
• {return_raw=...} (since version 2.10.0). If set to true, net.box returns response data wrapped in a MsgPack object instead of decoding it to Lua. The default value is false. For an example, see option description below.

The diagram below shows possible connection states and transitions:

On this diagram:

• net_box.connect() method spawns a worker fiber, which will establish the connection and start the state machine.
• The state machine goes to the initial state.
• Authentication and schema upload. It is possible later on to re-enter the fetch_schema state from active to trigger schema reload.
• The state changes to the graceful_shutdown state when the state machine receives a box.shutdown event from the remote host (see conn:on_shutdown()). Once all pending requests are completed, the state machine switches to the error (error_reconnect) state.
• The transport goes to the error state in case of an error. It can happen, for example, if the server closed the connection. If the reconnect_after option is set, instead of the 'error' state, the transport goes to the error_reconnect state.
• conn.close() method sets the state to closed and kills the worker. If the transport is already in the error state, close() does nothing.

Below is a list of all net.box functions.

### connect(URI [, {option[s]}]) {params[anchor=net_box-connect]} Create a new connection. The connection is established on demand, at the time of the first request. It can be re-established automatically after a disconnect (see `reconnect_after` option below). The returned `conn` object supports methods for making remote requests, such as select, update or delete.

Parameters:

• URI — the URI of the target for the connection. The URI type may be string or table as for the uri.parse() function. The table form is used to set up connection parameters, see the URI page for details.

• options — the supported options are shown below:

• user/password: two options to connect to a remote host other than through URI. For example, instead of connect('username:userpassword@localhost:3301') you can write connect('localhost:3301', {user = 'username', password='userpassword'}).

• wait_connected: a connection timeout. By default, the connection is blocked until the connection is established, but if you specify wait_connected=false, the connection returns immediately. If you specify this timeout, it will wait before returning (wait_connected=1.5 makes it wait at most 1.5 seconds).

• reconnect_after: a number of seconds to wait before reconnecting. The default value, as with the other connect options, is nil. If reconnect_after is greater than zero, then a net.box instance will attempt to reconnect if a connection is lost or a connection attempt fails. This makes transient network failures transparent to the application. Reconnection happens automatically in the background, so requests that initially fail due to connection drops fail, are transparently retried. The number of retries is unlimited, connection retries are made after any specified interval (for example, reconnect_after=5 means that reconnect attempts are made every 5 seconds). When a connection is explicitly closed or when the Lua garbage collector removes it, then reconnect attempts stop.

• connect_timeout: a number of seconds to wait before returning "error: Connection timed out".

• fetch_schema: a boolean option that controls fetching schema changes from the server. Default: true. If you don't operate with remote spaces, for example, run only call or eval, set fetch_schema to false to avoid fetching schema changes which is not needed in this case.

• required_protocol_version: a minimum version of the IPROTO protocol supported by the server. If the version of the IPROTO protocol supported by the server is lower than specified, the connection will fail with an error message. With required_protocol_version = 1, all connections fail where the IPROTO protocol version is lower than 1.

• required_protocol_features: specified IPROTO protocol features supported by the server. You can specify one or more net.box features from the table below. If the server does not support the specified features, the connection will fail with an error message. With required_protocol_features = {'transactions'}, all connections fail where the server has transactions: false.

To learn more about IPROTO features, see IPROTO_ID and the IPROTO_FEATURES key.

Returns

conn object

Return type

userdata

Examples:

 
net_box = require('net.box')conn = net_box.connect('localhost:3301')conn = net_box.connect('127.0.0.1:3302', {wait_connected = false})conn = net_box.connect('127.0.0.1:3304', {required_protocol_version = 4, required_protocol_features = {'transactions', 'streams'}, })

new() is a synonym for connect(). It is retained for backward compatibility. For more information, see the description of net_box.connect().

For a local Tarantool server, there is a pre-created always-established connection object named {net_box}.self. Its purpose is to make polymorphic use of the net_box API easier. Therefore conn = {net_box}.connect('localhost:3301') can be replaced by conn = {net_box}.self.

However, there is an important difference between the embedded connection and a remote one:

• With the embedded connection, requests which do not modify data do not yield. When using a remote connection, due to the implicit rules any request can yield, and the database state may have changed by the time it regains control.
• All the options passed to a request (as is_async, on_push, timeout) will be ignored.

: conn

Execute a PING command.

Parameters:

• options (table) — the supported option is timeout={seconds}

Returns

true on success, false on error

Return type

boolean

Example:

 
net_box.self:ping({timeout = 0.5})

Wait for connection to be active or closed.

• timeout (number) — in seconds

Returns

true when connected, false on failure.

Return type

boolean

Example:

 
net_box.self:wait_connected()

Show whether connection is active or closed.

Returns

true if connected, false on failure.

Return type

boolean

Example:

 
net_box.self:is_connected()

[since 1.7.2] Wait for a target state.

• states (string) — target states

• timeout (number) — in seconds

Returns

true when a target state is reached, false on timeout or connection closure

Return type

boolean

Examples:

 
-- wait infinitely for 'active' state:conn:wait_state('active')-- wait for 1.5 secs at most:conn:wait_state('active', 1.5)-- wait infinitely for either `active` or `fetch_schema` state:conn:wait_state({active=true, fetch_schema=true})

Close a connection.

Connection objects are destroyed by the Lua garbage collector, just like any other objects in Lua, so an explicit destruction is not mandatory. However, since close() is a system call, it is good programming practice to close a connection explicitly when it is no longer needed, to avoid lengthy stalls of the garbage collector.

Example:

 
conn:close()

conn.space.{space-name}:select``({...}) is the remote-call equivalent of the local call box.space.{space-name}:select``{...} (see details). For an additional option see Module buffer and skip-header.

Example:

 
conn.space.testspace:select({1,'B'}, {timeout=1})

conn.space.{space-name}:get(...) is the remote-call equivalent of the local call box.space.{space-name}:get(...)(see details).

Example:

 
conn.space.testspace:get({1})

conn.space.{space-name}:insert(...) is the remote-call equivalent of the local call box.space.{space-name}:insert(...) (see details). For an additional option see Module buffer and skip-header.

Example:

 
conn.space.testspace:insert({2,3,4,5}, {timeout=1.1})

conn.space.{space-name}:replace(...) is the remote-call equivalent of the local call box.space.{space-name}:replace(...) (see details). For an additional option see Module buffer and skip-header.

Example:

 
conn.space.testspace:replace({5,6,7,8})

conn.space.{space-name}:update(...) is the remote-call equivalent of the local call box.space.{space-name}:update(...) (see details). For an additional option see Module buffer and skip-header.

Example:

 
conn.space.Q:update({1},{{'=',2,5}}, {timeout=0})

conn.space.{space-name}:upsert(...) is the remote-call equivalent of the local call box.space.{space-name}:upsert(...). (see details). For an additional option see Module buffer and skip-header.

conn.space.{space-name}:delete(...) is the remote-call equivalent of the local call box.space.{space-name}:delete(...) (see details). For an additional option see Module buffer and skip-header.

conn:eval({Lua-string}) evaluates and executes the expression in Lua-string, which may be any statement or series of statements. An execute privilege is required; if the user does not have it, an administrator may grant it with box.schema.user.grant({username}, 'execute', 'universe').

To ensure that the return from conn:eval is whatever the Lua expression returns, begin the Lua-string with the word "return".

Examples:

 
tarantool> --Lua-stringtarantool> conn:eval('function f5() return 5+5 end; return f5();')---- 10...tarantool> --Lua-string, {arguments}tarantool> conn:eval('return ...', {1,2,{3,'x'}})---- 1- 2- [3, 'x']...tarantool> --Lua-string, {arguments}, {options}tarantool> conn:eval('return {nil,5}', {}, {timeout=0.1})---- [null, 5]...

{/note}

method call(function-name, [, {arguments} [, {options} ]])

conn:call('func', {'1', '2', '3'}) is the remote-call equivalent of func('1', '2', '3'). That is, conn:call is a remote stored-procedure call. The return from conn:call is whatever the function returns.

Limitation: the called function cannot return a function, for example if func2 is defined as function func2 () return func end then conn:call(func2) will return "error: unsupported Lua type 'function'".

Examples:

 
tarantool> -- create 2 functions with conn:eval()tarantool> conn:eval('function f1() return 5+5 end;')tarantool> conn:eval('function f2(x,y) return x,y end;')tarantool> -- call first function with no parameters and no optionstarantool> conn:call('f1')---- 10...tarantool> -- call second function with two parameters and one optiontarantool> conn:call('f2',{1,'B'},{timeout=99})---- 1- B...

method watch(key, func)

Subscribe to events broadcast by a remote host.

• key (string) — a key name of an event to subscribe to

• func (function) — a callback to invoke when the key value is updated

Returns

a watcher handle. The handle consists of one method – unregister(), which unregisters the watcher.

To read more about watchers, see the Functions for watchers section.

The method has the same syntax as the box.watch() function, which is used for subscribing to events locally.

Watchers survive reconnection (see the reconnect_after connection option). All registered watchers are automatically resubscribed when the connection is reestablished.

If a remote host supports watchers, the watchers key will be set in the connection peer_protocol_features. For details, check the net.box features table.

Example 1:

Server:

 
-- Broadcast value 42 for the 'foo' key.box.broadcast('foo', 42)

Client:

 
conn = net.box.connect(URI)local log = require('log')-- Subscribe to updates of the 'foo' key.w = conn:watch('foo', function(key, value)    assert(key == 'foo')    log.info("The box.id value is '%d'", value)end)

If you don't need the watcher anymore, you can unregister it using the command below:

 
w:unregister()

Example 2:

The net.box module provides the ability to monitor updates of a configuration stored in a Tarantool-based configuration storage by watching path or prefix changes. In the example below, conn:watch() is used to monitor updates of a configuration stored by the /myapp/config/all path:

 
net_box = require('net.box')local conn = net_box.connect('127.0.0.1:4401')local log = require('log')conn:watch('config.storage:/myapp/config/all', function(key, value)    log.info("Configuration stored by the '/myapp/config/all' key is changed")end)

You can find the full example here: config_storage.

method request(... {is_async=...})

{is_async=true|false} is an option which is applicable for all net_box requests including conn:call, conn:eval, and the conn.space.space-name requests.

The default is is_async=false, meaning requests are synchronous for the fiber. The fiber is blocked, waiting until there is a reply to the request or until timeout expires. Before Tarantool version 1.10, the only way to make asynchronous requests was to put them in separate fibers.

The non-default is is_async=true, meaning requests are asynchronous for the fiber. The request causes a yield but there is no waiting. The immediate return is not the result of the request, instead it is an object that the calling program can use later to get the result of the request.

This immediately-returned object, which we'll call "future", has its own methods:

• future:is_ready() which will return true when the result of the request is available,
• future:result() to get the result of the request (returns the response or nil in case it's not ready yet or there has been an error),
• future:wait_result(timeout) to wait until the result of the request is available and then get it, or throw an error if there is no result after the timeout exceeded,
• future:discard() to abandon the object.

Typically a user would say future=request-name(...{is_async=true}), then either loop checking future:is_ready() until it is true and then say request_result=future:result(), or say request_result=future:wait_result(...). Alternatively the client could check for "out-of-band" messages from the server by calling pairs() in a loop – see box.session.push().

A user would say future:discard() to make a connection forget about the response – if a response for a discarded object is received then it will be ignored, so that the size of the requests table will be reduced and other requests will be faster.

Examples:

 
-- Insert a tuple asynchronously --tarantool> future = conn.space.bands:insert({10, 'Queen', 1970}, {is_async=true})---...tarantool> future:is_ready()---- true...tarantool> future:result()---- [10, 'Queen', 1970]...-- Iterate through a space with 10 records to get data in chunks of 3 records --tarantool> while true do               future = conn.space.bands:select({}, {limit=3, after=position, fetch_pos=true, is_async=true})               result = future:wait_result()               tuples = result[1]               position = result[2]               if position == nil then                   break               end               print('Chunk size: '..#tuples)           endChunk size: 3Chunk size: 3Chunk size: 3Chunk size: 1---...

Typically {is_async=true} is used only if the load is large (more than 100,000 requests per second) and latency is large (more than 1 second), or when it is necessary to send multiple requests in parallel then collect responses (sometimes called a "map-reduce" scenario).

{return_raw=true} is ignored for:

• Methods that return nil: begin, commit, rollback, upsert, prepare.
• index.count (returns number).

For execute, the option is applied only to data (rows). Metadata is decoded even if {return_raw=true}.

Example:

 
local c = require('net.box').connect(uri)local mp = c.eval('eval ...', {1, 2, 3}, {return_raw = true})mp:decode() -- {1, 2, 3}

The option can be useful if you want to pass a response through without decoding or with partial decoding. The usage of MsgPack object can reduce pressure on the Lua garbage collector.

Create a stream.

Example:

 
-- Start a server to create a new streamlocal conn = net_box.connect('localhost:3301')local conn_space = conn.space.testlocal stream = conn:new_stream()local stream_space = stream.space.test

stream

Begin a stream transaction. Instead of the direct method, you can also use the call, eval or execute methods with SQL transaction.

• txn_isolation — transaction isolation level

Commit a stream transaction. Instead of the direct method, you can also use the call, eval or execute methods with SQL transaction.

Examples:

 
-- Begin stream transactionstream:begin()-- In the previously created ``accounts`` space with the primary key ``test``, modify the fields 2 and 3stream.space.accounts:update(test_1, {{'-', 2, 370}, {'+', 3, 100}})-- Commit stream transactionstream:commit()

Rollback a stream transaction. Instead of the direct method, you can also use the call, eval or execute methods with SQL transaction.

Example:

 
-- Test rollback for memtx spacespace:replace({1})-- Select return tuple that was previously inserted, because this select belongs to stream transactionspace:select({})stream:rollback()-- Select is empty, stream transaction rollbackspace:select({})

With the net.box module, you can use the following triggers:

conn:on_connect([trigger-function[, old-trigger-function]])

Define a trigger for execution when a new connection is established, and authentication and schema fetch are completed due to an event such as net_box.connect.

If a trigger function issues net_box requests, they must be asynchronous ({is_async = true}). An attempt to wait for request completion with future:pairs() or future:wait_result() in the trigger function will result in an error.

If the trigger execution fails and an exception happens, the connection's state changes to 'error'. In this case, the connection is terminated, regardless of the reconnect_after option's value. Can be called as many times as reconnection happens, if reconnect_after is greater than zero.

• trigger-function (function) — the trigger function. Takes the conn object as the first argument.

• old-trigger-function (function) — an existing trigger function to replace with trigger-function

Returns

nil or function pointer

conn:on_disconnect([trigger-function[, old-trigger-function]])

Define a trigger for execution after a connection is closed. If the trigger function causes an error, the error is logged but otherwise is ignored. Execution stops after a connection is explicitly closed, or once the Lua garbage collector removes it.

• trigger-function (function) — the trigger function. Takes the conn object as the first argument

• old-trigger-function (function) — an existing trigger function to replace with trigger-function

Returns

nil or function pointer

conn:on_shutdown([trigger-function[, old-trigger-function]])

Define a trigger for shutdown when a box.shutdown event is received.

The trigger starts in a new fiber. While the on_shutdown() trigger is running, the connection stays active. It means that the trigger callback is allowed to send new requests.

After the trigger return, the net.box connection goes to the graceful_shutdown state (check the state diagram for details). In this state, no new requests are allowed. The connection waits for all pending requests to be completed.

Once all in-progress requests have been processed, the connection is closed. The state changes to error or error_reconnect (if the reconnect_after option is defined).

Servers that do not support the box.shutdown event or IPROTO_WATCH just close the connection abruptly. In this case, the on_shutdown() trigger is not executed.

• trigger-function (function) — the trigger function. Takes the conn object as the first argument

• old-trigger-function (function) — an existing trigger function to replace with trigger-function

Returns

nil or function pointer

Define a trigger executed when some operation has been performed on the remote server after schema has been updated. So, if a server request fails due to a schema version mismatch error, schema reload is triggered.

If a trigger function issues net_box requests, they must be asynchronous ({is_async = true}). An attempt to wait for request completion with future:pairs() or future:wait_result() in the trigger function will result in an error.

• trigger-function (function) — the trigger function. Takes the conn object as the first argument

• old-trigger-function (function) — an existing trigger function to replace with trigger-function

Returns

nil or function pointer