ActiveRecord PostgreSQL CTE, UPSERT extensions and extension gem proposal

Hello, another great release (6.1) of ActiveRecord was cut recently and some external dependencies I use to extend ActiveRecord functionality of PostgreSQL stuff are looking for a little maintenance love again. I would like to discuss and propose some ideas I have regarding this.

CTE support

There were already some tries of integrating CTEs in ActiveRecord before (, Add support for basic CTEs by introducing Relation#with. by simi · Pull Request #37973 · rails/rails · GitHub). Current gem solution I do use (Not support rails 6.1.0 · Issue #9 · kmurph73/ctes_in_my_pg · GitHub) will need to get patched. I would prefer to spend my time on integration CTEs in to ActiveRecord directly again, but I would like to get some response from core people if that is welcomed contribution and if anyone would like to be “reviewer” for this addition.

Looking back at the PRs, it seems last time we failed to explain what’s the benefit of CTE and how are CTEs useful to be integrated in ActiveRecord directly. As I have found out, CTEs are not very known pattern across web-dev people, but it is well known and widely used tool in SQL world.

UPSERT extensions

ActiveRecord 6 was released with support for basic “bulk insert/upsert” feature. IMHO it was very well adopted and my initial blog post elaborating on this (Upcoming Rails 6 Bulk insert/Upsert feature | by Josef Šimánek | Medium) is popular until today (250+ reads / week).

Me and other contributors tried to extend this feature to be able to replace widely used gem (GitHub - zdennis/activerecord-import: A library for bulk insertion of data into your database using ActiveRecord.), but we failed again. Probably again due to lack of proper communication how beneficial it would be to get this extension in ActiveRecord directly.

Thanks to those missing features, I’m still not able to replace activerecord-import with baked in functionality. Again I’m looking for someone from core interested in this and being able to guide me a little on reviews to make this happen.

Official ActiveRecord PostgreSQL extension

Finally, I would like to share an idea of official ActiveRecord PostgreSQL extension (gem, but could be placed in mono repo as well). PostgreSQL is trending RDBMS (PostgreSQL is the DBMS of the Year 2020) in last years. Unfortunately it is not probably widely used by big companies behind Rails (like Basecamp, GitHub, Shopify) and it is sometimes hard to explain how useful some contributions are for PostgreSQL users. At the same time I’m aware of ActiveRecord being general library, not PostgreSQL oriented.

What about to bring official gem extension maintained by Rails contributors using PostgreSQL? It should be for example possible to add support for all I mentioned before (even CTE is useful in MySQL/MariaDB world as well). Next to this, it would be possible to following without baking in PostgreSQL only oriented code into ActiveRecord in general:

There are currently some PostgreSQL only features in ActiveRecord and there is special guides page Active Record and PostgreSQL — Ruby on Rails Guides. Those features could be extracted to this extending gem. It can also make it possible to introduce MySQL/MariaDB extension if needed at some point.

Thinking about this, I have more and more ideas. For example including this could enable more optimised (but not portable) queries in some cases… There’s a lot of possibilities :thinking:

If anyone would like to continue on this thinking with me, feel free to ping me. We can set some kind of meeting in chat somewhere to take a look and think together at possibilities.

PS: Thanks to everyone contributing to Rails. It is still the best web solution in 2021. :heart:


Great suggestions, @simi! I’d love to see better support for CTEs in ActiveRecord, or at least in Arel, too.

We’ve been monkey-patching several parts of Arel to be able to generate queries using CTEs and (or even UNIONs). I think just moving some methods around in Arel would already improve things a lot (so it would be possible to use CTEs not just on SELECT queries).

Really great idea @simi

Another gem I have seen for additional Postgres features with ActiveRecord is GitHub - crashtech/torque-postgresql: Add support to complex resources of PostgreSQL, like data types, array associations, and auxiliary statements (CTE).

I think an official gem extension would be really useful.