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)
2

@slwu89 ,

Here is a version that works:

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 = true

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 R/W connection 
con1 = DBInterface.connect(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

# Open a R connection (FunSQL)
con2 = DBInterface.connect(FunSQL.DB{duckdb ? DuckDB.DB : SQLite.DB}, "./test.db")

q = From(:tab)
DBInterface.execute(con2, q) |> DataFrame

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

It is strange that the other connection does not see the update. I am not sure why. Almost like it is being held in an “old” state. However, if I can make a suggestion that gives a bit more elegant of a solution to support building FunSQL queries, I might do something more like the following:

using DataFrames
using DuckDB
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, reflect

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

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

conn = DBInterface.connect(DuckDB.DB, "./test.db")

# List out all tables in schema of interest
tables = reflect(
    conn;
    # Default DuckDB Schema
    schema = "main",
    # FunSQL uses the :postgresql dialect under the hood fur DuckDB
    dialect = :postgresql 
);

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

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

# Develop FunSQL Query using FunSQL Tables
fun_sql = From(tables[:tab])

# Resolve to SQL
sql = render(tables, q)

# Execute SQL
DBInterface.execute(conn, sql) |> DataFrame
DBInterface.close!(conn)

In either case, this feels like a DuckDB.jl issue. It is not clear to me what is happening here.

1 Like
3

Doing the same thing from Python works:

Python 3.13.3 (main, Apr  9 2025, 07:44:25) [GCC 14.2.1 20250207] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import duckdb
... 
... con = duckdb.connect('test.db')
... con.sql("""
...     CREATE TABLE tab (
...         name TEXT,
...         val NUMERIC
...     );
... """)
... con.close()
... 
... con1 = duckdb.connect('test.db')
... con2 = duckdb.connect('test.db')
... 
... # insert data
... con1.sql("""
...     INSERT INTO tab VALUES
...     ('a', 5),
...     ('b', 6),
...     ('c', 5),
...     ('a' ,3);
... """)
... 
... # gets the data
... con1.sql("SELECT * FROM tab;").show()
... con2.sql("SELECT * FROM tab;").show()
... 
... con1.close()
... con2.close()
... 
┌─────────┬───────────────┐
│  name   │      val      │
│ varchar │ decimal(18,3) │
├─────────┼───────────────┤
│ a       │         5.000 │
│ b       │         6.000 │
│ c       │         5.000 │
│ a       │         3.000 │
└─────────┴───────────────┘

┌─────────┬───────────────┐
│  name   │      val      │
│ varchar │ decimal(18,3) │
├─────────┼───────────────┤
│ a       │         5.000 │
│ b       │         6.000 │
│ c       │         5.000 │
│ a       │         3.000 │
└─────────┴───────────────┘

So indeed appears to be an issue with the Julia bindings of DuckDB.

2 Likes
4

It can support concurrent connection across multiple threads as shown in the documentation here. I think you are missing the Database type which you create with DuckDB.DB(), from which you can create multiple Connections using DuckDB.connect(). Databases are thread safe, but Connections are not, so it is better to pass a Database within your code so you know how to be thread safe. DuckDB will cache results in memory and does not rely on file locking like sqlite.

2 Likes