Working with SQLite

Neko brings you a set of tools to interact with SQLite databases with neko.data.sqlite namespace. It includes functions to create a database, execute queries against it, insert and update data and seamlessly convert received results into Clojure’s data types and structures.

Tools provided are really subpar to mature SQL wrappers like clojure.java.jdbc or Korma. They are quite limited in their capabilities, on the other hand you don’t have to pay for big libraries with your app loading time.

Define a schema

Schema is an ordinary map that contains a database description, such as what is the name of the file database will be stored in, what tables with what columns does it have and so on.

To create a schema use make-schema function that takes a list of optional arguments passed in key-value fashion (so you just passed a map to it without top-level curly braces).

The following rules apply to a correct schema:

• :name should be a string that represents database file name;
• :version should be a number that represents the version of schema;
• :tables should be a map where keys are table names presented as keywords, and values are also maps;
• each table map should contain a :columns value which should also be a map;
• in a columns map keys are column names presented as keywords, and values are strings that define SQLite column types to be inserted in CREATE statement. For example, a column for textual data can have “text” or “text not null” sql-type.

Example:

(def db-schema
  (make-schema
   :name "local.db"
   :version 1
   :tables {:employees
            {:columns {:_id         "integer primary key"
                       :name        "text not null"
                       :vacation    "boolean"
                       :certificate "blob"}}}))

Get database

Now that you have a schema you can create a database from it. But first you need to make a helper. Calling (create-helper schema) or (create-helper context schema) gives you an object that can be used to access the database. Most of the time it is necessary to make the helper a singleton, because accessing the database object obtained from multiple helpers can lead to race conditions. Inside the helper creates tables if they don’t exist yet and recreates them if schema’s and current database’s versions mismatch.

get-database is a function to get the database. First argument to it is a helper, the second is access mode (either :read or :write, you can just always use :write). The returned value is TaggedDatabase instance. TaggedDatabase is an object that keeps SQLiteDatabase instance and a schema. You can get the former by addressing .db field and the latter from .schema field. Although you’ll only need to do this when executing Java code directly since all neko.data.sqlite functions work with TaggedDatabase as argument.

It is usually better to save the reference to a helper object rather than to the database object, and reopen database each time on demand.

Example:

(require '[neko.data.sqlite :as db])

;; Make a function that returns the same helper instance.
(def get-db-helper
  (memoize
    (fn [] (db/create-helper db-schema))))

;; Somewhere in the application code.
(db/get-database (get-db-helper) :write)

SQL operations

neko.data.sqlite contains functions to perform INSERT, UPDATE and SELECT queries.

Insert data

You can use insert function to INSERT data into the database. It takes three arguments: a TaggedDatabase, a table name (as stated in schema) and a data map, which keys are column names and values are actual values to be inserted.

Example:

(db/insert db :employees {:name "Shelley Levene"
                          :vacation false
                          :certificate (.getBytes "quick brown fox")})

Update data

update function is used to run UPDATE queries against the database. It takes four arguments: a TaggedDatabase, a table name, data-map (same as in INSERT) and a WHERE clause. WHERE clause could be a simple SQL string or a map as well where key-value pairs are treated as comparison operations.

For example, {:_id 1, :vacation false} turns into “WHERE _id = 1 AND vacation = false”.

You can also use a vector as WHERE clause value that has the following form: [op-keyword & values]. Op-keyword will be transformed into a string and interpose the values.

For instance, {:_id [:or 1 2], :name "John Doe"} turns into “WHERE (_id = 1 OR _id = 2) AND name = ‘John’;”.

Example:

;; Send employees with IDs 1, 5 and 7 on vacation
(db/update db :employees
           {:vacation true}
           {:_id [:or 1 5 7]})

Retrieve data

query executes a SELECT query and gets the result in a TaggedCursor object (the same behavior as .query method shows). It takes a TaggedDatabase, a table-name and a where clause (same as in db-update). The returned cursor can be then consumed by cursor-adapter or by anything else in Android that expects a Cursor.

Example:

(db/query db :employees {:vacation false})

If you want to get all the data in EDN form, you can use query-seq which after retrieving the cursor consumes it and puts data on the lazy sequence. query-seq has the same syntax as db-query.

(db/query-seq db :employees {:vacation false})

=> ({:_id 5, :name "Dave Moss", :vacation false, :certificate #<byte[] [B@64dfeb>}
    {:_id 17, :name "Ricky Roma", :vacation false, :certificate #<byte[] [B@a8c19b>})

You can also pass optional select argument to filter only necessary columns.

(db/query-seq db [:name :vacation] :employees {:vacation false})

=> ({:name "Dave Moss", :vacation false}
    {:name "Ricky Roma", :vacation false})

Querying multiple tables

In case you need to join multiple tables in a single query, you should do the following:

• Specify a vector of table names for from argument.
• When writing column names (either in select or where clause) add table names as keyword namespaces.

Keep in mind that column names in returned data will be qualified too.

;; For all employees not on vacation get their bosses.
(db/query-seq db [:employees/name :bosses/name]
              [:employees :bosses]
              {:employees/vacation false})

=> ({:employees/name "Dave Moss", :bosses/name "Mr. Williamson"}
    {:employees/name "Ricky Roma", :bosses/name "Blake"})

Specifying a vector of table names will unite them with simple comma (INNER JOIN). If you need more complex join strategy, you can replace the vector with a SQL statement string. Same goes for where clause: in case Neko’s capabilities are insufficient you can fall back to writing the clause manually.

Querying for a scalar value

Sometimes you want to get a single value of the database. It can be either a specific cell of a table, or some computed aggregated value. query-scalar takes a database, a column name (or a vector where first element is a string aggregate function and the second is a column name), table name and where clause.

(db/query-scalar db :name :employees {:_id 17})
=> "Ricky Roma"

(db/query-scalar db ["count" :_id] :employees nil)
=> 6

Executing batch operations

In case you have to perform a large quantity of INSERT or UPDATE statements, it will happen quicker if you group them into a single transaction. transact macro takes a database and the code to be executed in transaction bounds.

(db/transact db
  (doseq [employee (get-employees-from-server)]
    (db/insert db :employees employee)))