Building MVP with React Flask Full Stack – Day 5 – Setup SQLite database

Hi everyone.

In 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 landing page.

Backend is 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 database system to my Flask MVP, fill database with dummy data and learn how to display that data on frontend by using Jinja2 template tags.

I will use SQLite as my database backend, because it is easy to setup, 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.

 

DB Models

 

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

app/models.py

Next, install Flask-SQLAlchemy library, that will glue SQLite and Flask together and will allow us to query database with ORM, objected 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, that is located at same level as __init__.py file.

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

from flask_sqlalchemy import SQLAlchemy

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

from __init__ import app

db = SQLAlchemy(app)

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 db model definition into SQLite tables.

pipenv install Flask-Migrate

Test first, if we can create SQLite objects by initialising 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 migrate object in main app and did not initialise Flask-Migrate extension.

Fix this by adding object under SQLAlchemy initialisation.

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

Try init db again. But with same error.

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

from flask_migrate import Migrate

Still same error though.

Maybe I forgot to import my models.py to 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 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.

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, SQLite database file app.db  was created in our project root folder.

But, there are no tables?

Try 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’s 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 app.db SQLite database, that is stored in project folder and I was looking at app.db file in app folder.

I didn’t notice this and was looking into wrong database 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!

Share it!


Want to support me in my journey 🚀? You can send me a donation below 👇.

Donate with Paypal

Leave a Reply

avatar

Want to support me in my journey 🚀? You can send me a donation below 👇.

Donate with Paypal

Made with 🔥 by Viet Phan 2018