Using SQLite URI filename format for in-memory database still creates a file

[Sparrow0hawk]

Outline

When using the SQLite URI filename format for an in memory database sqlite:///file::memory:?uri=true Flask-SQLAlchemy still creates a database file on disk.

MRE

You can replicate this behaviour locally with the following code:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.orm import DeclarativeBase, mapped_column, Mapped

class Base(DeclarativeBase):
    pass

db = SQLAlchemy(model_class=Base)

class User(db.Model):
    id: Mapped[int] = mapped_column(primary_key=True)
    username: Mapped[str] = mapped_column(unique=True)
    email: Mapped[str]

app = Flask(__name__)

app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///file::memory:?uri=true"

db.init_app(app)

with app.app_context():
    db.create_all()

@app.route("/")
def index():
    return "<h1>Hello world</h1>"

If you start the app with flask run --debug you'll find you have an instance/:memory: file on disk.

Expected behaviour

Running the above snippet does not produce a file on disk.

Environment:

• Python version: Python 3.11.7
• Flask-SQLAlchemy version: 3.1.1
• SQLAlchemy version: 2.0.27

[davidism]

This looks weird, because :memory: is not a file. Is this actually valid or suggested syntax?

[Sparrow0hawk]

There are examples on SQLite docs of using :memory: as a URI filename with the syntax file::memory: so I believe it is an accepted syntax.