SQL and Macros

General Usage
1

I have the following script that runs fine:

using DuckDB
using DBInterface
using DataFrames

# Global connection variable for reuse
const duckdb_connection = Ref{Union{DuckDB.DB, Nothing}}(nothing)

# Function to manage the connection
function get_duckdb_connection()
    if isnothing(duckdb_connection[])
        duckdb_connection[] = DBInterface.connect(DuckDB.DB, ":memory:")
    end
    return duckdb_connection[]
end

# SQL macro that accepts a string literal
macro SQL1(query::String)
    return quote
        try
            con = get_duckdb_connection()
            DBInterface.execute(con, $query)
        catch err
            println("Error executing SQL: ", err)
            throw(err)
        end
    end
end

# Execute statements individually
@SQL1 "CREATE TABLE sample (id INTEGER, name TEXT, gender TEXT)"
@SQL1 "INSERT INTO sample VALUES (1, 'Alice', 'F'), (2, 'Bob', 'M'), (3, 'Charlie', 'M')"
result = @SQL1 "SELECT * FROM sample"
@show DataFrame(result)

However, I would like to have sequences of SQL statements inside a begin ... end block without quotes, something like:

result = @SQL2 begin
    CREATE TABLE sample (id INTEGER, name TEXT);
    INSERT INTO sample VALUES (1, 'Alice', 'F'), (2, 'Bob', 'M'), (3, 'Charlie', 'M');
    SELECT * FROM sample;
end

Would this be possible and if so, I wonder if there is a simple way to achieve this (e.g. without custom parsers)? Thank you!

2

This is not valid Julia source code (immediate ParseError), so no, a macro can’t transform this. If you want different syntax, you have to start from a string, maybe multiple if easier. SQL1 doesn’t do any transformations that needs a macro so far, you’re basically inlining the try-catch code.