0

Currently, the ORM for V only supports select, update, and insert. I would like to do a more complicated upsert like this:

INSERT INTO Settings (Key, Value)
VALUES (?0, ?1)
ON CONFLICT(Key)
DO UPDATE
SET Key = ?0,
    Value = ?1;`

But it looks like from the docs there is no exposure for doing an update like this. So, I would need to clean the inserted/updated values myself.

Is there a way to do direct SQL with variables directly? Am I missing something?

Another option I would have would be:

setting := Settings {
    key: key
    value: value
}
sql r.db {
    insert setting into Settings
}
sql r.db {
    update Settings set value = value where key == key
}

Although the above works it isn't very elegant and requires two calls to the DB. Is there a way to do direct queries with arguments?

Jon49
  • 4,444
  • 4
  • 36
  • 73
  • 1
    I'd suggest throwing away the ORM and just writing your own SQL so you can get full control of the database and its features. – Shawn Feb 21 '21 at 08:53
  • Yeah, I'm not really a C programmer, so it is much more difficult for me to go down that path. But I think you are probably right. I'll just have to learn it. But V I can copy some of the V code to learn I guess. Thanks! – Jon49 Feb 21 '21 at 19:26

1 Answers1

0

The vlang Sqlite library (not the Orm) has a method that can execute raw SQL against the db and return raw row data.

You could create your upsert query using the information in this stackoverflow and execute it as a string against the DB.

Something like this maybe (you'll have to test it against your particular database until it works):

r.db.exec("UPDATE Settings SET value = 'value' WHERE id = 2;
INSERT INTO Settings(key, value) SELECT 2, 'value' WHERE changes() = 0;")

Do not use this in production. Unfortunately, the exec function does not santize inputs or pass in parameterized values. This means that you will be vulnerable to sql injection by default.

José Mancharo
  • 175
  • 1
  • 14
  • `r.db.exec` was confusing to me. I think DB API has changed a bit in V 0.3.3. I did: `import db.sqlite db := sqlite.connect('db.sqlite') db.exec('SQL statement')` – jgran Apr 17 '23 at 08:29