I'm added a test to next.jdbc for PostgreSQL for the "id = any(?)" and (int-array [1 2 3 4]) case -- works out of the box with no need to extend anything /cc @bingen.galartza

Is PostgreSQL the only DB this works on?

(I did confirm it does not work on any of the "simple" embedded databases -- they do not like the ANY syntax at all)

I want to add it to the documentation but I'm honestly not entirely sure where in the docs to put this...

Doesn't look like MySQL supports it. I don't have easy access to SQL Server right now to test that.

So maybe it is only PostgreSQL?

Pushed an update with Tips & Tricks in Friendly SQL Functions that covers this. Hopefully it'll be a good place to expand and include DB-specific stuff...

Is there anyway to make hugsql able to run statements like CREATE DATABASE foobar ?

Not sure if HugSQL can run DDL (but I honestly don't understand why you would do that?). There is a #hugsql channel tho'...

@electricladyland156 HugSQL can run anything that the underlying jdbc library can. So, in the case of clojure.java.jdbc (the default), it uses clojure.java.jdbc/execute!.

Of course, you would need an existing database connection with the correct user permissions to create a database. Depending on your situation, this might create a chicken-or-the-egg problem for you.

@electricladyland156 Here's a repl example using def-db-fns-from-string. Notice that the command must set :transaction? false. It seems to work fine in Postgresql as long as the user has createdb permission.

Is there any documentation on using next.jdbc for handling associations when it comes to queries and inserts? For example a query to get an album and all its tracks or Inserting a new Album with all its tracks?

Checkout honeysql for data-fied SQL that you can manipulate and build up easily. ORMs introduce too much complexity IMHO.

@jaihindhreddy I've had a look at Toucan (https://github.com/metabase/toucan#easy-hydration-of-related-objects) which has hydration which solves my query concern.

I come from C# and Entityframework and had a lot of success with it but I've seen a lot of the Clojure community say ORMs add to much complexity so I'm looking for suitable replacements for certain things that I like in EF such as Hydration or Nested insertion.

these features don’t exist outside the scope of ORMs AFAIK, so you won’t find them.

I've been looking at Ecto from the Elixir community which calls it's self a Data Mapper which offers some of this functionality, Toucan is the closets I could find that matches it.

But was wondering what the equivalent would be in plain SQL + Clojure.

you write the SQL by hand or you generate it via HoneySQL which is still declarative, just not string-based

the general idea is that there is no need to abstract away SQL

@thenonameguy So how would you go about a nested associative query or insert in SQL? Like I did in my examples above?

your manual example is about right, yes

The query isn't too bad but the insert seems slightly error prone having to access the parent record manually and pass the Id to the child records.

Here is another example

@grierson Which database are you using? PostgreSQL has a way to do IN natively...

@seancorfield I am using postgres

Then you can use WHERE album_id = ANY(?) in the SQL and (int-array (map :albums/id albums)) as the parameter value.

I just added a new "Tips & Tricks" section to "Friendly SQL Functions" https://github.com/seancorfield/next-jdbc/blob/master/doc/friendly-sql-functions.md#tips--tricks (it's on master, so that's ahead of http://cljdoc.org right now).

But I would definitely recommend HoneySQL if you want a more sophisticated way to build your queries (disclaimer: I maintain HoneySQL -- as well as both next.jdbc and clojure.java.jdbc).

Ok, thank you.

(-> (select :*)
    (from :tracks)
    (where [:= :album_id [:any (int-array (map :albums/id albums))]])
    (h/format))

@seancorfield arh, I just noticed I can do nested queries in HoneySQL, thank you

@grierson As for the insertion of associations etc, I can't think of any easy way to automate that since it requires knowledge of the keys in your tables (which is out of scope for clojure.java.jdbc and next.jdbc).

@grierson If you end up using HoneySQL there's a #honeysql channel here where you can ask for help...

Ok, thank you.