Proposal: Orthogonal returning(ids) + strong-typed conflict policy tags for sqlgen::insert

[Perdixky]

Motivation

I’d like to add an API to collect auto-increment ids (rowid / integer primary key) after insertion, especially for bulk inserts.

At the same time, I want to keep insert-related behaviors orthogonal and composable. Instead of having dedicated functions like insert_or_replace, I propose strong-typed modifiers that can be composed (pipe style) or passed directly (direct style).

A key point: I prefer conflict policies to be strong types (tag types) rather than bool flags or enum class, so we can enforce valid combinations at compile time and keep room for future static checks (e.g., conflict target must be unique).

---

Goals

• Add sqlgen::returning(ids) to collect inserted ids, composable with insert builder.
• Redesign conflict handling with orthogonal modifiers (e.g. or_replace()), deprecating insert_or_replace (or making it a thin wrapper).
• Avoid insert(..., true) style flags; use strong-typed tags to improve readability and prevent misuse.
• Optional: support a stronger form that can be statically checked when schema/constraints are represented in types.

---

Proposed User-facing API

1) Returning inserted ids

Pipe style:

std::vector<std::uint64_t> ids;

sqlgen::sqlite::connect("database.db")
  .and_then(sqlgen::insert(people) | sqlgen::returning(ids))
  .value();

Direct style (equivalent):

std::vector<std::uint64_t> ids;

sqlgen::sqlite::connect("database.db")
  .and_then(sqlgen::insert(people, sqlgen::returning(ids)))
  .value();

2) Conflict policy as orthogonal modifier (strong typed)

Pipe style:

sqlgen::sqlite::connect("database.db")
  .and_then(sqlgen::insert(people) | sqlgen::or_replace())
  .value();

Direct style (equivalent):

sqlgen::sqlite::connect("database.db")
  .and_then(sqlgen::insert(people, sqlgen::or_replace()))
  .value();

3) Composition works naturally

std::vector<std::uint64_t> ids;

sqlgen::sqlite::connect("database.db")
  .and_then(sqlgen::insert(people) | sqlgen::or_replace() | sqlgen::returning(ids))
  .value();

---

Proposed Types / Signatures (sketch)

returning(ids)

namespace sqlgen {

template <class IdsContainer>
auto returning(IdsContainer& ids); // stores ref internally

} // namespace sqlgen

strong-typed conflict policy tags

namespace sqlgen::conflict_policy {
  struct error {};
  struct replace {};
  struct ignore {};
  template <typename T>
    concept ConflictPolicy = std::is_same_v<error, T>      ||
                                                std::is_same_v<replace, T> ||
                                                std::is_same_v<ignore, T> 
}

conflict modifier / sugar

namespace sqlgen {

inline auto or_replace() { return conflict_policy::replace{}; }
inline auto or_ignore()  { return conflict_policy::ignore{}; }

} // namespace sqlgen

insert overload accepting modifiers

namespace sqlgen {

template <class Rows, class... Modifiers>
auto insert(Rows&& rows, Modifiers&&... mods);

} // namespace sqlgen

Constraints/concepts can ensure only valid modifiers are accepted, and modifiers are only composable with insert statements.

---

Semantics notes

Returning ids (bulk insert)

Suggested semantics: 1:1 positional mapping

• ids.size() == number_of_input_rows
• ids[i] corresponds to the i-th row of the input sequence

Constraint Note: returning() is incompatible with or_ignore(). In SQLite, INSERT OR IGNORE silently skips rows on conflict, which would result in a mismatch between the number of input rows and the number of returned IDs. To maintain positional mapping integrity, this combination is disallowed at compile time.

SQLite OR REPLACE

SQLite INSERT OR REPLACE is effectively “delete conflicting row, then insert a new row”.
So ids returned by returning(ids) refer to the new inserted row, and may differ from old ids. This should be documented.

---

Compatibility / Migration

• Deprecate insert_or_replace(...) in docs/examples.

• Optionally keep it as a thin wrapper:

  • insert_or_replace(x) ⇒ insert(x) | or_replace()

---

What do you think of this proposal? I’d appreciate any feedback or suggestions. I'm excited to contribute this to the project! 😁

[liuzicheng1987]

Hi @Perdixky , thanks for your interest in contributing!

Here are my thoughts.

1. Sounds great. How would you make this work? Is something like this supported by all databases?

2. Also sounds great. My only thing is that the brackets behind or_replace are unnecessary and therefore should be left out:

sqlgen::sqlite::connect("database.db")
  .and_then(sqlgen::insert(people) | sqlgen::or_replace)
  .value();

Also, do we know that an IGNORE policy is supported by all relevant databases?

Finally, I wouldn't deprecate insert_or_replace. It would just be a few lines of code and therefore I don't see any issues in continuing to maintain it:

template<class ContainerType>
auto insert_or_replace(const auto&... args) {
    return insert(args...) | or_replace
}

3. Sure, that would be implied by 1) and 2)

[Perdixky]

Sounds great. How would you make this work? Is something like this supported by all databases?

I'm going to support this feature using different solutions for different databases:

• SQLite: generate INSERT ... RETURNING <id_expr> and read the result rows to fill ids. SQLite supports RETURNING on INSERT/UPDATE/DELETE.
• PostgreSQL: generate ... RETURNING ... (native).
• DuckDB: also RETURNING keyword.
• MySQL: things get complicated. It doesn't support RETURNING keyword, and the C API only returns the the first AUTO_INCREMENT ID. So I would like to support a limited form (only single-row inserts). And multi-row insertions with returning tag would be disabled by compile-time checking.

Also sounds great. My only thing is that the brackets behind or_replace are unnecessary and therefore should be left out:

Good idea, I can make it an inline constexpr tag like this:

namespace sqlgen::conflict_policy { 
    struct replace {};

    inline constexpr replace or_replace{};
}

IGNORE policy supported by all relevant databases?

Not universally with identical syntax, but many backends have an equivalent:

• SQLite: OR IGNORE
• MySQL: INSERT IGNORE
• PostgreSQL: ON CONFLICT DO NOTHING (different spelling, same intent)
• DuckDB: support both INSERT ... ON CONFLICT DO NOTHING and INSERT OR IGNORE

[liuzicheng1987]

@Perdixky , it looks like you know what you're doing. I am looking forward to your PR!

[liuzicheng1987]

@Perdixky , if you get stuck or need any help, just let me know.

[Perdixky]

OK! This isn’t an easy task for me, so I wanted to get your feedback to make sure the design is on the right track. I’ll do my best!