Macro or DSL for easier DBInterface usage?

DBInterface is great for using SQL and database queries. However, SQL queries can get lengthy, and the mixing of SQL and Julia code is a bit ugly. I am wondering if there are packages that would replace lines like this

DBInterface.execute(con, "CREATE TABLE integers(i INTEGER)")

and instead allow someone to write something like this

@dbexec con begin
    CREATE TABLE integers(i INTEGER)

where the stuff between begin ... end would be assumed to be proper syntax for whatever backend it is being sent to.

Also I am aware of FunSQL.jl which is a cool project but not what I’m looking for right now.

No, macros still need to be valid Julia syntax, i.e. it has to parse to a valid Julia expression. to do something like integers( INTEGER) you will need a string macro.

Not quite sure I understand. Presumably what this @dbexec macro I’m proposing would do is wrap stuff between begin ... end in quotes and pass it to DBInterface.execute. It doesn’t execute in Julia, it is just a string.

But, to be clear, the reason I don’t understand is that I have no idea how macros actually work. I just use Chain.jl all the time and I think it works wonders, so that was clear inspiration for this question.

See the following:

julia> macro donothing(x)

julia> @donothing begin 
       A B C
ERROR: syntax: "begin" at REPL[8]:1 expected "end", got "B"
 [1] top-level scope
   @ none:1

The Julia parser happens before the stuff gets to the macro. The macro gets a julia expression (a tree-like object), and then can just re-arrange that expression.

But the rules about what is allowed happens before the macro gets stuff.

1 Like

Do you just want to write the query in multiple lines? Ie, is

DBInterface.execute(con, """

close enough?

Have you considered Octo.jl?

No, I do multiple lines right now but I don’t love the look. I kind of want it to stand out more from the julia code.

I have not, it seems might they support DuckDB, which is what I am using now, so I may try it out.