LuaSQL

https://lunarmodules.github.io/luasql/
Admin
LuaSQL is a simple interface from Lua to various DBMSs, enabling Lua programs to connect to...
Tokens:10,413
Snippets:87
Trust Score:9.7
Update:1 month ago
Show doc for...
Tokens:
Raw
### Install LuaSQL drivers via LuaRocks

Source: https://lunarmodules.github.io/luasql/index.html

Install LuaSQL using LuaRocks with the driver for your database of choice. The command installs the specified driver package.

```bash
luarocks install luasql-duckdb
luarocks install luasql-firebird
luarocks install luasql-mysql
luarocks install luasql-oci8
luarocks install luasql-odbc
luarocks install luasql-postgres
luarocks install luasql-sqlite
luarocks install luasql-sqlite3
```

--------------------------------

### Iterator over SELECT results with LuaSQL MySQL driver

Source: https://lunarmodules.github.io/luasql/examples.html

Defines an iterator function that wraps a SQL query and returns a closure yielding rows via cursor:fetch(). The usage example connects with the MySQL driver and iterates over a 'contacts' table; the cursor is automatically closed at the end of the loop.

```lua
function rows (connection, sql_statement)
  local cursor = assert (connection:execute (sql_statement))
  return function ()
    return cursor:fetch()
  end
end

```

```lua
env = assert (require"luasql.mysql".mysql())
con = assert (env:connect"my_db")
for id, name, address in rows (con, "select * from contacts") do
  print (string.format ("%s: %s", name, address))
end

```

--------------------------------

### env:connect(sourcename[,username[,password[,hostname[,port[,socket[,client_flag]]]]]]])

Source: https://lunarmodules.github.io/luasql/manual.html

Connects to a MySQL database. This method extends the standard connect method with optional hostname, port, unix socket, and client flag parameters.

```APIDOC
## env:connect(sourcename[,username[,password[,hostname[,port[,socket[,client_flag]]]]]]])

### Description
Connects to a MySQL database. This method extends the standard connect method with optional hostname, port, unix socket, and client flag parameters.

### Method
N/A (Lua method)

### Endpoint
N/A

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
- **sourcename** (string) - Required - The database name.
- **username** (string) - Optional - The username for authentication.
- **password** (string) - Optional - The password for authentication.
- **hostname** (string) - Optional - The hostname of the database server.
- **port** (number) - Optional - The port number to connect to.
- **socket** (string) - Optional - The unix socket to use.
- **client_flag** (number) - Optional - The client flag.

### Request Example
```lua
local conn = env:connect("mydb", "myuser", "mypass", "localhost", 3306, "/tmp/mysql.sock", 0)
```

### Response
#### Success Response (200)
- **connection object** - The connection object on success.

#### Response Example
```lua
local conn = env:connect("mydb", "myuser", "mypass", "localhost", 3306, "/tmp/mysql.sock", 0)
```
```

--------------------------------

### env:connect(sourcename[,username[,password[,hostname[,port]]]]])

Source: https://lunarmodules.github.io/luasql/manual.html

Connects to a PostgreSQL database. In addition to the standard parameters, this method accepts optional hostname and port. The sourcename parameter can also contain all connection information as per the PQconnectdb function.

```APIDOC
## env:connect(sourcename[,username[,password[,hostname[,port]]]]])

### Description
Connects to a PostgreSQL database. This method extends the standard connect method with optional hostname and port parameters. The sourcename parameter can contain all connection information as specified in the PQconnectdb documentation.

### Method
N/A (Lua method)

### Endpoint
N/A

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
- **sourcename** (string) - Required - The database name or a connection string as per PQconnectdb.
- **username** (string) - Optional - The username for authentication.
- **password** (string) - Optional - The password for authentication.
- **hostname** (string) - Optional - The hostname of the database server.
- **port** (number) - Optional - The port number to connect to.

### Request Example
```lua
local conn = env:connect("dbname=mydb user=myuser")
```

### Response
#### Success Response (200)
- **connection object** - The connection object on success.

#### Response Example
```lua
local conn = env:connect("dbname=mydb user=myuser")
```
```

--------------------------------

### env:connect(sourcename[,username[,password]])

Source: https://lunarmodules.github.io/luasql/manual.html

Connects to a data source using the provided source name, username, and password. The format of sourcename varies by driver.

```APIDOC
## env:connect(sourcename[,username[,password]])

### Description
Connects to a data source specified in `sourcename` using `username` and `password` if they are supplied. The `sourcename` may vary according to each driver.

### Method
N/A (Lua method)

### Parameters
- **sourcename** (string) - Required - The data source name. Format varies by driver (e.g., database name for PostgreSQL, MySQL, SQLite; DSN for ODBC; service name for Oracle).
- **username** (string) - Optional - The username for authentication.
- **password** (string) - Optional - The password for authentication.

### Returns
- **connection object** - A connection object on success.

```

