sqlite-easy is a new database library for interacting with SQLite3 databases.

It provides the ability to create database resource pools, run SQL statements and queries, wrap statements in simple transactions, and perform database migrations.

It is built on top of sqlite-direct, migrant, and resource-pool.

I would advise against using it for anything serious, but it might be useful for fun hobby projects.

You can try it now in ghci:

➜  cabal repl --build-depends sqlite-easy
[...]
λ> :set -XOverloadedStrings
λ> import Database.Sqlite.Easy
λ>
λ> withDb ":memory:" $ run "select 1 + 1"
[[SQLInteger 2]]
λ>
λ> pool <- createSqlitePool "/tmp/mysqliteasydb.bin"
λ> withPool pool $ run "create table t (x text)"
[]
λ> :{
λ| withPool pool $
λ|   transaction $ do
λ|     run "insert into t values ('a'),('b'),('c')"
λ|     run "insert into t values ('b'),('a'),('b')"
λ| :}
[]
λ> withPool pool $ runWith "select x, count(x) from t where x <> ? group by x" [SQLText "c"]
[[SQLText "a",SQLInteger 2],[SQLText "b",SQLInteger 3]]

I created this library because I wanted something that is plug and play and doesn't require a lot of thinking. Because of these requirements I decided to:

  • Provide an easy API for common cases, and try to avoid pitfalls like SQL injection by default
  • Include solutions for migrations and resource-pooling - no need to search for libraries or read other docs
  • Avoid config files, Template Haskell, type classes and type level programming - if you know some basic Haskell (and SQL), you should be fine
  • Avoid customization or less common features - small API, less to learn
  • Just use SQL for interacting with the database - no restrictions, but also no validation
  • No external dependencies - SQLite3 bundled and embedded into the executable via sqlite-direct

None of the solutions I was familiar ticked all of the boxes, and after writing the same code a few times for different projects I decided to just make it a library and publish it.

See also How does it compare to sqlite-simple?

How to use sqlite-easy

The docs explain it better, but in essence there are 3 important steps:

1. Connect to a database

Two ways to connect to a SQLite3 database:

  1. withDb - takes a connection string and actions to run, and runs them, producing a result.
  2. Creating a database resource pool using createSqlitePool and then run actions using withPool like with withDb.

2. Run database migrations

Run migrations using the migrate function by defining a list of migration names, a setup function, and a teardown function that maps the name of the migration to the action to take.

3. Run statements and queries

We can define actions to run on the database using the functions run and runWith, These functions Take a SQL statement, and returns something of type SQLite [[SQLData]].

  • SQL is a type representing a SQL string. Use OverloadedStrings to construct values of this type.

  • SQLite is the type describing SQLite actions. SQLite actions can be composed using the monadic interface.

  • SQLData is a type representing a SQLite3 scalar value. SQLite3 has 5 possible value groups - integers, doubles, text strings, binary blobs and null. [[SQLData]] represents rows of columns of scalar values.

SQLite actions can also be wrapped in a transaction.

Examples

I've written two examples using this library:

  1. sqlite-easy-example-todo - a simple todo list application with persistent state
  2. learn-twain-bulletin-app the bulletin board from a previous blog post, but using sqlite-easy instead of stm
  3. jot-web - a webapp for writing down memos

The examples use The Handle Pattern.