Leveraging "INSERT INTO ... RETURNING": Practical Scenarios

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.

Case: E-Commerce Order Processing

Scenario: Generating and retrieving an order ID during order placement. This is very likely handled by ORMs, but still useful in case of scripts, absence of ORM, or even limitations with the ORM.

SQL Example:

INSERT INTO orders (customer_id, product_id, quantity)
VALUES (123, 456, 2)
RETURNING order_id;

Outcome: Instantly provides the unique order_id to the customer.

Case: Inventory Management

Scenario: Updating and returning the stock count after adding new inventory.

SQL Example:

INSERT INTO inventory (product_name, quantity_added)
VALUES ('New Product', 50)
RETURNING current_stock_count;

Outcome: Offers real-time stock updates for effective tracking.

Case: User Registration in Web Applications

Scenario: Creating a new user account and returning a confirmation message plus user ID. Here, we are returning a string, but any other kind of computed data can be returned. This is similar to the use case that I found for my demo (returning MariaDB's @@server_id).

SQL Example:

INSERT INTO users (username, password, email)
VALUES ('new_user', 'Password123!', 'user@example.com')
RETURNING user_id, 'Registration Successful';

Outcome: Confirms account creation (or returns computed data instead of having to process it later in application code) and provides the user ID for immediate use. Never store passwords in plain text like in this example!

Case: Personalized Welcome Messages in User Onboarding

Scenario: Customizing a welcome message based on the user's profile information during account creation. This is a more elaborated use case similar to the one shown in the previous section.

SQL Example:

INSERT INTO users (username, favorite_genre)
VALUES ('fantasyfan', 'Fantasy')
RETURNING CONCAT('Welcome, ', username, '! Explore the latest in ', favorite_genre, '!');

Outcome: Produces a personalized welcome message for the user, enhancing the onboarding experience. The message (or some sort of message template) could be provided from outside the SQL sentence, of course.

Case: Calculating and Displaying Order Discounts

Scenario: Automatically calculating a discount on a new order based on, for example, customer loyalty points.

SQL Example:

INSERT INTO orders (customer_id, total_amount, loyalty_points)
VALUES (123, 200, 50)
RETURNING total_amount - (loyalty_points * 0.1) AS discounted_price;

Outcome: Instantly provides the customer with the discounted price of their order, incentivizing loyalty. Obviously, let your boss know about this.

Case: Aggregating Survey Responses for Instant Summary

Scenario: Compiling survey responses and instantly providing a summary of the collective responses. It is worth mentioning at this point that even though the SQL examples show "hardcoded" values for IDs, they can be parameters for prepared statements instead.

SQL Example:

INSERT INTO survey_responses (question_id, response)
VALUES (10, 'Very Satisfied')
RETURNING (
  SELECT CONCAT(COUNT(*), ' responses, ', ROUND(AVG(rating), 2), ' average rating')
  FROM survey_responses WHERE question_id = 10
);

Outcome: Offers a real-time summary of responses, fostering immediate insights.

Case: Generating Custom Event Itineraries

Scenario: Selecting sessions for a conference event and receiving a personalized itinerary.

SQL Example:

INSERT INTO event_selections (attendee_id, session_id)
VALUES (789, 102)
RETURNING (SELECT CONCAT(session_name, ' at ', session_time) FROM event_sessions WHERE session_id = 102);

Outcome: Immediately create a custom itinerary for the attendees, improving the event experience right from the registration moment.

Conclusion

Get to know your database. In my case, the more I continue to explore MariaDB, the more I realize the many possibilities it has. The same applies to other databases. In application code, avoid implementing things at which databases excel — namely, handling data.