"ERROR: null value in column" when trying to insert into the database

[AndreaCrotti]

So I'm just trying to insert data into the database using postgres and next.jdbc, but when it actually tries to write the data the values in the foreign key fields are always NULLS.

I thought it was related to my model but I get the same behaviour with the sample code from the README, it works fine for the mock db but not for a real Postgres db. If I look at the output from generate I see that it's all correct, but not when I try to insert data, any idea why it could be wrong?

(def id-atom (atom 0))
(def monotonic-id-gen
  (gen/fmap (fn [_] (swap! id-atom inc)) (gen/return nil)))

(def ID
  [:and {:gen/gen monotonic-id-gen} pos-int?])

(def User
  [:map
   [:user/id ID]
   [:user/username string?]])

(def Post
  [:map
   [:post/id ID]
   [:post/created_by_id pos-int?]
   [:post/content string?]])

(def potato-schema
  {:user {:prefix   :u
          :generate {:schema User}
          :fixtures {:table-name "metagross.users"}}

   :post {:prefix    :p
          :generate  {:schema Post}
          :fixtures  {:table-name "metagross.posts"}
          :relations {:post/created_by_id [:user :user/id]}}})

(defn unwrap-connection
  [& _args]
  (.unwrap (postgres/connection) Connection))

(def potato-pg
  {:schema   potato-schema
   :generate {:generator mg/generate}
   :fixtures (merge dnj/config
                    {:get-connection unwrap-connection
                     :close-connection (fn close-connection [& _args]
                                         (.close (unwrap-connection)))})})

The code is pretty much just this, I had to redefine get-connection, but I don't think that's the problem, it connects just fine it just seems to lose the data when trying to write out.

[AndreaCrotti]

As you can see from this image, I just tapped visit-data in the insert-fixtures* function, and created_by_id is not nil in :generate, but it's nil in visit-val, any idea why?

[AndreaCrotti]

I wonder if it could be related with https://github.com/donut-party/datapotato/wiki/database-integration#optional-define-a-get-inserted-method even though in the docs it says that if I use postgres I should not need to worry about, I don't that defmethod defined for Postgres.

[raszi]

I also ran into this, because it seems that the documentation is a bit inaccurate here.

What was working for me if I set the relations as namespace-qualified keywords.

(def potato-schema
  {:company {:prefix :c
             :fixtures {:table-name "companies"}
             :generate {:schema ::spec.company/complete}}

   :user {:prefix :u
          :fixtures {:table-name "users"}
          :generate {:schema ::spec.user/complete}
          :relations {:company_id [:company :companies/id]}}})

(defn potato-db [db]
  {:schema potato-schema
   :generate {:generator generator}
   :fixtures (assoc dnj/config :dbspec db)})

(defn insert [db]
  (dc/with-fixtures (potato-db db)
    (dc/insert-fixtures {:user [{:count 3}]})))

I believe the reason is that next.jdbc.sql/insert! returns with namespace-qualified keywords at least with Postgres.

[raszi]

I can verify that is the problem there.

If you set a custom insert function that uses the next.jdbc.result-set/as-unqualified-maps as the builder-fn, then you don't need to use namespace-qualified keywords.

[raszi]

Because you are using a separate schema, I guess it would be :metagross.users/id.

[raszi]

I just realized that the easiest option is to override the :fixtures :get-inserted option with a custom get-inserted function that strips the namespace-qualified keywords. This get-inserted function receives the insert-result and the table-name as well.

https://github.com/donut-party/datapotato/blob/9e481389333775657af9b1b92db78f1b40d9354c/datapotato-next-jdbc/src/donut/datapotato/next_jdbc.clj#L78-L82