sqlite-simple

sqlite-simple copied to clipboard

The documentation for execute says "Execute an INSERT, UPDATE, or other SQL query that is not expected to return results.". However, ultimately execute calls Database.SQLite3.prepare whose documentation says "Unlike exec, prepare only executes the first statement, and ignores subsequent statements." (i.e. ignores subsequent statements separated by a semicolon).

Either

1. execute should be rewritten somehow to support executing multiple statements (my preference)
2. The documentation should make it very clear it doesn't support this

Yes, the sqlite-simple execute is not meant to be the same as sqlite3_exec(). I didn't verify this but AFAIK bound parameters (which execute uses) are only supported with prepared statements, thus prepare/step is necessary and using sqlite3_exec is not an option as it would break query params. I guess it can be argued that execute_ could still call sqlite3_exec but I want execute and execute_ to behave the same way.

Sqlite-simple doesn't look at the SQL query string at all (that'd require some level of parsing) but punts it directly to the native sqlite3 library. Thus it has no way of knowing how many statements are in the query string.

I definitely agree the least that should be done would be to state this limitation clearly in the API docs.

Specifically it's worth pointing out the behavior differs from the postgresql-simple function of the same name, which is originally why I got caught out.

Is there a workaround to this, if I have, for example, a .sql file with several queries and I want to run it from Haskell? Is the workaround to import .Internal, get a connection handle, and drop to direct-sqlite?

That'd be a reasonable work-around. I think it'd be fine to also change execute_ to use sqlite3_exec and document that this variant supports multiple SQL statements. I'd be happy to review and merge a PR that does this.

As I mentioned in https://github.com/nurpax/sqlite-simple/issues/44#issuecomment-120834848, sqlite-simple does not look into or modify the input query string at all, but passes it directly to the native sqlite library and thus is limited by what sqlite prepared statements support. AFAIK, postgresql-simple does parameter substitution differently and can support multiple statements.

@nurpax I don't see function that executes multiple SQL statements. Are you still waiting for PR for this feature? As I can see, execute_ is implemented like this:

https://github.com/nurpax/sqlite-simple/blob/ada45945d14ea8fee59ee3f02860e36b6331f8c9/Database/SQLite/Simple.hs#L380-L384

Am I right that it should use exec function from direct-sqlite library?

So the implementation would look like this:

-- | A version of 'execute' that does not perform query substitution.
execute_ :: Connection -> Query -> IO ()
execute_ conn template =
  withStatement conn template $ \(Statement stmt) ->
    void $ Base.exec (connectionHandle conn) (??? don't know what to pass here ???)

For those looking for an example on how to connect to the database and possibly populate it with a schema query in one go:

import qualified System.Posix.Files as File
import qualified Database.SQLite3 as Sqlite3
import qualified Database.SQLite.Simple as Sqlite
import Control.Monad (unless)

connect :: Sqlite.Query -> FilePath -> IO Sqlite.Connection
connect schema path = do
  exists <- File.fileExist path
  connection @ Sqlite.Connection {..} <- Sqlite.open path
  unless exists $ Sqlite3.exec connectionHandle (Sqlite.fromQuery schema)
  return connection