title: Efficient way to bulk transfer data from one sqlite database to another with haskell
body: <p>I want to move data from one database a to another database b using haskell. I was wondering what the most efficient method of doing this might be for bulk inserts. I have the following code which works but is incredibly slow in comparison to the python code that i write using the same query. Below is the code that i am currently using:</p>
  
  <pre><code>{-# LANGUAGE OverloadedStrings #-}
  {-# LANGUAGE DeriveGeneric #-}
  
  import           Control.Applicative
  import qualified Data.Text as T
  import           Database.SQLite.Simple
  import           Database.SQLite.Simple.FromRow
  import qualified Data.Time.Clock as CLK
  import           Control.Monad.IO.Class (liftIO)
  
  type DatabaseName = String
  
  data Pump = Pump 
    { rId :: Int
    , micropump_id :: Int
    , test_id :: Int 
    , signature :: T.Text
    , cycle :: Int
    , dispense :: T.Text
    , timestamp :: CLK.UTCTime
    } deriving (Eq, Show)
  
  instance ToRow Pump where
    toRow (Pump a b c d e f g) = toRow (a,b,c,d,e,f,g) 
  
  instance FromRow Pump where
    fromRow = Pump &lt;$&gt; field &lt;*&gt; field &lt;*&gt; field &lt;*&gt; field &lt;*&gt; field &lt;*&gt; field &lt;*&gt; field
  
  
  extractPumpsFromDatabase :: DatabaseName -&gt; Query -&gt; IO [Pump]
  extractPumpsFromDatabase n q = do
    conn &lt;- open n
    r &lt;- query_ conn (q) :: IO [Pump]
    close conn
    return r
  
  addPumpsToDatabase :: (ToRow r) =&gt; DatabaseName -&gt; Query -&gt; [r] -&gt; IO ()
  addPumpsToDatabase n q r = do
    conn &lt;- open n
    executeMany conn q r
    putStrLn ("Done: " ++ n) 
  
  main :: IO ()
  main = do
    conn &lt;- open "databaseA.db"
    execute_ conn "CREATE TABLE IF NOT EXISTS micropumps (id INTEGER PRIMARY KEY, micropump_id INTEGER, test_id INTEGER, data TEXT, cycle INTEGER, dispense TEXT, timestamp TIMESTAMP)"
    close conn
    newPumps &lt;- extractPumpsFromDatabase "databaseB"
    addPumpsToDatabase "databaseA" "INSERT INTO micropumps (id, micropump_id, test_id, data, cycle, dispense, timestamp) VALUES (?,?,?,?,?,?,?)" newPumps
  </code></pre>
  
  <p>is it the conversion to and from the Pump type that is taking so long?</p>