Why SQLalchemy create_all() can be reused?

Question

I have very little background knowledge about database and SQLalchemy/sqlite3, but my experiment with these tells me that create_all() can be used almost like "initialize the database if it already exists".

Here is the "form submission" part of flask webapp I wrote:

db = SQLAlchemy(app)
@app.route('/submit', methods=['POST'])
def submit():
    form = UserForm(request.form)
    if request.method == 'POST':
        new_entry = User(form.username.data,
                         form.password.data)
        db.create_all()
        add_entry(new_entry)

The db.create_all() should create a local .db file. If I already have that file, and I submit new data to the form, what happened is that the new_entry is appended to the database table instead of override that .db file. Can anyone confirm that this result is expected? And what is the better way to handle database initialization if this is poorly implemented?

Answer 1

create_all indeed creates only tables that do not exist. It is not intended for migrations but to create the initial structure in an empty database.

You want alembic for migrations, possibly via Flask-Migrate

---

Besides that, having any database structure related code in the web-facing part of your Flask app is questionable. Usually you do such things in a command-line tool (via Flask-Script or click), e.g. python manage.py create_db.

I disagree with your opinion of "poorly implemented". It's rather implemented in a way that stops you from doing the wrong (and possibly dangerous) thing. Imagine you rename a column. A ""smart"" create_all would DROP the old column (it's gone after all) and create a new one - it's impossibly for any tool to know that you renamed it instead of removing and adding one.