Hi

Is there a way to call Postgres stored functions with HugSQL?

In particular is there a way to make this type of call on a function that returns a value, or set of values, or has out parameters?

Thanks, Derek

I was translating some queries from yesql, and I ended up having a typo where the name key looked like -- :name:, it took me a little bit to track it down since the stack shows:

Caused by: java.lang.NullPointerException
    at clojure.lang.Symbol.intern(Symbol.java:59)
    at clojure.core$symbol.invokeStatic(core.clj:568)
    at clojure.core$symbol.invoke(core.clj:563)
    at hugsql.core$db_fn_map.invokeStatic(core.clj:463)
    at hugsql.core$db_fn_map.invoke(core.clj:454)

It would be good to validate the required metadata and error out explicitly if stuff like function name couldn't be parsed. Could even print the query string since it should be in context. :)

For some reason, Clojure Expressions doesn't work when I pass a false keyword to the params map. To explain it better, I am executing the next function:

(s/defn fetch-invoices-filterable :- [schemas/Invoice]
  [db :- ConnectionMap
   parameters :- schemas/SearchParameters]
  (println parameters)
  (execute! db :select-invoices-filterable parameters))

And that function calls this SQL query:

-- :name select-invoices-filterable :? :*
-- :doc Retrieve invoices according to a group of filters.
SELECT
  i.id,
  i.type,
  i.legal_date,
  i.invoice_year,
  i.invoice_month,
  i.code,
  i.comments,
  i.start_date,
  i.end_date,
  i.is_paid,
  i.created_at,
  i.person_id,
  p.first_name AS person_first_name,
  p.legal_uid AS person_legal_uid,
  p.org_representative AS person_org_representative
FROM
  invoices i,
  people p
WHERE
  i.person_id = p.id
--~ (when (:person-identifier params) "AND upper(p.first_name) LIKE :person-identifier")
--~ (when-not (nil? (:org-representative params)) "AND p.org_representative = :org-representative")
--~ (when (:type params) "AND upper(i.type::varchar) LIKE :type")
--~ (when (:invoice-year params) "AND i.invoice_year = :invoice-year")
--~ (when (:invoice-month params) "AND i.invoice_month = :invoice-month")
--~ (when (:code params) "AND upper(i.code) LIKE :code")
--~ (when (:comments params) "AND upper(i.comments) LIKE :comments")
GROUP BY
  i.id,
  p.id
ORDER BY
  :i*:order-by-fields
OFFSET :offset
LIMIT :page-size;

And I am getting this error:

user=> {:org-representative false, :page 1, :page-size 20, :offset 0, :order-by-fields (i.created_at ASC)}
ERROR Parameter Mismatch: :org-representative parameter data not found.
clojure.lang.ExceptionInfo: Parameter Mismatch: :org-representative parameter data not found. {}
    at clojure.core$ex_info.invokeStatic(core.clj:4617)
    at clojure.core$ex_info.invoke(core.clj:4617)
    at hugsql.core$validate_parameters_BANG_.invokeStatic(core.clj:61)
    at hugsql.core$validate_parameters_BANG_.invoke(core.clj:55)
    at hugsql.core$prepare_sql.invokeStatic(core.clj:172)
    at hugsql.core$prepare_sql.invoke(core.clj:160)
    at hugsql.core$db_fn_STAR_$y__10617.doInvoke(core.clj:437)
    at clojure.lang.RestFn.invoke(RestFn.java:445)

In the first line you can see that I am passing the missing keyword org-representative. Also, If I execute the same function with :org-representative true everything works fine.

Any idea on what could be happening?

Hi!

Let's assume this setup:

create table entities {
id SERIAL PRIMARY KEY,
props JSONB
};

INSERT INTO entities (props) VALUES('{"attr": "value-1"}');

Now I would like to use this query:

-- :name update-attr! :! :n
UPDATE entities
SET props = jsonb_set(props, '{"attr"}', :val)
WHERE id=:id

In clojure I do this:

(update-attr! {:id 1 :val "value-2"})

But there are several issues with this, and I do not know how to solve them:

1. I would have to change the clojure part to add double quotes like so :val "\"value-2\"". This will make the resulting sql statement look right, but psql still complaints that it can not execute the statement (copy+paste the statement into the psql console will work though). I managed to find a way to execute it with hugsql, but this doesn't seem right to me, since I am no longer using your sql injenction prevention. Check out how I did it:
2. Change the sql file like so:jsonb_set(props, '{"attr"}', :sql:val). Change the clojure part: (update-attr! {:id 1 :val "'\"value-2\"'"}). This code will now update the row succesfully.

I would be happy if I could use your sql injenction prevention methods, but I couldn't find a way.

Do you have an idea what I am missing?

Kind regards,

René

Currently, get-adapter is called inside the def-db-fns macro, which makes it difficult to switch adapters unless providing the adapter as an option to every def-db-fns call.

Hi!

I have a query like this (changed some names for this example):

-- :name update-young-people-coolness :! :n
UPDATE people
       SET cool = CASE
           WHEN id IN (:v*:cool_people) THEN TRUE
           ELSE FALSE
       END
WHERE age < 18;

If I use this with an empty sequence, together with MySQL, I get a syntax error:

> (update-young-people-coolness db-spec {:cool_people []})

Error:

MySQLSyntaxErrorException You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') THEN TRUE
           ELSE FALSE
       END
WHERE age < 18' at line 3  sun.reflect.NativeConstructorAccessorImpl.newInstance0 (NativeConstructorAccessorImpl.java:-2)

I'm not entirely sure, but it seems the empty value list causes a syntax error in MySQL. This is temporarily bypassed by checking the value list before running the query, and falling back to another query if it's an empty seq. I guess this might be something HugSQL could (and should?) handle, as the documentation says:

