sqlite-easy (empty) → 0.1.0.0

raw patch · 8 files changed

+584/−0 lines, 8 filesdep +basedep +bytestringdep +direct-sqlite

Dependencies added: base, bytestring, direct-sqlite, hspec, hspec-discover, migrant-core, resource-pool, sqlite-easy, text

Files

• LICENSE +30/−0
• README.md +9/−0
• sqlite-easy.cabal +63/−0
• src/Database/Sqlite/Easy.hs +264/−0
• src/Database/Sqlite/Easy/Internal.hs +108/−0
• src/Database/Sqlite/Easy/Migrant.hs +39/−0
• test/Spec.hs +1/−0
• test/SqliteEasySpec.hs +70/−0

@@ -0,0 +1,30 @@+Copyright (c) 2022, Gil Mizrahi++All rights reserved.++Redistribution and use in source and binary forms, with or without+modification, are permitted provided that the following conditions are met:++    * Redistributions of source code must retain the above copyright+      notice, this list of conditions and the following disclaimer.++    * Redistributions in binary form must reproduce the above+      copyright notice, this list of conditions and the following+      disclaimer in the documentation and/or other materials provided+      with the distribution.++    * Neither the name of Gil Mizrahi nor the names of other+      contributors may be used to endorse or promote products derived+      from this software without specific prior written permission.++THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS+"AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT+LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR+A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT+OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,+SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT+LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,+DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY+THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT+(INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE+OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

