How to use Flask-SQLAlchemy with Hasura PostgreSQL service

Hello, today I will will introduce you to Hasura. Hasura provides a kubernetes based PaaS and a PostreSQL based BaaS. You can get started with deploying your apps live with just a git push. Everything on Hasura runs as microservices on the cluster.

What is important for us today is the PostgreSQL service (called postgres). We will see how to use Flask-SQLAlchemy extension to Flask microframework on Hasura PostgreSQL service. SQLAlchemy is a ORM (Object Relational Mapper) written in Python. Flask-SQLAlchemy is an extension for Flask to add support for SQLAlchemy to your application. Although Hasura provides Data service with which you can operate on the database but you can also use a database ORM like SQLAlchemy with it to model your data within your python code.

Setup a Hasura Project

Let’s setup our flask application on Hasura, we will be using hello-python-flask from Hausra Hub

$ hasura quickstart hello-python-flask --type=free

Running the above command will:
– clone the project from hub
– create a free hasura cluster for your project and
– add your public SSH key (if you don’t have one, it’s time to create one) to the cluster so that you can access it.

Now that you have your everything setup, time to deploy it live, run the following.

$ cd hello-python-flask
$ git add . & git commit -m "Initial commit"
$ git push hasura master

Run the following to open the application on your browser

$ hasura microservices open app

You will see a Hello World application live. The code for that lies in microservices/app/src directory. You can make changes there and do a git push hasura master to see your changes take effect. The project hello-python-flask comes with two tables already, we will get rid of those first.

$ hasura migration db-reset
$ rm migrations/*.yaml migrations/*.sql

Setup environment variables

To connect to the PostgreSQL service running inside the cluster, you will need the username, password, database name and port number. Hasura provides a nice interface to store and access secrets such as passwords, access tokens via hasura secrets. Inside your application run hasura secrets ls to see the available secrets.

$ hasura secrets ls
• Fetching secrets...
notify.sparkpost.key----------|
notify.smtp.password----------|
auth.google.client_secret-----|
notify.twilio.accountsid------|
ssh.authorizedKeys------------|
postgres.password-------------|spectrographic-mortifyingly-inflationary-spinet
auth.secretKey----------------|k62mm8vkjox82q3dkf4b7vikpiy6vm5jxrits5ytg6834btdkr989imlh6lb
auth.sparkpost.key------------|
notify.hasura.token-----------|
notify.mandrill.key-----------|
notify.smtp.username----------|
notify.twilio.authtoken-------|
auth.admin.password-----------|agileness-refresh-altitude-redrew
auth.github.client_secret-----|
auth.linkedin.client_secret---|
postgres.user-----------------|admin
auth.facebook.client_secret---|
notify.msg91.key--------------|

As you can see there is postgres.user and postgres.password, these are the username and password for your postgres database. Among other variables, the hostname is postgres-{name-of-your-cluster}, the port is always 5432 for postgres and the name of the database is hasuradb. Although we can hardcode it in our application code but it’s not a good practice as you will be changing it whenever you change your cluster and also if you host the code on a public repository, other people can get access to your credentials. This is why it’s best to use environment variables for this purpose. As Hasura cluster is essentially a kubernetes cluster, we will add few environment variables to our cluster by editing microservices/app/k8s.yaml. Add the following lines under your image‘s env values.
k8s_screenshot

As you can see POSTGRES_USERNAME and POSTGRES_PASSWORD are fetching value from hasura secrets. Rest are self explanatory. Now we are ready to use SQLAlchemy inside our python code. Let’s push our changes to the cluster.

$ git add .
$ git commit -m "Add environment variables to access postgres service"
$ git push hasura master

Create models

Let’s create a users table. But before that, we have to connect our flask application to the database. Add the following in microservices/app/src/__init__.py

import os
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://{username}:{password}@{hostname}:{port}/hasuradb'.format(
 username=os.environ.get('POSTGRES_USERNAME'),
 password=os.environ.get('POSTGRES_PASSWORD'),
 hostname=os.environ.get('POSTGRES_HOSTNAME'),
 port=os.environ.get('POSTGRES_PORT'))

from src import server, models

So we will now create a user table with the following schema.

schema

Create a new file microservices/app/src/models.py and add the following code. We will create our SQLAlchemy models inside this file.

from src import db

class User(db.Model):
 id = db.Column(db.Integer, primary_key=True, autoincrement=True)
 first_name = db.Column(db.String, nullable=False)
 last_name = db.Column(db.String, nullable=False)
 address = db.Column(db.String)
 city = db.Column(db.String)

db.create_all()
db.session.commit()

Add dependencies to requirements.txt

flask
requests
gunicorn
flask-sqlalchemy
psycopg2

We have added psycopg2 because Flask-SQLAlchemy doesn’t come with the database driver. Push your changes.

$ git add .
$ git commit -m "Add user table"
$ git push hasura master

There you go. You will now be able to see the table via api-console. Run hasura api-console to open the api console in your browser and head over to the DATA tab. You will be able to see the user table there, add the table. Now let’s edit models.py to add some rows to our user table.

...
user1 = User(first_name='Peter', last_name='Parker', address='Forest Hills', city='New York')
user2 = User(first_name='John', last_name='Williams')
db.session.add(user1)
db.session.add(user2)
db.session.commit()

Push your changes and you will be able to see the rows added to the user table.

$ git add .
$ git commit -m "Add users to user table"
$ git push hasura master

table_final
Note that as we have set the id as autoincrement integer, everytime the app starts in your cluster, the two users above will get added again and again in your tables with different ids. You can add unique=True property to avoid that in the User class and will need to handle the errors for it when duplicate data is tried to insert.

That’s it, you can now use SQLAlchemy within your Flask application, wasn’t that easy? Comment if you liked it. See you in the next post. Thanks for reading 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s