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
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!