Todo App with Flask & Vue II: Model & Database

Second chapter of an articles' series about making a todo app using Flask and Vue. Today we will focus on our Todo model and how to store our objects inside a database.

Python ORM: SQLAlchemy

There are two possibilities in handling our database: Raw SQL or ORM. Each have their various pros and cons. To briefly sum them, raw SQL provides you with the freedom of writing your own queries. Yet it comes with security issues if you’re not cautious and cross-engine problems. On the other hand, an ORM works like an API. Instead of SQL you’re using objects and methods. You get less permissibility but the environement is more safer and is often compatible with most of the existing engine.

We’re going to use the later. Python has an extremely well-made ORM called SQLAlchemy and it has its own flask extension, Flask-SQLAlchemy. For our database, sqlite3 will be more than enough.

Installation

Like Flask, the installation of Flask-SQLAlchemy is straightforward.

pipenv install flask-sqlalchemy

Then we have to create our db object in our project. Let’s get back to our app.py.

from flask import flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///todos.db'
db = SQLAlchemy(app)

if __name__ == '__main__':
    app.run(debug=True, port=8000)

We provide both the engine and the path of the database. In our case, at the project’s root.

Todo Model

Now that we have SQLAlchemy working, we have to define our model. To achieve this, we must define a class inheriting db.model. Inside, we put the different fields required: an unique id, a task and a creation date.

from datetime import datetime

class Todo(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    task = db.Column(db.String(140), nullable=False)
    creation_date = db.Column(
        db.DateTime, nullable=False, default=datetime.utcnow)

    def __repr__(self):
      return f'<Todo {self.id}: {self.task}>'

The nullable=False property prevents null values for a field while default puts a default one if none’s provided at the object’s creation.

Playing with Todos

First, let’s open a shell.

pipenv shell
python

Then we create our Todo’s table.

>>> from app import db
>>> db.create_all()

Our database is now ready. We can add todos:

>>> from app import Todo
>>> todo1 = Todo(task="Prepare pasta for this evening")
>>> todo2 = Todo(task="Bake a chocolate cake")
>>> todo3 = Todo(task="Buy some sugar at the grocery store")
>>> db.session.add(todo1)
>>> db.session.add(todo2)
>>> db.session.add(todo3)
>>> db.session.commit()

Access them:

>>> Todo.query.all()
[
    <Todo 1: Prepare pasta for this evening>,
    <Todo 2: Bake a chocolate cake>,
    <Todo 3: Buy some sugar at the grocery store>
]
>>> Todo.query.get(1)
<Todo 1: Prepare pasta for this evening>
>>> Todo.query.filter_by(task="Bake a chocolate cake").first()
<Todo 2: Bake a chocolate cake>

Modify them:

>>> todo = Todo.query.get(3)
>>> todo.task
"Buy some sugar at the grocery store"
>>> todo.task = "Buy sugar and pepper at the grocery store"
>>> db.session.commit()

Or delete them:

>>> todo = Todo.query.filter(Todo.task.startswith('Prepare')).first()
>>> db.session.delete(todo)
>>> db.session.commit()
>>> Todo.query.all()
[
    <Todo 2: Prepare pasta for this evening>,
    <Todo 3: Buy sugar and pepper at the grocery store>
]

Whenever you want to save a change in the database you have to use db.session.commit(). It will send all the necessary queries to apply the modifications.

Conclusion

Along this article we scratched the SQLAlchemy’s and saw the benefits of using an ORM. In the next chapter we will open our app to the world by implementing a Restful API.