The * indicates a sequence of zero or more values.

Thanks!

This is/should-be valid HugSQL because it is valid PSQL (works correctly in Yesql).

SELECT * FROM "the_table"
WHERE id = :key::bigint

But it will generate

SELECT * FROM "the_table" WHERE id = $1

instead of

SELECT * FROM "the_table" WHERE id = $1::bigint

The work-around is to add whitespace between the cast and the keyword, but it should support the concatenated format, no?

I'm new to HugSQL and I'm trying this simple query with Apache Drill:

-- :name test-query :? :*
select :test as test from (values (1))

(test-query db {:test "test"})

This throws an exception:

java.sql.SQLFeatureNotSupportedException: Prepared-statement dynamic parameters are not supported.

Is there any easy way to workaround this limitation in Drill? Or maybe I'm doing something wrong :?

for ex:
SELECT id AS mixId FROM some_table will give result

[{:mixid 123}, {:mixid 124}, ...]

It is common to use lowercase in clojure symbol, but to compatible with SQL structure, IMHO, we should make sure the keyword is exactly same with the alias after AS.

The documentation for snippets shows how to join snippets together in clojure code. But Im wondering if its possible to do something like this:

-- :snip where-user-id
WHERE user_id = :user-id

-- :snip where-order-id
WHERE order_id = :order-id

-- :snip main-query
SELECT * FROM ORDERS

-- name: select-by-user-id :? :*
:snip:main-query
:snip:where-user-id

-- name: select-by-order-id :? :*
:snip:main-query
:snip:where-order-id

(select-by-user-id db {:user-id 1})

(select-by-oder-id {:order-id 2})

Using hugsql 0.4.7 (through conman 0.5.8), I get the following error using :i! to get the insert id from a MySQL database insert:

Wrong number of args (2) passed to: jdbc/db-do-prepared-return-keys

Head of the stack trace:

clojure.lang.ArityException: Wrong number of args (2) passed to: jdbc/db-do-prepared-return-keys
    at clojure.lang.AFn.throwArity(AFn.java:429)
    at clojure.lang.AFn.invoke(AFn.java:36)
    at hugsql.adapter.clojure_java_jdbc.HugsqlAdapterClojureJavaJdbc.execute(clojure_java_jdbc.clj:11)
    at hugsql.adapter$eval16389$fn__16390$G__16369__16395.invoke(adapter.clj:3)
    at hugsql.adapter$eval16389$fn__16390$G__16368__16401.invoke(adapter.clj:3)
    at clojure.lang.Var.invoke(Var.java:394)
    at hugsql.core$db_fn_STAR_$y__16710.doInvoke(core.clj:463)
    at clojure.lang.RestFn.invoke(RestFn.java:445)
    at hugsql.core$db_fn_STAR_$y__16710.invoke(core.clj:453)

E.g.

-- :name insert-record! :i!
INSERT INTO records (id, name, value)
VALUES (NULL, :name, :value)

-- example

-- :name example-all :? :*
-- :doc Example
TABLE examples

Will succeed, while

-- example


-- :name example-all :? :*
-- :doc Example
TABLE examples

will fail with: clojure.lang.ExceptionInfo: Encountered SQL with no hugsql header line: 4, column: 1 {:line 4, :column 1}

Just finished going through the documentation but didn't see any mention of renaming columns.

I can do something like this:

(hugsql/sqlvec "select :i*:cols from users"
               {:cols ["name AS my_name" "email AS my_email"]})

But Im in a scenario where Im joining to the users table multiple times and each time I'm selecting the same columns. But, I just need them named differently in the resultset (ie: user1_name, user1_email, user2_name, user2_email).

The only way I can think to do this is to have a clojure array of the columns: ["name" "email"] and a couple arrays for the renamed columns ["user1_name", "user1_email"] and ["user2_name", "user2_email"] then zip these together and map to insert the 'AS'.

Not sure if theres a better way to compose this?

Thanks!

As far as I understand, HugSQL doesn't currently support namespace-qualified keywords:

> (hugsql.core/sqlvec "SELECT :test/value" {:test/value 42 :test 5})
["SELECT ? /value" 5]

(The expected result would be ["SELECT ?" 42], I guess.)

I think it would be very helpful to be able to process namespaced keywords, especially with the advent of clojure.spec. Are there any known obstacles to handling them? I would try to implement what is needed but wanted to check for known issues or objections first...

At the moment clojure.repl/doc shows documentation defined with -- :doc only.
It would be nice if function signatures are shown too like Yesql does https://github.com/krisajenkins/yesql#one-file-one-query.
I think it's handy to know which parameters are required within an sql query.

(clojure.repl/doc users-by-country)

;=> -------------------------
;=> user/users-by-country
;=> ([{:keys [country_code]}]
;=>  [{:keys [country_code]} {:keys [connection]}])
;=>
;=>   Counts the users in a given country.

Hi,
This is more a question than an issue I think. I wrote a SQL query with a date operation, e.g.

SELECT item
  ,date
  ,value
FROM table AS X
JOIN (
  SELECT item,value,date
  FROM history
  WHERE date = :date - integer '7'
  ) AS H
ON X.item = H.item

When I called this with a {:date yyyy-mm-dd} (a joda.date type instance), I've got a:

PSQLException ERROR: operator does not exist: date = integer
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  Position: 249  org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse (QueryExecutorImpl.java:2182)

When I tried to explicit the type, such as WHERE date = date :date - integer '7', I've got a:

PSQLException ERROR: syntax error at or near "$1"
  Position: 256  org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse (QueryExecutorImpl.java:2182)

I would like to figure out why I've got these errors what I could do to fix them.

See also https://www.postgresql.org/docs/current/static/functions-datetime.html for different date operations.

Cheers,
Damien

