-1

I've got a pretty straightforward question. I intend to make a flask application that may end up making some complex SQL queries. For this reason, I have decided not to use an ORM and plus I prefer writing my own SQLs.

I wrote some simple SQLs for reading/writing data in a postgres function and then used psycopg2 to make function calls. I am thinking this approach would be better than writing raw SQLs as it would be easy to maintain.

Does anyone know of any pitfalls in taking this approach, or any limitations specific to psycopg2? Thank you.

mania_device
  • 235
  • 3
  • 8

1 Answers1

2

An ORM can help you a lot, even if your application is Postgres only. I had to support really complicated deploys in different OS and I know ORMs can save you a lot of work and pain when requirements change. Definitely has some (small!) performance hit, but you can avoid most of the slow path when it is really important (bulk inserts/updates, plain SQL, etc.).

I'll take SQLAlchemy as an example, but most ORMs will have equivalent features.

  1. You have to write some data mapping to objects (or even a dict) in plain Python or with an ORM, the ORM have it for free if you want to use this feature.
  2. Define tables and models in your code. You don't even have to use the SQLAlchemy objects.
  3. Write SQL statements with Python code most of the time. It is compiled to the database of your choice. It does not work for a really complex query, but you always can fallback to plain SQL.
  4. Built-in migrations with alembic. All your database schema history with your code and most schema changes are auto generated.
  5. It is database and database driver agnostic. Internally, SQLAlchemy uses psycopg2 or other Postgres driver. No ORM I remember has its own driver. Once upon a time, I had unicode issues with psycopg2 (probably I was doing something wrong) and just changed SQLAlchemy to use another driver and worked fine. Other time, I wanted to run my application with PyPy and psycopg2 was not supported.
iurisilvio
  • 4,868
  • 1
  • 30
  • 36
  • Thank you for your answer. SQLAlchemy does look very compelling and the fact that you can use plain SQL seems nice. However, couple of points I want to make is that, it's very unlikely that I may change my deployment in the future, and more importantly I may have to write queries with complex JOINs. If someone can convince me that `psycopg2` is an absolute no no, then I am definitely going to look at SQLAlchemy. – mania_device May 01 '16 at 09:34
  • SQLAlchemy do a lot of things with JOINs. Some times, it is easier to just write SQL instead of understand how SQLAlchemy generate the SQL. I just tried to give you some opinionated reasons, but `psycopg2` is great, even SQLAlchemy use it. :) Go ahead with `psycopg2`. – iurisilvio May 01 '16 at 15:01
  • 1
    Your answer has given me some perspective. I am giving SQLAlchemy a thought, now. Also, found some valuable information [here](http://stackoverflow.com/questions/8588126/sqlalchemy-or-psycopg2) – mania_device May 03 '16 at 18:43