Clojurians Log v2

Is it possible to do bulk delete w/ honeysql?

(sql/format {:delete-from :distributors
                        :where [:> :did :10]
                        :returning [:*] }))

Looking at the tests I see something like this, but can’t figure out how to allow it to have multiple values

@njj Not sure what you mean by "have multiple values"?

The :where clause can be [:and cond1 cond2] etc if that's what you mean.

Not sure what you expect :returning to produce on a delete?

I don’t need the return

but basically delete multiple rows by id

I find the HoneySQL helper functions to be a much easier way to build the data structures, just FYI:

(-> (delete-from :distributors)
    (where [:> :did 10] [:> :foo 42])
    (sql/format))

I agree

Are you trying to delete where id in [vector of ids]? [:in :id vector-of-ids]?

yes basically I’ll have ["123", "321", "456"], etc..

(-> (delete-from :distributors)
    (where [:in :id vector-of-ids])
    (sql/format))

honeysql ftw! 🙂

@seancorfield How would this for multi update?

(-> (update table)
    (values data)
    hsql/format)

@njj Does it generate the SQL you want?

(I've no idea what you're trying to do so I can't comment on what HoneySQL code you would need)

no it doesn’t seem to

I’m just trying to do a batch update

upsert works:

(-> (insert-into table)
                  (values data-vec)
                  (upsert (-> (on-conflict :id)
                              (do-update-set :record)))
                  hsql/format)

upsert isn't part of HoneySQL so I don't know what it does.

But that's an insert with something additional added. Not a batch update. But if that's what you're trying to do, fair enough.

If you do want to update a bunch of records with update instead of upsert, you might look into batch execution with a prepared statement.

Which doesn't really have anything to do with honeysql

Yeah, doing a batch insert and expecting it to do it all in one round-trip to the DB is going to depend on a lot of things. I have some information about that in the next.jdbc docs...

https://cljdoc.org/d/seancorfield/next.jdbc/1.0.13/doc/getting-started/prepared-statements covers some of the quirks around this, including the (different) JDBC connection options that MySQL and PostgreSQL need as hints in order to rewrite multi-row inserts behind the scenes so they might happen as a single DB statement -- assuming you don't run foul of the various limits on parameters etc.

(and as that page indicates, you may be better off using execute-batch! anyway if you have a large set of parameter groups)

See also https://cljdoc.org/d/seancorfield/next.jdbc/1.0.13/doc/getting-started/friendly-sql-functions#insert-multi and https://cljdoc.org/d/seancorfield/next.jdbc/1.0.13/doc/getting-started/friendly-sql-functions#tips--tricks for more information about batch inserts and DB-specific behaviors in general

^ @njj