crystal-db
crystal-db copied to clipboard
crystal-lang
Perform multiple commands in a single exec
In some use cases, you simply want to send a number of SQL commands to the server and have them executed in bulk. A basic example for this would be running a *.sql file.
DB.exec "CREATE TABLE foo (foo int); INSERT INTO foo VALUES (42);"
Currently, this is not really supported in crystal-db because QueryMethods#exec is implemented to always build a statement first and then execute it. But a statement can only be a single command.
So in order to execute commands in bulk there needs to be a way to bypass the statement creation and directly execute the query. This obviously makes it impossible to bind query arguments. But that's not an issue for bulk commands. In fact, executing a query without arguments directly is usually much faster than building and executing a statement first. Golangs's pg driver for example uses simple queries as a performance improvement whenever there are no query arguments.
crystal-pg driver already provides a Connection#exec_all method which executes a simple query, supporting bulk commands. But this is only a driver-specific implementation. It requires a separate method and only works with pg.
It would be great to have a single interface with #exec allowing to execute multiple commands as long as no query arguments are used. This could be improved inside crystal-pg by overriding PG::Connection#exec, but Database#exec can't be overridden by a driver (or, shouldn't).
I suppose this could be implemented by changing QueryMethods#exec to delegate to a new methods #simple_exec when args are empty.
Connection#simple_exec would need to be implemented by the driver and it might fall back to build(query).exec. This could probably be used as default implementation.
Database#simple_exec would checkout a connection and delegate to the same method.
The exec returns ExecResult, so the simple_exec would need to return the same struct if we want the crystal-db to do the optimization. Although crystal-pg already needs to ignore last_insert_id.
Maybe we can add a Database/Connection#run : Nil that takes no argument. And each driver could override the implementation if doing an exec of unprepared statements is not the best thing to do.
