Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
Newer
Older
100644 306 lines (224 sloc) 10.81 kB
5e9ab11 @rsandor First commit
authored
1 Migrate - A database agnostic migration system for Node.js
2 ================================================================================
3
4 By Ryan Sandor Richards
5
63f28a1 Update READMEs to reflect SQLite 3 support.
Curtis Schlak authored
6 SQLite 3 Contribution by Curtis Schlak
7
5e9ab11 @rsandor First commit
authored
8 Introduction
9 --------------------------------------------------------------------------------
3b98b6e @rsandor Updated the system to use the new version of node-mysql (via npm) and…
authored
10 Migrate is a tool that allows you to define database schema migrations with
11 javascript. It borrows very heavily from the ruby migration system and contains
12 many of the same features. If you are unfamiliar with how migrations work don't
13 fret, just read on and everything will be explained!
5e9ab11 @rsandor First commit
authored
14
15 Requirements
16 --------------------------------------------------------------------------------
17 1. Node.js - http://github.com/ry/node
3b98b6e @rsandor Updated the system to use the new version of node-mysql (via npm) and…
authored
18 2. node-mysql - https://github.com/felixge/node-mysql
63f28a1 Update READMEs to reflect SQLite 3 support.
Curtis Schlak authored
19 3. node-sqlite3 - https://github.com/developmentseed/node-sqlite3
5e9ab11 @rsandor First commit
authored
20
63f28a1 Update READMEs to reflect SQLite 3 support.
Curtis Schlak authored
21 Please note that at the current time we only support MySQL and SQLite 3 but
22 other DBMS' are on their way (next up: Postgres).
5e9ab11 @rsandor First commit
authored
23
24 Installation
25 --------------------------------------------------------------------------------
26 1. Download the Migrate source - http://github.com/rsandor/node-migrate
27 2. Fill out the supplied "config.js"
28
29 Where, exactly, you include the migrate source in your project matters very
30 little as long as both migrate.js and config.js are in the same directory and
31 the node-mysql and migration paths in the configuration file are relative to
32 the directory where the migrate.js file resides.
33
6ac9435 @rsandor Updates to the readme files, added more detailed information about co…
authored
34 The configuration file has the following keys:
35
36 * `dbms` - The database management system to use (currently we only support a
63f28a1 Update READMEs to reflect SQLite 3 support.
Curtis Schlak authored
37 a value of either `'mysql'` or `'sqlite3'`).
6ac9435 @rsandor Updates to the readme files, added more detailed information about co…
authored
38 * `migration_path` - Relative path to the directory in which migrations are
39 stored.
3b98b6e @rsandor Updated the system to use the new version of node-mysql (via npm) and…
authored
40 * `mysql` - Just a simple client configuration for mysql. Fill out your username,
41 password, and any other information needed to connect to the database via
42 node-mysql's `Client` class.
63f28a1 Update READMEs to reflect SQLite 3 support.
Curtis Schlak authored
43 * `sqlite3` - Just a simple client configuration for SQLite 3. Fill out the
44 filename for the database to which you would like to connect.
6ac9435 @rsandor Updates to the readme files, added more detailed information about co…
authored
45
5e9ab11 @rsandor First commit
authored
46 How do I use migrate?
47 --------------------------------------------------------------------------------
48 Once you have the configuration file filled you you can create a new migration
49 from the command line:
50
036dd24 @rsandor Fixed a couple markdown issues.
authored
51 `node migrate.js create create_users_table`
5e9ab11 @rsandor First commit
authored
52
53 This command will create a blank migration and stick it in the migrations folder
54 that you supplied in the configuration file. Once you fill out the migration's
55 up and down functions you can then apply the migration to your schema like so:
56
036dd24 @rsandor Fixed a couple markdown issues.
authored
57 `node migrate.js migrate`
5e9ab11 @rsandor First commit
authored
58
59 That command will determine if there are any migrations that have not been
60 applied and apply them sequentially until they are all done or one of them
61 fails.
62
d2f35fc @rsandor More markdown fixes.
authored
63 If you wish to roll back any migrations that's super simple too, just use:
5e9ab11 @rsandor First commit
authored
64
036dd24 @rsandor Fixed a couple markdown issues.
authored
65 `node migrate.js rollback`
5e9ab11 @rsandor First commit
authored
66
67 By default this will roll back only a single migration, but you can provide
68 a numeric parameter to tell it how many migrations you'd like it to roll back.
69 For instance, here's how you would roll back five migrations:
70
036dd24 @rsandor Fixed a couple markdown issues.
authored
71 `node migrate.js rollback 5`
5e9ab11 @rsandor First commit
authored
72
73 What is a migration?
74 --------------------------------------------------------------------------------
75 A migration is a programmatic way of defining incremental database schema
76 changes. It has an "up" method for describing how to apply the changes, and a
77 "down" method for removing them. Here is an example migration:
f360bdf @sodoku made markdown documentation readable
sodoku authored
78
aecb0aa @rsandor More markdown fixes.
authored
79 var create_users_table = new Migration({
80 up: function() {
81 this.create_table('users', function(t) {
82 t.integer('id');
83 t.string('email');
84 t.string('password');
85 t.primary_key('id');
86 });
87 },
88 down: function() {
89 this.drop_table('users');
90 }
91 });
f360bdf @sodoku made markdown documentation readable
sodoku authored
92
5e9ab11 @rsandor First commit
authored
93 In the above migration the "up" function creates a table named "users" with
94 three fields (id, email, and password) and a primary key on id. The "down"
95 function reverses these changes and simply drops the entire "users" table.
96
97 When you run the migration it gets converted into a collection of database
98 agnostic objects which are then translated into SQL for the appropriate DBMS.
99
100 What can I do in a migration?
101 --------------------------------------------------------------------------------
102 The "up" and "down" methods of a migration support the exact same set of methods
103 . This means you can create and destroy schema information in both methods. The
104 Migration object supports the following methods:
105
106 ### create_table(name, body)
107
108 This method creates a table with the given name and passes the newly created table
109 representation to the supplied `body` closure.
110 From within the body closure one can execute methods on the table to add columns
111 and indices. Here is a complete list of all the "table" methods available:
112
113 * `t.column(name, type, options)` - Creates a column with the given name, type
114 and additional options. Additional options include: `limit`, `not_null`,
115 `precision`, `scale`, and `default_value`. `limit` controls the number of
116 bytes to use for the integer type, `not_null` is used to determine if the
117 column is allowed to be null, `precision` and `scale` are used for the
118 decimal data type, and `default_value` allows you to set the default value
119 for the column.
120 * `t.primary_key(name)` - Sets the primary key for the table to the column with
121 the given name.
122 * `t.index(name)` - Sets an index on the table for the column with the given name
123
124 Finally the body also contains shortcut functions for each abstract data-type
125 tracked by Migrate. Each function has the form `t.type(name, options)` where name
126 and options are as explained in the `t.column` method. Here's a complete list:
127
128 * `string`, `text`, `integer`, `float`, `decimal`, `datetime`, `timestamp`, `time`,
129 `date`, `binary`, `boolean`
130
131 Example:
f360bdf @sodoku made markdown documentation readable
sodoku authored
132
aecb0aa @rsandor More markdown fixes.
authored
133 this.create_table('high_scores', function(t) {
134 t.integer('id');
135 t.string('name', {limit: 32});
136 t.create('score', 'integer', {limit: 8})
137 t.datetime('date');
138 t.primary_key('id');
139 t.index('name');
140 });
f360bdf @sodoku made markdown documentation readable
sodoku authored
141
5e9ab11 @rsandor First commit
authored
142 Producing SQL:
f360bdf @sodoku made markdown documentation readable
sodoku authored
143
aecb0aa @rsandor More markdown fixes.
authored
144 CREATE TABLE high_scores (
145 id INT,
146 name VARCHAR(32),
147 score BIGINT,
148 date DATETIME,
149 PRIMARY KEY (id),
150 INDEX (name)
151 );
5e9ab11 @rsandor First commit
authored
152
153 ### drop_table(name)
154 Simply drops a table from the schema. Example:
155
d2f35fc @rsandor More markdown fixes.
authored
156 `this.drop_table('high_scores');`
5e9ab11 @rsandor First commit
authored
157
861bb76 @rsandor Minor fixes to the create migration routine and the configuration file.
authored
158 Producing SQL:
5e9ab11 @rsandor First commit
authored
159
d2f35fc @rsandor More markdown fixes.
authored
160 `DROP_TABLE high_scores;`
5e9ab11 @rsandor First commit
authored
161
162 ### rename_table(old_name, new_name)
163 Renames a table. Example:
164
d2f35fc @rsandor More markdown fixes.
authored
165 `this.rename_table('high_scores', 'all_time_high_scores');`
5e9ab11 @rsandor First commit
authored
166
167 Producing SQL:
168
d2f35fc @rsandor More markdown fixes.
authored
169 `RENAME TABLE high_scores TO all_time_high_scores;`
5e9ab11 @rsandor First commit
authored
170
171 ### change_table(name, body)
172
173 Has all of the same functionality as `create_table` except it is used to
174 modify existing tables and adds the following functionality to body method:
175
176 * `t.rename(old_name, new_column)` - Renames and alters a column.
177 * `t.change(name, type, options)` - Alters a column without changing its name.
178 * `t.remove(name)` - Removes a column from the table.
179 * `t.remove_index(name)` - Removes an index from the table.
180 * `t.remove_primary_key()` - Removes a primary key from the table.
181
182 Example:
f360bdf @sodoku made markdown documentation readable
sodoku authored
183
aecb0aa @rsandor More markdown fixes.
authored
184 this.change_table('all_time_high_scores', function(t) {
185 t.remove_index('name');
186 t.remove_primary_key();
187 t.remove('date');
188 t.date('date');
189 t.rename('score' {
190 name: 'high_score',
191 type: 'integer',
192 limit: 4
193 });
194 t.change('name', 'string' {limit: 128});
5e9ab11 @rsandor First commit
authored
195 });
f360bdf @sodoku made markdown documentation readable
sodoku authored
196
5e9ab11 @rsandor First commit
authored
197 Producing SQL:
f360bdf @sodoku made markdown documentation readable
sodoku authored
198
aecb0aa @rsandor More markdown fixes.
authored
199 ALTER TABLE all_time_high_scores
200 DROP INDEX (name),
201 DROP PRIMARY KEY,
202 DROP COLUMN 'date',
203 ADD COLUMN date DATE,
204 CHANGE COLUMN score high_score INT,
205 MODIFY COLUMN name VARCHAR(128);
5e9ab11 @rsandor First commit
authored
206
207 ### add_column(table_name, column_name, type, options)
208 Adds a column to a table. Example:
209
d2f35fc @rsandor More markdown fixes.
authored
210 `this.add_column('all_time_high_scores', 'comment', 'string', {limit: 512});`
5e9ab11 @rsandor First commit
authored
211
212 Producing SQL:
213
d2f35fc @rsandor More markdown fixes.
authored
214 `ALTER TABLE all_time_high_scores ADD COLUMN comment VARCHAR(512);`
5e9ab11 @rsandor First commit
authored
215
216 ### rename_column(table_name, column_name, new_column)
217 Renames and modifies a column in a table. Example:
f360bdf @sodoku made markdown documentation readable
sodoku authored
218
aecb0aa @rsandor More markdown fixes.
authored
219 this.rename('all_time_high_scores', 'high_score', {
220 name: 'score',
221 type: 'integer',
222 limit: 8
223 });
f360bdf @sodoku made markdown documentation readable
sodoku authored
224
5e9ab11 @rsandor First commit
authored
225 Producing SQL:
f360bdf @sodoku made markdown documentation readable
sodoku authored
226
227 `ALTER TABLE all_time_high_scores CHANGE COLUMN high_score score BIGINT;`
5e9ab11 @rsandor First commit
authored
228
229 ### change_column(table_name, column_name, type, options)
230 Changes a column's definition. Example:
231
d2f35fc @rsandor More markdown fixes.
authored
232 `this.change_column('all_time_high_scores', 'comment', 'text');`
5e9ab11 @rsandor First commit
authored
233
234 Producing SQL:
235
d2f35fc @rsandor More markdown fixes.
authored
236 `ALTER TABLE all_time_high_scores MODIFY COLUMN comment TEXT;`
5e9ab11 @rsandor First commit
authored
237
238 ### remove_column(table_name, column_name)
239 Removes a column from a table. Example:
240
d2f35fc @rsandor More markdown fixes.
authored
241 `this.remove_column('all_time_high_scores', 'date');`
5e9ab11 @rsandor First commit
authored
242
243 Producing SQL:
244
d2f35fc @rsandor More markdown fixes.
authored
245 `ALTER TABLE all_time_high_scores DROP COLUMN date;`
5e9ab11 @rsandor First commit
authored
246
247 ### add_index(table_name, column_name, options)
248 Adds an index to a table. Example:
249
d2f35fc @rsandor More markdown fixes.
authored
250 `this.add_index('all_time_high_scores', 'id');`
5e9ab11 @rsandor First commit
authored
251
252 Producing SQL:
253
d2f35fc @rsandor More markdown fixes.
authored
254 `ALTER TABLE all_time_high_scores ADD INDEX (id);`
5e9ab11 @rsandor First commit
authored
255
256 ### remove_index(table_name, index_name)
257 Removes an index from a table. Example:
258
d2f35fc @rsandor More markdown fixes.
authored
259 `this.remove_index('id');`
5e9ab11 @rsandor First commit
authored
260
261 Producing SQL:
262
d2f35fc @rsandor More markdown fixes.
authored
263 `ALTER TABLE all_time_high_scores DROP INDEX (id);`
5e9ab11 @rsandor First commit
authored
264
265 ### execute(sql)
266 Executes arbitrary SQL. Example:
267
d2f35fc @rsandor More markdown fixes.
authored
268 `this.execute('insert into all_time_high_scores (name, score) values ('Ryan', 100000000);');`
5e9ab11 @rsandor First commit
authored
269
270 Producing SQL:
271
d2f35fc @rsandor More markdown fixes.
authored
272 `insert into all_time_high_scores (name, score) values ('Ryan', 100000000);`
5e9ab11 @rsandor First commit
authored
273
274 Outtro
275 --------------------------------------------------------------------------------
276 So that about sums it up. Simple and easy ;). It's a very early alpha version so
63f28a1 Update READMEs to reflect SQLite 3 support.
Curtis Schlak authored
277 please don't hate on only having MySQL and SQLite 3 support! If you have a
278 feature request feel free to send me a message and I'll try to get it in ASAP.
5e9ab11 @rsandor First commit
authored
279
280 Thanks!
281
282 License and Legalese
283 --------------------------------------------------------------------------------
284
285 Copyright (c) 2010 Ryan Sandor Richards
286
287 Permission is hereby granted, free of charge, to any person
288 obtaining a copy of this software and associated documentation
289 files (the "Software"), to deal in the Software without
290 restriction, including without limitation the rights to use,
291 copy, modify, merge, publish, distribute, sublicense, and/or sell
292 copies of the Software, and to permit persons to whom the
293 Software is furnished to do so, subject to the following
294 conditions:
295
296 The above copyright notice and this permission notice shall be
297 included in all copies or substantial portions of the Software.
298
299 THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
300 EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES
301 OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
302 NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
303 HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,
304 WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
305 FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
306 OTHER DEALINGS IN THE SOFTWARE.
Something went wrong with that request. Please try again.