Hey everyone,

today is the 21th 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 minimum viable product is ready to go live.

But there is one final task, that I planned to do for the 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, that used SQLite as main database backend.

SQLite is simple SQL database, that is easy to setup, configure and maintain.

You don’t need to install anything.

Just define your DB models, run few python SQLAlchemy commands and database is created with all defined tables and columns.

Your SQLite database then lives in a 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 production server, you will start to care about things, that SQLite lack.

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 main SQLite cons, speed performance, concurrency and security.

In the past I always used apache stack with built in MySQL database.

I thought I would stick to known solutions, but hey, this is a learning exercise right?

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 existing library for Flask so I told myself why not.

Today, I will try to setup PostgreSQL on my mac development environment.

Integrate PostgreSQL into Flask MVP.

And migrate database objects and data from SQLite to new PostgreSQL database.

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

brew update

brew install postgresql

That was easy.

Now create 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 psql 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 database correctly.

createdb mydb

Then install necessary database adapters for python and Flask.

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

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 existing migrations folder and SQLite file into new temporary folder.

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

flask db init

This created new migration folder.

flask db migrate

Versioned database migration files have been created.

Now apply migrations to 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 column have been actually created.

And I don’t want to use PSQL commands so I need some UI client to manage PostgreSQL.

Fortunately there is a free PostgreSQL client for mac called DBeaver.

The last thing to do is migrate data from SQLite to PostgreSQL db.

I am lazy to figure out how to do it programatically so I just use DBeaver to import data by copy pasting data from CSV.

Aaand it is done.

My Flask MVP now runs on PostgreSQL database.

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

This is it.

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

It took me only 21 working days to finish and MVP from zero to fully functional Flask website.

From which more than two third 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 new project and ship it in 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.

Also sorry for being mislead, that I there would be a part of development in React, but I could not justify using React for functionalities, that ended up in 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 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 title of this diary to remove React though.

So my Flask MVP diary ends here.

Another big roadblock is to buy and setup VPS with Ubuntu and deploy Flask MVP into production environment.

I might write about it, I might not.

It is a huge topic where many thing 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.

Share it!