--------------------------------

### Create an environment object with the ODBC driver

Source: https://lunarmodules.github.io/luasql/manual.html

Loads the LuaSQL ODBC driver and calls its initialization function to create an environment object. The driver table is indexed with the same name as the driver.

```lua
local driver = require"luasql.odbc"
local env = driver.odbc()

```

--------------------------------

### Basic LuaSQL usage with PostgreSQL driver and expected output

Source: https://lunarmodules.github.io/luasql/examples.html

Basic LuaSQL usage with the PostgreSQL driver: load the driver, create an environment, connect to a data source, create a table, insert rows, and fetch results. The output shows the expected printed rows; note that the cursor is automatically closed when all rows are consumed.

```lua
-- load driver
local driver = require "luasql.postgres"
-- create environment object
env = assert (driver.postgres())
-- connect to data source
con = assert (env:connect("luasql-test"))
-- reset our table
res = con:execute"DROP TABLE people"
res = assert (con:execute[[
  CREATE TABLE people(
    name  varchar(50),
    email varchar(50)
  )
]])
-- add a few elements
list = {
  { name="Jose das Couves", email="jose@couves.com", },
  { name="Manoel Joaquim", email="manoel.joaquim@cafundo.com", },
  { name="Maria das Dores", email="maria@dores.com", },
}
for i, p in pairs (list) do
  res = assert (con:execute(string.format([[
    INSERT INTO people
    VALUES ('%s', '%s')]], p.name, p.email)
  ))
end
-- retrieve a cursor
cur = assert (con:execute"SELECT name, email from people")
-- print all rows, the rows will be indexed by field names
row = cur:fetch ({}, "a")
while row do
  print(string.format("Name: %s, E-mail: %s", row.name, row.email))
  -- reusing the table of results
  row = cur:fetch (row, "a")
end
-- close everything
cur:close() -- already closed because all the result set was consumed
con:close()
env:close()

```

```text
Name: Jose das Couves, E-mail: jose@couves.com
Name: Manoel Joaquim, E-mail: manoel.joaquim@cafundo.com
Name: Maria das Dores, E-mail: maria@dores.com

```

--------------------------------

### Load ODBC driver with require "luasql.odbc"

Source: https://lunarmodules.github.io/luasql/manual.html

Loads the ODBC driver and returns a table with an entry named 'odbc'. This is the standard way to load a LuaSQL driver.

```lua
local driver = require "luasql.odbc"
```

--------------------------------

### Load multiple drivers with require

Source: https://lunarmodules.github.io/luasql/manual.html

Loads both the ODBC and OCI8 drivers simultaneously. The driver name corresponds to the file system name, not necessarily the database name; here 'oci8' refers to the OCI8 API.

```lua
local odbc_driver = require "luasql.odbc"
local oci8_driver = require "luasql.oci8"
```

--------------------------------

### cur:fetch([table[,modestring]])

Source: https://lunarmodules.github.io/luasql/manual.html

Retrieves the next row of results. Can return data directly or copy into a provided table with optional mode string for indexing.

```APIDOC
## cur:fetch([table[,modestring]])

### Description
Retrieves the next row of results. If called without parameters, results are returned directly. If called with a table, results are copied into the table and the changed table is returned. An optional mode string controls the indexing of the resulting table.

### Method
fetch

### Parameters
#### Optional Parameters
- **table** (table) - Optional - A table to store the next row. Allows reuse of a table for performance.
- **modestring** (string) - Optional - A string indicating how the resulting table should be constructed. Can contain:
  - "n" - numerical indices (default)
  - "a" - alphanumerical indices

### Returns
- **data** (varies) - The next row of results, or nil if there are no more rows. Note that nil can be a valid result.
- **table** (table) - The changed table when a table parameter is provided.
```

--------------------------------

### env:close()

Source: https://lunarmodules.github.io/luasql/manual.html

Closes the environment object. Only succeeds if all connections associated with it have been closed.

```APIDOC
## env:close()

### Description
Closes the environment `env`. Only successful if all connections pertaining to it were closed first.

### Method
N/A (Lua method)

### Parameters
None

### Returns
- **true** (boolean) - In case of success.
- **false** (boolean) and **string message** (string) - In case of failure, stating the failure reason.

```

--------------------------------

### cur:getcolnames()

Source: https://lunarmodules.github.io/luasql/manual.html

Returns a list of column names for the result set.

