/readme.md
Markdown | 291 lines | 222 code | 69 blank | 0 comment | 0 complexity | 261d684e85cb6a89fae5c7b83fa00684 MD5 | raw file
1# Lobos 2 3[](http://travis-ci.org/budu/lobos) 4 5**Lobos** is a SQL database schema manipulation and migration library 6written in [Clojure]. It currently support supports H2, MySQL, 7PostgreSQL, SQLite and SQL Server. 8 9If you have any questions, please join the [mailing list]. 10 11## Features 12 13 * A comprehensive data definition language DSL. 14 * Migrations for schema changes. 15 * An analyzer to query the schema. 16 17## Installation 18 19Lobos is available through [Clojars]. 20 21For the latest release, in Cake/Leiningen, use: 22 23#### `project.clj` 24```clojure 25:dependencies [[lobos "1.0.0-beta3"]] 26``` 27 28or in Maven: 29 30#### `pom.xml` 31```xml 32<dependency> 33 <groupId>lobos</groupId> 34 <artifactId>lobos</artifactId> 35 <version>1.0.0-beta3</version> 36</dependency> 37``` 38 39## Usage 40 41Here's a small tutorial on how to use Lobos. 42 43### Basic Example 44 45Start by creating a new project with your preferred tool. Assuming a 46[Leiningen] compatible project file, add **Lobos** and a database driver 47to the `dependencies` section: 48 49```clojure 50:dependencies [... 51 [lobos "1.0.0-beta1"] 52 [postgresql "9.1-901.jdbc4"]] 53``` 54 55Once you have your dependencies downloaded, open up a REPL and load 56these namespaces: 57 58```clojure 59(use '(lobos connectivity core schema)) 60``` 61 62You'll get warnings about some already defined function, just ignore 63this for now. 64 65Then you'll need to create a connection, the following snippet define 66one and makes it the default global connection: 67 68```clojure 69(def db 70 {:classname "org.postgresql.Driver" 71 :subprotocol "postgresql" 72 :user "test" 73 :password "test123" 74 :subname "//localhost:5432/test"}) 75 76(open-global db) 77``` 78 79You can now send DDL statements (called *actions*) directly like this: 80 81```clojure 82(create (table :users (integer :id :unique))) 83``` 84 85You can omit the connection altogether. In that case, actions will use 86the connection bound by `lobos.connectivity/with-connection` or the 87default one. 88 89```clojure 90(drop (table :users)) 91``` 92 93Now that you've tested the basics of **Lobos** at the REPL, let's try 94out a more real-world example. 95 96### Real-world Example 97 98By *real-world*, I'm not talking about a production ready database 99schema, it's about how to integrate **Lobos** into your real 100projects. We'll continue using the previously created test project, but 101this time we'll use files. In your `src/` directory, create a directory 102named `lobos` and a file called `config.clj` inside it. 103 104#### `src/lobos/config.clj` 105```clojure 106(ns lobos.config 107 (:use lobos.connectivity)) 108 109(def db 110 {:classname "org.postgresql.Driver" 111 :subprotocol "postgresql" 112 :user "test" 113 :password "test123" 114 :subname "//localhost:5432/test"}) 115 116(open-global db) 117``` 118 119Next, we'll see how to create helpers that will show the composable 120nature of the **Lobos** DSL. This part is entirely optional. 121 122Add a new file called `helpers.clj` 123 124#### `src/lobos/helpers.clj` 125```clojure 126(ns lobos.helpers 127 (:refer-clojure :exclude [bigint boolean char double float time]) 128 (:use (lobos schema))) 129``` 130 131The above namespace declaration exclude some `clojure.core` definitions 132that clashes with data-type functions, this prevents warnings from being 133shown. 134 135Every schema element definition is a simple function that return an 136intermediate representation that abstract real database schema element. 137This allow you to make your own functions (and macros) that can help 138define a database schema more concisely. 139 140Now, let's define a bunch of useful helpers: 141 142#### `src/lobos/helpers.clj` 143```clojure 144(defn surrogate-key [table] 145 (integer table :id :auto-inc :primary-key)) 146 147(defn timestamps [table] 148 (-> table 149 (timestamp :updated_on) 150 (timestamp :created_on (default (now))))) 151 152(defn refer-to [table ptable] 153 (let [cname (-> (->> ptable name butlast (apply str)) 154 (str "_id") 155 keyword)] 156 (integer table cname [:refer ptable :id :on-delete :set-null]))) 157``` 158 159The first one add a standardized surrogate key called `id` to the 160specified table. The second takes a table definition and add two 161generally useful columns to it. Finally, the third one create a 162foreign-key column to another table given its name. We can use these 163helpers in the same way as already existing column definition functions. 164 165To wrap it up, we'll add a macro that we'll use instead of the included 166`table` macro. It will help us create tables which implicitly include a 167surrogate key and the timestamp columns. 168 169#### `src/lobos/helpers.clj` 170```clojure 171(defmacro tbl [name & elements] 172 `(-> (table ~name) 173 (timestamps) 174 ~@(reverse elements) 175 (surrogate-key))) 176``` 177 178We have everything set up in place to create our first migrations, so 179let's do that. 180 181### Migrations 182 183By default all migrations are kept in the `lobos.migrations` 184namespace. It'll get automatically loaded by migration commands, so 185there's no need to load it yourself. Thus, to use another namespace you 186must change the `lobos.migration/*migrations-namespace*` dynamic 187variable. This is a normal Clojure source file so if you prefer having 188only one migration per file, just do that and require these files in the 189migrations namespace. 190 191#### `src/lobos/migrations.clj` 192```clojure 193(ns lobos.migrations 194 (:refer-clojure :exclude [alter drop 195 bigint boolean char double float time]) 196 (:use (lobos [migration :only [defmigration]] core schema 197 config helpers))) 198``` 199 200Migrations are defined using the `defmigration` macro which is composed 201of two bodies, one making whatever changes you want to do, the other 202reverting those changes. 203 204#### `src/lobos/migrations.clj` 205```clojure 206(defmigration add-users-table 207 (up [] (create 208 (tbl :users 209 (varchar :name 100 :unique) 210 (check :name (> (length :name) 1))))) 211 (down [] (drop (table :users)))) 212 213(defmigration add-posts-table 214 (up [] (create 215 (tbl :posts 216 (varchar :title 200 :unique) 217 (text :content) 218 (refer-to :users)))) 219 (down [] (drop (table :posts)))) 220 221(defmigration add-comments-table 222 (up [] (create 223 (tbl :comments 224 (text :content) 225 (refer-to :users) 226 (refer-to :posts)))) 227 (down [] (drop (table :comments)))) 228``` 229 230Each migrations must have a unique name that will be used by **Lobos** 231to figure out which ones have been applied. 232 233To apply all pending migrations, use the `migrate` function in a REPL: 234 235```clojure 236(migrate) 237;=> add-users-table 238;=> add-posts-table 239;=> add-comments-table 240``` 241 242This function can take a bunch of migration names in which case it will 243only apply those. 244 245If, for some reason, you need to rollback some migrations, use the aptly 246named `rollback` function: 247 248```clojure 249(rollback) 250;=> add-comments-table 251``` 252 253By default it will rollback only the most recently applied migration. It 254can also take migration names, an integer or the `:all` keyword and 255perform the appropriate rollback. 256 257### Analyzer 258 259Lobos includes a database analyzer which use the database meta-data or 260information schema to construct an abstract schema definition from an 261actual database schema. 262 263Here's an interactive session that show some possible uses: 264 265```clojure 266(use 'lobos.analyzer) 267;=> nil 268(-> (analyze-schema) :tables keys) 269;=> (:comments :lobos_migrations :posts :users) 270(-> (analyze-schema) :tables :users :columns :name :data-type :name) 271;=> :varchar 272(-> (analyze-schema) :tables :posts :constraints :posts_unique_title :columns) 273;=> [:title] 274``` 275 276### Documentation 277 278For more detailed information on **Lobos**, please refer to the 279[documentation]. 280 281## License 282 283Distributed under the [Eclipse Public License], the same as Clojure. 284 285 286[Clojure]: http://clojure.org/ 287[mailing list]: http://groups.google.com/group/lobos-library 288[Clojars]: http://clojars.org/lobos 289[Leiningen]: https://github.com/technomancy/leiningen 290[Eclipse Public License]: http://budu.github.com/lobos/epl-v10.html 291[documentation]: http://budu.github.com/lobos/documentation.html