Haskell bindings for duckdb.

Full-featured haskell bindings for the duckdb database.

duckdb-haskell

Full-featured Haskell bindings for DuckDB.

Installation

The library is available on Hackage.

Usage

• Database.DuckDB.Internal.FFI: fully FFI bindings to the DuckDB C API
• Database.DuckDB.Connection: connection management
• Database.DuckDB.Query: query execution
• Database.DuckDB.Appender: bulk data loading

Connection

• Connect to a database:

  defaultConnectionTest :: TestTree
  defaultConnectionTest = testCase "Setup the default duckdb database" $ do
      r <- runDuckDB $ do
          (conn, db) <- defaultConnection
          close (conn, db)
      r @?= Right ()
  

Query results

• Query from the database:

  query42Test :: TestTree
  query42Test = testCase "Query the constant (42)" $ do
      r <- runDuckDB $ withDefaultConnection $ \(_db, conn) -> do
          r <- query conn "select 42;"
          v <- valueInt32 r 0 0
          liftIO $ v @?= 42
      r @?= Right ()
  

• Query from the database, more complex example:

  queryCreateTableTest :: TestTree
  queryCreateTableTest = testCase "Create table and query" $ do
      r <- runDuckDB $ withDefaultConnection $ \(_db, conn) -> do
          _ <- query conn "CREATE TABLE integers (i INTEGER)"
          _ <- query conn "INSERT INTO integers VALUES (1), (2), (3), (999)"
          r <- query conn "SELECT i FROM integers"
          valueInt32 r 0 0 >>= \v -> liftIO $ v @?= 1
          valueInt32 r 0 1 >>= \v -> liftIO $ v @?= 2
          valueInt32 r 0 2 >>= \v -> liftIO $ v @?= 3
          valueInt32 r 0 3 >>= \v -> liftIO $ v @?= 999
      r @?= Right ()
  

• Query from the database, inspecting the value using Data.Vector.Storable:

  queryDataVector :: TestTree
  queryDataVector = testCase "Create table and query as efficient vectors" $ do
      r <- runDuckDB $ withDefaultConnection $ \(_db, conn) -> do
          _ <- query conn "CREATE TABLE integers (i INTEGER)"
          _ <- query conn "INSERT INTO integers VALUES (1), (2), (3), (999)"
          r <- query conn "SELECT i FROM integers"
  
          nchk <- chunkCount r
          liftIO $ nchk @?= 1
  
          chk <- chunkAt r 0
          columns <- getChunkColumnCount chk
          liftIO $ columns @?= 1
  
          rows <- getChunkSize chk
          liftIO $ rows @?= 4
  
          pointer <- getVectorData =<< getChunkVector chk 0
  
          liftIO $ do
              vec <-
                  Vec.unsafeFromForeignPtr0 <$> newForeignPtr_ pointer <*> (pure rows)
                      :: IO (Vec.Vector Int32)
              vec @?= Vec.fromList [1, 2, 3, 999]
      r @?= Right ()
  

Bulk data loading

• Efficiently loading data using appenders:

  appenderTableTest :: TestTree
  appenderTableTest = testCase "Create table, append data and query" $ do
      r <- runDuckDB $ withDefaultConnection $ \(_db, conn) -> do
          _ <- query conn "CREATE TABLE integers (i INTEGER, j INTEGER)"
  
          withAppender conn "" "integers" $ \app ->
              forM_ [1 .. 100] $ \i -> withAppenderRow app $ do
                  appendInt32 app i
                  appendInt32 app (i + 99)
  
          r <- query conn "SELECT i, j FROM integers"
  
          liftIO $ chunkCount r >>= print