GatorLF Part 3: AWS RDS and SQLAlchemy

We are going to need some way to store the data used by our application. Let’s use Postgres because it’s free for non-commercial use and open source. I also like being able to use pgAdmin for a graphical look at my database if I need it. I also like elephants.

We will use the standard postgres database and password for now since this is just a development database. Obviously, that will need to be hardened before being deployed to production.

SQLAlchemy has to know where to find our database. We can pass its the location with the config flags. We haven’t set any configuration flags yet so we need to create a new file called config.py that we can use for future configuration options.

Inside the file we are going to set up the database URI to read from an environment variable so that it can’t accidentally be pushed to GitHub (especially important for API/secret keys). Okay, here’s what the file looks like:

import os

class DevConfig():
     DEBUG = False
     CSRF_ENABLED = True
     SECRET_KEY = os.environ['SECRET_KEY']
     SQLALCHEMY_DATABASE_URI = os.environ['DATABASE_URI']
class TestConfig():
     pass
class ProdConfig():
     pass

I set up some other config classes that are empty for the moment but in the future, we will use them to switch between configuration settings on different deployment environments.

We need to set the environment variable, do that (on Windows) with:

set DATABASE_URI = "postgresql://[user:pass]@localhost/postgres

SQLAlchemy needs to be passed an instance of our Flask app as a parameter. We need to edit our __init__.py file in the package app to include a few extra lines relevant to SQLAlchemy.

from flast.ext.sqlalchemy import SQLAlchemy
import os
from config import DevConfig

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = DevConfig.SQLALCHEMY_DATABASE_URI
db = SQLAlchemy(app)

The changes are simple. We import the SQLAlchemy and os packages, read in the database URI and instantiate a db object that forms our connection to Postgres. From now on, we can use SQLAlchemy almost exclusively to interact with the database.

We have an empty database, but that isn’t very useful. Let’s define some database schema for users and lost and found items. I’ll create a file called models.py that will hold our SQLAlchemy schema definitions.

from app import db

Class User(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    username = db.Column(db.String(80), unique = True, nullable = False)
    email = db.Column(db.String(120), unique = True, nullable = False)

Class Item(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    title = db.Column(db.String(120), nullable = False)
    location = db.Column(db.String(120), nullable = False)
    lostBy = db.Column(db.Integer, db.ForeignKey('User.id'), nullable = False)
    active = db.Column(db.Boolean, default = True, nullable = False)
    date = db.Column(db.DateTime, nullable = False)
Class Lost(Item):
    pass

Class Found(Item):
    pass

I’m not trying to write a full-fledged SQLAlchemy tutorial so suffice it to say that the models.py file creates a table for users, items, and some sub-classes that might prove useful later on.

Before creating the initial database I want to setup Flask-Migrate. This will automatically update our database and changes in Postgres when we change the models.py file. Very useful. Some code needs to be added to __init__.py that creates a Flask-Migrations object when the flask application is started.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate, MigrateCommand
import os

from config import DevConfig

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = DevConfig.SQLALCHEMY_DATABASE_URI

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

from app import routes

We create an instance of Flask-Migrate’s migrate class and pass it instances of the Flask application and database. Now migrations can be created by running commands of the form:

flask db <migration_name>

Let’s do that now to create an initial database.

flask db init

> Creating directory C:\Users\Sean Ziegler\Coding\GatorLF\migrations … done
> Creating directory C:\Users\Sean Ziegler\Coding\GatorLF\migrations\versions … done
> Generating C:\Users\Sean Ziegler\Coding\GatorLF\migrations\alembic.ini … done
> Generating C:\Users\Sean Ziegler\Coding\GatorLF\migrations\env.py … done
> Generating C:\Users\Sean Ziegler\Coding\GatorLF\migrations\README … done
> Generating C:\Users\Sean Ziegler\Coding\GatorLF\migrations\script.py.mako … done

and let’s run our first migration (I also needed to install psycopg2 and regenerate the requirements.txt file).

flask db migrate

If this works, you’ll end up seeing a bunch of terminal commands fire off and have a new migrations file in the migrations folder. Now you’ll always have a record of the changes to your database schema!

Leave a Reply