Connecting to a database using net.box

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 Module net.box.

Sample database configuration

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 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 how data is stored in a database and includes 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.

Making net.box requests interactively

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 -i
Tarantool 3.0.0-entrypoint-1144-geaff238d9
type 'help' for interactive help
tarantool>

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

Creating a net.box connection

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

net_box = require('net.box')
--[[
---
...
]]

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
...
]]

Using data operations

To get a space object and perform CRUD operations on it, use conn.space.<space_name>.

Note

Learn more about performing data operations from the CRUD operation examples section.

Inserting data

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]
...
]]

Querying data

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]
...
]]

Updating data

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]
...
]]

Deleting data

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]
...
]]

Executing stored procedures

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]]
-- ...

Closing the connection

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

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

Note

You can find the example with all the requests above on GitHub: net_box.

Version:

Connecting to a database using net.box

Sample database configuration

Making net.box requests interactively

Creating a net.box connection

Using data operations

Inserting data

Querying data

Updating data

Deleting data

Executing stored procedures

Closing the connection