Is there a way to "Get or Insert" in a single query_row call?

[Byron]

Right now I found myself writing something like this each time:

        fn get_or_insert_gitoxide_version_id(&self) -> anyhow::Result<Id> {
            Ok(
                match self
                    .con
                    .query_row(
                        "SELECT rowid FROM gitoxide_version WHERE version = ?1",
                        [&self.gitoxide_version],
                        |r| r.get(0),
                    )
                    .optional()?
                {
                    Some(existing) => existing,
                    None => {
                        self.con.execute(
                            "INSERT INTO gitoxide_version (version) VALUES (?1)",
                            [&self.gitoxide_version],
                        )?;
                        self.con.query_row(
                            "SELECT rowid FROM gitoxide_version WHERE version = ?1",
                            [&self.gitoxide_version],
                            |r| r.get(0),
                        )?
                    }
                },
            )
        }

…which appears more repetitive than I'd like.

What I tried to do was is to have multiple statements, the first inserts a value, the next obtains the newly inserted row id.

                        self.con.query_row(
                            "INSERT INTO gitoxide_version (version) VALUES (?1); SELECT rowid FROM gitoxide_version WHERE version = ?1",
                            [&self.gitoxide_version],
                            |r| r.get(0),
                        )?

However, this claims nothing was returned so I have a feeling that it only executes one statement (and looking at the code, I believe that's also the case).

I couldn't find a way to make this work, but hope I am missing something.

Thanks a lot for your help 🙏.

Context

I would like to use rowids as foreign keys in another table, so I need those based on some parameters, without being able to use these parameters directly.

[gwenn]

See https://sqlite.org/lang_upsert.html (if there is an unique constraint on version).
And https://sqlite.org/lang_returning.html

sqlite> CREATE TABLE gitoxide_version(version INTEGER NOT NULL UNIQUE);
sqlite> INSERT INTO gitoxide_version (version) VALUES (1) ON CONFLICT DO NOTHING RETURNING rowid;
1
sqlite> INSERT INTO gitoxide_version (version) VALUES (1) ON CONFLICT DO NOTHING RETURNING rowid;
sqlite> -- we should do a fake update instead:
sqlite> INSERT INTO gitoxide_version (version) VALUES (1) ON CONFLICT DO UPDATE SET version = version RETURNING rowid;
1
sqlite> INSERT INTO gitoxide_version (version) VALUES (3) ON CONFLICT DO UPDATE SET version = version RETURNING rowid;
2
sqlite> INSERT INTO gitoxide_version (version) VALUES (3) ON CONFLICT DO UPDATE SET version = version RETURNING rowid;
2

Maybe you should ask on SQLite forum because this is not strictly related to rusqlite.

[Byron]

Thanks so much for your help!

Now the function looks like this and is even better than I thought it could be :).

  pub(crate) fn gitoxide_version_id_or_insert(&self) -> anyhow::Result<Id> {
      Ok(self
              .con
              .query_row(
                  "INSERT INTO gitoxide_version (version) VALUES (?1) ON CONFLICT DO UPDATE SET version = ?1 RETURNING id",
                  [&self.gitoxide_version],
                  |r| r.get(0),
              )?)
  }

[gwenn]

I suggest that you try searching or posting (anonymous post is OK) on the SQLite forum for a better solution (its users are SQL gods).
And that you keep using version = version (instead of version = ?1) because (at least for me) it is clear that version doesn't change...

[Byron]

Thanks again: right, version = version should also be better as it probably can optimize such a query more easily. Maybe I should ask 'the gods' if there is an even better way - I will be sure to post their answer here if I have one :).