Migration to PostgreSQL

[docapotamus]

Migrate database infrastructure from MongoDB to PostgreSQL.

There is nothing wrong with MongoDB. There are a few features which would be really handy that we get from PostgreSQL such as recursive CTEs and JOINs.

I don't want to start a religious arguments about database technologies. This will allow us to perform some more things with the database and that is why the move is being proposed.

I do not want to use MySQL/MariaDB as the CTEs are not available there.

We WILL NOT be using an ORM. I personally do not thing they adding anything that can't be done with SQL itself and in some ways make it more complex that straight SQL.

I have changed my mind on the above. We will more than likely use SQLAlchemy due to support and documentation. We can use raw SQL in the places where we need it. We just need to make sure using the ORM does not obscure the code or hide the details of how the underlying SQL is being used.

We will use psycopg2 as the driver.

I am hoping we can clean a lot of the code out during this migration and hopefully simplify the internal back end APIs.

SQL is a very stable technology and in particular PostgreSQL. This should make deploying Pjuu much easier for other users. Also SQL knowledge is much more wide spread than MongoDB knowledge so it should lower the barrier to contribution.

Another thing we have never had is a proper migration framework. We have used customer scripts in the scripts/ folder to handle transforming data between upgrade.

I will add more notes in the future but these will more than likely be handled manually with an actual method of keeping track on what revision we are at. Similar to Django/Alembic. I am not sure if we will use these or not.

This work will start when I have time and will be performed in a long running PR. I will reference this issue when it is created.

[docapotamus]

As part of this work I propose to clean up the development work flow and use the Click package (now part of Flask).

This will allow us to have a manage.py file similar to Django which we can integrate everything in to. This will remove the need for the Makefile.

Flask-Migrate can plug in to it so we can handle database migrations. We can add custom commands for running the test suite and flake.

[docapotamus]

@mcrmonkey Work still under way with the PR. Going to be removing the backend.py abstraction. I don't think this has really helped with fine tuning queries with MongoDB. Also when I get round to adding an API the queries will be similar but not the exact same so it isn't that useful

[mcrmonkey]

👍

[docapotamus]

Just going to put this on hold for now. I want to add a couple of features before going further just so I know the whole scope of the work.

[mcrmonkey]

#338 blocks this

[docapotamus]

We are not using Python 3.8 so there are few more options here like https://github.com/nackjicholson/aiosql

This would mean we can have nicer Python code without SQL littering it and we don't have to use an ORM