[Bug]: preload_extensions doesn't INSTALL first

[tboddyspargo]

What happened?

Thanks for this convenient sqlalchemy extension, @Mause!

I appreciate the preload_extensions and config options to facilitate extension loading and connection configuration when connections are started. It reduces the boilerplate that's necessary before we can start simply executing queries.

I realize that preload_extensions is just "experimental," but I think it may provide value into the future given that some extensions are not yet "autoloadable" (e.g. spatial) and presumably there will always be the possibility of 3rd party extensions that aren't.

In order to support those cases, I think you may need to ensure that INSTALL {extension} is run before LOAD {extension} to avoid errors like this:

OperationalError: duckdb.duckdb.IOException: IO Error: Extension "/home/user/.duckdb/extensions/v0.9.1/linux_amd64_gcc4/spatial.duckdb_extension" not found.
Extension "spatial" is an existing extension.
Install it first using "INSTALL spatial".

DuckDB Engine Version

0.9.2

DuckDB Version

0.9.1

SQLAlchemy Version

1.4.46

Relevant log output

OperationalError: duckdb.duckdb.IOException: IO Error: Extension "/home/user/.duckdb/extensions/v0.9.1/linux_amd64_gcc4/spatial.duckdb_extension" not found.
Extension "spatial" is an existing extension.
Install it first using "INSTALL spatial".

Code of Conduct

• I agree to follow this project's Code of Conduct

[Mause]

Hey there, this has been raised before, but the landscape has changed a bit since.

We'd probably need a list[str | dict] or something I think? Will have a think

[tboddyspargo]

We'd probably need a list[str | dict] or something I think? Will have a think

Would the dict option be used to support key-value pairs of extension name and URLs from which to install them?

for k,v in preload_extensions.items():
    conn.execute(f"INSTALL {k} FROM {v}")

I hadn't considered that, but it would make sense. I wasn't familiar with that installation syntax.

I imagine str would be used for the default install url or path-based installs, I think:

for e in preload_extensions:
    conn.execute(f"INSTALL {e}")

Are there any other install syntaxes to consider?

[Mause]

Version support is incoming as well: duckdb/duckdb#10437

[Mause]

duckdb/duckdb#11677