Issue: How to use instance-relative SQLite databases with FlaskDB
Author: eddyg
State: closed
Created: 2023-01-21 19:10:44

I have this code in `test/__init__.py`:

```python
import os
import contextlib
from flask import Flask
from peewee import CharField, PrimaryKeyField
from playhouse.flask_utils import FlaskDB

db_wrapper = FlaskDB()

class User(db_wrapper.Model):
    user_id = PrimaryKeyField()
    user_name = CharField(max_length=128, unique=True)

def create_app() -> Flask:
    app = Flask(__name__, instance_relative_config=True)

    app.config.from_mapping(
        DATABASE="sqlite://" + os.path.join(app.instance_path, "db.sqlite"),
        # DATABASE="sqlite:///db.sqlite",
    )

    # create instance path if it doesn't already exist
    with contextlib.suppress(OSError):
        os.makedirs(app.instance_path)

    db_wrapper.init_app(app)

    if db := db_wrapper.database:
        db.create_tables([User], safe=True)

    return app
```

and when I run:

```shell
flask --app test --debug run
```

It errors with:

```text
peewee.OperationalError: unable to open database file
```

*__but__* if I uncomment the `DATABASE` line shown above so it _doesn't_ use `app.instance_path`, it works fine (but then the database isn't in the `instance` directory).

The `app.instance_path` does exist (or gets created by the code if it's missing).

FWIW, if I replace the call to `db.create_tables` with `db.connect()` _(for example)_ I get the same error.

Is there something I'm missing with getting the SQLite database into the `instance_path` directory?


What is the value of app.instance_path at the time you're trying to use it? The error seems to me to indicate it's a badly formed filename.

If I add this line to code above (before the call to `db.create_tables`):

```python
    print(f"{os.getcwd()=}\n{app.instance_path=}\n{app.config['DATABASE']=}")
```

I get:

```text
os.getcwd()='/Users/eddyg/git/test'
app.instance_path='/Users/eddyg/git/test/instance'
app.config['DATABASE']='sqlite:///Users/eddyg/git/test/instance/db.sqlite'
```


Does that full directory path exist? You might add a `os.makedirs(app.instance_path)`

That call (to ensure the instance_path directory exists) is in the sample code I posted; it definitely exists (or gets created).

Ugh sorry bro I should have double checked the docs. I believe you need three slashes before the path rather than two, http://docs.peewee-orm.com/en/latest/peewee/playhouse.html#db-url

```
"sqlite:///" + ...
```

Ugh, that was indeed the problem.

As you can see from the output above, I already had 3 slashes (the [normal amount](https://en.wikipedia.org/wiki/File_URI_scheme#Unix) for path-based URIs), with **4** slashes (`sqlite:///` + `/path/to/db`) it works to get the database file into the `instance_path` directory.

Any idea why it requires the extra slash?

Just how the stdlib urlparse works.

Hmm... I still don't get it.

With 3 slashes:

```python
from urllib.parse import urlparse
results = urlparse("sqlite:///path/to/file.sqlite")
print(f"{results.scheme=}\n{results.hostname=}\n{results.path=}")
```

```
results.scheme='sqlite'
results.hostname=None
results.path='/path/to/file.sqlite'
```

With 4 slashes:
```python
results = urlparse("sqlite:////path/to/file.sqlite")
print(f"{results.scheme=}\n{results.hostname=}\n{results.path=}")
```

```
results.scheme='sqlite'
results.hostname=None
results.path='//path/to/file.sqlite'
```

Those are the results I expected, and why it seems 3 slashes should be enough. 🤷 

Thanks for your time and the solution! 🎉