The INSERT INTO ... RETURNING SQL clause inserts one or more records into a table and immediately retrieves specified values from these newly inserted rows or additional data from expressions. This is particularly useful when you need to get values generated by the database upon insertion, such as auto-incremented IDs, calculated fields, or default values. Is this useful? Are there any actual use cases for this SQL clause? Don't ORM frameworks make it obsolete?
I don't have definitive answers to these questions. However, I recently found it useful when I created a demo to explain how read/write splitting works (see this article). I needed a SQL query that inserted a row and returned the "server ID" of the node that performed the write (this, to demonstrate that the primary node is always performing writes as opposed to the replicas). INSERT INTO ... RETURNING was perfect for this demo, and it got me thinking about other possible scenarios for this feature. After speaking with colleagues, it was clear that there actually are real-world use cases where INSERT INTO ... RETURNING is a good fit. These use cases include situations in which efficiency, simplicity, readability, direct access to the database, or database-specific features are needed, not to mention, when possible limitations in ORMs hit. Even though you might still feel the urge to implement this in application code, it's worth looking at how others use this SQL construct and evaluate whether it's useful in your project or not. Let's dig in.