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.

Note

This tutorial shows how to make CRUD requests to a single-instance Tarantool database. To make requests to a sharded Tarantool cluster with the CRUD module, use its API for CRUD operations.

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

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