```APIDOC
## cur:getcolnames()

### Description
Returns a list (table) of column names.

### Method
getcolnames

### Parameters
None

### Returns
- **list** (table) - A list of column names.
```

--------------------------------

### conn:ping()

Source: https://lunarmodules.github.io/luasql/manual.html

Checks whether the connection is closed (returns false) or opened (returns true). It returns nil followed by an error message in case of error. This method is specific to the MySQL driver.

```APIDOC
## conn:ping()

### Description
Checks whether the connection is closed (returns false) or opened (returns true). It returns nil followed by an error message in case of error. This method is specific to the MySQL driver.

### Method
N/A (Lua method)

### Endpoint
N/A

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Request Example
```lua
local is_open = conn:ping()
```

### Response
#### Success Response (200)
- **is_open** (boolean) - true if the connection is opened, false if it is closed.

#### Response Example
```lua
local is_open = conn:ping()
```
```

--------------------------------

### conn:close()

Source: https://lunarmodules.github.io/luasql/manual.html

Closes the connection. Only succeeds if all cursors associated with it have been closed and the connection is still open.

```APIDOC
## conn:close()

### Description
Closes the connection `conn`. Only successful if all cursors pertaining to it have been closed and the connection is still open.

### Method
N/A (Lua method)

### Parameters
None

### Returns
- **true** (boolean) - In case of success.
- **false** (boolean) and **string message** (string) - In case of failure, stating the failure reason.

```

--------------------------------

### cur:close()

Source: https://lunarmodules.github.io/luasql/manual.html

Closes the cursor. Returns true on success, or false and an error message on failure.

```APIDOC
## cur:close()

### Description
Closes this cursor.

### Method
close

### Parameters
None

### Returns
- **true** (boolean) - In case of success.
- **false** (boolean) - In case of failure.
- **message** (string) - Error message stating the failure reason (when failure).
```

--------------------------------

### conn:rollback()

Source: https://lunarmodules.github.io/luasql/manual.html

Rolls back the current transaction. May not work on database systems that do not implement transactions.

```APIDOC
## conn:rollback()

### Description
Rolls back the current transaction. This feature might not work on database systems that do not implement transactions.

### Method
N/A (Lua method)

### Parameters
None

### Returns
- **true** (boolean) - In case of success.
- **false** (boolean) - When the operation could not be performed or when it is not implemented.

```

--------------------------------

### conn:execute(statement)

Source: https://lunarmodules.github.io/luasql/manual.html

Executes the given SQL statement. Returns a cursor object if there are results, or the number of rows affected otherwise.

```APIDOC
## conn:execute(statement)

### Description
Executes the given SQL `statement`.

### Method
N/A (Lua method)

### Parameters
- **statement** (string) - Required - The SQL statement to execute.

### Returns
- **cursor object** - If there are results.
- **number** - The number of rows affected by the command otherwise.

```

--------------------------------

### cur:nextresult()

Source: https://lunarmodules.github.io/luasql/manual.html

Retrieves the next result set, if another result is available (e.g. multiple statements). This method is specific to the MySQL driver and requires the CLIENT_MULTI_STATEMENTS flag on connection.

```APIDOC
## cur:nextresult()

### Description
Retrieves the next result set, if another result is available (e.g. multiple statements). This method is specific to the MySQL driver and requires the CLIENT_MULTI_STATEMENTS flag on connection.

### Method
N/A (Lua method)

### Endpoint
N/A

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Request Example
```lua
local has_next = cur:nextresult()
```

### Response
#### Success Response (200)
- **has_next** (boolean) - true if next result was selected, false and -1 if no other result is available, false, errno and error message if an error occurred.

#### Response Example
```lua
local has_next = cur:nextresult()
```
```

--------------------------------

### cur:getcoltypes()

Source: https://lunarmodules.github.io/luasql/manual.html

Returns a list of column types for the result set.

```APIDOC
## cur:getcoltypes()

### Description
Returns a list (table) of column types.

### Method
getcoltypes

### Parameters
None

### Returns
- **list** (table) - A list of column types.
```

--------------------------------

### cur:hasnextresult()

Source: https://lunarmodules.github.io/luasql/manual.html

Checks if next result is available. This method is specific to the MySQL driver and requires the CLIENT_MULTI_STATEMENTS flag on connection.

```APIDOC
## cur:hasnextresult()

### Description
Checks if next result is available. This method is specific to the MySQL driver and requires the CLIENT_MULTI_STATEMENTS flag on connection.

### Method
N/A (Lua method)

### Endpoint
N/A

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Request Example
```lua
local has_next = cur:hasnextresult()
```

### Response
#### Success Response (200)
- **has_next** (boolean) - true or false.

#### Response Example
```lua
local has_next = cur:hasnextresult()
```
```

