Bulk insert story

[mikeyhew]

I'm trying out squirrel on a new project and I was wondering, is there a good way to do bulk insert/upsert? I have a table that looks like this and am trying to figure out the best way to do this:

create table products (
  id uuid primary key,
  legacy_id bigint,  -- nullable
  name text not null
);

Let's say I have 100s of products I want to insert, and want to do it all in one query. Is there a recommended way to do this right now with squirrel?

One way that I tried is this:

insert into products (id, legacy_id, name)
select id, legacy_id, name
from unnest($1::uuid[], $2::bigint[], $3::text[]) as t(id, legacy_id, name)

This almost works. It generates a function with this signature:

pub fn insert_products(
  db: pog.Connection,
  arg_1: List(Uuid),
  arg_2: List(Int),
  arg_3: List(String),
) -> Result(pog.Returned(Nil), pog.QueryError)

Which, if you had a list of products, you could turn it into a list of each field value for each field in product.

However, for this to work I'd need the generated type of arg_2 to be List(Option(Int)). Postgres arrays are allowed to have nulls in them, but squirrel maps array[T] to List(T). If there was some way to hint to squirrel that array elements can be null so it map to List(Option(T)), that would make this work. Alternatively, squirrel could map all array types to List(Option(T)) which would be more correct, but also can be annoying sometimes.

Does anyone have any ideas on how to make this work in the current version of squirrel? Otherwise for now I'll have to just use pog directly

[giacomocavalieri]

Hello! Yeah unfortunately Postgres doesn't expose enough information for squirrel to understand if those are nullable or not. There's a workaround though! You could pick a value to represent "null" and switch on that in your query:

 insert into products (id, legacy_id, name)
 select
   id,
+  nullif(legacy_id, -1),
   name
 from unnest($1::uuid[], $2::bigint[], $3::text[]) as t(id, legacy_id, name)

In this case I'm assuming "-1" is not a valid legacy id, so it's ok to use that as representing "null". It's a workaround but works quite alright if you can pick one such value. Hope this helps!

[mikeyhew]

Hey thanks for the suggestion of using a sentinel value. Do you see that as the long-term solution or do you have any ideas of how squirrel could better support bulk insert or nullable array values in the future?

[giacomocavalieri]

Happy to help! I think that is going to be the solution for the foreseeable future: sadly that's a limitation inherited from Postgres and the only "real" solution would be to land the needed changes into Postgres. I once asked about it in the mailing list and the answer was something along the lines of "it could be a nice to have, but it's hard and no one has shown interest in this" 😁

An alternative would be to have some system in Squirrel to add type hints so it can infer a different type, maybe through comments. Though I will hold back on that for as long as possible, the main idea with Squirrel is that it's plain SQL that you can reuse everywhere, with no special extensions built on top