Description
Is your feature request related to a problem?
Option No
Describe the solution you'd like
We propose adding support for the PostgreSQL RETURNING clause to the database/gdb package. This feature allows INSERT, UPDATE, and DELETE statements to return values from rows affected by the operation, directly within the same query.
Benefits:
- Efficiently retrieve auto-generated primary keys (e.g.,
id). - Fetch other default or computed values (e.g.,
created_at,updated_at, or any other column). - Avoid an extra database round trip for a subsequent
SELECTstatement, improving performance and simplifying code. - Ensures atomicity when retrieving generated/modified data.
Suggested API:
A new method, possibly named Returning(), could be added to the gdb.Model chain. It should allow specifying which columns to return. The results of the RETURNING clause should then be scannable into a struct, slice of structs, or map, similar to how Scan(), ScanList(), One(), or All() work.
Example Usage:
-
INSERT ... RETURNING:
// Assuming 'users' table has 'id' (auto-increment), 'name', 'created_at' (default now()) var insertedUser struct { Id int Name string CreatedAt gtime.Time } err := g.Model("users"). Data(g.Map{"name": "John Doe"}). Returning("id", "name", "created_at"). // or Returning("*") Scan(&insertedUser) // insertedUser will now contain the id, name, and created_at of the new row
-
UPDATE ... RETURNING:
var updatedUsers []struct { // To handle updates affecting multiple rows Id int Name string UpdatedAt gtime.Time // Assuming an auto-update trigger or default } err := g.Model("users"). Where("status", "pending"). Data(g.Map{"status": "active"}). Returning("id", "name", "updated_at"). Scan(&updatedUsers) // updatedUsers will contain the updated values for all users whose status was changed
-
DELETE ... RETURNING:
var deletedUser struct { Id int Name string } err := g.Model("users"). Where("id", 1). Returning("id", "name"). Scan(&deletedUser) // or ScanList for multiple deleted rows if Where condition matches multiple // deletedUser will contain the id and name of the deleted row
This would provide a more idiomatic, powerful, and efficient way to interact with PostgreSQL when data needs to be retrieved immediately after a DML operation.
Describe alternatives you've considered
Currently, to achieve similar functionality with GoFrame and PostgreSQL, developers might:
-
Use
InsertAndGetId(): This method is useful for retrieving the last inserted ID but is limited toINSERToperations and typically only returns a single ID. It doesn't support returning multiple columns or returning data fromUPDATEorDELETEstatements. For PostgreSQL, it often relies onlastval()or similar mechanisms, whereasRETURNING idis more direct. -
Perform a separate
SELECTquery: After anINSERT,UPDATE, orDELETE, a developer would execute anotherSELECTquery to fetch the desired data. This involves an additional network round trip, is less efficient, and can introduce race conditions if not managed carefully (e.g., within a transaction, selecting by a unique key other than the primary key if the PK was just generated). -
Use Raw SQL (
gdb.Raw): While GoFrame allows raw SQL execution withRETURNING, this bypasses the ORM's chainable API, type safety benefits for query building, and can be more cumbersome for dynamic query construction. It also makes the code more database-specific in a less abstracted way.
The RETURNING clause offers a more direct, efficient, and SQL-native solution for PostgreSQL users, which would be a valuable addition to GoFrame's ORM capabilities.
Additional
- Supporting
RETURNINGwould significantly enhance GoFrame's capabilities for PostgreSQL users, making it more competitive with other ORMs that offer this feature. - It aligns with GoFrame's goal of providing a powerful yet easy-to-use database abstraction layer.
- This feature is specific to PostgreSQL (though other databases like SQLite >3.35.0 and SQL Server via
OUTPUThave similar concepts). The implementation should be specific to the PostgreSQL driver and ideally be designed in a way that it doesn't break or is gracefully ignored for database drivers that do not support this syntax. - Consideration should be given to how
RETURNING *would be handled, especially concerning scanning into structs with potentially more or fewer fields than all columns in the table. - This could potentially unify or extend the
InsertAndGetIdfunctionality for PostgreSQL.