--------------------------------

### conn:commit()

Source: https://lunarmodules.github.io/luasql/manual.html

Commits the current transaction. May not work on database systems that do not implement transactions.

```APIDOC
## conn:commit()

### Description
Commits the current transaction. This feature might not work on database systems that do not implement transactions.

### Method
N/A (Lua method)

### Parameters
None

### Returns
- **true** (boolean) - In case of success.
- **false** (boolean) - When the operation could not be performed or when it is not implemented.

```

--------------------------------

### conn:setautocommit(boolean)

Source: https://lunarmodules.github.io/luasql/manual.html

Turns on or off the auto commit mode. May not work on database systems that do not implement transactions.

```APIDOC
## conn:setautocommit(boolean)

### Description
Turns on or off the "auto commit" mode. This feature might not work on database systems that do not implement transactions. On database systems that do not have the concept of "auto commit mode", but do implement transactions, this mechanism is implemented by the driver.

### Method
N/A (Lua method)

### Parameters
- **boolean** (boolean) - Required - The new auto commit mode (true to enable, false to disable).

### Returns
- **true** (boolean) - In case of success.
- **false** (boolean) - When the operation could not be performed or when it is not implemented.

```

--------------------------------

### cur:numrows()

Source: https://lunarmodules.github.io/luasql/manual.html

Returns the number of rows in the query result. This method is available in both PostgreSQL and MySQL drivers.

```APIDOC
## cur:numrows()

### Description
Returns the number of rows in the query result. This method is available in both PostgreSQL and MySQL drivers.

### Method
N/A (Lua method)

### Endpoint
N/A

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Request Example
```lua
local num = cur:numrows()
```

### Response
#### Success Response (200)
- **num_rows** (number) - The number of rows in the query result.

#### Response Example
```lua
local num = cur:numrows()
```
```

--------------------------------

### To-be-closed cursor with Lua 5.4 <close> attribute

Source: https://lunarmodules.github.io/luasql/examples.html

Uses a to-be-closed variable to automatically close the cursor when the function returns. Requires Lua 5.4 or higher and LuaSQL version > 2.6 compiled against Lua 5.4 or higher.

```lua
function getName(db, id)
  -- This code requires Lua 5.4 or higher due to the use of to-be-closed variables.
  local cur <close> = db:execute("SELECT name FROM contacts WHERE id = " .. id)
  return cur:fetch()
end

```

--------------------------------

### conn:escape(str)

Source: https://lunarmodules.github.io/luasql/manual.html

Escapes special characters in a string according to the connection's character set. This method is available in both PostgreSQL and MySQL drivers.

```APIDOC
## conn:escape(str)

### Description
Escapes special characters in the given string according to the connection's character set. This method is available in both PostgreSQL and MySQL drivers.

### Method
N/A (Lua method)

### Endpoint
N/A

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
- **str** (string) - Required - The string to escape.

### Request Example
```lua
local escaped = conn:escape("O'Reilly")
```

### Response
#### Success Response (200)
- **escaped_string** (string) - The escaped string.

#### Response Example
```lua
local escaped = conn:escape("O'Reilly")
```
```

--------------------------------

### cur:seek(row_number)

Source: https://lunarmodules.github.io/luasql/manual.html

Seeks to an arbitrary row in a query result set. The number of the first row is 1. This method is specific to the MySQL driver.

```APIDOC
## cur:seek(row_number)

### Description
Seeks to an arbitrary row in a query result set. The number of the first row is 1. This method is specific to the MySQL driver.

### Method
N/A (Lua method)

### Endpoint
N/A

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
- **row_number** (number) - Required - The row number to seek to (1-based).

### Request Example
```lua
cur:seek(3)
```

### Response
#### Success Response (200)
- **nothing** - No return value.

#### Response Example
```lua
cur:seek(3)
```
```

--------------------------------

### conn:getlastautoid()

Source: https://lunarmodules.github.io/luasql/manual.html

Obtains the value generated for an AUTO_INCREMENT column by the previous INSERT or UPDATE statement. This method is specific to the MySQL driver.

```APIDOC
## conn:getlastautoid()

### Description
Obtains the value generated for an AUTO_INCREMENT column by the previous INSERT or UPDATE statement. This method is specific to the MySQL driver.

### Method
N/A (Lua method)

### Endpoint
N/A

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Request Example
```lua
local id = conn:getlastautoid()
```

### Response
#### Success Response (200)
- **last_auto_id** (number) - The number of the last value generated for an AUTO_INCREMENT column.

#### Response Example
```lua
local id = conn:getlastautoid()
```
```
Tokens:
Raw