Fork me on GitHub
#sql
<
2017-10-10
>
ricardo12:10:15

What's the best approach for Clojurric parametrized ddl? Say, if I wanted to build CREATE TABLE or CREATE INDEX statements on the fly. Wondering if there's a cleaner route than just going to clojure.java.jdbc.

donaldball12:10:51

DDL isn’t well standardized so I’m not aware of any general purpose solutions but honeysql has a postgresql extension that covers some of the bases, e.g. : https://github.com/nilenso/honeysql-postgres#create-table

bja13:10:50

In general Honeysql is easy to extend for custom statements. honeysql-postgres is a good start for postgres (covers the standard stuff (indexes, columns, tables)). It's a little tricky to know which DDL is and isn't transaction-safe in postgres since it sometimes varies between versions.

ricardo13:10:04

@bostonaholic I use HugSQL pretty much everywhere for querying, but don't see on the docs how to parametrize DDL - say, sending a table name as a parameter.

bostonaholic13:10:50

have you tried create table :table-name?

bostonaholic13:10:12

I haven’t, so I don’t know if that works or not

ricardo13:10:46

About to, needed to start a test project.

ricardo13:10:53

Doesn't work for either column or table names.

pesterhazy14:10:30

@ricardo sadly I think that your best bet is (str "create table " (your-escape-fn table-name))

pesterhazy14:10:45

note that this may induce php deja vu

ricardo14:10:07

So I may just go that route.

pesterhazy14:10:21

sweet. does it require support on the db driver side?

ricardo15:10:19

@pesterhazy Haven't tested it yet, but I'd be doing it with Postgres, so chances of support are high.

seancorfield16:10:47

Basic CREATE TABLE / DROP TABLE works on everything java.jdbc is tested against (listed in the README). Conditional DDL is not widely supported tho' (e.g., CREATE TABLE IF NOT EXISTS ...)

seancorfield16:10:24

java.jdbc has helpers for CREATE / DROP table, but not CREATE INDEX so it's down to db-do-commands and strings 😐

bja17:10:52

Dynamic DDL is a bit of a sore spot in my opinion. I haven't seen a ton of libraries do it well, and my company's solution (and that of migratus) is to stick some .sql on the resource path and load that up when necessary. The best approach I've seen is from honeysql-postgres, but that is postgres-specific given that you can easily compose it. A more generic honeysql-ddl would be do-able, but I doubt the demand is there. I would recommend trying to adapt that over string-banging if you have anything moderately complicated to do.

bja17:10:30

In particular, dynamic create tables populated by selects or similar.

seancorfield18:10:03

Any c3p0 connection pool users around? I have an odd situation where it's creating its automatic test table in one DB but not in another.