Using Flask-SQLAlchemy with Flask Applications on RStudio Connect

Beginning in RStudio Connect 1.8.2+, it's possible to deploy and manage runtime settings for Flask applications.

Not all data science application requires full create, read, update, and destroy (C.R.U.D.) operations.  However, many use cases require some level of data access. The Flask-SQLAlchemy extension extends the capability of SQLAlchemy within a Flask application by leveraging the Flask extension interface. 

This example will step through a generic setup of access to local and remote data using the Flask-SQLAlchemy  extension by: 

  • Creating a minimal application based on the Flask-SQLAlchemy quickstart guide
  • Define the database model to use
  • Initialize a SQLite database
  • Commit data to the database
  • Deploy the application to RStudio Connect with rsconnect-python

And, optionally:

  • Switch to a PostgreSQL database server
  • Add pyscopg2 dependency to the Python environment
  • Add the database server connection string as an environment variable in RStudio Connect
  • Redeploy the application


For this example to work, the prerequisite configurations and setup include:


If needed, rsconnect-python is available on PyPI.  To install with pip, run the following command:

pip install rsconnect-python


Example application

The example application is the minimal Flask example that will leverage an index template and also a remote database query.
The initial structure of the application:
├─ templates
│ └─ index.html
└─ .gitignore
Add the following snippet to

from flask import Flask, render_template
from flask_sqlalchemy import SQLAlchemy
import os

basedir = os.path.abspath(os.path.dirname(__file__))

app = Flask(__name__)

# set the database to be use
'sqlite:///' + os.path.join(basedir, 'users.db')


# initialize the extension
db = SQLAlchemy(app)

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


def index():
users = User.query.all()
return render_template('index.html', users=users)
The basedir definition and SQLALCHEMY_DATABASE_URI work in conjunction to determine:
  • An environment variable, DATABASE_URI, exists, or
  • Set the SQLALCHEMY_DATABASE_URI to use a SQLite database in the current working directory.

And in index.html:

<!-- index.html -->

{% for user in users %}
Hello, {{ user.username }} from RStudio Connect! <br />
{% endfor %}

This template loops through the received users data and displays a message for each record.

Initializing the database

For a successful database connection, the database must exist. These steps will assume that your working environment is configured to work with the database connection strings.

The database, and tables, need to exist to use the database in the above application. Once the above file is saved, open a Python shell in the environment that is used to test the application. This environment should be where the dependencies are installed.

Note: If there are errors associated with unknown tables or database, it's likely that the database and tables need to be created in the target database.

Create the database

Within the Python REPL, import the database and User model from the app:
>>> from app import db, User

Then create all the tables in this database. Right now, there is only one table - the Users table. 
>>> db.create_all()

This will create the database.
├─ templates
│ └─ index.html
├─ .gitignore
+ └─ users.db
Now records can be committed to the table. We'll create a single row as and example with a username and email.
>>> db.session.add(User(username="Flask", email=""))

Commit the data:
>>> db.session.commit()

Now exit the Python REPL and run the application locally. The data should into the index.html template that is rendered when the base route (/) is accessed.

Deploying the application

We'll deploy the app right from the terminal using rsconnect-python.

Before deployment, it's recommended that a requirements.txt file is created in the working environment. This file will list all the Python packages that exist in the environment. It's good practice to validate this environment before deployment. If the file doesn't exist,  rsconnect-python will create the file during deployment.
pip freeze > requirements.txt
The directory before deployment will be structured as below:
├─ templates
│ └─ index.html
├─ .gitignore
├─ users.db
+ └─ requirements.txt
Now, deploy the application using the rsconnect-python CLI.
rsconnect deploy api . -n <server-name> templates/* users.db

The rsconnect deploy api command instructs RStudio Connect that this is a Flask application. The <server-name> placeholder is a reference to the target RStudio Connect server. Additionally, the templates directory and the SQLite database, users.db is sent to the server.

The database (and database tables) must be created for the application to work with the Flask-SQLAlchemy extension when the application is started. The initial database structure must be deployed alongside the application when using SQLite. This is not the case when using a remote database server (below).

Extending with more data

Following the process above, open the Python REPL again in the local development environment and add another row into the database.

Since the database and table already exist, it doesn't need to be created again:
>>> from app import db, User
>>> db.session.add(User(username="User 1", email=""))
>>> db.session.commit()
Reloading the application should now display a line for each database record.

To deploy the updated application, use the same rsconnect-python command as before:
rsconnect deploy api . -n <server-name> templates/* users.db

Applying database migrations to a deployed SQLite database

  • Download the application from the source versions in RStudio Connect
  • Apply the database changes/migrations to the downloaded database version
  • Test the changes locally
  • Redeploy the application with the updated SQLite database file

Switching to a database server

The above example uses a SQLite database to manage and display data within the application. The accepted databases and connection string patterns for SQLAlchemy are documented here.
The URI formats follow the below structure:
Be aware that the database and table create in the previous section will need to be created again in the database server. The same Python REPL commands are required.

Use with PostgreSQL (optional)

If working with remote database server, the above steps can be followed and adjusted for the database that is used.

Following the SQLAlchemy and Flask-SQLAlchemy documentation, add the PostgreSQL dialect library, pyscopg2, into the Python environment that is being used.
pip install pyscopg2
Note, the above command may differ if a package manager other than pip is used.

Adding environment variables

Once the application is deployed, it will error out when visited in RStudio Connect. The reason is that the application expects the environment variable DATABASE_URI. Add the appropriate connection string as an Environment Variable in the settings pane for the application and confirm. The addition of this variable will automatically restart the application.

'sqlite:///' + os.path.join(basedir, 'users.db')

The above snippet in the file looks for an environment variable by the name of DATABASE_URI. If it does not exist then the SQLite database is used.

The application will throw an error if neither of these options returns a database URI.  Also, the database and table(s) must exist.

After the environment variable is saved, refresh the page, and the correct data should show.

See also

Additional examples and walkthroughs of getting started with Flask application deployment on RStudio Connect: