Fork me on GitHub
#sql
<
2020-02-21
>
grounded_sage19:02:01

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!

grounded_sage19:02:38

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

seancorfield19:02:26

@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

🙃 4
grounded_sage19:02:40

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

grounded_sage19:02:41

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.

seancorfield20:02:21

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.

seancorfield20:02:23

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=> 

seancorfield20:02:15

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

seancorfield20:02:27

(where db is your Toucan alias)

grounded_sage20:02:11

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

grounded_sage20:02:26

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]]}

seancorfield20:02:28

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

grounded_sage20:02:24

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

seancorfield20:02:33

(! 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=> 

seancorfield20:02:14

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

grounded_sage20:02:46

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

grounded_sage20:02:59

because the string is tagged?

seancorfield20:02:34

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

seancorfield20:02:05

user=> (db/query (honeysql.types/->SqlRaw "select * from foo where id = 2"))
({:id 2, :name "Wade"})
user=> 
The record type is honeysql.types.SqlRaw but if you use sql/raw you don't need to worry about that.

seancorfield20:02:31

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

seancorfield20:02:31

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=> 

grounded_sage20:02:51

Sorry I was playing around in the repl.

grounded_sage20:02:12

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

grounded_sage20:02:15

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

seancorfield20:02:26

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=> 

seancorfield20:02:09

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

👍 4
grounded_sage21:02:56

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

seancorfield21:02:03

What "extra dependency"?

seancorfield21:02:03

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

seancorfield21:02:33

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"}}}'

grounded_sage23:02:43

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

grounded_sage23:02:13

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

seancorfield23:02:47

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

seancorfield23:02:34

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

seancorfield23:02:03

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

👍 4
❤️ 4
grounded_sage23:02:13

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

grounded_sage23:02:37

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

4
seancorfield19:02:58

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