Building MVP with Flask Day 5 – Setup SQLite

Hi everyone.

In the last session, I prepared grounds for designing front end components by adding Bootstrap 4 to my MVP.

Today, I want to spend some time on backend first, before we can continue on building a landing page. Backend is an important piece in any minimum viable product.

Why? Because I am too lazy to generate dummy data by copy-pasting HTML code to test, how my web page would look like, while filled with real content. Second, without backend, I can't learn and test Jinja2 template tags for conditions and loops.

My goal is to add a database system to my Flask MVP, fill a database with dummy data and learn how to display that data on the frontend by using Jinja2 template tags. I will use SQLite as my database backend because it is easy to set up, I have some experience with it from my Django project and I am afraid to learn PostgreSQL :).

Let's see how far I can get today.

Install Flask SQLAlchemy

Create a new py file inside our app folder right there, that will store our database model definitions.

app/models.py

Next, install the Flask-SQLAlchemy library, which will glue SQLite and Flask together and will allow us to query the database with ORM, object-relational mapping technique.

pipenv install flask-sqlalchemy

Now add some Flask configuration to our Flask app by adding few settings to __init__.py file.

app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///app.db’

This will tell Flask to connect to our SQLite database file, which is located at the same level as __init__.py file.

Continuing with building DB models, import SQLAlchemy to models.py.

from flask_sqlalchemy import SQLAlchemy

Also, import the Flask app object into models.py and create an SQLAlchemy object with an app object as a parameter.

from __init__ import app

db = SQLAlchemy(app) [wbcr_html_snippet id="629" title="adsense-inarticle"]

Create a Flask DB Model

Now I will create my first database model, that will store basic info about financial products. I won't post code of my model here, so let's skip this step and assume, that I correctly defined model in models.py.

One more thing regarding models. Database models need to somehow be created physically in our SQLite file. For this purpose, there is a library, that will migrate the DB model definition into SQLite tables.

pipenv install Flask-Migrate

Test first, if we can create SQLite objects by initializing tables from our models. In CMD execute.

flask db init

Ok, this didn't work. There is some error.

directory = current_app.extensions['migrate'].directory
KeyError: ‘migrate'

Seems like I didn't create a migrate object in the main app and did not initialize Flask-Migrate extension.

Fix this by adding an object under SQLAlchemy initialization.

db = SQLAlchemy(app)
migrate = Migrate(app, db)

Try init DB again. But with the same error.

Ah. I forgot to import the Flask-migrate library, so add it now at the top of models.py.

from flask_migrate import Migrate

Still the same error though.

Maybe I forgot to import my models.py to the main Flask init file?

from models import db

Another error appeared.

ModuleNotFoundError: No module named ‘models'

So import syntax is clearly wrong. Try to change it to...

from app import models

Now it throws a new error. We are making progress :).

db = SQLAlchemy(app)
NameError: name 'app' is not defined

Ok, I can't figure this out now, so let's move all models code into Flask init file and delete models.py for now.

Finally no error after trying Flask init DB again.

Cool, it prepared necessary files for DB migration.

Migrate Flask DB Models

Now, we can create fill migration files with our models' definition with this cmd.

flask db migrate

So far so good.

Check SQLite if tables defined by models have been created.

Yep, the SQLite database file app.db  was created in our project root folder. But, there are no tables?

Try the next step in DB migration flow.

flask db upgrade

Hmm, tables are still missing.

I am desperate, so I will try to move my models to models.py file. And import SQLAlchemy object at the top of my models.py.

from app import db

And again import our models into Flask init file.

from app import models

Try to migrate again, but with no success.

Error: Directory migrations already exists

Fine, I will delete migration directory and app.db and run DB init command again. After that run DB upgrade command.

Hmm, I still don't see any tables.

Omg, I forgot to run migrate command. So, execute DB migrate and then DB upgrade.

Phew, it worked, I see SQLite tables now.

I have no idea what I did wrong in the first place.

Many days later I found out, that Flask migration will migrate changes into the app.db SQLite database, which is stored in the project folder and I was looking at the app.db file in the app folder. I didn't notice this and was looking into the wrong database the whole time!

Well, I didn't manage to fill SQLite tables with dummy data and display content in frontend. Maybe next time. See you in the next post!