Enabling Non-Legacy SQLite Transactional Modes

[hyperknot]

Hi, SQLAlchemy has this topic on the subject:

Enabling Non-Legacy SQLite Transactional Modes with the sqlite3 or aiosqlite driver

Can you explain how can this be integrated with FastSQLA?

In the current version I found the only way to pass anything DB related is via the os.environ trick. This is a bit weird, as I export a string I prepared into an env var, just so that FastSQLA can read it again.

This is good for the basic URL, but how can I pass connect_args={"autocommit": False} into it?

This is how my code looks.

from fastsqla import lifespan

sqlalchemy_url_async = f'sqlite+aiosqlite:///{sqlite_path}?check_same_thread=false'

os.environ['SQLALCHEMY_URL'] = sqlalchemy_url_async

app = FastAPI(
    lifespan=lifespan,
)

---

Also, I believe check_same_thread is not required anymore, as it's the default:
https://docs.sqlalchemy.org/en/20/dialects/sqlite.html#threading-pooling-behavior

[hadrien]

Hi @hyperknot , sorry for the delay. Thanks for taking the time to submit this.
Looking into this right now.

[hadrien]

Enabling Non-Legacy SQLite Transactional Modes with the sqlite3 or aiosqlite driver

Can you explain how can this be integrated with FastSQLA?

It cannot currently. And passing dict as json strings in an environment variable as documented here fails as the JSON string is not deserialized as a dict by SQLAlchemy:

export SQLALCHEMY_CONNECT_ARGS={"autocommit":0}

Will fail because connect_args will be a string internally rather than a dict as expected.

In the current version I found the only way to pass anything DB related is via the os.environ trick. This is a bit weird, as I export a string I prepared into an env var, just so that FastSQLA can read it again.

Currently, fastsqla is only relying on the environment to get DB configuration.
We should probably allow configuring programatically.

What are you trying to achieve exactly?

I could better help giving a quick fix until fixing how to do configuration.

[hyperknot]

I just wanted to set autocommit: False as per the guidelines. It's not a big problem as right now FastSQLA is a single file, so I could just copy it into my project and modify it there. But strictly speaking this is a "fork" of FastSQLA even if I'm just copying a single file into my repo.

It'd be nice to have a full dict based configuration into lifespan and not just a single value. Passing SQLALCHEMY_URL as os.environ is also a bit weird. There can be reasons why I don't want to put a password in the global os.environ.

So for me, the best solution would be passing a dict into lifespan.

[hyperknot]

Thanks for the quick fix!!

[hadrien]

@hyperknot np :)

Got issues with packaging that I just fixed FYI
Changelog has not been automatically updated as expected though :(

[hyperknot]

I still like to copy this into my repo, with a single file it's such a nice library to work with.

[hadrien]

Changelog has been fixed.

I still like to copy this into my repo, with a single file it's such a nice library to work with.

Unsolicited advice: use https://docs.astral.sh/uv/ to manage dependencies instead.

[hyperknot]

I'm using uv of course. The biggest advantage of copying into my repo was:

• This issue - got fixed by feat: new_lifespan to allow configuring sqla engine directly #25
• upping max_page_size to 99999. It's nice not to implement pagination in fast prototyping phase, while keeping the request/response format identical once I do. Currently it's hard coded. Could be a one line PR though. Should I open an issue?
• I like using https://github.com/simonw/files-to-prompt and pasting all .py files into an LLM. That's basically the only proper reason to copy instead of uv add.

[hadrien]

• upping max_page_size to 99999. It's nice not to implement pagination in fast prototyping phase, while keeping the request/response format identical once I do. Currently it's hard coded. Could be a one line PR though. Should I open an issue?

It's only documented in https://hadrien.github.io/FastSQLA/#features and I should probably expand on it in the pagination doc. You can actually already configure this without hardcoding anything IIUC:

...
from fastapi import Page, new_pagination
...

Paginate = new_pagination(max_page_size=99999)

@app.get("/heros", response_model=Page[HeroModel])
async def get_heros(paginate:Paginate):
    return paginate(select(Hero))