What is the best way to COPY/INSERT ON CASCADE in PostgreSQL?

user_3242066 0
Viewed 204

I'm wondering what is the best way to do an "on cascade copy/insert" of related elements in PostgreSQL.

I made a simple example to illustrate my use case:

Database Definition

Three entities: Version, Element, ElementEffect.

  • A version has many elements.
  • An element belongs to one version.
  • An Element has many element effects.
  • An Effect belongs to one Element.

enter image description here

The Problem

Let say that we have 1 version, with 1 element with 1 effect in the database.

enter image description here

For my use case, I need to be able to create a new version copying all elements and element effects of the previous version and update some of them.

For example, if a new version is created: Version 2:

  • The database should copy the existence element into a new one referencing the new version.
  • The new element should create a new element effect referencing the new element.

enter image description here

A new version arrived. The new version has the same elements and effects that the previous version and one change: the element effect text changed from null to "loremp ipsum".

The operations that we need to do are:

  1. Create a copy for all elements and their relationships relate to version —> Element > ElementEffect

  2. Make data updates to new copy elements with the changes that the new version has.

Question

  1. What is the best way to achieve requisite 1 in PostgreSQL with/without Sequelize ORM and Node.js?
  2. Does PostgreSQL have any built-in feature to make this possible?
  3. Should I solve this at the database level (maybe with psql rules and triggers) or a code level with a node script making queries and transactions?

Solution Requisites

  • I'm building this on PostgreSQL using Sequelize as ORM managed by Node.js so if I can build this using Sequelize will be even better.

  • My use case is way more complex than the example. I have more than 15 entities including Many to Many relationships so the solution needs to scale over time. Meaning, I do not want to test this every time I add a new entity or modify an existing one.

sequelize.js node.js postgresql javascript database
1 Answers

The best idea coming in my mind is to create the stored procedure in psql like make_new_version(my_table, oldid, newid) and this procedure must copy rows with old id in table my_table and replace oldid with newid. this function also needs to return id from newly inserted rows to make another call of same function to copy rows for the next entity.

When you test and confirm this stored procedure, you can call it from another procedure (almost) without testing.

This way you will solve problem at the database layer.

user_33899860
Related