Does DuckDB not allow multiple connections?

Specific Domains Data
1

Hi all. Just curious if I am able to do something like the following MWE. I want to have 2 connections to a relational DB, one of which I can use for R/W and one which is read-only. The read only one I’d like to use FunSQL.jl to design my queries. It seems with DuckDB I cannot do that (set duckdb = true in MWE and see it does not return data which was just written). However SQLite does work.

I don’t know enough about DuckDB’s design, is this a fundamental limitation or can I address it somehow on the Julia side? I guess there should be some kwarg somewhere to tell it whether or not the connection is read-only or not, but I could not find any docs which told me about it.

using FunSQL, DataFrames
using FunSQL:
    FunSQL, Agg, Append, As, Asc, Bind, CrossJoin, Define, Desc, Fun, From,
    Get, Group, Highlight, Iterate, Join, LeftJoin, Limit, Lit, Order,
    Partition, Select, Sort, Var, Where, With, WithExternal, render

using DuckDB, SQLite

duckdb = false

if isfile("./test.db")
    rm("./test.db")
end

# set up schema
con = DBInterface.connect(duckdb ? DuckDB.DB : SQLite.DB, "./test.db")
DBInterface.execute(con, """
    CREATE TABLE tab (
        name TEXT,
        val NUMERIC
    );
""")
DBInterface.close!(con)

# open 2 connections, one for R/W one for R (FunSQL)
con1 = DBInterface.connect(duckdb ? DuckDB.DB : SQLite.DB, "./test.db")
con2 = DBInterface.connect(FunSQL.DB{duckdb ? DuckDB.DB : SQLite.DB}, "./test.db")

# insert data
DBInterface.execute(con1, """
    INSERT INTO tab VALUES
    ('a', 5),
    ('b', 6),
    ('c', 5),
    ('a' ,3);
""")

# gets the data
DBInterface.execute(con1, "SELECT * FROM tab;") |> DataFrame

# returns nothing w/DuckDB, works with SQLite
q = From(:tab)
DBInterface.execute(con2, q) |> DataFrame

DBInterface.close!(con1)
DBInterface.close!(con2)