Safe Haskell | Safe-Inferred |
---|---|
Language | Haskell2010 |
Easy to use interface for SQLite3 using the direct-sqlite
library.
This can be useful for your toy, hobby projects.
Synopsis
- withDb :: ConnectionString -> (Database -> IO a) -> IO a
- newtype ConnectionString = ConnectionString {}
- data Database
- data Pool a
- createSqlitePool :: ConnectionString -> IO (Pool Database)
- withResource :: Pool a -> (a -> IO r) -> IO r
- destroyAllResources :: Pool a -> IO ()
- run :: SQL -> Database -> IO [[SQLData]]
- runWith :: SQL -> [SQLData] -> Database -> IO [[SQLData]]
- data SQL
- data SQLData
- = SQLInteger !Int64
- | SQLFloat !Double
- | SQLText !Text
- | SQLBlob !ByteString
- | SQLNull
- data SQLError = SQLError {
- sqlError :: !Error
- sqlErrorDetails :: Text
- sqlErrorContext :: Text
- data ColumnType
- asTransaction :: Typeable a => Database -> IO a -> IO a
- cancelTransaction :: Typeable a => a -> IO a
- module Database.Sqlite.Easy.Migrant
- data Int64
- data Text
- data ByteString
Connect to the database
The easiest way to run some statements on the database is using the
withDb
function. withDb
uses continuation-passing style and expects
a connection string (such as a file with flags or :memory:
,
see sqlite3 docs: https://www.sqlite.org/c3ref/open.html), and
a function that will take the database handle and do things with it.
It will open the connection to the database and call the function,
passing the database handle to it.
Example
do results <- withDb ":memory:" (run "select 1 + 1") case results of [[SQLInteger n]] -> print n _ -> error ("Got unexpected results: " <> show results)
Note: use fromString
to convert a String
to a ConnectionString
or to SQL
if you prefer not to use OverloadedStrings
.
withDb :: ConnectionString -> (Database -> IO a) -> IO a Source #
Open a database, run some stuff, close the database.
newtype ConnectionString Source #
A SQLite3 connection string
Instances
IsString ConnectionString Source # | |
Defined in Database.Sqlite.Easy.Internal fromString :: String -> ConnectionString # | |
Show ConnectionString Source # | |
Defined in Database.Sqlite.Easy.Internal showsPrec :: Int -> ConnectionString -> ShowS # show :: ConnectionString -> String # showList :: [ConnectionString] -> ShowS # |
Pooling connections
An alternative to withDb
is to create a resource Pool
.
A resource pool is an abstraction for automatically managing connections
to a resource (such as a database).
We can use the createSqlitePool
function to create a Pool
and pass that around until you are ready to use the database.Database
We can use withResource
to essentially make withDb
from a pool.
Example
do pool <- createSqlitePool ":memory:" results <- withResource pool (run "select 1 + 1") case results of [[SQLInteger n]] -> print n _ -> error ("Got unexpected results: " <> show results)
Note: a resource pool disconnects automatically after some time,
so if you are using :memory:
as your database, you will lose your
data when the connection closes!
createSqlitePool :: ConnectionString -> IO (Pool Database) Source #
Create a pool of a sqlite3 db with a specific connection string.
withResource :: Pool a -> (a -> IO r) -> IO r #
destroyAllResources :: Pool a -> IO () #
Running statements and queries
To execute a statement or query, use the run
or runWith
functions.
run
is used to execute a statement or query and fetch the results.
runWith
is similar to run
, but lets us place parameters instead of
places where we write ?
in the query.
If you want to pass user data, use runWith
to avoid SQL injection!
The list of lists of SQLData returned from these functions are
rows of columns of sqlite values. Sqlite only has a few possible
values: integers, floating-point numbers, text, bytes and null.
The SQLData
type encodes these options.
Example
do results <- withDb ":memory:" $ \db -> do [] <- run "CREATE TABLE characters(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)" db [] <- run "INSERT INTO characters(name) VALUES ('Scanlan'),('Nott'),('Fresh Cut Grass')" db runWith "SELECT * FROM characters WHERE id = ?" [SQLInteger 2] db for_ results $ \case [SQLInteger id', SQLText name] -> putStrLn (show id' <> ", " <> show name) row -> hPutStrLn stderr ("Unexpected row: " <> show row)
run :: SQL -> Database -> IO [[SQLData]] Source #
Run a SQL statement on a database and fetch the results.
runWith :: SQL -> [SQLData] -> Database -> IO [[SQLData]] Source #
Run a SQL statement with certain parameters on a database and fetch the results.
Database types
A SQL statement
SQLError | |
|
Instances
Exception SQLError | |
Defined in Database.SQLite3 toException :: SQLError -> SomeException # fromException :: SomeException -> Maybe SQLError # displayException :: SQLError -> String # | |
Show SQLError | |
Eq SQLError | |
data ColumnType #
Instances
Show ColumnType | |
Defined in Database.SQLite3.Bindings.Types showsPrec :: Int -> ColumnType -> ShowS # show :: ColumnType -> String # showList :: [ColumnType] -> ShowS # | |
Eq ColumnType | |
Defined in Database.SQLite3.Bindings.Types (==) :: ColumnType -> ColumnType -> Bool # (/=) :: ColumnType -> ColumnType -> Bool # | |
FFIType ColumnType CColumnType | |
Defined in Database.SQLite3.Bindings.Types toFFI :: ColumnType -> CColumnType fromFFI :: CColumnType -> ColumnType |
Running transactions
If you'd like to run multiple statements and queries atomically, use asTransaction
.
Please note that sqlite transactions do not nest.
Example
withDb ":memory:" $ \db -> do ( asTransaction db $ do [] <- run "CREATE TABLE t1(id INTEGER, name TEXT)" db [] <- run "CREATE TABLE t2(id INTEGER, name TEXT)" db [] <- run "CREATE TABLE t3id INTEGER, name TEXT)" db -- whoops [] <- run "CREATE TABLE t4(id INTEGER, name TEXT)" db pure () ) `catch` (\(SomeException e) -> print ("Transaction rolled back", e)) run "select * from t1" db -- throws an exception (table not found) because the transaction was rolled back
You can also decide to cancel a transaction yourself by supplying the result value
with cancelTransaction
.
asTransaction :: Typeable a => Database -> IO a -> IO a Source #
Run operations as a transaction. If the action throws an error, the transaction is rolled back.
Note: Transactions do not nest.
For more information, visit: https://www.sqlite.org/lang_transaction.html
cancelTransaction :: Typeable a => a -> IO a Source #
Cancel a transaction by supplying the return value. To be used inside transactions.
Migrations
Database migrations are a way to setup a database with the relevant information (such as table structure) needed for the application to start, and update it from a possible older version to a newer version (or even go the other direction).
Migrations are a list of statements we run in order to upgrade or downgrade a database.
We use the migrant
library to semi-automate this process - we write the upgrade
and downgrade steps, and it runs them. For more information, consult the migrant
documentation: https://github.com/tdammers/migrant.
To create a migration we need to write the following things:
1) A list of migration names
migrations :: [MigrationName] migrations = [ "user-table" , "article-table" ]
2) Migration up steps - a mapping from migration name to what to do.
migrateUp :: MigrationName -> Database -> IO () migrateUp = \case "user-table" -> void . run "CREATE TABLE user(id INTEGER, name TEXT)" "article-table" -> void . run "CREATE TABLE article(id integer, title TEXT, content TEXT, author_id integer)" unknown -> error ("Unexpected migration: " <> show unknown)
3) Migration down steps
migrateDown :: MigrationName -> Database -> IO () migrateDown = \case "user-table" -> void . run "DROP TABLE user" "article-table" -> void . run "DROP TABLE article" unknown -> error ("Unexpected migration: " <> show unknown)
After doing that, we can run a migration with the migrate
function:
runMigrations :: Database -> IO () runMigrations = migrate migrations migrateUp migrateDown
module Database.Sqlite.Easy.Migrant
Fun types to export
64-bit signed integer type
Instances
A space efficient, packed, unboxed Unicode text type.
data ByteString #
A space-efficient representation of a Word8
vector, supporting many
efficient operations.
A ByteString
contains 8-bit bytes, or by using the operations from
Data.ByteString.Char8 it can be interpreted as containing 8-bit
characters.
Instances
Suggestions
A suggestion for the architecture of your database interactions: use the handler pattern!
1. Create a type for your API
Think about what actions you want to perform on your database:
For example:
data DB = DB { getPost :: Id -> IO (Id, Post) , getPosts :: IO [(Id, Post)] , insertPost :: Post -> IO Id , deletePostById :: Id -> IO () }
Note how we don't mention the database connection here!
2. Create a smart constructor
This function should:
- Create a resource pool with the database
- Run the database migrations
- Return a
DB
such that each function in the API is a closure over the pool
For example:
mkDB :: ConnectionString -> IO DB mkDB connectionString = do pool <- createSqlitePool connectionString withResource pool runMigrations pure $ DB { getPost = getPostFromDb pool , getPosts = getPostsFromDb pool , insertPost = insertPostToDb pool , deletePostById = deletePostByIdFromDb pool }
Where:
getPostFromDb :: Pool Database -> Id -> IO (Id, Post)
insertPostToDb :: Pool Database -> Post -> IO Id
and so on.
3. Use the handler from your application code
When you start the application, run mkDB
and get a handler, and pass it around
(or use ReaderT
). When you need to run a database command, call a field from DB
:
-- A page for a specific post [ Twain.get "/post/:id" $ do postId <- Twain.param "id" post <- liftIO $ getPost db postId -- (*) Twain.send (displayPost post) ]
Or, with OverloadedRecordDot
,
post <- liftIO $ db.getPost postId
Complete Example
Visit this link for a more complete example: https://github.com/soupi/sqlite-easy-example-todo