Clojurians Log v2

Is there a way to pass a raw sql string to this execute! function in the Toucan library. https://cljdoc.org/d/toucan/toucan/1.15.0/api/toucan.db#execute!

I’d like to create a table without pulling in jdbc and creating another connection to the db.

I actually have an example of the code I have so far here. https://github.com/groundedSAGE/toucan-lib-sqlite-example/blob/master/src/core.clj

@grounded_sage I'll resist expressing horror about the dynamically bound global state in Toucan and point you to HoneySQL's raw feature: https://github.com/jkk/honeysql/#raw-sql-fragments

That should allow you to pass a HoneySQL-constructed form to Toucan's execute! function.

Can you please clarify what you mean by the dynamically bound global state?

I found the raw but have been unable to construct the shape of data that passes the whole way through. Ideally I like keeping the .sql files for table generation at this point as well. As I can take advantage of text highlighting and formatting etc in editor.

Toucan uses a dynamic Var for the db connection: mutable global state. So you can't use Toucan with multiple databases in a single app easily.

user=> (require '[honeysql.core :as h])
nil
user=> (h/raw "select * from table")
#sql/raw "select * from table"
user=> (h/format (h/raw "select * from table"))
["select * from table"]
user=> 

You should be able to call (db/execute! (h/raw "your sql string here"))

(where db is your Toucan alias)

(db/execute! (sql/raw "select * from address"))

Returns this error:

#error {
 :cause "Query returns results"
 :via
 [{:type java.sql.SQLException
   :message "Query returns results"
   :at [org.sqlite.jdbc3.JDBC3PreparedStatement executeUpdate "JDBC3PreparedStatement.java" 95]}]
 :trace
 [[org.sqlite.jdbc3.JDBC3PreparedStatement executeUpdate "JDBC3PreparedStatement.java" 95]
  [clojure.java.jdbc$db_do_execute_prepared_statement$fn__6328 invoke "jdbc.clj" 1038]
  [clojure.java.jdbc$db_transaction_STAR_ invokeStatic "jdbc.clj" 806]
  [clojure.java.jdbc$db_transaction_STAR_ invoke "jdbc.clj" 776]
  [clojure.java.jdbc$db_transaction_STAR_ invokeStatic "jdbc.clj" 789]
  [clojure.java.jdbc$db_transaction_STAR_ invoke "jdbc.clj" 776]
  [clojure.java.jdbc$db_do_execute_prepared_statement invokeStatic "jdbc.clj" 1037]
  [clojure.java.jdbc$db_do_execute_prepared_statement invoke "jdbc.clj" 1031]
  [clojure.java.jdbc$db_do_prepared invokeStatic "jdbc.clj" 1069]
  [clojure.java.jdbc$db_do_prepared invoke "jdbc.clj" 1049]
  [clojure.java.jdbc$execute_BANG_$execute_helper__6402 invoke "jdbc.clj" 1453]
  [clojure.java.jdbc$execute_BANG_ invokeStatic "jdbc.clj" 1457]
  [clojure.java.jdbc$execute_BANG_ invoke "jdbc.clj" 1424]
  [toucan.db$execute_BANG_ invokeStatic "db.clj" 424]
  [toucan.db$execute_BANG_ doInvoke "db.clj" 419]
  [clojure.lang.RestFn invoke "RestFn.java" 410]
  [core$eval12288 invokeStatic "NO_SOURCE_FILE" 1]
  [core$eval12288 invoke "NO_SOURCE_FILE" 1]
  [clojure.lang.Compiler eval "Compiler.java" 7177]
  [clojure.lang.Compiler eval "Compiler.java" 7132]
  [clojure.core$eval invokeStatic "core.clj" 3214]
  [clojure.core$eval invoke "core.clj" 3210]
  [clojure.main$repl$read_eval_print__9086$fn__9089 invoke "main.clj" 437]
  [clojure.main$repl$read_eval_print__9086 invoke "main.clj" 437]
  [clojure.main$repl$fn__9095 invoke "main.clj" 458]
  [clojure.main$repl invokeStatic "main.clj" 458]
  [clojure.main$repl doInvoke "main.clj" 368]
  [clojure.lang.RestFn invoke "RestFn.java" 1523]
  [nrepl.middleware.interruptible_eval$evaluate invokeStatic "interruptible_eval.clj" 79]
  [nrepl.middleware.interruptible_eval$evaluate invoke "interruptible_eval.clj" 55]
  [nrepl.middleware.interruptible_eval$interruptible_eval$fn__935$fn__939 invoke "interruptible_eval.clj" 142]
  [clojure.lang.AFn run "AFn.java" 22]
  [nrepl.middleware.session$session_exec$main_loop__1036$fn__1040 invoke "session.clj" 171]
  [nrepl.middleware.session$session_exec$main_loop__1036 invoke "session.clj" 170]
  [clojure.lang.AFn run "AFn.java" 22]
  [java.lang.Thread run "Thread.java" 830]]}

