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 |
|
15 | Examples
|
16 |
|
17 | ```js
|
18 | // with no table options
|
19 | exports.up = function (db, callback) {
|
20 | db.createTable('pets', {
|
21 | id: { type: 'int', primaryKey: true, autoIncrement: true },
|
22 | name: 'string' // shorthand notation
|
23 | }, callback);
|
24 | }
|
25 |
|
26 | // with table options
|
27 | exports.up = function (db, callback) {
|
28 | db.createTable('pets', {
|
29 | columns: {
|
30 | id: { type: 'int', primaryKey: true, autoIncrement: true },
|
31 | name: 'string' // shorthand notation
|
32 | },
|
33 | ifNotExists: true
|
34 | }, callback);
|
35 | }
|
36 | ```
|
37 | ```js
|
38 | //data_type list:
|
39 | module.exports = {
|
40 | CHAR: 'char',
|
41 | STRING: 'string',
|
42 | TEXT: 'text',
|
43 | SMALLINT: 'smallint',
|
44 | BIGINT: 'bigint',
|
45 | INTEGER: 'int',
|
46 | SMALL_INTEGER: 'smallint',
|
47 | BIG_INTEGER: 'bigint',
|
48 | REAL: 'real',
|
49 | DATE: 'date',
|
50 | DATE_TIME: 'datetime',
|
51 | TIME: 'time',
|
52 | BLOB: 'blob',
|
53 | TIMESTAMP: 'timestamp',
|
54 | BINARY: 'binary',
|
55 | BOOLEAN: 'boolean',
|
56 | DECIMAL: 'decimal'
|
57 | };
|
58 | ```
|
59 |
|
60 | Column Specs
|
61 |
|
62 | The following options are available on column specs
|
63 |
|
64 | type - the column data type. Supported types can be found in db-migrate-shared/data_type.js
|
65 | length - the column data length, where supported
|
66 | 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
|
67 | autoIncrement - true to mark the column as auto incrementing
|
68 | notNull - true to mark the column as non-nullable, omit it archive database default behavior and false to mark explicitly as nullable
|
69 | unique - true to add unique constraint to the column
|
70 | 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()')
|
71 | foreignKey - set a foreign key to the column
|
72 | Column ForeignKey Spec Examples
|
73 |
|
74 | Note: Currently only supported together with mysql!
|
75 |
|
76 | ```js
|
77 | exports.up = function(db, callback) {
|
78 |
|
79 | //automatic mapping, the mapping key resolves to the column
|
80 | db.createTable( 'product_variant',
|
81 | {
|
82 | id:
|
83 | {
|
84 | type: 'int',
|
85 | unsigned: true,
|
86 | notNull: true,
|
87 | primaryKey: true,
|
88 | autoIncrement: true,
|
89 | length: 10
|
90 | },
|
91 | product_id:
|
92 | {
|
93 | type: 'int',
|
94 | unsigned: true,
|
95 | length: 10,
|
96 | notNull: true,
|
97 | foreignKey: {
|
98 | name: 'product_variant_product_id_fk',
|
99 | table: 'product',
|
100 | rules: {
|
101 | onDelete: 'CASCADE',
|
102 | onUpdate: 'RESTRICT'
|
103 | },
|
104 | mapping: 'id'
|
105 | }
|
106 | },
|
107 | }, callback );
|
108 | };
|
109 |
|
110 | exports.up = function(db, callback) {
|
111 |
|
112 | //explicit mapping
|
113 | db.createTable( 'product_variant',
|
114 | {
|
115 | id:
|
116 | {
|
117 | type: 'int',
|
118 | unsigned: true,
|
119 | notNull: true,
|
120 | primaryKey: true,
|
121 | autoIncrement: true,
|
122 | length: 10
|
123 | },
|
124 | product_id:
|
125 | {
|
126 | type: 'int',
|
127 | unsigned: true,
|
128 | length: 10,
|
129 | notNull: true,
|
130 | foreignKey: {
|
131 | name: 'product_variant_product_id_fk',
|
132 | table: 'product',
|
133 | rules: {
|
134 | onDelete: 'CASCADE',
|
135 | onUpdate: 'RESTRICT'
|
136 | },
|
137 | mapping: {
|
138 | product_id: 'id'
|
139 | }
|
140 | }
|
141 | },
|
142 | }, callback );
|
143 | };
|
144 | dropTable(tableName, [options,] callback)
|
145 | ```
|
146 |
|
147 | Drop a database table
|
148 |
|
149 | Arguments
|
150 |
|
151 | tableName - name of the table to drop
|
152 | options - table options
|
153 | callback(err) - callback that will be invoked after dropping the table
|
154 | Table Options
|
155 |
|
156 | ifExists - Only drop the table if it already exists
|
157 | renameTable(tableName, newTableName, callback)
|
158 |
|
159 | Rename a database table
|
160 |
|
161 | Arguments
|
162 |
|
163 | tableName - existing table name
|
164 | options - new table name
|
165 | callback(err) - callback that will be invoked after renaming the table
|
166 | addColumn(tableName, columnName, columnSpec, callback)
|
167 |
|
168 | Add a column to a database table
|
169 |
|
170 | Arguments
|
171 |
|
172 | tableName - name of table to add a column to
|
173 | columnName - name of the column to add
|
174 | columnSpec - a hash of column definitions
|
175 | callback(err) - callback that will be invoked after adding the column
|
176 | Column spec is the same as that described in createTable
|
177 |
|
178 | removeColumn(tableName, columnName, callback)
|
179 |
|
180 | Remove a column from an existing database table
|
181 |
|
182 | tableName - name of table to remove a column from
|
183 | columnName - name of the column to remove
|
184 | callback(err) - callback that will be invoked after removing the column
|
185 | renameColumn(tableName, oldColumnName, newColumnName, callback)
|
186 |
|
187 | Rename a column
|
188 |
|
189 | Arguments
|
190 |
|
191 | tableName - table containing column to rename
|
192 | oldColumnName - existing column name
|
193 | newColumnName - new name of the column
|
194 | callback(err) - callback that will be invoked after renaming the column
|
195 | changeColumn(tableName, columnName, columnSpec, callback)
|
196 |
|
197 | Change the definition of a column
|
198 |
|
199 | Arguments
|
200 |
|
201 | tableName - table containing column to change
|
202 | columnName - existing column name
|
203 | columnSpec - a hash containing the column spec
|
204 | callback(err) - callback that will be invoked after changing the column
|
205 | addIndex(tableName, indexName, columns, [unique], callback)
|
206 |
|
207 | Add an index
|
208 |
|
209 | Arguments
|
210 |
|
211 | tableName - table to add the index too
|
212 | indexName - the name of the index
|
213 | columns - an array of column names contained in the index
|
214 | unique - whether the index is unique (optional, default false)
|
215 | callback(err) - callback that will be invoked after adding the index
|
216 | addForeignKey
|
217 |
|
218 | Adds a foreign Key
|
219 |
|
220 | Arguments
|
221 |
|
222 | tableName - table on which the foreign key gets applied
|
223 | referencedTableName - table where the referenced key is located
|
224 | keyName - name of the foreign key
|
225 | fieldMapping - mapping of the foreign key to referenced key
|
226 | rules - ondelete, onupdate constraints
|
227 | callback(err) - callback that will be invoked after adding the foreign key
|
228 | Example
|
229 |
|
230 | ```js
|
231 | exports.up = function (db, callback)
|
232 | {
|
233 | db.addForeignKey('module_user', 'modules', 'module_user_module_id_foreign',
|
234 | {
|
235 | 'module_id': 'id'
|
236 | },
|
237 | {
|
238 | onDelete: 'CASCADE',
|
239 | onUpdate: 'RESTRICT'
|
240 | }, callback);
|
241 | };
|
242 | ```
|
243 |
|
244 | removeForeignKey
|
245 |
|
246 | Arguments
|
247 |
|
248 | tableName - table in which the foreign key should be deleted
|
249 | keyName - the name of the foreign key
|
250 | options - object of options, see below
|
251 | callback - callback that will be invoked once the foreign key was deleted
|
252 | Options
|
253 |
|
254 | dropIndex (default: false) - deletes the index with the same name as the foreign key
|
255 | Examples
|
256 |
|
257 | ```js
|
258 | //without options object
|
259 | exports.down = function (db, callback)
|
260 | {
|
261 | db.removeForeignKey('module_user', 'module_user_module_id_foreign', callback);
|
262 | };
|
263 |
|
264 | //with options object
|
265 | exports.down = function (db, callback)
|
266 | {
|
267 | db.removeForeignKey('module_user', 'module_user_module_id_foreign',
|
268 | {
|
269 | dropIndex: true,
|
270 | }, callback);
|
271 | };
|
272 | insert(tableName, columnNameArray, valueArray, callback)
|
273 | ```
|
274 |
|
275 | Insert an item into a given column
|
276 |
|
277 | Arguments
|
278 |
|
279 | tableName - table to insert the item into
|
280 | columnNameArray - the array existing column names for each item being inserted
|
281 | valueArray - the array of values to be inserted into the associated column
|
282 | callback(err) - callback that will be invoked once the insert has been completed.
|
283 | removeIndex([tableName], indexName, callback)
|
284 |
|
285 | Remove an index
|
286 |
|
287 | Arguments
|
288 |
|
289 | tableName - name of the table that has the index (Required for mySql)
|
290 | indexName - the name of the index
|
291 | callback(err) - callback that will be invoked after removing the index
|
292 | runSql(sql, [params,] callback)
|
293 |
|
294 | Run arbitrary SQL
|
295 |
|
296 | Arguments
|
297 |
|
298 | sql - the SQL query string, possibly with ? replacement parameters
|
299 | params - zero or more ? replacement parameters
|
300 | callback(err) - callback that will be invoked after executing the SQL
|
301 | all(sql, [params,] callback)
|
302 |
|
303 | 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!
|
304 |
|
305 | Arguments
|
306 |
|
307 | sql - the SQL query string, possibly with ? replacement parameters
|
308 | params - zero or more ? replacement parameters
|
309 | callback(err, results) - callback that will be invoked after executing the SQL |
\ | No newline at end of file |