dbxray provides metadata about JDBC databases and uses that data to generate specs/schemas that you can copy into your application. It can save you some time if you're just getting started with spec, malli, or plumatic schema.
Here's the kind of metadata it produces:
(ns donut.dbxray.todo-list
(:require
[donut.dbxray :as dbx]
[next.jdbc :as jdbc])
(:import
(io.zonky.test.db.postgres.embedded EmbeddedPostgres)))
(defonce embedded-pg (delay (EmbeddedPostgres/start)))
(defonce conn (delay (jdbc/get-connection (.getPostgresDatabase ^EmbeddedPostgres @embedded-pg))))
;; Given these tables:
;; (NOTE: actual table creation is not shown in this example)
(def create-users
(str
"CREATE TABLE users ("
" id serial PRIMARY KEY, "
" username varchar(256) NOT NULL UNIQUE "
")"))
(def create-todo-lists
(str
"CREATE TABLE todo_lists ("
" id serial PRIMARY KEY, "
" user_id integer NOT NULL, "
" name varchar(256) NOT NULL, "
" FOREIGN KEY(user_id)"
" REFERENCES users(id)"
")"))
(def create-todos
(str
"CREATE TABLE todos ("
" id serial PRIMARY KEY, "
" todo_list_id integer NOT NULL, "
" description varchar(256) NOT NULL, "
" FOREIGN KEY(todo_list_id)"
" REFERENCES todo_lists(id)"
")"))
;; dbxray produces this metadata:
(dbxray/xray @conn {:include-raw? true}) ;; =>
{:tables
{:users
{:columns {:id {:column-type :integer
:raw {...}
:primary-key? true
:unique? true
:autoincrement? true}
:username {:column-type :varchar
:raw {...}
:unique? true}}
:column-order [:id :username]}
:todo_lists
{:columns {:id {:column-type :integer
:raw {...}
:primary-key? true
:unique? true
:autoincrement? true}
:user_id {:column-type :integer
:raw {...}
:refers-to [:users :id]}
:name {:column-type :varchar
:raw {...}}}
:column-order [:id :user_id :name]}
:todos
{:columns {:id {:column-type :integer
:raw {...}
:primary-key? true
:unique? true
:autoincrement? true}
:todo_list_id {:column-type :integer
:raw {...}
:refers-to [:todo_lists :id]}
:description {:column-type :varchar
:raw {...}}}
:column-order [:id :todo_list_id :description]}}
:table-order
[:users :todo_lists :todos]}Note that you must pass in {:include-raw? true} as the second argument to
xray to include raw metata. :raw has been been elided
to keep the example focused.
You can generate basic specs or schemas from this metadata:
(require '[donut.dbxray :as dbx])
(def xray (dbxray/xray connection))
(dbx/malli-schema xray)
;; =>
[(def User
[:map
[:users/id {:optional? false} pos-int?]
[:users/username {:optional? false} string?]])
(def TodoList
[:map
[:todo_lists/id {:optional? false} pos-int?]
[:todo_lists/user_id {:optional? false} pos-int?]
[:todo_lists/name {:optional? false} string?]])
(def Todo
[:map
[:todos/id {:optional? false} pos-int?]
[:todos/todo_list_id {:optional? false} pos-int?]
[:todos/description {:optional? false} string?]])]
(dbx/clojure-spec xray)
;; =>
[(s/def :users/id pos-int?)
(s/def :users/username string?)
(s/def :record/user (s/keys :req [:users/id :users/username]))
(s/def :todo_lists/id pos-int?)
(s/def :todo_lists/user_id pos-int?)
(s/def :todo_lists/name string?)
(s/def :record/todo_list
(s/keys :req [:todo_lists/id :todo_lists/user_id :todo_lists/name]))
(s/def :todos/id pos-int?)
(s/def :todos/todo_list_id pos-int?)
(s/def :todos/description string?)
(s/def :record/todo
(s/keys :req [:todos/id :todos/todo_list_id :todos/description]))]
(dbx/plumatic-schema xray)
;; =>
[(s/defschema User
{(s/required-key :users/id) s/Int
(s/required-key :users/username) s/Str})
(s/defschema TodoList
{(s/required-key :todo_lists/id) s/Int
(s/required-key :todo_lists/user_id) s/Int
(s/required-key :todo_lists/name) s/Str})
(s/defschema Todo
{(s/required-key :todos/id) s/Int
(s/required-key :todos/todo_list_id) s/Int
(s/required-key :todos/description) s/Str})]
(dbx/datapotato-schema xray)
;; =>
{:users {:prefix :u}
:todo_lists {:prefix :tl
:relations #:todo_lists{:user_id [:users :users/id]}}
:todos {:prefix :t
:relations #:todos{:todo_list_id [:todo_lists :todo_lists/id]}}}If you run this in a REPL you can just copy and paste the results into your source code.
The library has some utility in its current state but has a lot of room for improvement. I'd love feedback / collaboration on:
- How to represent the metadata. I'm not a database expert and am unsure whether the approach taken might lead to problems.
- Adapter implementations. Every vendor has different internal representations
for their metadata, and dbxray attempts to provide a consistent Clojure
representation. The translation between vendor and Clojure happens via the
adapter*multimethod, and I've only implemented a couple so far.
Join the #donut channel in Clojurians slack for discussion
Thanks to Sean Corfield for his work on next-jdbc, which this relies on which helped me understand how to interact with JDBC.