Flask: initialize your database with a command line argument!

One common task when deploying applications is to create the database and tables before first run, there are many ways to do this but an elegant one is to be part of your application, lets see how we can do this with the help of click a library which can be used to create and extend command line options in scripts. For the shake of simplicity we will use sqlite as database which creates a local in file database.

Creating the models.py file

First we need to create the models.py file, this file holds a class which is the representation of a table named employees and its schema.

from flask_sqlalchemy import SQLAlchemy 
 
db = SQLAlchemy() 
 
 
class EmployeeModel(db.Model): 
 
    __tablename__ = 'employees' 
 
    id = db.Column(db.Integer, primary_key=True) 
    employee_id = db.Column(db.Integer(),unique=True) 
    name = db.Column(db.String()) 
    age = db.Column(db.Integer()) 
    position = db.Column(db.String(80)) 
 
    def __init__(self,employee_id,name,age,position): 
        self.employee_id = employee_id 
        self.name = name 
        self.age = age 
        self.position = position 
 
    def __repr__(self): 
        return f"{self.name}:{self.employee_id}"

Creating the main.py file

Now we will create the main.py file which will serve as our Flask entrypoint file. We can notice the following two imports, they are necessery to make possible for flask to accept the command line argument that will init the database

from flask.cli import with_appcontext 
import click

The following snippet creates a command line argument named init-db which when given with flask command will initialize the database

@click.command(name='init-db') 
@with_appcontext 
def init_db_command(): 
    db.create_all() 
    click.echo('Initialized the database.') 
 
# Register the CLI command 
app.cli.add_command(init_db_command)

Here is the full script

from flask import Flask 
from models import db 
from flask.cli import with_appcontext 
import click 
 
app = Flask(__name__) 
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///database.db' 
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False 
db.init_app(app) 
 
@click.command(name='init-db') 
@with_appcontext 
def init_db_command(): 
    db.create_all() 
    click.echo('Initialized the database.') 
 
# Register the CLI command 
app.cli.add_command(init_db_command) 
 
if __name__ == '__main__': 
    app.run(host='0.0.0.0', port=5001)

Testing and running

To init the database before first run enter

export FLASK_APP=main.py 
flask init-db 
Initialized the database.

Checking ./instance we can see that the database file is created

ls -l ./instance 
total 24 
-rw-r--r--  1 kpatronas  staff  12288 Jan 19 21:35 database.db

To run the flask app enter

flask run

I hope you found this short article interesting! i think is very convenient to init the database within your script and not by external shell scripts that need maintenance in any change of the schema.