Motivation

I’m working on a larger dashboard application which uses duckdb. I found out a bug which in a nutshell is like this:

# create new connection
con = duckdb.connect('/somedir/duckdb.db')

# do something

# remove or replace the file
Path('/somedir/duckdb.db').unlink()
shutil.copy('/otherdir/duckdb.db', '/somedir/duckdb.db')

# create new connection to the *new* file with same location
con = duckdb.connect('/somedir/duckdb.db')

# PROBLEM: the connection remembers the contents from the first file!

Question

How do you guarantee that when you create a new duckdb.DuckDBPyConnection object with:

import duckdb

con = duckdb.connect('/somedir/duckdb.db')

that duckdb would not try to use some internal cache? In essence, I want a new, clean connection to the specified location no matter what has happened before in the application.

MWE for reproducing the problem

• Requires: panel==1.3.8, duckdb==0.10.2 (also tested the duckdb 1.0.0)
• Note: The problems in the MWE (not closing the connection, etc.) are not part of this question. It is there to demonstrate that if you have a bug in your application logic, you may have bizarre results with duckdb connections. The point is to find a bullet-proof way to open a new duckdb connection to a file, no matter how buggy your application is.

import random
from pathlib import Path

import duckdb
import panel as pn

filepath = Path("/tmp/foo1.db")


class Application:
    def __init__(self):
        self.column = pn.Column("foo")
        self.con = duckdb.connect(str(filepath))
        add_data_to_table(f"table_{random.randint(0,1_000_000)}")

    def make_servable(self) -> None:
        pn.extension(template="fast")
        self.column.servable()


def add_data_to_table(tablename):
    pth = Path(filepath)
    pth.parent.mkdir(parents=True, exist_ok=True)

    # this should remove the database.
    pth.unlink(missing_ok=True)
    # this should create a new database to the same filepath.
    con = duckdb.connect(str(filepath))
    print("before:n" + str(con.sql("show tables")))
    con.sql(f"CREATE TABLE {tablename} AS SELECT 42 AS i, 84 AS j")
    print("after:n" + str(con.sql("show tables")))


if __name__.startswith("bokeh"):
    app = Application()
    app.make_servable()

To reproduce the behavior, serve the app, and refresh the page once:

python -m panel serve mwe_app.py --show --admin

What one would expect in the output

• Note: Each time the ‘before’ shows an empty database as there is not such file.

2024-06-06 14:16:20,566 Starting Bokeh server version 3.2.2 (running on Tornado 6.4)
2024-06-06 14:16:20,566 User authentication hooks NOT provided (default user enabled)
2024-06-06 14:16:20,569 Bokeh app running at: http://localhost:5006/mwe_app
2024-06-06 14:16:20,569 Starting Bokeh server with process id: 170840
before:
┌─────────┐
│  name   │
│ varchar │
├─────────┤
│ 0 rows  │
└─────────┘

after:
┌──────────────┐
│     name     │
│   varchar    │
├──────────────┤
│ table_327465 │
└──────────────┘

2024-06-06 14:16:20,958 WebSocket connection opened
2024-06-06 14:16:20,958 ServerConnection created
before:
┌─────────┐
│  name   │
│ varchar │
├─────────┤
│ 0 rows  │
└─────────┘

after:
┌──────────────┐
│     name     │
│   varchar    │
├──────────────┤
│ table_374801 │
└──────────────┘

What you will see in the output

• Note: After one page refresh (new session), ‘before’ shows the contents of the old database (even if the file has been deleted).

2024-06-06 14:19:07,186 Starting Bokeh server version 3.2.2 (running on Tornado 6.4)
2024-06-06 14:19:07,187 User authentication hooks NOT provided (default user enabled)
2024-06-06 14:19:07,189 Bokeh app running at: http://localhost:5006/mwe_app
2024-06-06 14:19:07,189 Starting Bokeh server with process id: 172876
before:
┌─────────┐
│  name   │
│ varchar │
├─────────┤
│ 0 rows  │
└─────────┘

after:
┌──────────────┐
│     name     │
│   varchar    │
├──────────────┤
│ table_604433 │
└──────────────┘

2024-06-06 14:19:07,556 WebSocket connection opened
2024-06-06 14:19:07,556 ServerConnection created
before:
┌──────────────┐
│     name     │
│   varchar    │
├──────────────┤
│ table_604433 │
└──────────────┘

after:
┌──────────────┐
│     name     │
│   varchar    │
├──────────────┤
│ table_30485  │
│ table_604433 │
└──────────────┘