1 | Migrations API - SQL
|
2 |
|
3 | Below are examples of all the different migrations supported by db-migrate. Please note that not all migrations are supported by all databases. For example, SQLite does not support dropping columns.
|
4 |
|
5 | createTable(tableName, columnSpec, callback)
|
6 |
|
7 | Creates a new table with the specified columns.
|
8 |
|
9 | Arguments
|
10 |
|
11 | tableName - the name of the table to create
|
12 | columnSpec - a hash of column definitions
|
13 | callback(err) - callback that will be invoked after table creation
|
14 | Examples
|
15 |
|
16 | // with no table options
|
17 | exports.up = function (db, callback) {
|
18 | db.createTable('pets', {
|
19 | id: { type: 'int', primaryKey: true, autoIncrement: true },
|
20 | name: 'string' // shorthand notation
|
21 | }, callback);
|
22 | }
|
23 |
|
24 | // with table options
|
25 | exports.up = function (db, callback) {
|
26 | db.createTable('pets', {
|
27 | columns: {
|
28 | id: { type: 'int', primaryKey: true, autoIncrement: true },
|
29 | name: 'string' // shorthand notation
|
30 | },
|
31 | ifNotExists: true
|
32 | }, callback);
|
33 | }
|
34 | Column Specs
|
35 |
|
36 | The following options are available on column specs
|
37 |
|
38 | type - the column data type. Supported types can be found in db-migrate-shared/data_type.js
|
39 | length - the column data length, where supported
|
40 | primaryKey - true to set the column as a primary key. Compound primary keys are supported by setting the primaryKey option to true on multiple columns
|
41 | autoIncrement - true to mark the column as auto incrementing
|
42 | notNull - true to mark the column as non-nullable, omit it archive database default behavior and false to mark explicitly as nullable
|
43 | unique - true to add unique constraint to the column
|
44 | defaultValue - set the column default value. To set an expression (eg a function call) as the default value use this syntax: defaultValue: new String('uuid_generate_v4()')
|
45 | foreignKey - set a foreign key to the column
|
46 | Column ForeignKey Spec Examples
|
47 |
|
48 | Note: Currently only supported together with mysql!
|
49 |
|
50 | exports.up = function(db, callback) {
|
51 |
|
52 | //automatic mapping, the mapping key resolves to the column
|
53 | db.createTable( 'product_variant',
|
54 | {
|
55 | id:
|
56 | {
|
57 | type: 'int',
|
58 | unsigned: true,
|
59 | notNull: true,
|
60 | primaryKey: true,
|
61 | autoIncrement: true,
|
62 | length: 10
|
63 | },
|
64 | product_id:
|
65 | {
|
66 | type: 'int',
|
67 | unsigned: true,
|
68 | length: 10,
|
69 | notNull: true,
|
70 | foreignKey: {
|
71 | name: 'product_variant_product_id_fk',
|
72 | table: 'product',
|
73 | rules: {
|
74 | onDelete: 'CASCADE',
|
75 | onUpdate: 'RESTRICT'
|
76 | },
|
77 | mapping: 'id'
|
78 | }
|
79 | },
|
80 | }, callback );
|
81 | };
|
82 |
|
83 | exports.up = function(db, callback) {
|
84 |
|
85 | //explicit mapping
|
86 | db.createTable( 'product_variant',
|
87 | {
|
88 | id:
|
89 | {
|
90 | type: 'int',
|
91 | unsigned: true,
|
92 | notNull: true,
|
93 | primaryKey: true,
|
94 | autoIncrement: true,
|
95 | length: 10
|
96 | },
|
97 | product_id:
|
98 | {
|
99 | type: 'int',
|
100 | unsigned: true,
|
101 | length: 10,
|
102 | notNull: true,
|
103 | foreignKey: {
|
104 | name: 'product_variant_product_id_fk',
|
105 | table: 'product',
|
106 | rules: {
|
107 | onDelete: 'CASCADE',
|
108 | onUpdate: 'RESTRICT'
|
109 | },
|
110 | mapping: {
|
111 | product_id: 'id'
|
112 | }
|
113 | }
|
114 | },
|
115 | }, callback );
|
116 | };
|
117 | dropTable(tableName, [options,] callback)
|
118 |
|
119 | Drop a database table
|
120 |
|
121 | Arguments
|
122 |
|
123 | tableName - name of the table to drop
|
124 | options - table options
|
125 | callback(err) - callback that will be invoked after dropping the table
|
126 | Table Options
|
127 |
|
128 | ifExists - Only drop the table if it already exists
|
129 | renameTable(tableName, newTableName, callback)
|
130 |
|
131 | Rename a database table
|
132 |
|
133 | Arguments
|
134 |
|
135 | tableName - existing table name
|
136 | options - new table name
|
137 | callback(err) - callback that will be invoked after renaming the table
|
138 | addColumn(tableName, columnName, columnSpec, callback)
|
139 |
|
140 | Add a column to a database table
|
141 |
|
142 | Arguments
|
143 |
|
144 | tableName - name of table to add a column to
|
145 | columnName - name of the column to add
|
146 | columnSpec - a hash of column definitions
|
147 | callback(err) - callback that will be invoked after adding the column
|
148 | Column spec is the same as that described in createTable
|
149 |
|
150 | removeColumn(tableName, columnName, callback)
|
151 |
|
152 | Remove a column from an existing database table
|
153 |
|
154 | tableName - name of table to remove a column from
|
155 | columnName - name of the column to remove
|
156 | callback(err) - callback that will be invoked after removing the column
|
157 | renameColumn(tableName, oldColumnName, newColumnName, callback)
|
158 |
|
159 | Rename a column
|
160 |
|
161 | Arguments
|
162 |
|
163 | tableName - table containing column to rename
|
164 | oldColumnName - existing column name
|
165 | newColumnName - new name of the column
|
166 | callback(err) - callback that will be invoked after renaming the column
|
167 | changeColumn(tableName, columnName, columnSpec, callback)
|
168 |
|
169 | Change the definition of a column
|
170 |
|
171 | Arguments
|
172 |
|
173 | tableName - table containing column to change
|
174 | columnName - existing column name
|
175 | columnSpec - a hash containing the column spec
|
176 | callback(err) - callback that will be invoked after changing the column
|
177 | addIndex(tableName, indexName, columns, [unique], callback)
|
178 |
|
179 | Add an index
|
180 |
|
181 | Arguments
|
182 |
|
183 | tableName - table to add the index too
|
184 | indexName - the name of the index
|
185 | columns - an array of column names contained in the index
|
186 | unique - whether the index is unique (optional, default false)
|
187 | callback(err) - callback that will be invoked after adding the index
|
188 | addForeignKey
|
189 |
|
190 | Adds a foreign Key
|
191 |
|
192 | Arguments
|
193 |
|
194 | tableName - table on which the foreign key gets applied
|
195 | referencedTableName - table where the referenced key is located
|
196 | keyName - name of the foreign key
|
197 | fieldMapping - mapping of the foreign key to referenced key
|
198 | rules - ondelete, onupdate constraints
|
199 | callback(err) - callback that will be invoked after adding the foreign key
|
200 | Example
|
201 |
|
202 | exports.up = function (db, callback)
|
203 | {
|
204 | db.addForeignKey('module_user', 'modules', 'module_user_module_id_foreign',
|
205 | {
|
206 | 'module_id': 'id'
|
207 | },
|
208 | {
|
209 | onDelete: 'CASCADE',
|
210 | onUpdate: 'RESTRICT'
|
211 | }, callback);
|
212 | };
|
213 | removeForeignKey
|
214 |
|
215 | Arguments
|
216 |
|
217 | tableName - table in which the foreign key should be deleted
|
218 | keyName - the name of the foreign key
|
219 | options - object of options, see below
|
220 | callback - callback that will be invoked once the foreign key was deleted
|
221 | Options
|
222 |
|
223 | dropIndex (default: false) - deletes the index with the same name as the foreign key
|
224 | Examples
|
225 |
|
226 | //without options object
|
227 | exports.down = function (db, callback)
|
228 | {
|
229 | db.removeForeignKey('module_user', 'module_user_module_id_foreign', callback);
|
230 | };
|
231 |
|
232 | //with options object
|
233 | exports.down = function (db, callback)
|
234 | {
|
235 | db.removeForeignKey('module_user', 'module_user_module_id_foreign',
|
236 | {
|
237 | dropIndex: true,
|
238 | }, callback);
|
239 | };
|
240 | insert(tableName, columnNameArray, valueArray, callback)
|
241 |
|
242 | Insert an item into a given column
|
243 |
|
244 | Arguments
|
245 |
|
246 | tableName - table to insert the item into
|
247 | columnNameArray - the array existing column names for each item being inserted
|
248 | valueArray - the array of values to be inserted into the associated column
|
249 | callback(err) - callback that will be invoked once the insert has been completed.
|
250 | removeIndex([tableName], indexName, callback)
|
251 |
|
252 | Remove an index
|
253 |
|
254 | Arguments
|
255 |
|
256 | tableName - name of the table that has the index (Required for mySql)
|
257 | indexName - the name of the index
|
258 | callback(err) - callback that will be invoked after removing the index
|
259 | runSql(sql, [params,] callback)
|
260 |
|
261 | Run arbitrary SQL
|
262 |
|
263 | Arguments
|
264 |
|
265 | sql - the SQL query string, possibly with ? replacement parameters
|
266 | params - zero or more ? replacement parameters
|
267 | callback(err) - callback that will be invoked after executing the SQL
|
268 | all(sql, [params,] callback)
|
269 |
|
270 | Execute a select statement, even in dry run mode. Attention, only use this if you know what you're doing. This can cause you issues if you're utilizing the dry-run mode for testings. To execute sql queries always use runSql!
|
271 |
|
272 | Arguments
|
273 |
|
274 | sql - the SQL query string, possibly with ? replacement parameters
|
275 | params - zero or more ? replacement parameters
|
276 | callback(err, results) - callback that will be invoked after executing the SQL |
\ | No newline at end of file |