How to insert multiple values?

[baransu]

I have following table:

let create_table = [%sqlf
  {|
    CREATE TABLE users (
      id INTEGER NOT NULL PRIMARY KEY,
      email VARCHAR NOT NULL,
      name VARCHAR NOT NULL
    )
  |}
];

And I want to insert all columns at once, possible adding multiple rows.

This query adds two rows with all columns:

let insert = [%sqlf {|
  INSERT INTO users
  VALUES (1, 'John', 'email1'), (2, 'Mary', 'email2')
  RETURNING id
|}];

but replacing (1, 'John', 'email1') with $user or $@user fails during compilation:
Error: [%sqlf]: ERROR: 42601: syntax error at or near "$1"
Would be amazing to have support for something like this:

let insert = [%sqlf {|
  INSERT INTO users
  VALUES $user
  RETURNING id
|}];

let insert = (~user: (int32, string, string)) => ...

it would be just a shortcut for

let insert = [%sqlf {|
  INSERT INTO users
  VALUES ($id, $email, $name)
  RETURNING id
|}];

[tizoc]

Hello @baransu. What the extension does is just replace variables with placeholders, build a prepared statement with the resulting query, and wrap it in a function with named arguments.

So this:

INSERT INTO users
  VALUES ($id, $email, $name)
  RETURNING id

becomes this

INSERT INTO users
  VALUES ($1, $2, $3)
  RETURNING id

and the list of params that is sent to PG'OCaml is built like this:

    let params : string option list list =
      [[Some (PGOCaml.string_of_int32 id)];
       [Some (PGOCaml.string_of_string email)];
       [Some (PGOCaml.string_of_string name)]] in

I don't have time to work on this now (it is quite a bit more complicated to implement than what it may seem at first), and it is not a feature I need, but if you are interested in working in this change I can assist you on figuring things out.

A different symbol would have to prefix tuple-variables btw, because if$ is used then the SQL queries would have to be parsed to be able to figure out how to expand a variable based on the context, something that this extension avoids doing because it would complicate things a lot.