@@ -0,0 +1,9 @@+sqlite-easy+===========++This library provides a primitive yet easy to use api for SQLite3 using the `direct-sqlite` library,+resource pooling using `resource-pool`, and migrations using the `migrant` library.++It might be useful for small toy projects and save a tiny bit of boilerplate.++[See the docs for more information](https://gilmi.gitlab.io/sqlite-easy/haddocks/sqlite-easy-0.1.0.0/Database-Sqlite-Easy.html)!

@@ -0,0 +1,63 @@+cabal-version: 2.4++name:           sqlite-easy+version:        0.1.0.0+synopsis:       A primitive yet easy to use sqlite library.+description:    A primitive yet easy to use sqlite library built using sqlite-direct, resource-pool and migrant.+author:         Gil Mizrahi+category:       Database+stability:      Experimental+maintainer:     gilmi@posteo.net+copyright:      2022 Gil Mizrahi+license:        BSD-3-Clause+license-file:   LICENSE+build-type:     Simple+extra-source-files:+    README.md+--    cbits/sqlite3.c+--    cbits/sqlite3.h++library+  exposed-modules:+      Database.Sqlite.Easy+      Database.Sqlite.Easy.Internal+      Database.Sqlite.Easy.Migrant+  hs-source-dirs:+      src+  build-depends:+      base >=4.12 && <5+    , bytestring+    , text+    , direct-sqlite+    , resource-pool+    , migrant-core+  default-language: Haskell2010+  ghc-options: -Wall++  -- c-sources: cbits/sqlite3.c+  -- include-dirs: cbits+  -- includes: sqlite3.h+  -- install-includes: sqlite3.h+  -- cc-options: -fPIC -std=c99++  -- if !os(windows)+  --   extra-libraries: pthread++test-suite sqlite-easy-test+  type: exitcode-stdio-1.0+  hs-source-dirs: test+  main-is: Spec.hs+  other-modules:+    SqliteEasySpec+  build-depends:+      base+    , hspec+    , hspec-discover+    , sqlite-easy+    , text+    , direct-sqlite+  default-language: Haskell2010+  ghc-options:+    -threaded -rtsopts -with-rtsopts=-N+  build-tool-depends:+    hspec-discover:hspec-discover

@@ -0,0 +1,264 @@+-- | Easy to use interface for SQLite3 using the @direct-sqlite@ library.+--+-- This can be useful for your toy, hobby projects.+module Database.Sqlite.Easy+  ( -- * Connect to the database+    -- $connecting+    withDb+  , ConnectionString(..)+  , Database+    -- ** Pooling connections+    -- $pooling+  , Pool+  , createSqlitePool+  , withResource+  , destroyAllResources+    -- * Running statements and queries+    -- $running+  , run+  , runWith+  , -- ** Database types+    SQL+  , SQLData(..)+  , SQLError(..)+  , ColumnType(..)+    -- * Running transactions+    -- $transactions+  , asTransaction+  , cancelTransaction+  , -- * Migrations+    -- $migrations+    module Migrant+  , -- * Fun types to export+    Int64, Text, ByteString+    -- * Suggestions+    -- $suggestions+  )+  where++import Data.Pool (Pool, withResource, destroyAllResources)+import Database.SQLite3 (SQLData(..), Database, SQLError(..), ColumnType(..))+import Database.Sqlite.Easy.Internal+import Database.Sqlite.Easy.Migrant as Migrant+import Database.Migrant as Migrant+import Data.Int (Int64)+import Data.Text (Text)+import Data.ByteString (ByteString)++{- $connecting++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 'Data.String.fromString' to convert a 'String' to a 'ConnectionString'+      or to 'SQL' if you prefer not to use @OverloadedStrings@.+-}++{- $pooling++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 'Database'@+and pass that around until you are ready to use the 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!++-}++{- $running++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)+-}++{- $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 ()+>   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'.++-}++{- $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++-}++{- $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:++1. Create a resource pool with the database+2. Run the database migrations+3. 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 <- DB.createSqlitePool connstr+>   DB.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/learn-twain-bulletin-app/blob/sqlite/src/DB.hs>+-}

@@ -0,0 +1,108 @@+{-# language DerivingVia #-}+{-# language OverloadedStrings #-}+{-# language ScopedTypeVariables #-}++module Database.Sqlite.Easy.Internal where++import Database.SQLite3+import Data.String (IsString)+import Data.Text (Text)+import Control.Exception+import Data.Typeable+import Data.Pool++-- * Connection++-- | A SQLite3 connection string+newtype ConnectionString+  = ConnectionString+    { unConnectionString :: Text+    }+  deriving IsString via Text+  deriving Show++-- | Create a pool of a sqlite3 db with a specific connection string.+createSqlitePool :: ConnectionString -> IO (Pool Database)+createSqlitePool (ConnectionString connStr) =+  newPool PoolConfig+    { createResource = open connStr+    , freeResource = close+    , poolCacheTTL = 180+    , poolMaxResources = 50+    }++-- | Open a database, run some stuff, close the database.+withDb :: ConnectionString -> (Database -> IO a) -> IO a+withDb (ConnectionString connStr) = bracket (open connStr) close++-- * Execution++-- | A SQL statement+newtype SQL+  = SQL+    { unSQL :: Text+    }+  deriving (Semigroup, IsString) via Text+  deriving Show++-- | Run a SQL statement on a database and fetch the results.+run :: SQL -> Database -> IO [[SQLData]]+run (SQL stmt) db = prepare db stmt >>= fetchAll++-- | Run a SQL statement with certain parameters on a database and fetch the results.+runWith :: SQL -> [SQLData] -> Database -> IO [[SQLData]]+runWith (SQL stmt) params db = do+  preparedStmt <- prepare db stmt+  bind preparedStmt params+  fetchAll preparedStmt++-- | Run a statement and fetch all of the data.+fetchAll :: Statement -> IO [[SQLData]]+fetchAll stmt = do+  res <- step stmt+  case res of+    Row -> do+      row <- columns stmt+      rows <- fetchAll stmt+      pure (row : rows)+    Done -> do+      finalize stmt+      pure []++-- * Transaction++-- | 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>+asTransaction :: Typeable a => Database -> IO a -> IO a+asTransaction db action = do+  [] <- run "BEGIN" db+  catches+    (action <* run "COMMIT" db)+    [ Handler $ \(CancelTransaction a) -> run "ROLLBACK" db *> pure a+    , Handler $ \(ex :: SomeException) -> run "ROLLBACK" db *> throwIO ex+    ]++asTransaction' :: Database -> IO a -> IO a+asTransaction' db action = do+  [] <- run "BEGIN" db+  catches+    (action <* run "COMMIT" db)+    [ Handler $ \(ex :: SomeException) -> run "ROLLBACK" db *> throwIO ex+    ]++-- | Cancel a transaction by supplying the return value.+--   To be used inside transactions.+cancelTransaction :: Typeable a => a -> IO a+cancelTransaction = throwIO . CancelTransaction++data CancelTransaction a+  = CancelTransaction a++instance Show (CancelTransaction a) where+  show CancelTransaction{} = "CancelTransaction"++instance (Typeable a) => Exception (CancelTransaction a)

@@ -0,0 +1,39 @@+{-#LANGUAGE OverloadedStrings #-}+module Database.Sqlite.Easy.Migrant+  ( module Database.Migrant.Driver.Class+  )+  where++import Database.Migrant.Driver.Class+import Database.Migrant.MigrationName+import qualified Database.Sqlite.Easy.Internal as Sqlite+import qualified Database.SQLite3 as Sqlite++instance Driver Sqlite.Database where+  withTransaction action conn = Sqlite.asTransaction' conn (action conn)++  initMigrations conn = do+    [] <- Sqlite.run+      "CREATE TABLE IF NOT EXISTS _migrations (id INTEGER PRIMARY KEY, name TEXT)"+      conn+    pure ()++  markUp name conn = do+    [] <- Sqlite.runWith+      "INSERT INTO _migrations (name) VALUES (?)"+      [Sqlite.SQLText $ unpackMigrationName name]+      conn+    pure ()++  markDown name conn = do+    [] <- Sqlite.runWith+      "DELETE FROM _migrations WHERE name = ?"+      [Sqlite.SQLText $ unpackMigrationName name]+      conn+    pure ()++  getMigrations conn = do+    result <- Sqlite.run+      "SELECT name FROM _migrations ORDER BY id"+       conn+    return [ MigrationName name | [Sqlite.SQLText name] <- result ]

@@ -0,0 +1,1 @@+{-# OPTIONS_GHC -F -pgmF hspec-discover #-}

@@ -0,0 +1,70 @@+{-# language OverloadedStrings #-}+{-# language ScopedTypeVariables #-}++module SqliteEasySpec (spec) where++import Test.Hspec+import Database.Sqlite.Easy+import Control.Exception (try)++spec :: Spec+spec = do+  describe "sanity" $ do+    it "create insert select" $ do+      result <- withDb ":memory:" $ \db -> do+        [] <- run "create table t(x int not null)" db+        [] <- run "insert into t values (1),(2),(3)" db+        run "select * from t" db+      shouldBe+        result+        [[SQLInteger 1], [SQLInteger 2], [SQLInteger 3]]++    it "select with params" $ do+      result <- withDb ":memory:" $ \db -> do+        [] <- run "create table t(x int not null)" db+        [] <- run "insert into t values (1),(2),(3)" db+        runWith "select * from t where x = ?" [SQLInteger 1] db+      shouldBe+        result+        [[SQLInteger 1]]++  describe "transactions" $ do+    it "transaction is rolled back in case of error" $ do+      result <- withDb ":memory:" $ \db -> do+        [] <- run "create table t(x int not null)" db+        Left SQLError{} <- try $ asTransaction db $ do+          [] <- run "insert into t values (1)" db+          [] <- run "insert into t values (2" db+          pure ()+        run "select * from t" db+      shouldBe result []++    it "transaction is commited" $ do+      result <- withDb ":memory:" $ \db -> do+        [] <- run "create table t(x int not null)" db+        (Right () :: Either SQLError ()) <- try $ asTransaction db $ do+          [] <- run "insert into t values (1)" db+          [] <- run "insert into t values (2)" db+          pure ()+        run "select * from t" db+      shouldBe result [[SQLInteger 1], [SQLInteger 2]]++    it "transaction is cancelled" $ do+      result <- withDb ":memory:" $ \db -> do+        [] <- run "create table t(x int not null)" db+        (Right transactionResult :: Either SQLError [[SQLData]]) <- try $ asTransaction db $ do+          [] <- run "insert into t values (1)" db+          cancelTransaction []+        result <- run "select * from t" db+        pure (transactionResult, result)+      uncurry shouldBe result++  describe "pool" $ do+    it "create, use, and destroy" $ do+      pool <- createSqlitePool ":memory:"+      result <- withResource pool $ \db -> do+        [] <- run "create table t(x int not null)" db+        [] <- run "insert into t values (1)" db+        run "select * from t" db+      destroyAllResources pool+      shouldBe result [[SQLInteger 1]]