sqlite-simple: mid-level bindings to the sqlite database

This library is a mid-level Haskell binding to the SQLite database.

Sqlite-simple provides a convenient API to sqlite that does some level of automatic data conversion between the database and Haskell types. The API has been modeled directly after postgresql-simple which in turn borrows from mysql-simple.

The sqlite-simple API reference contains more examples of use and information on its features.

The library is well tested and stable. The library should also be reasonably performant. You can find its benchmark suite here: db-bench. You can read more about sqlite-simple's expected performance in my blog about sqlite-simple performance against direct-sqlite, Python and C.

Build Status

Installation

You can install sqlite-simple from Hackage with:

cabal install sqlite-simple

A Windows user? It works but please see this note on direct-sqlite Windows installation.

Examples of use

Create a test database by copy&pasting the below snippet to your shell:

sqlite3 test.db "CREATE TABLE test (id INTEGER PRIMARY KEY, str text);\
INSERT INTO test (str) VALUES ('test string');"

..and access it in Haskell:

{-# LANGUAGE OverloadedStrings #-}
import Control.Applicative
import Database.SQLite.Simple
import Database.SQLite.Simple.FromRow

data TestField = TestField Int String deriving (Show)

instance FromRow TestField where
  fromRow = TestField <$> field <*> field

main :: IO ()
main = do
  conn <- open "test.db"
  execute conn "INSERT INTO test (str) VALUES (?)"
    (Only ("test string 2" :: String))
  r <- query_ conn "SELECT * from test" :: IO [TestField]
  mapM_ print r
  close conn

More simple usage examples can be found from sqlite-simple unit tests.

Development

The development roadmap for sqlite-simple is mostly captured in the github issue database.

I'm happy to receive bug reports, fixes, documentation enhancements, and other improvements.

Please report bugs via the github issue tracker.

For general database issues with a Haskell focus, I recommend sending e-mail to the database-devel mailing list.

Contributing

If you send pull requests for new features, it'd be great if you could also develop unit tests for any such features.

Credits

A lot of the code is directly borrowed from mysql-simple by Bryan O'Sullivan and from postgresql-simple by Leon P. Smith. Like Leon in postgresql-simple, I borrow code and documentation directly from both of these ancestor libraries.

This package builds on top of the direct-sqlite package by Irene Knapp.

SQLite is rather weakly-typed and thus the SQL to Haskell type strictness of the parent projects does not necessarily apply to this package.

Changes

0.4.13.0
* Add columnCount (thanks @Shimuuar!)
* Add withImmediateTransaction, withExclusiveTransaction (thanks @mbucc!)
* Expose the Database.SQLite3 Statement type through Database.SQLite.Simple Statement

0.4.12.1
* Add Setup.hs (https://github.com/fpco/stackage/issues/2145)

0.4.12.0
* Add queryWith

0.4.11.0
* Add executeMany

0.4.10.0
* Expose sqlite3_changes/total_changes

0.4.9.0
* Provide queryWith_ to allow more fine-grained access to
constructing queries.
* Expose error data constructors (pull request #42)
* Improve haddocks

0.4.8.0
* Export `bindNamed'

0.4.7.0
* Add `withTransaction' for running IO actions inside SQL
transactions with automated rollback if any exceptions are thrown.

0.4.6.1
* Fix unit test build break with older bytestring versions

0.4.6.0
* Add "named parameters" variants of query & al. Named params
allow queries like:
res <- queryNamed conn "SELECT * FROM posts WHERE id = :id" [":id" := postId]
* Add FromField instances for Int8, Word, Word8, Word16, Word32
and Word64.
* Fix typos in some type conversion error messages.
* Improved test coverage.

0.4.5.2
* Build fix for GHC 7.4

0.4.5.1
* Docs changes - uploaded new version to Hackage to update the
Hackage page.

0.4.5.0

* Various improvements to documentation. Especially UTCTime
parsing and printing, and how it relates to SQLite datetimes is
better documented now.

* Improved date/time parsing performance by adapting Leon
P. Smith's parsers from postgresql-simple for SQLite. UTCTime
handling is also better defined now.

* Improved query performance
(https://github.com/nurpax/sqlite-simple/issues/23)

* Improved tests for all areas touched by the above change.

v0.4.4.0

* Add FromField instance for Float

* Improve error handling for day parsing

* + with tests

v0.4.1.0 - v0.4.3.0 (missed tagging v0.4.2.0)

* Improvements to withBind functionality and documentation
(see https://github.com/nurpax/sqlite-simple/pull/26)

* Add columnName accessor for statements

* Expose MonadPlus on RowParser

* Allow access to the underlying direct-sqilte connection from an
sqlite-simple connection

* Add Data.Text.Lazy and lazy ByteString From/ToField instances

v0.4.0.0

* Add lastInsertRowId

* Expose SQLite statements based streaming API
(see https://github.com/nurpax/sqlite-simple/pull/22)

v0.3.0.0

* Add fold, fold_, withConnection

v0.2.0.0 - v0.2.1.0

* Optimizations to improve query rows/sec performance
comments powered byDisqus