Did I misuse `:set`?

[Invertisment]

I'm not sure if I'm doing everything correctly so I'll instead frame this question as a "if you can please add docs" one.

I couldn't find tests where you use :set to specify and insert data that is interlinked with other data. Maybe even in two or more fields.

For instance I want to insert data into in-mem Postgres DB and I try this insertion:

           (dc/with-fixtures (pg-fixtures/mk-potato-db)
             (dc/insert-fixtures
              {:customer {2222 {:count 1}
                          1111 {:count 1}}
               :staff {8888 {:count 1}
                       9999 {:count 1}}
               :rental {:rent1 {:set {:rental/rental_id 1919
                                      :rental/staff_id 8888
                                      :rental/inventory_id 8080
                                      :rental/customer_id 2222}}
                        :rent2 {:set {:rental/staff_id 8888
                                      :rental/customer_id 1111}}
                        :rent3 {:set {:rental/staff_id 9999
                                      :rental/customer_id 2222}}}})

First I have to mention that I specified the namespaces for attributes because it turns out that I can't even use fields without the namespaces. If I use them then Postgres gives me this error (it's because rental/staff_id and staff_id are specified together in one doc):

; (err) Execution error (PSQLException) at org.postgresql.core.v3.QueryExecutorImpl/receiveErrorResponse (QueryExecutorImpl.java:2675).
; (err) ERROR: column "staff_id" specified more than once
; (err)   Position: 108

This is why I've specified the keys with the namespaces. I opened your JDBC code and printed what you insert into the DB. And it turns out that it inserts prefixed keys. Sometimes it works and it's inserted correctly. I don't know why this works this way.

I also tried to not prefix the keys in query but then it simply generates the prefixed keys for the omitted ones (I printed before inserting into DB) and takes my unomitted as additions. Then the inserter crashes because they're treated as the duplicate attributes. It sometimes inserts data with my specified IDs but in other times it inserts random IDs and I can't do anything.

Also if I freshly restart my REPL and only run the fixture once it also doesn't insert the data correctly. It's still half-there or the fields don't match my input at all. Maybe I contaminate my test database in some way? I don't know. What should I try?

For instance this code (in that same test):

               {:country (count (sql/query dc/*connection* ["SELECT * FROM country"]))
                :customer (count (sql/query dc/*connection* ["SELECT * FROM customer"]))
                :rental (sql/query dc/*connection* ["SELECT * FROM rental"])
                :address (count (sql/query dc/*connection* ["SELECT * FROM address"]))
                :store (count (sql/query dc/*connection* ["SELECT * FROM staff"]))
                :staff (count (sql/query dc/*connection* ["SELECT * FROM staff"]))}

gives me this output:

{:country 1,
 :customer 2,
 :rental
 [#:rental{:rental_id 14394517,
           :rental_date #inst "1969-12-31T23:59:59.818000000-00:00",
           :inventory_id 381,
           :customer_id 5,
           :return_date #inst "1970-01-01T00:23:41.913000000-00:00",
           :staff_id 30902,
           :last_update #inst "1969-12-31T05:52:35.102000000-00:00"}
  #:rental{:rental_id 1919,
           :rental_date #inst "1969-12-31T23:59:59.941000000-00:00",
           :inventory_id 381,
           :customer_id 5,
           :return_date #inst "1969-12-31T23:59:59.998000000-00:00",
           :staff_id 30902,
           :last_update #inst "1969-12-29T21:43:30.526000000-00:00"}
  #:rental{:rental_id 8085,
           :rental_date #inst "1969-12-31T23:59:59.999000000-00:00",
           :inventory_id 381,
           :customer_id 5,
           :return_date #inst "1969-12-31T19:47:35.241000000-00:00",
           :staff_id 30902,
           :last_update #inst "1969-12-31T23:59:36.353000000-00:00"}],
 :address 1,
 :store 2,
 :staff 2}

If I simply don't include the insert-fixtures call then the database is actually empty:

{:country 0, :customer 0, :rental [], :address 0, :store 0, :staff 0}

It says that there is one entity where rental_id is 1919 which I wanted but then it didn't save staff_id which I also specified.

Did I misconfigured something?

From everything that I tried it's either my input issue or the :set doesn't really work when I want to set IDs across multiple documents and this way link them to each other. The amount of inserted entities is still kind of on the correct side but I can't use that in tests because my IDs aren't called id.

I use this data model (you can download their tables and so on): https://www.postgresqltutorial.com/postgresql-getting-started/postgresql-sample-database/

[Invertisment]

I opened the docs and instead of using :set I needed to use :refs. I didn't know about existence of it.

This is what I wanted to do (or something similar):

              {:customer [{:ent-name :customer-1}
                          {:ent-name :customer-2}]
               :staff [{:ent-name :staff-1
                        :set {:staff/staff_id 8888}}
                       {:ent-name :staff-2
                        :set {:staff/staff_id 9999}}]
               :rental {:rent1 {:refs {:rental/staff_id :staff-1
                                       :rental/customer_id :customer-1}
                                :set {:rental/rental_id 1919}}
                        :rent2 {:refs {:rental/staff_id :staff-1
                                       :rental/customer_id :customer-2}}
                        :rent3 {:refs {:rental/staff_id :staff-2
                                       :rental/customer_id :customer-2}}}}

Can you add some kind of documentation about :set? I think my query works now and I should only use :set on the parent entity and once. But I don't really know if this is true until the docs are out. It's my guess that this is the design.