Save associations along with main record

[snimavat]

There are multiple scenarios where user may want to save one or many related instances along with the main record.

For example: there are 3 tables, product, product variants and tags

1. When creating a product row, I may want to select existing tags, or i may want to add new tags
2. I would like to insert product variants along with product
3. All in same api calls, and not individual calls for first inserting product, thn variants and thn tags etc

If you look at directus, it supports all of these through UI,
so while creating product, i can select exiting tags, add few new tags and add new variants and all of it will be saved.

Would be great if db2rest can provide similar capability

[kdhrubo]

This is easy to implement. Let's assume these are the three tables

-- Create the Product table
CREATE TABLE Product (
    ProductID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(255) NOT NULL,
    Description TEXT,
    Price DECIMAL(10, 2) NOT NULL,
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UpdatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Create the ProductVariant table
CREATE TABLE ProductVariant (
    VariantID INT PRIMARY KEY AUTO_INCREMENT,
    ProductID INT NOT NULL,
    VariantName VARCHAR(255) NOT NULL,
    SKU VARCHAR(100) UNIQUE NOT NULL,
    Price DECIMAL(10, 2) NOT NULL,
    StockQuantity INT DEFAULT 0,
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UpdatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (ProductID) REFERENCES Product(ProductID) ON DELETE CASCADE
);

-- Create the Tags table
CREATE TABLE Tags (
    TagID INT PRIMARY KEY AUTO_INCREMENT,
    ProductID INT NOT NULL,
    TagName VARCHAR(100) NOT NULL,
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (ProductID) REFERENCES Product(ProductID) ON DELETE CASCADE
);

End point:

/chain

Request payload structure :

[

  {
    "table": "Product",
    "op": "Create",
    "data" : [{
      
    }]
    
  },

  {
    
    "table": "ProductVariant",
    "op": "Create",
    "data" : [{

      "ProductID" : "{{productId}}"
      
    }]
    
  },

  {
    
    "table": "Tags",
    "op": "Create",
    "data" : [{

      "ProductID" : "{{productId}}"
      
    }]
    
  }
  
]

For now only support insert/create. Later same model can be extended further to include UPDATE, DELETE.

Let us know your thoughts @snimavat