Fetches 1 or 0 results from the database

Params:

• query: The query string that needs to be executed

• params: An array (table) containing the parameters that this function needs

Fetches all results into a table

Params:

• query: The query string that needs to be executed

• params: An array (table) containing the parameters that this function needs

Runs a thread in the background that fetches all results. Allowing you to consume the results in batches, or do other things while the query is being executed

Params:

• query: The query string that needs to be executed

• params: An array (table) containing the parameters that this function needs

• chunk_count: How big the batches are that will be returned from the background thread to the main one. Higher batch count may improve performance

Fetches exactly 1 value from the database.

Params:

• query: The query string that needs to be executed

• params: An array (table) containing the parameters that this function needs

Params:

• query: The query string that needs to be executed

• params: An array (table) containing the parameters that this function needs

A shorthand to run a basic insert command.

WARNING!:

the table and column names are NOT escaped. SQL injection IS possible if user input is allowed for these values.

The values that get inserted ARE properly escaped. For these, SQL injection is NOT possible.

Parameters:

• name: the table name that will be inserted into

• values: A table where the keys are the column names and the values are the values that will be inserted

• needs_to_get_quoted: If the table name should get quotes around it. Defaults to false, set to true if the name contains .'s

A shorthand to run a basic bulk insert command.

WARNING!:

the table and column names are NOT escaped. SQL injection IS possible if user input is allowed for these values.

The values that get inserted ARE properly escaped. For these, SQL injection is NOT possible.

Parameters:

• name: the table name that will be inserted into

• columns: the columns that the query will insert into

• values: an table containing a table for every row. Columns are entirely decided by order

• needs_to_get_quoted: If the table name should get quotes around it. Defaults to false, set to true if the name contains .'s

A shorthand to run a basic update command.

WARNING!:

the table and column names are NOT escaped. SQL injection IS possible if user input is allowed for these.

The values that get inserted ARE properly escaped. For these, SQL injection is NOT possible.

Parameters:

• name: the table name that will be inserted into

• old_values: A table used to construct the where part of the query. The keys are the column names and the values are the values that will be matched against

• new_values: A table where the keys are the column names and the values are the values that this column will be updated to

• needs_to_get_quoted: If the table name should get quotes around it. Defaults to false, set to true if the name contains .'s

A simple shorthand to do an insert and specify how the row needs to be updated instead if there is a conflict on the given index

WARNING!:

the table, index and column names are NOT escaped. SQL injection IS possible if user input is allowed for these.

The values that get inserted ARE properly escaped. For these, SQL injection is NOT possible.

Parameters:

• name: the table name that will be inserted into

• values: A table used to construct the where part of the query. The keys are the column names and the values are the values that will be matched against

• new_values: A table where the keys are the column names and the values are the values that this column will be updated to

• needs_to_get_quoted: If the table name should get quotes around it. Defaults to false, set to true if the name contains .'s

A shorthand to run a basic delete command.

WARNING!:

the table and column names are NOT escaped. SQL injection IS possible if user input is allowed for these values.

The values that get inserted ARE properly escaped. For these, SQL injection is NOT possible.

Parameters:

• name: the table name that will be inserted into

• old_values: A table used to construct the where part of the query. The keys are the column names and the values are the values that will be matched against

• needs_to_get_quoted: If the table name should get quotes around it. Defaults to false, set to true if the name contains .'s

Starts a new transaction.

Params:

• func: The function that will be executed after the transaction has been made.

This function can return 2 values, the first is a boolean that determines if the transaction should be committed or not.

The second can be of any type and will be returned as is

After this function is executed the transaction will either be committed or rolled back.

It will be rolled back if the callback threw an error, or returned false for the first return value

Otherwise, it will be committed

Examples:

Committing

local tealsql = require("pages/tealsql/definitions.tealsql")
tealsql.connect("postgres://userName:password@host/database",function(con:tealsql.Connection):{string:integer}
    local success, res = con:begin(function(con:tealsql.Connection):(boolean,integer)
        con:execute("INSERT INTO some_table (some_column) VALUES (1)");
        return true, 1
    end)
    assert(success)
    assert(res ==  1)
end)

local tealsql = require("pages/tealsql/definitions.tealsql")
tealsql.connect("postgres://userName:password@host/database", function(con)
   local success, res = con:begin(function(con)
      con:execute("INSERT INTO some_table (some_column) VALUES (1)");
      return true, 1
   end)
   assert(success)
   assert(res == 1)
end)

Manual Rollback

local tealsql = require("pages/tealsql/definitions.tealsql")
tealsql.connect("postgres://userName:password@host/database",function(con:tealsql.Connection):{string:integer}
    local success, res = con:begin(function(con:tealsql.Connection):(boolean,integer)
        con:execute("INSERT INTO some_table (some_column) VALUES (1)");
        return false, 1
    end)
    assert(not success)
    assert(res ==  1)
end)

local tealsql = require("pages/tealsql/definitions.tealsql")
tealsql.connect("postgres://userName:password@host/database", function(con)
   local success, res = con:begin(function(con)
      con:execute("INSERT INTO some_table (some_column) VALUES (1)");
      return false, 1
   end)
   assert(not success)
   assert(res == 1)
end)

Rollback on error

local tealsql = require("pages/tealsql/definitions.tealsql")
tealsql.connect("postgres://userName:password@host/database",function(con:tealsql.Connection):{string:integer}
    local success, res = con:begin(function(con:tealsql.Connection):(boolean,integer)
        con:execute("INSERT INTO some_table (some_column) VALUES (1)");
        error("This will also cause a rollback")
    end)
    --we will never reach this part, as the error gets rethrowed
    assert(res ==  1)
end)

local tealsql = require("pages/tealsql/definitions.tealsql")
tealsql.connect("postgres://userName:password@host/database", function(con)
   local success, res = con:begin(function(con)
      con:execute("INSERT INTO some_table (some_column) VALUES (1)");
      error("This will also cause a rollback")
   end)

   assert(res == 1)
end)