Create sqlite database using Flask-Migrate

Question

I'm working on a Flask app using SQLAlchemy to interface a sqlite database and Flask-Migrate to manage the migrations.

I generated a first migration script using

flask db init
flask db migrate

I pushed the repo to another server, and I'm trying to create the database using

flask db upgrade

Is this the right procedure?

I'm pretty sure the first time I did this, the sqlite database was created.

I'm trying to do it again on another server, and I get

SqlDatabaseDoesNotExistError: Database "sqlite:////absolute/path/to/sqlite.db" does not exist

I also get the same error if delete and try to recreate the database on the first server.

The directory exists and is writable, but the database file does not exist.

Shouldn't sqlalchemy create the sqlite database if it does not exist?

How am I supposed to create the database? Flask-Migrate documentation is not explicit about this, as this is a sqlite specific issue (other database engine wouldn't create the database automatically, I guess).

I managed to get it to work by first creating the empty DB file first using touch. Maybe that's what I did the other day, I can't remember.

Looks like a permissions issue except I don't get it.

The permissions are:

drwxrwx--- 2 root www-data 4096 Apr  5 18:50 sqlite

and I'm running

flask db upgrade

as root anyway so I don't see why it wouldn't work.

Here's what I did:

touch sqlite/sqlite.db
chown www-data:www-data sqlite/sqlite.db
chmod 600 sqlite/sqlite.db
flask db upgrade

And then it works.

I still have the feeling I'm not doing it the right way.

Make sure the directory containing it is writable by the user as well! — Klaus D.
– Klaus D., Commented Apr 5, 2018 at 15:41
have you set the FLASK_APP environment variable set on new server. — majin
– majin, Commented Apr 5, 2018 at 15:42
@KlausD. the directory exists and is writable. Only the database file does not exist. — Jérôme
– Jérôme, Commented Apr 5, 2018 at 15:44
@majin yes. Otherwise I'd get "Could not locate Flask application." error. — Jérôme
– Jérôme, Commented Apr 5, 2018 at 15:47
@Jerome take a look at this discussion about creating the database within flask-Migrate. I think SQLAlchemy.create_all() could be called by a custom CLI command when you deploy the app the first time — PRMoureu
– PRMoureu, Commented Apr 7, 2018 at 11:10

Jérôme · Accepted Answer · 2018-04-09 15:46:57Z

2

TL; DR

Yes, the procedure is correct. In the case of a sqlite database, this should work.

Shame on me. The error came from a check I introduced a while ago in the application code to stop at init time if there is no database. I wonder how I missed that.

Since flask db upgrade initiates the application, we go through this check before we get to create the BD, which I didn't anticipate...

The check uses

sqlalchemy_utils.functions.database_exists(base_uri)

which in the case of a sqlite DB will return True if the file exists, even if it is empty, which explains the need to touch the file.

I removed the check. If the admin didn't launch flask db upgrade and the app is started with no DB, there will be no feedback until the first request to the DB. This is not ideal but this is a minor and unrelated issue.

edited Apr 9, 2018 at 15:46

answered Apr 9, 2018 at 8:21

Jérôme

14.9k9 gold badges74 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create sqlite database using Flask-Migrate

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related