`DBInterface.@prepare` fails when used with multiple connections

[mattwigway]

DBInterface.@prepare creates a key for the prepared statement when the macro is evaluated, and then at runtime only prepares the statement if a statement with that key has not already been prepared; otherwise it reuses the already-prepared statement. This is a problem when it is used in a function that may be called on multiple database connections, since the prepared statement is associated with a specific connection. For instance, something like this:

function do_insert(db, val)
    DBInterface.execute(identity, DBInterface.@prepare(() -> db, "INSERT INTO test_table (num) VALUES (?)"), (val,))
end

When do_insert is compiled, a unique key for this @prepare statement will be generated. The first time do_insert runs, the statement will be compiled with whatever the database it is called with happens to be. Subsequent calls to do_insert will use that same prepared statement, even if the database is different; effectively all calls to this function will affect whatever the first database connection opened was. Even if you're only reading one database, this could be an issue in a multithreaded application where threads are not supposed to share connections.

Complete MWE is here

Output (write.jl calls the function above once on a connection to 1.db and once on a connection to 2.db, so both databases should have one entry, but 1.db has two entries and 2.db has none):

(base) macbookpro:dbi_mwe mwbc$ julia --project write.jl 
(base) macbookpro:dbi_mwe mwbc$ julia --project read.jl
1.db:
[:num => 1]
[:num => 2]

2.db: