Skip to content
Reflex Reflex Reflex Docs

Sql

library

Connects to relational databases over JDBC and runs SQL on them. Sql.connect returns a SqlConnection with query, execute and transaction functions. Things to know: the agent does not bundle JDBC drivers. Add the driver JAR of your database (PostgreSQL, MySQL, SQL Server, Oracle, ...) to the `plugins/` folder next to agent.jar, or put it on the agent's classpath, and restart the agent; otherwise connect fails with "Failed to open SQL connection: No suitable driver found for ...". Always pass values as ? parameters instead of building SQL strings. Errors from the driver are raised as Lua errors with the driver's message. Connections are not closed automatically: call close() on the connection, or Sql.close() to close every connection opened in this script. Use secret() for passwords.

  • Kind Library, a global in every script
  • Category APIs & Data
  • Functions 3

Functions

  • connect SqlConnection Opens a JDBC connection and returns it.
  • nullValue table Returns a marker that stands for SQL NULL.
  • close self Closes every connection opened with Sql.connect in this script that is still open.

connect()

Section titled “connect()”
Sql.connect(url: string)→SqlConnectionSql.connect(url: string, options: table)→SqlConnectionSql.connect(url: string, user: string, password: string)→SqlConnection

Opens a JDBC connection and returns it. The driver for the URL must be available: JAR files in the `plugins/` folder (or on the classpath) are picked up, none is bundled. Raises an error when the URL is empty, the driver class is not found, no driver accepts the URL ("No suitable driver") or the database refuses the connection ("Failed to open SQL connection: ..."). Credentials can be passed as separate user and password arguments, or inside the options table. The options table understands: user, password, driver (a driver class name to load first; modern drivers register themselves, so it is rarely needed), autoCommit (boolean; the driver default, normally true, when left out), properties (a table of extra JDBC properties) and every other string key, which is passed to the driver as a JDBC property (for example ssl = true or ApplicationName = "reflex"). Property values are converted to strings.

Parameters

url string
JDBC URL, for example jdbc:postgresql://host:5432/db
user string
Database user name
password string
Database password; use secret()
options table
Connection options: user, password, driver, autoCommit, properties; any other string key is passed to the driver as a JDBC property

Returns

SqlConnection An open JDBC connection, returned by Sql.connect.

Example 1

Connect with user and password, run a parameterised query

local db = Sql.connect("jdbc:postgresql://db.example.com:5432/shop", "reporter", secret("db_password"))
local rows = db.query("select id, name from customers where country = ?", { "NL" })
for _, row in ipairs(rows) do
    print(row.id, row.name)
end
db.close()

Example 2

Connect with an options table and manage the transaction yourself

local db = Sql.connect("jdbc:sqlserver://sql.example.com:1433;databaseName=shop", {
    user = "reporter",
    password = secret("db_password"),
    encrypt = true,
    autoCommit = false,
})
db.execute("update stock set quantity = quantity - ? where sku = ?", { 1, "A-100" })
db.commit()
db.close()

nullValue()

Section titled “nullValue()”
Sql.nullValue()→tableSql.nullValue(sqlType: string)→table

Returns a marker that stands for SQL NULL. Pass it in the parameter list of query or execute to bind NULL: a Lua nil cannot be used for this, because a nil in the middle of a list leaves a gap and the list is rejected ("must contain contiguous indexes from 1"). With a type name the NULL is bound with that SQL type, which some drivers need (VARCHAR, INTEGER, BIGINT, TIMESTAMP and the other java.sql.Types names, case-insensitive); an unknown name raises an error. Query results use the same marker for NULL column values, as a table {__sqlNull = true}. A NULL column is therefore not nil: test it with type(value) == "table" and value.__sqlNull.

Parameters

sqlType string optional
SQL type name such as VARCHAR, INTEGER or TIMESTAMP

Returns

table A marker table ({__sqlNull = true}), with sqlType set when a type was given

Example 

local db = Sql.connect("jdbc:postgresql://db.example.com/shop", "app", secret("db_password"))
db.execute("update customers set phone = ? where id = ?", { Sql.nullValue("VARCHAR"), 42 })
db.close()

close()

Section titled “close()”
Sql.close()→self

Closes every connection opened with Sql.connect in this script that is still open. Errors while closing are ignored. Changes that were not committed are not committed by this call.

Returns

self chainable The library itself, so calls can be chained.