2014-01-25

Flask TodoMVC: Dataset

This is the third article in the Flask TodoMVC Tutorial, a series that creates a Backbone.js backend with Flask for the TodoMVC app. In the first article, we created a Flask app using the Backbone.js example as a starting point. In the second article, we added server side synchronization. In this article, we add a database backend using dataset.

We will begin with where we left off in the previous article. If you would like to follow along, but do not have the code, it is available on GitHub.

# Optional, use your own code if you followed part 2
$ git clone https://github.com/kevinbeaty/flask-todomvc
$ cd flask-todomvc
$ git checkout -b dataset part2

Now that we have the code, we are ready to begin.

Install dataset

We are going to use dataset because we love the icon and are feeling lazy. We need to install it. If you followed part 1 and are using pip and virtualenvwrapper, it looks something like this:

$ workon flask-todomvc
$ pip install dataset

Easy enough.

Replace todo list with dataset table

In the previous article we used a Python list to store our todo items. We are going to replace this list with a table in a SQLite database managed by dataset.

Open server.py and use dataset to connect to a SQLite database named todos.db and retrieve the todo table.

import dataset
db = dataset.connect('sqlite:///todos.db')
todos = db['todos']

That's really all there is to it. The database and table will be loaded or created automatically. What about the schema? That is also automatically managed by dataset. These three lines will:

  1. Connect to a SQLite database, specified by the path, creating the file if it does not exist.
  2. Load the schema for the todos table. If the table does not exist, it will be created with an id column.

Let's modify todo_create to store the items in this table.

@app.route('/todos/', methods=['POST'])
def todo_create():
    todo = request.get_json()
    todos.insert(todo)
    return _todo_response(todo)

So what's going on here?

  1. Backbone.js posts the new todo item as a JSON document
  2. We retrieve this JSON request as a dict using Flask get_json
  3. Since dataset accepts a dict, we can pass this directly to insert. Again, dataset will detect and add any new columns automatically.

That's it.

NOTE: In a production app you would want to add validation. You may have noticed that we never defined nor identified any fields for our todo items. This is because we are trusting the client to validate any input posted to our server. This is, of course, a bad idea in any production app.

Now that we can add new items, let's modify the index to load them from the table.

@app.route('/')
def index():
    _todos = list(todos.all())
    return render_template('index.html', todos=_todos)

We retrieve all items from the table and wrap them in a list so the result set is JSON serializable.

Next modify update and delete.

@app.route('/todos/<int:id>', methods=['PUT', 'PATCH'])
def todo_update(id):
    todo = request.get_json()
    todos.update(todo, ['id'])
    return _todo_response(todo)


@app.route('/todos/<int:id>', methods=['DELETE'])
def todo_delete(id):
    todos.delete(id=id)
    return _todo_response({})

Here we are updating or deleting the item identified by the id passed with the JSON document. Again, we would add more validation in production.

Finally modify todo_read.

@app.route('/todos/<int:id>')
def todo_read(id):
    todo = _todo_get_or_404(id)
    return _todo_response(todo)

...

def _todo_get_or_404(id):
    todo = todos.find_one(id=id)
    if todo is None:
        abort(404)
    return todo

We simply used dataset find_one to find the item with given id and aborting with a 404 if not found.

Conclusion

In this article, we used dataset to add database persistence to our todo app. Our todo list will now survive a server restart. We did neglect to add validation or any access control. We may revisit these oversights in future articles. In the next article we will setup unit testing.

The code is available on GitHub with tag dataset or compared to previous article.