box.execute() | Tarantool


box.execute(sql-statement[, extra-parameters])

Execute the SQL statement contained in the sql-statement parameter.


depends on statement

There are two ways to pass extra parameters to box.execute():

  • The first way, which is the preferred way, is to put placeholders in the string, and pass a second argument, an extra-parameters table. A placeholder is either a question mark “?”, or a colon “:” followed by a name. An extra parameter is any Lua expression.

    If placeholders are question marks, then they are replaced by extra-parameters values in corresponding positions. That is, the first ? is replaced by the first extra parameter, the second ? is replaced by the second extra parameter, and so on.

    If placeholders are :names, then they are replaced by extra-parameters values with corresponding names.

    For example, this request that contains literal values 1 and 'x':

    box.execute([[INSERT INTO tt VALUES (1, 'x');]]);

    … is the same as the request below containing two question-mark placeholders (? and ?) and a two-element extra-parameters table:

    x = {1,'x'}
    box.execute([[INSERT INTO tt VALUES (?, ?);]], x);

    … and is the same as this request containing two :name placeholders (:a and :b) and a two-element extra-parameters table with elements named “a” and “b”:

    box.execute([[INSERT INTO tt VALUES (:a, :b);]], {{[':a']=1},{[':b']='x'}})
  • The second way is to concatenate strings. For example, the Lua script below inserts 10 rows with different primary-key values into table t:

    for i=1,10,1 do
        box.execute("insert into t values (" .. i .. ")")

    When creating SQL statements based on user input, application developers should beware of SQL injection.

Since box.execute() is an invocation of a Lua function, it either causes an error message or returns a value.

For some statements the returned value contains a field named rowcount, for example:

tarantool> box.execute([[CREATE TABLE table1 (column1 INT PRIMARY key, column2 VARCHAR(10));]])
- rowcount: 1
tarantool> box.execute([[INSERT INTO table1 VALUES (55,'Hello SQL world!');]])
- rowcount: 1

For statements that cause generation of values for PRIMARY KEY AUTOINCREMENT columns, there is a field named autoincrement_id.

For SELECT or PRAGMA statements, the returned value is a result set, containing a field named metadata (a table with column names and Tarantool/NoSQL type names) and a field named rows (a table with the contents of each row).

For example, for a statement SELECT "x" FROM t WHERE "x"=5; where "x" is an INTEGER column and there is one row, a display on the Tarantool client might look like this:

tarantool> box.execute([[SELECT "x" FROM t WHERE "x"=5;]])
- metadata:
  - name: x
    type: integer
  - [5]

For a look at raw format of SELECT results, see Binary protocol – responses for SQL.

The order of components within a map is not guaranteed.

If sql_full_metadata in the _session_settings system table is TRUE, then result set metadata may include these things in addition to name and type:

  • collation (present only if COLLATE clause is specified for a STRING) = “Collation”.
  • is_nullable (present only if the select list specified a base table column and nothing else) = false if column was defined as NOT NULL, otherwise true. If this is not present, that implies that nullability is unknown.
  • is_autoincrement (present only if the select list specified a base table column and nothing else) = true if column was defined as PRIMARY KEY AUTOINCREMENT, otherwise false.
  • span (always present) = the original expression in a select list, which often is the same as name if the select list specifies a column name and nothing else, but otherwise differs, for example, after SELECT x+55 AS x FROM t; the name is X and the span is x+55. If span and name are the same then the content is MP_NIL.

Alternative: if you are using the Tarantool server as a client, you can switch languages as follows:

\set language sql
\set delimiter ;

Afterwards, you can enter any SQL statement directly without needing box.execute().

There is also an execute() function available in module For example, you can execute conn:execute(sql-statement]) after conn = net_box.connect(url-string).

Found what you were looking for?