Building MVP with Flask Day 18-21 – Migration from SQLite to PostgreSQL

Hey everyone,

today is the 21st day of me building Flask MVP from scratch. I managed to finish About us page design-wise in last the two sessions.

That means, that the minimum viable product is ready to go live. But there is one final task, that I planned to do for a long long time. And that is doing a migration of database backend from SQLite to PostgreSQL.

Why do you ask?

Well up until now, I always followed Django or Flask tutorials, which used SQLite as the main database backend.

SQLite pros

SQLite is a simple SQL database, that is easy to set up, configure and maintain. You don't need to install anything.

Just define your DB models, run a few python SQLAlchemy commands and database is created with all defined tables and columns. Your SQLite database then lives in an SQLite file on your disk.

I see why online Flask and Django tutorials like to use SQLite. For the sake of simplicity and time saving, there is no point to use anything else.

But once you finished your minimum viable product and start to think about deployment into a production server, you will start to care about things, that SQLite lack.

[wbcr_html_snippet id="629" title="adsense-inarticle"]

SQLite cons

To scale your Flask minimum viable product and prepare it for huge upcoming traffic, you want your SQL database to be lightning fast. You want it to serve multiple parallel read-write operations. You want your database to be secure.

Those are the main SQLite cons, speed performance, concurrency, and security.

In the past, I always used an apache stack with a built-in MySQL database. I thought I would stick to known solutions, but hey, this is a learning exercise right?

PostgreSQL as the best DB for Flask

So after googling around for the best free open source SQL database on the market, I decided to go with PostgreSQL.

PostgreSQL is positively reviewed across all areas, there is an existing library for Flask so I told myself why not.

Today, I will try to set up PostgreSQL on my mac development environment. Integrate PostgreSQL into Flask MVP. And migrate database objects and data from SQLite to the new PostgreSQL database.

Migrating from SQLite to PostgreSQL

Installing PostgreSQL

So without further ado, let's start with PostgreSQL installation.

brew update

brew install postgresql

That was easy.

Create a new PostgreSQL database

Now create a new database, that will replace our old SQLite database. Type this to command lien to access PostgreSQL commands.

psql

That went well, it returned some error.

psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/tmp/.s.PGSQL.5432”?

Maybe PostgreSQL is not up and running?

brew services start postgresql

It does now.

==> Successfully started `postgresql` (label: homebrew.mxcl.postgresql)

Try again SQL command, but unsuccessfully.

psql: FATAL:  database "home" does not exist

Home is the name of my mac account and main folder.

Maybe I need to create PostgreSQL manually.

initdb /usr/local/var/postgres

This doesn't seem like the right command.

initdb: directory "/usr/local/var/postgres" exists but is not empty
If you want to create a new database system, either remove or empty
the directory "/usr/local/var/postgres" or run initdb
with an argument other than "/usr/local/var/postgres”.

Ok, this command will create a database correctly.

createdb mydb

Installing PostgreSQL python dependencies

Then install necessary database adapters for python and Flask.

pipenv install psycopg2 Flask-SQLAlchemy Flask-Migrate, psycopg2-binary

[wbcr_html_snippet id="629" title="adsense-inarticle"]

Configuring PostgreSQL in Flask app

Integrating PostgreSQL into Flask is as easy as changing database connection URI in our __init__.py file.

Like so.

#app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///../app.sqlite'
app.config['SQLALCHEMY_DATABASE_URI']='postgresql://localhost/mydb'

Before continuing further, I archive the existing migrations folder and SQLite file into the new temporary folder.

Create PostgreSQL migration files

Now create new migration files by typing this into python shell.

flask db init

This created a new migration folder.

flask db migrate

Versioned database migration files have been created.

Migrate to PostgreSQL

Now apply migrations to the PostgreSQL database.

flask db upgrade

I think it went well.

INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> 88b94cf131e9, empty message

Now I need to check, that tables and columns have been actually created.

And I don't want to use PSQL commands so I need some UI clients to manage PostgreSQL. Fortunately, there is a free PostgreSQL client for mac called DBeaver.

Migrating data to PostgreSQL tables

The last thing to do is to migrate data from SQLite to PostgreSQL DB. I am lazy to figure out how to do it programmatically so I just use DBeaver to import data by copy-pasting data from CSV.

Aaand it is done.

My Flask MVP now runs on the PostgreSQL database.

There is no visible speed improvement in the local development environment, but I hope learning about PostgreSQL and deploying it live will pay off in the future.

The End of Flask MVP journey

This is it. Flask MVP is done and it has been a rewarding journey.

It took me only 21 working days to finish an MVP from zero to fully functional Flask website. From which more than two-thirds was spent on designing and styling in HTML and CSS.

I can't believe I feared learning Flask so much in the past. It is so easy and to fire up a new project and ship it in a matter of days.

Yeah, you can do it too with Wordpress, but once you want to do some behind the scene calculations or other sophisticated logics, nothing beats Flask.

No React-Redux needed after all

Also sorry for being misled, that I there would be a part of development in React. But I could not justify using React for basic simple functionalities, that ended up in the final MVP.

I opted to learn jQuery instead. Yeah, that is a consequence of bad initial analysis and architecture planning.

I thought I will need a single page mini-application for my core features, but SEO reasons won the argument.

I need my content to be pre-rendered, so it can be crawled by search engine robots. I won't go back and change the title of this diary to remove React though.

So my Flask MVP diary ends here.

Another big roadblock is to buy and set up VPS with Ubuntu and deploy Flask MVP into the production environment. I might write about it, I might not. It is a huge topic where many things can go wrong.

If you are interested in my notes from doing setup of Linode Ubuntu VPS and deploying Flask app to it, then write a comment below.

Thanks for sticking with me in my journey. I hope you learned from my mistakes as well and are now doing your own minimum viable products.

Peace.