How do deploy a Flask app to Heroku without using SQLAlchemy?

Question

I have built an an app with Flask, without using SQLAlchemy (so that I don't get an ORM, which for me give less transparency, and understanding), so just plain SQL with SQLite.

Now that I want to publish it online (now I want to test it with a free Heroku), all the tutorials I find seem to use SQLAlchemy to be able to use PostgreSQL instead of SQLite.

I am also using a schema.sql file containing all the code to create the schema (CREATE TABLE user, etc.) which is launched with the command flask init-db.

I am also a bit worried that I will need to change way more code than simply the database link.

Using the db.py code that is in the Flask tutorial, I changed the connect URL to use the one which Heroku gave me.

def get_db():
    if 'db' not in g:
        if os.environ.get('DATABASE_URL') is None:
            g.db = sqlite3.connect(
                current_app.config['DATABASE'],
                detect_types=sqlite3.PARSE_DECLTYPES
            )
            g.db.row_factory = sqlite3.Row
        else:
            DATABASE_URL = os.environ['DATABASE_URL']
            g.db = psycopg2.connect(DATABASE_URL, sslmode='require')

    return g.db

When pushing the code to Heroku with the initial commit, on the URL of my website I get a "Application error" message. And logs say:

2019-08-21T15:12:50.333541+00:00 app[init.1]: Usage: flask [OPTIONS] COMMAND [ARGS]...
2019-08-21T15:12:50.333564+00:00 app[init.1]: Try "flask --help" for help.
2019-08-21T15:12:50.333567+00:00 app[init.1]: 
2019-08-21T15:12:50.333642+00:00 app[init.1]: Error: No such command "init_db".
2019-08-21T15:12:50.434989+00:00 heroku[init.1]: Process exited with status 2
2019-08-21T15:12:54.909027+00:00 heroku[init.1]: Starting process with command `FLASK_APP=myapp flask init_db`
2019-08-21T15:12:55.504819+00:00 heroku[init.1]: State changed from starting to up
2019-08-21T15:12:57.997833+00:00 heroku[init.1]: State changed from up to crashed
2019-08-21T15:12:57.888599+00:00 app[init.1]: Usage: flask [OPTIONS] COMMAND [ARGS]...
2019-08-21T15:12:57.888627+00:00 app[init.1]: Try "flask --help" for help.
2019-08-21T15:12:57.888630+00:00 app[init.1]: 
2019-08-21T15:12:57.888639+00:00 app[init.1]: Error: No such command "init_db".
2019-08-21T15:12:57.978596+00:00 heroku[init.1]: Process exited with status 2

So can I easily deploy a Flask app to Heroku without using SQLAlchemy, or at least not an ORM, so that I can use my plain SQL commands?

score 1 · Accepted Answer · answered Aug 21 '19 at 16:11

You're conflating SQLAlchemy and PostgreSQL.

SQLAlchemy provides

a unified, relatively low-level interface for working with many different database backends via SQLAlchemy Core, and
an optional ORM

It can be used with both PostgreSQL and SQLite (and others).

PostgreSQL is a relational database management system, much like SQLite is.

You can use Postgres directly, e.g. with psycopg2, or with something like SQLAlchemy.

You can't use SQLite on Heroku, with or without SQLAlchemy. SQLite saves its data in a file on the filesystem, and this doesn't play nicely with Heroku's ephemeral filesystem.

To use Postgres without SQLAlchemy, simply

provision the add-on (this should happen automatically if you add psycopg2 to your dependencies),
and connect to it via the connection string given in the DATABASE_URL environment variable.

I am also using a schema.sql file containing all the code to create the schema (CREATE TABLE user, etc.) which is launched with the command flask init-db.

As long as the schema.sql is part of your application you should be able to do

heroku run flask init-db

If that doesn't work you should be able to do something like

cat schema.sql | heroku pg:psql

though personally I'd dig into why heroku run isn't doing what you want, first.

Finally, I strongly recommend switching to PostgreSQL in development as well.

SQLite and PostgreSQL are different products and don't always behave the same way. Some measure of uniformity can be achieved through an ORM or SQLAlchemy's Core (though even then I recommend using the same database everywhere), but if you're writing raw SQL the differences become more important.

How do deploy a Flask app to Heroku without using SQLAlchemy?

1 Answers1