API: sql

sql

A library for database support

Drivers

• mysql

  • MySQL SQL dialect.
  • DSN: https://github.com/go-sql-driver/mysql#dsn-data-source-name

• postgres

  • Postgres SQL dialect.
  • DSN: https://godoc.org/github.com/lib/pq#hdr-Connection_String_Parameters

• sqlite3

  • SQLite3 SQL dialect.
  • DSN: https://godoc.org/github.com/mattn/go-sqlite3#SQLiteDriver.Open

• ql/ql-mem

  • Simple database purely in Go.
  • Own dialect of SQL, like every other entry here...
  • Use types string and float64 for strings and numbers respectively.
  • Needs every modification to be in a transaction (using begin)
  • https://godoc.org/github.com/cznic/ql
  • Recommended for new projects who are not limited by incompatibility.

• yql

  • YQL: Yahoo Query Language
  • Searches/Queries Yahoo, for more information on what it can do, search YQL.
  • DSN:
    • ""
      • Defaults to "", public queries.
    • "env"
      • Public query with custom env.
      • env is for YQL datastore, for example: to use datatables.org, store://datatables.org/alltableswithkeys
    • "consumerkey|consumersecret"
      • Private API query
    • `"consumerkey|consumersecret|env"``
      • Private API query with env
  • https://github.com/vifino/go-yql

The following examples are for sqlite3. You should look at tests/sqltest.lua for a ql/ql-mem example.

---

Backend recommendation? Uhhh, I find ql/ql-mem interesting.
It is completely written in Go and has compression and who knows what other goodies.
- vifino

---

API

drivers = sql.drivers()

Returns a table containing all drivers available, currently: {"mysql","sqlite3","postgres","yql","ql","ql-mem"}

db, error = sql.open(drivername[, dsn])

Returns a new sql_database object, driver name has to be one returned from sql.drivers() dsn defaults to "".

Example for a sqlite3 database in memory:

local db = sql.open("sqlite3",":memory:")

The dsn string, SQL syntax and more depends on the driver, look above.

error = sql_database:exec(statement[, ...])

Executes a sql statement, $1, $2 etc. are replaced by the extra arguments passed to it, if any.

Example

db:exec("CREATE TABLE users (name text, age int)") -- Create a new table called users with the rows name and age
db:exec("CREATE TABLE admins (name text, age int)") -- Same as above for admins
db:exec("INSERT INTO users (name, age) VALUES ($1, $2)", "bauen1", 15)
db:exec("INSERT INTO users (name, age) VALUES ($1, $2)", "bob", 21)
db:exec("INSERT INTO admins (name, age) VALUES ($1, $2)", "vifino", 16)

rows, error = sql_database:query(statement[, ...])

Executes a sql statement and returns the results if successful else returns nil and a error message It also accepts extra arguments like sql_database:exec(statement[, ...]) Surround with assert() to see the error messages

Example

local rows = assert(db:query("SELECT * FROM users"))
for i=1, rows.n do
  print("Showing row number " .. tostring(i))
  for k,v in pairs(rows[i]) do
    print("> " .. tostring(k), tostring(v))
  end
end

statement, error = sql_database:prepare(statement)

Just like sql_database:query(statement[, ...]) but you prepare a statement beforehand and then execute it repeatedly

Example

local min_ages = {8, 18}
local statement = db:prepare("SELECT * FROM users WHERE age>=$1")
for _,min_age in pairs(min_ages) do
  print("Users with a minimum age of " .. tostring(min_age))
  local rows = statement:query(min_age)
  for i=1, rows.n do
    print("Showing row number " .. tostring(i))
    for k,v in pairs(rows[i]) do
      print("> " .. tostring(k), tostring(v))
    end
  end
end

sql_database:begin([fn])

Enters transaction mode.

If you pass it a function, it will be executed. The body should contain sql_database:exec and such. At the end, you have to return either true or false, which symbolizes to commit or rollback the transaction respectively.

If you do not pass it a function, you can call sql_database:commit() and sql_database:rollback() on your own.

success, error = sql_database:close()

Closes a database object

sql_database.con

Don't mess with this unless you know what your doing.