CODEWITHSUNDEEP
pythonsqlalchemyamazon-redshiftmaterialized-views
Ugur Yilmaz
Ugur Yilmaz

Reputation: 499

Is it a good idea to use SQLAlchemy with Redshift Materialized Views?

I am working on a new project with Flask that will heavily read from a redshift database and particularly from materialized views. I'm fairly new to Flask/SQLAchemy/ORMs. I want to abstract the database layer with ORM by using Flask-SQLAlchemy. When i was reading the documents, i noticed SQL Alchemy requires underlying database source to have a primary key. However, i am worried that having materialized view without any primary key will cause a problem.

I found out that there are some workarounds to specify some columns as primary key even when they are not but i'm not sure if that will cause an issue when i perform a join on materialized views. I am sure there might be a workaround for this one as well but i'm thinking if using ORM with workarounds is actually a good idea when most of my operations will be heavy read operations from materialized views. So i have two questions

1)

Is it possible to use SQLAlchemy with Redshift Materialized Views (I wasn't able to find enough resources on this one)

2)

If possible, is it a good idea to use SQLAlchemy or should I stick to raw sql queries with my own logic of postgresql pooling?

Thank you.

P.S: I have no primary keys in redshift but i have dist/sort keys.

References/Links I used:

How to define a table without primary key with SQLAlchemy?

sqlalchemy materialized relationships

Upvotes: 0

Views: 625

Answers (1)

Mikko Ohtamaa
Mikko Ohtamaa

Reputation: 83788

i noticed SQL Alchemy requires underlying database source to have a primary key.

This is not true. You can use synthetic primary keys. I am using them with TimescaleDB hypertables that do not have single-column primary keys.

Is it possible to use SQLAlchemy with Redshift Materialized Views (I wasn't able to find enough resources on this one)

SQLAlchemy does not care about the underlying database, as long SQL wire protocol and its flavour is compatible (PostgreSQL, MySQL, etc.)

If possible, is it a good idea to use SQLAlchemy or should I stick to raw sql queries with my own logic of postgresql pooling?

Using SQLAlchemy improves the readability your code and then reduces maintenance costs in long term.

Upvotes: 1

Related Questions