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.
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:3301address. sampleuserhas privileges to select and modify data in thebandsspace and execute theget_bands_older_thanstored function. This user can be used to connect to the instance remotely.myapp.luadefines 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.
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.
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
...
]]
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.
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()
--[[
---
...
]]
Note
You can find the example with all the requests above on GitHub: net_box.