You should use db/query for a query. db/execute! is apparently only for DDL.

Oh okay. Well actually I am trying to create tables. But I’m just trying to get something working where I pass Honey format first lol

(! 798)-> clj -Sdeps '{:deps {toucan {:mvn/version "RELEASE"} honeysql {:mvn/version "RELEASE"} org.xerial/sqlite-jdbc {:mvn/version "3.28.0"}}}'
Clojure 1.10.1
user=> (require '[toucan.db :as db] '[toucan.models :as models :refer [defmodel]] '[honeysql.core :as sql])
nil
user=> (def db2 {:dbtype "sqlite" :dbname "example"})
#'user/db2
user=> (db/set-default-db-connection! db2)
{:dbtype "sqlite", :dbname "example"}
user=> (db/execute! (sql/raw "create table foo ( id int, name varchar(256) )"))
[0]
user=> (defmodel Foo :foo)
#'user/Foo
user=> (db/insert! Foo {:id 1 :name "Sean"})
#user.FooInstance{:id 1, :name "Sean"}
user=> (db/insert! Foo {:id 2 :name "Wade"})
#user.FooInstance{:id 2, :name "Wade"}
user=> (db/select Foo)
[#user.FooInstance{:id 1, :name "Sean"} #user.FooInstance{:id 2, :name "Wade"}]
user=> (db/query (sql/raw "select * from foo where id = 2"))
({:id 2, :name "Wade"})
user=> 

(that's how I found out that db/execute! doesn't work for selects, in my previous REPL session before this one)

I see

It seems strange to me that this is not allowed. (db/execute! {:s "create table foo ( id int, name varchar(256) )"})

because the string is tagged?

HoneySQL uses a record behind the scenes for raw SQL so that it can dispatch on type.

user=> (db/query (honeysql.types/->SqlRaw "select * from foo where id = 2"))
({:id 2, :name "Wade"})
user=> 

Here's how I figured that out

user=> (sql/raw "select * from foo where id = 2")
#sql/raw "select * from foo where id = 2"
user=> (type *1)
honeysql.types.SqlRaw

You can use the tag instead of the call if you prefer:

user=> (db/query #sql/raw "select * from foo where id = 2")
({:id 2, :name "Wade"})
user=> 

Sorry I was playing around in the repl.

Yea the tag works. But I was also trying to pull in the code from an sql file.

So it would look something like this. (db/execute! #sql/raw [(get-sql "sql/users.sql")])

You can only use the tag with a literal. You have to use the call for an expression

user=> (def sql-from-file "select * from foo where id = 1")
#'user/sql-from-file
user=> (db/query (sql/raw sql-from-file))
({:id 1, :name "Sean"})
user=> 

(db/execute! (sql/raw (get-sql "sql/users.sql"))) should work.

So there doesn’t seem to be any way around having the extra dependency for reading and creating the tables as an external resource.

What "extra dependency"?

My REPL session used Toucan, HoneySQL (probably not needed -- I bet Toucan already includes it), and the SQLite JDBC driver. Nothing else.

Yup, just confirmed, all of the above works with just clj -Sdeps '{:deps {toucan {:mvn/version "RELEASE"} org.xerial/sqlite-jdbc {:mvn/version "3.28.0"}}}'

Yea it includes it. Which is why I was trying to find a way to avoid another dependency.

I’m not sure how to reach in and get the raw though.

Exactly like I did in that REPL session. I'm not sure what you're asking otherwise?

Are you complaining about the need to require HoneySQL so you can call sql/raw?

It isn't a "dependency" because HoneySQL is already pulled in via Toucan -- you're not adding anything to your project.

Oh wow. I actually had no idea I could use it that way without explicitely having it in my dependencies…

I knew there had to be a way to tap into the lower level libraries but didn’t know how.. 😅