Why isn't there a sql_update processor?

[FatmanUK]

I'm using Benthos in streams mode to decouple my database back-end from my basic webapp front-end. I can simulate a sql_update using a sql_raw DELETE and a sql_insert, but a sql_update instead would be handy. I can't figure out how to do the update using sql_raw.

Benthos is pretty cool. Well done devs! :)

[mihaitodor]

Hey @FatmanUK 👋 I think nobody asked for such a processor yet, so it wasn't added. Not sure why you're having a hard time using sql_raw to execute UPDATE queries, though. For example, this works for me:

input:
  generate:
    mapping: |
      root.id = count("generate")
      root.foo = uuid_v4()
    count: 3
    interval: 0s

  processors:
    - log:
        message: ${! content() }

    - sql_raw:
        driver: sqlite
        dsn: file:./foobar.db
        query: |
          UPDATE TEST SET FOO = ? WHERE ID = ?
        args_mapping: |
          root = [
            this.foo,
            this.id
          ]
        conn_max_idle: 1
        conn_max_open: 1
        init_statement: |
          CREATE TABLE IF NOT EXISTS TEST (ID int, FOO varchar);
          INSERT INTO TEST(ID, FOO) VALUES(1, 'foo'), (2, 'bar'),  (3, 'baz');

output:
  drop: {}

Note that I'm setting conn_max_idle: 1 and conn_max_open: 1 because the sqlite driver has some issues when using init_statement.

After running that config, if I open the DB file using sqlite3 foobar.db and I execute SELECT * FROM TEST;, I get something like this:

1|1baa2a32-51b5-4998-9128-cdb2b1b33651
2|f9e3c40f-1840-481b-8cdf-a01e1d71e97e
3|c3c4e6cd-9043-4460-82d6-c5a948790674

instead of this:

1|foo
2|bar
3|baz