Ran into a funny one here. Postgres has some reserved keywords like randomNumber, so if you name a column that, then it won't be found. With plain clojure.java.jdbc you can escape it like this:

(jdbc/update!
    db
    :world
    {"\"randomNumber\"" randomNumber}
    ["id = ?" id])

but it won't work with HugSQL:

-- :name update-world<! :<!
-- update an existing world record
UPDATE world
SET "randomNumber" = :randomNumber
WHERE id = :id
RETURNING id

I'm guessing the quotes don't end up being protected when the query is read.

It's a bit awkward to use keywords with underscores when the rest of the application uses hyphens. It would be useful if hugsql could convert underscores in keywords (in and out) automatically without an additional wrapper.

I have the following SQL:

-- :snip my-join
JOIN users U on U.id = F.user_id

-- :snip my-where
WHERE U.id = 1

-- :name all-characters :? :*
-- :doc Get all characters
select * from first_table F
--~ (when (:join-table params) ":snip:join-table")
:snip:where-clause

And the following clojure:

(all-characters-sqlvec
  {:join-table (my-join)
   :where-clause (my-where)})
=> ["select * from first_table F  JOIN users U on U.id = F.user_idWHERE U.id = 1"]

Notice that there is no space before the WHERE.

It seems if you have a clojure snippet followed by a sql snippet theres no space in between them.

Hey,

I am having troubles to define an insert query which should return the new record id. I defined the query this way:

-- :name insert-contact! :!
-- :doc Insert a contact record.
INSERT INTO contacts
(:i*:fields)
VALUES
(:v*:values)
RETURNING id;

When I execute the query through HugSQL I get a BatchUpdateException, however, If I execute the query on psql it pass without problems (returning the record's id). This is the error stacktrace:

user=> ERROR Batch entry 0 INSERT INTO contacts
(address, email, first_name, organization_name, phone, comments, identifier, last_name)
VALUES
('Apoquindo 6371','[email protected]','Arturo','Moptte','+56 2 7781 2831','4 oficinas 12 meses.','13.121.981-K','Clode')
RETURNING id was aborted.  Call getNextException to see the cause.
java.sql.BatchUpdateException: Batch entry 0 INSERT INTO contacts
(address, email, first_name, organization_name, phone, comments, identifier, last_name)
VALUES
('Apoquindo 6371','[email protected]','Arturo','Moptte','+56 2 7781 2831','4 oficinas 12 meses.','13.121.981-K','Clode')
RETURNING id was aborted.  Call getNextException to see the cause.
    at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2717)
    at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleResultRows(AbstractJdbc2Statement.java:2666)
    at org.postgresql.core.v3.QueryExecutorImpl$1.handleResultRows(QueryExecutorImpl.java:373)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1828)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:338)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2898)
    at clojure.java.jdbc$execute_batch.invokeStatic(jdbc.clj:404)
    at clojure.java.jdbc$execute_batch.invoke(jdbc.clj:397)
    at clojure.java.jdbc$db_do_execute_prepared_statement$fn__10737.invoke(jdbc.clj:764)
    at clojure.java.jdbc$db_transaction_STAR_.invokeStatic(jdbc.clj:595)
    at clojure.java.jdbc$db_transaction_STAR_.doInvoke(jdbc.clj:568)
    at clojure.lang.RestFn.invoke(RestFn.java:425)
    at clojure.java.jdbc$db_do_execute_prepared_statement.invokeStatic(jdbc.clj:763)
    at clojure.java.jdbc$db_do_execute_prepared_statement.invoke(jdbc.clj:748)
    at clojure.java.jdbc$db_do_prepared.invokeStatic(jdbc.clj:786)
    at clojure.java.jdbc$db_do_prepared.doInvoke(jdbc.clj:770)
    at clojure.lang.RestFn.invoke(RestFn.java:464)
    at clojure.java.jdbc$execute_BANG_$execute_helper__10764.invoke(jdbc.clj:891)
    at clojure.java.jdbc$execute_BANG_.invokeStatic(jdbc.clj:896)
    at clojure.java.jdbc$execute_BANG_.doInvoke(jdbc.clj:875)
    at clojure.lang.RestFn.invoke(RestFn.java:425)
    at clojure.lang.AFn.applyToHelper(AFn.java:156)
    at clojure.lang.RestFn.applyTo(RestFn.java:132)
    at clojure.core$apply.invokeStatic(core.clj:649)
    at clojure.core$apply.invoke(core.clj:640)
    at hugsql.adapter.clojure_java_jdbc.HugsqlAdapterClojureJavaJdbc.execute(clojure_java_jdbc.clj:11)

Any thoughts on what could I be missing?

