Accessing SQL databases

Database access in Kaya uses the DB module to provide a single consistent interface to multiple backend databases. The Kaya standard library currently includes support for MySQL, Postgres and SQLite. This tutorial assumes you already have a working knowledge of SQL and your chosen database backend.

Connecting to your database

To connect to a database, you need to import the database module for that type of database, and then call the connect function for that database.

import MyDB;
//...
    db = connect("mysql.kayalang.org","kaya","mypassword","kaya_test");

The database connection function returns a DBHandle which can then be used to carry out queries on the database.

Querying the database

Since Kaya 0.2.7 there have been two types of query possible - prepared queries and normal queries. In general, you should use prepared queries as these make it easier to avoid SQL injection vulnerabilities.

Prepared queries

To prepare a query, call the prepare function. This constructs a query template, where parts of the query use replacement characters. The replacement characters used vary depending on the database - some use "?", whereas others use "$1", "$2", etc. These replacement parameters are then replaced when the query is executed.

statement = prepare(connection,
                    "SELECT * FROM Users WHERE username = $1 AND user_active = $2"
// or depending on the backend database, perhaps:
statement = prepare(connection,
                    "SELECT * FROM Users WHERE username = ? AND user_active = ?"

The query is then executed with the execPrepared function. This function uses a list of parameters, which may either be nothing for SQL's NULL value, or just(x) for a parameter with string representation 'x'

result = execPrepared(statement,[just("apn3"),just("1")]);

Normal queries

If the query will not contain any user-submitted data, and will only be executed once, then it is more efficient to not create an intermediate prepared query. In this case, the query may be executed without preparation using exec.

result = exec(connection,
              "DELETE FROM Users WHERE user_active = 0");

Using query results

Both exec and execPrepared return a DBResult structure. This consists of various fields, of which the two most important are rows, which gives the number of rows returned (usually zero for non-SELECT queries) and table, which contains the returned values in a 2-dimensional array. The first array index is the result row, and the second the fields.

if (result.rows == 0) {
    putStrLn("No results found!");
} else {
    for row in result.table {
        user = string(row[0]);
        active = bool(row[1]);
        putStr("User "+user+" is ");
        if (!active) {
            putStr("in");
        }
        putStrLn("active.");
    }
}

The entries in table are of the DBValue type, and must be converted to the appropriate Kaya type using one of the coercion functions in the DB module.

Closing connections

To close a connection, call close. You should close all open connections before the program exits.