ActiveRecord PostgreSQL CTE, UPSERT extensions and extension gem proposal

simi · February 7, 2021, 1:05pm

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 (https://github.com/rails/rails/pull/37944, 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.

github.com/rails/rails

Allow to specify custom attributes to update on conflict for upsert_all method

rails:master ← simi:upsert_all_updatable_columns

opened 09:40PM - 15 Mar 19 UTC

simi

+25 -8

This allows to specify attributes to update on conflict. It opens door to handli…ng timestamps updates manually in reasonable transparent way. ```ruby now = Time.now row = { slug: 'new-title', post: 'New post', created_at: now, updated_at: now } Post.upsert(row1, unique_by: { columns: [:slug]}, update: [:post, :updated_at]) ``` In combination with defaults (I'll try to add this in different PR) mentioned at https://github.com/rails/rails/issues/35493#issuecomment-471526290 it can provide easy and transparent way to handle timestamps. Anyway I think it should not be default behaviour since methods like `update_all` doesn't handle this on its own as well. WDYT @boblail @dhh @kaspth :question: references https://github.com/rails/rails/issues/35493 https://github.com/rails/rails/pull/35494 I'll update documentation once approved.

github.com/rails/rails

Enhance AR#insert_all/upsert_all options

rails:master ← palkan:feat/ar-insert-all-enhance-options

opened 11:29PM - 15 Mar 19 UTC

palkan

+73 -15

### Summary Follow-up for https://github.com/rails/rails/pull/35077#issuecomm…ent-471695480 - Added ability to use custom SQL in `returning:` option: ```ruby Article.insert_all( [ {title: "Article 1", slug: "article-1", published: false}, {title: "Article 2", slug: "article-2", published: false} ], # Some PostgreSQL magic here to detect which rows have been actually inserted returning: "id, (xmax = '0') as inserted, name as new_name" ) ``` - Added new `update_sql:` option to specify SQL fragment to use when updating rows on conflict: ```ruby Book.upsert_all( [{ id: 1, status: 1 }, { id: 2, status: 1 }], update_sql: "status = GREATEST(books.status, EXCLUDED.status)" ) ``` [Another example](https://github.com/rails/rails/pull/35077#issuecomment-472913686) by @boblail: ```ruby ExceptionReport.upsert(new_report, update_sql: "count = count + 1") ``` /cc @boblail @kaspth

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:

RETURNING clause (for INSERT/UPDATE)
advanced ENUM (GitHub - alassek/activerecord-pg_enum: Integrate PostgreSQL's enumerated types with the Rails enum feature)
CURSOR (DECLARE, FETCH, …) (GitHub - afair/postgresql_cursor: ActiveRecord PostgreSQL Adapter extension for using a cursor to return a large result set)

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

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.

benedikt · February 20, 2021, 11:25am

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).

strivedi183 · March 2, 2021, 1:24pm

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.