My postgres-async adapter is ready for use (https://github.com/Skinney/hugsql-async).
It's easier if people have an easy access list of adapters available. Like the bottom of the readme.

Having clojurescript support would enable use of hugsql on Amazon Lambda.

On a Mac, if I run(println (keys (hugsql/map-of-db-fns "hugsql/sql/test.sql"))), I get 34 keys, but if I run it on a Windows machine I only get one. I think this might have something to do with how the parser is treating newlines, but I haven't been able to isolate it.

Is it possible to do "real" batch inserts (à la clojure.java.jdbc/insert!) with hugsql?

I just tried to import ~8000 records with the syntax from the documentation:

-- :name create-sentences! :! :n
-- :doc creates a number of new sentence records
INSERT INTO sentences
(tokens, tags, document_id)
VALUES :t*:sentences

(->> sentences
     (map #(vec (map % [:tokens :tags :document_id])))
     vec
     (#(q/create-sentences! {:sentences %}))))

This yields a "Too many SQL variables" exception (I'm using SQLite).

Doing the same with clojure.java.jdbc/insert!, however, works like a charm:

(->> sentences                                                                          
     (apply (partial jdbc/insert! q/conn :sentences [:tokens :tags :document_id]))
     (reduce +)))

I have a weird case where I wanted to add a raw "left or "right" to my sql, as it is:

UPDATE workflows
SET scan_:sql:side = :scan
WHERE id = :id
RETURNING *

Unfortunately this does not seem to work as the computed sqlvec is:
["UPDATE workflows\nSET scan_ right = ?\nWHERE id = ?\nRETURNING *" "z.\\+g" 6N]

(The garbage comes from a generator, but there is a space between scan_ and right)

Am I taking the right approach?

This is a catch-all ticket for exploring features making HugSQL more composable. All ideas welcome!

It would be great if hugsql allowed adapters to catch exceptions. The jdbc-adapters can simply just re-throw the exceptions, but my async adapter could return the exception on a channel, which would make it consistent with db-errors.

Trying to insert/update Postgres arrays from Clojure vectors. With korma and yesql, all I had to do was:

(extend-protocol clojure.java.jdbc/ISQLParameter
  clojure.lang.IPersistentVector
  (set-parameter [v ^java.sql.PreparedStatement stmt ^long i]
    (let [conn (.getConnection stmt)
          meta (.getParameterMetaData stmt)
          type-name (.getParameterTypeName meta i)]
      (.setObject stmt i (.createArrayOf conn (clojure.string/replace type-name #"[^A-Za-z]" "") (to-array v))))))

Hugsql doesn't seem to go through this part of JDBC and seems to always use a string instead of the array.

I've got these two lines in a SQL file. Note that the second function contains a single blank comment right before the WHERE clause:

-- :name without-blank-comment :? :1
SELECT COUNT(*)
FROM users u
WHERE u.id = 1

-- :name with-blank-comment :? :1
SELECT COUNT(*)
FROM users u
--
WHERE u.id = 1

When I run this file via Clojure code looking similar to:

(hugsql/def-db-fns "hugsql/user.sql")

(defn comment-test []
  (log/spy (with-blank-comment (db/get-db-spec) {}))
  (log/spy (without-blank-comment (db/get-db-spec) {})))

The first result returns 1 (as I expected) but the second row returns 10 (one per row in that table in the database I'm connecting to).

It appears that somewhere in the SQL parsing, having a line that is only a comment string is aborting the processing, so that the WHERE clause is ignored in the name with-blank-comment function.

I doubt this is relevant, but I'm connecting to a Postgres backend; I've got [com.layerware/hugsql "0.4.6"]and [org.clojure/java.jdbc "0.4.2"] in my lein deps :tree output.

One common problem is SQL column names tend to be snake_case and clojure map keywords tend to be kebab-case. Is there a way to automatically change them on the way in and out of sql statements. Pupeno is using quite a neat trick here https://carouselapps.com/2015/10/23/automatically-converting-case-between-sql-and-clojure/ with conman and yesql but it'd be nice if this could happen out of the box. Is this possible or any plans for anything like this?

I'm looking to reuse some snippets, so a way to do that currently would be to prepare a file for input to def-sqlvec-fns. but... i dont really want to write this out to a file first. Ideally def-sqlvec-fns would accept a stream or string as well, or just delegate to another macro that accepts parsed defs etc.

Since HugSQL uses named parameters starting with a colon :, it is considered as special HugSQL syntax. We specifically work around a common use case in Postgresql SQL for explicit type casts starting with double colons ::. However, there are more uses of the colon : in Postgresql and possibly other DBs. For instance, Postgresql array slicing syntax uses colons.

Instead of working around every possible use of colons across many flavors of SQL, HugSQL should support the escaping of the colon : with a backslash \.

\: will be interpreted as a : in SQL output
\\ will be interpreted as a \ in SQL output

YeSQL allows you to suffix your sql fns with <! to return the created row.
Maybe hugsql should have a :r result type that does the same?

I'm converting all my yesql code to hugsql, and I've run into this very strange bug. Need some helping in understanding why this happens.

I'm trying to run the following sql:

--:name create! :!
INSERT INTO shows (tvdb_id, name, description, banner, poster, network, next_update)
VALUES (:v:tvdb_id, :v:name, :v:description, :v:banner, :v:poster, :v:network, :v:next_update);

tvdb_id is an int, next_update is a joda-time instance. The other values are strings.

Postgres displays the following::

LOG:  execute <unnamed>: SHOW TRANSACTION ISOLATION LEVEL
LOG:  execute <unnamed>: BEGIN
LOG:  execute <unnamed>: INSERT INTO shows (tvdb_id, name, description, banner, poster, network, next_update)
    VALUES ($1, $2, $3, $4, $5, $6, $7)
DETAIL:  parameters: $1 = '278518', $2 = 'Last Week Tonight with John Oliver', $3 = 'Last Week Tonight with John Oliver is an American late-night talk and news satire television program.
    L.W.T draws its comedy and satire from recent news stories, political figures, media organizations, pop culture and often aspects of the show itself.', $4 = 'graphical/278518-g3.jpg', $5 = 'posters/278518-2.jpg', $6 = 'HBO', $7 = '2015-11-09 01:00:00+01'
LOG:  execute <unnamed>: SELECT pg_type.oid   FROM pg_catalog.pg_type   LEFT   JOIN (select ns.oid as nspoid, ns.nspname, r.r           from pg_namespace as ns           join ( select s.r, (current_schemas(false))[s.r] as nspname                    from generate_series(1, array_upper(current_schemas(false), 1)) as s(r) ) as r          using ( nspname )        ) as sp     ON sp.nspoid = typnamespace  WHERE typname = $1  ORDER BY sp.r, pg_type.oid DESC LIMIT 1
DETAIL:  parameters: $1 = 'hstore'
LOG:  execute S_3: ROLLBACK

And this is my stacktrace:

ERROR] org.postgresql.util.PSQLException: No hstore extension installed.
org.postgresql.util.PSQLException: No hstore extension installed.
    at org.postgresql.jdbc2.AbstractJdbc2Statement.setMap(AbstractJdbc2Statement.java:1746)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.setObject(AbstractJdbc2Statement.java:2138)
    at org.postgresql.jdbc3g.AbstractJdbc3gStatement.setObject(AbstractJdbc3gStatement.java:38)
    at org.postgresql.jdbc4.AbstractJdbc4Statement.setObject(AbstractJdbc4Statement.java:48)
    at com.zaxxer.hikari.proxy.HikariPreparedStatementProxy.setObject(HikariPreparedStatementProxy.java)
    at clojure.java.jdbc$eval1178$fn__1179.invokePrim(jdbc.clj:341)
    at clojure.java.jdbc$eval1178$fn__1179.invoke(jdbc.clj)
    at clojure.java.jdbc$eval1157$fn__1158$G__1148__1167.invoke(jdbc.clj:328)
    at clojure.java.jdbc$set_parameters$fn__1254.invoke(jdbc.clj:478)
    at clojure.core$map_indexed$mapi__6824$fn__6825.invoke(core.clj:7015)
    at clojure.lang.LazySeq.sval(LazySeq.java:40)
    at clojure.lang.LazySeq.seq(LazySeq.java:49)
    at clojure.lang.RT.seq(RT.java:507)
    at clojure.core$seq__4128.invoke(core.clj:137)
    at clojure.core$dorun.invoke(core.clj:3009)
    at clojure.java.jdbc$set_parameters.invoke(jdbc.clj:477)
    at clojure.java.jdbc$db_query_with_resultset$run_query_with_params__1341.invoke(jdbc.clj:823)
    at clojure.java.jdbc$db_query_with_resultset.invoke(jdbc.clj:831)
    at clojure.java.jdbc$query.doInvoke(jdbc.clj:866)
    at clojure.lang.RestFn.invoke(RestFn.java:425)
    at hugsql.adapter.clojure_java_jdbc.HugsqlAdapterClojureJavaJdbc.query(clojure_java_jdbc.clj:15)
    at episode_next.db.show$id_for_tvdb_id.invoke(show.clj:5)
    at episode_next.db.show$id_for_tvdb_id.invoke(show.clj:5)
    at episode_next.routes.show$upsert_show_BANG_$fn__59504.invoke(show.clj:20)
    at clojure.java.jdbc$db_transaction_STAR_.doInvoke(jdbc.clj:595)
    at clojure.lang.RestFn.invoke(RestFn.java:521)
    at clojure.java.jdbc$db_transaction_STAR_.doInvoke(jdbc.clj:611)
    at clojure.lang.RestFn.invoke(RestFn.java:425)
    at episode_next.routes.show$upsert_show_BANG_.invoke(show.clj:18)
    at episode_next.routes.show$connect_show__GT_device_BANG_$fn__59513.invoke(show.clj:39)[INFO] /show/follow 500 194ms {:device-id #uuid "4443b630-8534-11e5-898f-0e2267e517ab", :tvdb-id 278518}

    at episode_next.routes.show$connect_show__GT_device_BANG_.invoke(show.clj:38)
    at episode_next.routes.show$eval59517$fn__59518.invoke(show.clj:53)
    at clojure.lang.MultiFn.invoke(MultiFn.java:229)
    at episode_next.http$router.invoke(http.clj:32)
    at episode_next.http$wrap_components$fn__60290.invoke(http.clj:27)
    at episode_next.http$wrap_error_page$fn__60287.invoke(http.clj:20)
    at episode_next.transit$wrap_transit_response$fn__60226.invoke(transit.clj:44)
    at episode_next.transit$wrap_transit_body$fn__60223.invoke(transit.clj:38)
    at episode_next.log$wrap_with_logger$fn__59304.invoke(log.clj:29)
    at aleph.http.server$wrap_stream__GT_input_stream$fn__21998.invoke(server.clj:403)
    at aleph.http.server$handle_request$fn__21916$f__17232__auto____21917.invoke(server.clj:153)
    at clojure.lang.AFn.run(AFn.java:22)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:745)

Any idea why this is happening?

A thing just happened to me where I was writing a query and started in the .sql file with:

-- :name

but I didn't know what I wanted to name my query yet, so I wrote out the query:

-- :name
select * from ...
...

and when I was satisfied with the query I reloaded my code, forgetting to go back and give the query a name. And the result was a NullPointerException. It's kind of a rare case, and didn't take me too long to figure out the problem, but it's a fairly vague error message.

If you are interested in a validation there I can make a patch, but I won't be offended if you aren't interested.

Hey,

I am working on a small piece of code to insert rows in a table. The SQL is very simple:

-- :name insert-contact :? :1
-- :doc Insert a contact record.
INSERT INTO contacts
  (:i*:fields)
VALUES
  (:v*:values)
RETURNING id;

As you can see, fields is a keyword which contains a list of strings (the name of some table fields), and values is another keyword which contains values (integers, strings, uuid, etc.) for those table fields.

On top of that, I have a function which call this SQL with a map with two keywords: fields and values. See here:

(s/defn create-contact :- {s/Keyword s/Uuid}
  [db :- ConnectionMap
   contact :- schemas/Contact]
  (let [contact (select-keys contact schemas/contact-db-fields)
        sql-args {:fields (map name (keys contact))
                  :values (vals contact)}]
    new-contact (execute! db :insert-contact sql-args)))

If I print the sql-args (params) map I get something like this:

{:fields (owner_id address email first_name created_by_id extra_service_data organization_name phone identifier last_name), 
 :values (#uuid "57dce206-acc4-43a4-849c-9491bda0cd22" abc [email protected] ABC #uuid "57dce206-acc4-43a4-849c-9491bda0cd22"  ABC CORP 123123123 1 ABC)}

However, when I execute the function with those parameters, I get this error:

#error {
 :cause "contains? not supported on type: java.util.UUID"
 :via
 [{:type clojure.lang.Compiler$CompilerException
   :message "java.lang.IllegalArgumentException: contains? not supported on type: java.util.UUID, compiling:(scv2backend/models/contacts_test.clj:10:1)"
   :at [clojure.lang.Compiler load "Compiler.java" 7391]}
  {:type java.lang.IllegalArgumentException
   :message "contains? not supported on type: java.util.UUID"
   :at [clojure.lang.RT contains "RT.java" 814]}]
 :trace
 [[clojure.lang.RT contains "RT.java" 814]
  [clojure.core$contains_QMARK_ invokeStatic "core.clj" 1443]
  [clojure.core$contains_QMARK_ invoke "core.clj" 1435]
  [hugsql.core$validate_parameters_BANG_ invokeStatic "core.clj" 60]
  [hugsql.core$validate_parameters_BANG_ invoke "core.clj" 55]
  [hugsql.core$prepare_sql invokeStatic "core.clj" 172]
  [hugsql.core$prepare_sql invoke "core.clj" 160]
  [hugsql.core$db_fn_STAR_$y__10617 doInvoke "core.clj" 437]
  [clojure.lang.RestFn invoke "RestFn.java" 445]
  [hugsql.core$db_fn_STAR_$y__10617 invoke "core.clj" 428]
  [scv2backend.models.common$eval47760$executeQuery__47765$fn__47766 invoke "common.clj" 17]
  [scv2backend.models.common$eval47760$executeQuery__47765 invoke "common.clj" 12]

So, I am suspecting that there is a problem in the hugsql.core$validate_parameters function when it has to deal with a sequence which contains UUID values.

Also, If I remove the UUID fields, I got a PostgreSQL exception (violation of a not-null constraint), so I guess in that case the query is being executed.

A "Parameter Mismatch" exception is thrown when calling snippets that receive parameters when using Conman.

snippet:

FROM   trd_product p, trd_contract c
WHERE  p.productid = c.productid AND p.tenantid = :tenant-id

(search-snip {:tenant-id 1698})

This is the state of sqlvec-fn* when debugging:

Class: clojure.lang.PersistentArrayMap
Contents: 
  0. [ psql [ "FROM   trd_product p, trd_contract c\nWHERE  p.productid = c.productid AND\n       p.tenantid = " { :type :v, :name :tenant-id } ] ]
  1. [ options {} ]
  2. [ y hugsql.core$sqlvec_fn_STAR_$y__64066@2084d943 ]
  3. [ param-data { :datasource HikariDataSource (HikariPool-21) } ]
  4. [ opts { :tenant-id 1698 } ]

Problems I stumbled onto when trying out this library:

Placing this in my project.clj file gave me an error: [com.layerware/hugsql "0.2.0"]
The error was something about not finding hugsql-core "0.1.0" in clojars if I remember correctly.

You should also note that hugsql uses io/resource to find sql files. My app crashed and I had no idea why, removing "resources" from the provided path-string solved the issue.

You should also probably have at least one execute (:!) example in the README. All your examples are currently select statements.

Other than that, great stuff!

Trying to do this in postgres:

and timestamp > now() - interval :recent days

What I want it to look at the end is:

and timestamp > now() - interval '10' days

But plugging :recent as a string doesn't work... returns org.postgresql.util.PSQLException: ERROR: syntax error at or near "$2" Position: 240

DEPRECATED: unrolled sql / param groups in db-do-prepared-return-keys

I'm most interested in marking functions private, but other metadata might be helpful too.

Hi,

Since migrating from Yesql to Hugsql we sometimes on startup run into this exception, which is probably a weird race condition (maybe require is too slow?).

 #error {
 :cause No such var: adp/hugsql-adapter-clojure-java-jdbc
 :via
 [{:type clojure.lang.Compiler$CompilerException
   :message java.lang.RuntimeException: No such var: adp/hugsql-adapter-clojure-java-jdbc, compiling:(NO_SOURCE_PATH:32:37)
   :at [clojure.lang.Compiler analyze Compiler.java 6688]}
  {:type java.lang.RuntimeException
   :message No such var: adp/hugsql-adapter-clojure-java-jdbc
   :at [clojure.lang.Util runtimeException Util.java 221]}]
 :trace
 [[clojure.lang.Util runtimeException Util.java 221]
  [clojure.lang.Compiler resolveIn Compiler.java 7138]
  [clojure.lang.Compiler resolve Compiler.java 7108]
  [clojure.lang.Compiler analyzeSymbol Compiler.java 7069]
  [clojure.lang.Compiler analyze Compiler.java 6648]
  [clojure.lang.Compiler analyze Compiler.java 6625]
  [clojure.lang.Compiler$InvokeExpr parse Compiler.java 3766]
  [clojure.lang.Compiler analyzeSeq Compiler.java 6870]
  [clojure.lang.Compiler analyze Compiler.java 6669]
  [clojure.lang.Compiler analyze Compiler.java 6625]
  [clojure.lang.Compiler$InvokeExpr parse Compiler.java 3834]
  [clojure.lang.Compiler analyzeSeq Compiler.java 6870]
  [clojure.lang.Compiler analyze Compiler.java 6669]
  [clojure.lang.Compiler analyze Compiler.java 6625]
  [clojure.lang.Compiler$BodyExpr$Parser parse Compiler.java 6001]
  [clojure.lang.Compiler$FnMethod parse Compiler.java 5380]
  [clojure.lang.Compiler$FnExpr parse Compiler.java 3972]
  [clojure.lang.Compiler analyzeSeq Compiler.java 6866]
  [clojure.lang.Compiler analyze Compiler.java 6669]
  [clojure.lang.Compiler eval Compiler.java 6924]
  [clojure.lang.Compiler eval Compiler.java 6917]
  [clojure.lang.Compiler eval Compiler.java 6890]
  [clojure.core$eval invokeStatic core.clj 3105]
  [clojure.core$eval invoke core.clj 3101]
  [hugsql.core$get_adapter invokeStatic core.clj 30]
  [hugsql.core$get_adapter invoke core.clj 17]
  [hugsql.core$db_fn_STAR_$y__20666 doInvoke core.clj 459]
  [clojure.lang.RestFn invoke RestFn.java 445]
  [hugsql.core$db_fn_STAR_$y__20666 invoke core.clj 453]}

Not quite sure what can be done about it. I've tried to set the adapter in our application explicitly since we're using clojure.java.jdbc anyway, but for HugSQL it might be done by leaving out the require part and explicitly accessing the whole qualified path to the namespace?

In SQLLite, there are no boolean types. You have to represent them as the integers 0 or 1. Is there a way for me to pass a function to a select that will be called on each row of a ResultSet that can transform it in some way? In my case, I can change integers to booleans, etc. Thank you.

Hello again @csummers, I have a problem and I cannot figure out what I am doing wrong:

The error is an obscure:

Unhandled org.postgresql.util.PSQLException
   ERROR: syntax error at or near "$1" Position: 23

The query is:

-- :name update-workflow! :<! :1
-- :doc update an existing workflow's fields
UPDATE workflows
SET :tuple:cols = :tuple:vals
WHERE id = :id
RETURNING *

The payload:

{:cols ["photo" "last_modified"], :vals [#object[org.postgresql.util.PGobject 0x2f148979 "{\"url\":\"http://lorempixel.com/640/480/cats/test-399\"}"] #inst "2016-06-22T21:27:10.131-00:00"], :id 11}

The sqlvec is:

["UPDATE workflows\nSET (?,?) = (?,?)\nWHERE id = ?\nRETURNING *" "photo" "last_modified" #object[org.postgresql.util.PGobject 0x2f148979 "{\"url\":\"http://lorempixel.com/640/480/cats/test-399\"}"] #inst "2016-06-22T21:27:10.131-00:00" 11]

I spent a bit on it and I apologize if this is silly (and not strictly a bug/feature request...btw are you on Slack?).

Thank you!

It would be nice to highlight key differences with Yesql.

I'm currently using Yesql in Luminus, but I'd like to either switch to HugSQL or support it as an alternative. Currently, I manage the connection using the conman wrapper library I wrote.

One of the things I wanted to do was to ensure that the transactional connection was used by default. I find that the behavior of having to remember to pass it in is quite error prone, and you only get an error in case the transaction would fail.

My approach was to use a dynamic var store the connection, and then rebind it within transactions. What I ended up doing with Yesql is creating a shadow namespace where it declares its functions and then wrapping those with functions I generate. he functions defined by conman use the dynamic var and get the transactional connection by default.

It would be really nice to have the ability to get a map of anonymous functions keyed on their names from HugSQL that could then be used to declare my own functions in conman.

If something like this was available, that would be great :)

(defn parse-queries [file]
  (reduce
    (fn [fns {{:keys [name doc]} :hdr sql :sql}]
      (assoc fns
        (-> name first keyword)
        {:doc doc
         :fn (sql->fn sql)})
      {} (parsed-defs-from-file file))))

HUGSQL:

...is missing a required parameter.

is not nearly as useful as:

YESQL:

java.lang.AssertionError: Assert failed: Query argument mismatch.
Expected keys: (:id)
Actual keys: 
Missing keys: (:id)

Planning on contributing to this project, but currently the tests fail.

➜ hugsql-core git:(minor-changes) lein test
Exception in thread "main" java.io.FileNotFoundException: Could not locate clojure/tools/reader/reader_types__init.class or clojure/tools/reader/reader_types.clj on classpath. Please check that namespaces with dashes use underscores in the Clojure file name., compiling:(hugsql/parser.clj:1:1)
at clojure.lang.Compiler.load(Compiler.java:7239)
at clojure.lang.RT.loadResourceScript(RT.java:371)
at clojure.lang.RT.loadResourceScript(RT.java:362)
at clojure.lang.RT.load(RT.java:446)
at clojure.lang.RT.load(RT.java:412)
at clojure.core$load$fn__5448.invoke(core.clj:5866)
at clojure.core$load.doInvoke(core.clj:5865)
at clojure.lang.RestFn.invoke(RestFn.java:408)
at clojure.core$load_one.invoke(core.clj:5671)
at clojure.core$load_lib$fn__5397.invoke(core.clj:5711)
at clojure.core$load_lib.doInvoke(core.clj:5710)
at clojure.lang.RestFn.applyTo(RestFn.java:142)
at clojure.core$apply.invoke(core.clj:632)
at clojure.core$load_libs.doInvoke(core.clj:5749)
at clojure.lang.RestFn.applyTo(RestFn.java:137)
at clojure.core$apply.invoke(core.clj:632)
at clojure.core$require.doInvoke(core.clj:5832)
at clojure.lang.RestFn.invoke(RestFn.java:482)
at hugsql.core$eval196$loading__5340__auto____197.invoke(core.clj:1)
at hugsql.core$eval196.invoke(core.clj:1)
at clojure.lang.Compiler.eval(Compiler.java:6782)
at clojure.lang.Compiler.eval(Compiler.java:6771)
at clojure.lang.Compiler.load(Compiler.java:7227)
at clojure.lang.RT.loadResourceScript(RT.java:371)
at clojure.lang.RT.loadResourceScript(RT.java:362)
at clojure.lang.RT.load(RT.java:446)
at clojure.lang.RT.load(RT.java:412)
at clojure.core$load$fn__5448.invoke(core.clj:5866)
at clojure.core$load.doInvoke(core.clj:5865)
at clojure.lang.RestFn.invoke(RestFn.java:408)
at clojure.core$load_one.invoke(core.clj:5671)
at clojure.core$load_lib$fn__5397.invoke(core.clj:5711)
at clojure.core$load_lib.doInvoke(core.clj:5710)
at clojure.lang.RestFn.applyTo(RestFn.java:142)
at clojure.core$apply.invoke(core.clj:632)
at clojure.core$load_libs.doInvoke(core.clj:5749)
at clojure.lang.RestFn.applyTo(RestFn.java:137)
at clojure.core$apply.invoke(core.clj:632)
at clojure.core$require.doInvoke(core.clj:5832)
at clojure.lang.RestFn.invoke(RestFn.java:457)
at hugsql.core_test$eval190$loading__5340__auto____191.invoke(core_test.clj:1)
at hugsql.core_test$eval190.invoke(core_test.clj:1)
at clojure.lang.Compiler.eval(Compiler.java:6782)
at clojure.lang.Compiler.eval(Compiler.java:6771)
at clojure.lang.Compiler.load(Compiler.java:7227)
at clojure.lang.RT.loadResourceScript(RT.java:371)
at clojure.lang.RT.loadResourceScript(RT.java:362)
at clojure.lang.RT.load(RT.java:446)
at clojure.lang.RT.load(RT.java:412)
at clojure.core$load$fn__5448.invoke(core.clj:5866)
at clojure.core$load.doInvoke(core.clj:5865)
at clojure.lang.RestFn.invoke(RestFn.java:408)
at clojure.core$load_one.invoke(core.clj:5671)
at clojure.core$load_lib$fn__5397.invoke(core.clj:5711)
at clojure.core$load_lib.doInvoke(core.clj:5710)
at clojure.lang.RestFn.applyTo(RestFn.java:142)
at clojure.core$apply.invoke(core.clj:632)
at clojure.core$load_libs.doInvoke(core.clj:5749)
at clojure.lang.RestFn.applyTo(RestFn.java:137)
at clojure.core$apply.invoke(core.clj:632)
at clojure.core$require.doInvoke(core.clj:5832)
at clojure.lang.RestFn.applyTo(RestFn.java:137)
at clojure.core$apply.invoke(core.clj:632)
at user$eval89.invoke(form-init3333871924506929611.clj:1)
at clojure.lang.Compiler.eval(Compiler.java:6782)
at clojure.lang.Compiler.eval(Compiler.java:6772)
at clojure.lang.Compiler.load(Compiler.java:7227)
at clojure.lang.Compiler.loadFile(Compiler.java:7165)
at clojure.main$load_script.invoke(main.clj:275)
at clojure.main$init_opt.invoke(main.clj:280)
at clojure.main$initialize.invoke(main.clj:308)
at clojure.main$null_opt.invoke(main.clj:343)
at clojure.main$main.doInvoke(main.clj:421)
at clojure.lang.RestFn.invoke(RestFn.java:421)
at clojure.lang.Var.invoke(Var.java:383)
at clojure.lang.AFn.applyToHelper(AFn.java:156)
at clojure.lang.Var.applyTo(Var.java:700)
at clojure.main.main(main.java:37)
Caused by: java.io.FileNotFoundException: Could not locate clojure/tools/reader/reader_types__init.class or clojure/tools/reader/reader_types.clj on classpath. Please check that namespaces with dashes use underscores in the Clojure file name.
at clojure.lang.RT.load(RT.java:449)
at clojure.lang.RT.load(RT.java:412)
at clojure.core$load$fn__5448.invoke(core.clj:5866)
at clojure.core$load.doInvoke(core.clj:5865)
at clojure.lang.RestFn.invoke(RestFn.java:408)
at clojure.core$load_one.invoke(core.clj:5671)
at clojure.core$load_lib$fn__5397.invoke(core.clj:5711)
at clojure.core$load_lib.doInvoke(core.clj:5710)
at clojure.lang.RestFn.applyTo(RestFn.java:142)
at clojure.core$apply.invoke(core.clj:632)
at clojure.core$load_libs.doInvoke(core.clj:5749)
at clojure.lang.RestFn.applyTo(RestFn.java:137)
at clojure.core$apply.invoke(core.clj:632)
at clojure.core$require.doInvoke(core.clj:5832)
at clojure.lang.RestFn.invoke(RestFn.java:421)
at hugsql.parser$eval202$loading__5340__auto____203.invoke(parser.clj:1)
at hugsql.parser$eval202.invoke(parser.clj:1)
at clojure.lang.Compiler.eval(Compiler.java:6782)
at clojure.lang.Compiler.eval(Compiler.java:6771)
at clojure.lang.Compiler.load(Compiler.java:7227)
... 77 more
Tests failed.

I have a table with two fields:

CREATE TABLE IF NOT EXISTS data
(id           VARCHAR(20) NOT NULL PRIMARY KEY,
 arr  INTEGER[]);

I have a method to add some data to the 'arr' field of the table:

-- :name set-arr :! :n
-- :doc updates arr
UPDATE data SET arr = :arr
WHERE id = :id

Sometimes (often) this method throws an exception with such message:

:cause "ERROR: column "arr" is of type integer[] but expression is of type jsonb\n Hint: You will need to rewrite or cast the expression.\n Position: 32"

What can cause this problem?

I have a Postgres query which looks like this:

-- :name add-thing :<! :1
update things
set count = count + 1
where id = :id
returning count;

It works fine, but when I try to make the query fn private (i.e., using :name-), it ignores the :1 and treats the result value as :raw.