1 |
|
2 | # Igo Model API
|
3 |
|
4 | The Igo Model API for MySQL is the only part of Igo that is not just the integration of another module.
|
5 | As you will notice, the syntax was very inspired by [Ruby on Rails Active Record](http://guides.rubyonrails.org/active_record_basics.html).
|
6 |
|
7 | ## MySQL Configuration
|
8 |
|
9 | This is the default MySQL configuration (`config.mysql`) defined by Igo:
|
10 | ```
|
11 | config.mysql = {
|
12 | host : process.env.MYSQL_HOST || 'localhost',
|
13 | port : process.env.MYSQL_PORT || 3306,
|
14 | user : process.env.MYSQL_USERNAME || 'root',
|
15 | password : process.env.MYSQL_PASSWORD || '',
|
16 | database : process.env.MYSQL_DATABASE || 'igo',
|
17 | debug : false, // mysql driver debug mode
|
18 | connectionLimit : 5,
|
19 | debugsql : false // show sql logs
|
20 | };
|
21 | ```
|
22 |
|
23 | This configuration is given to the [node.js driver for mysql](https://github.com/mysqljs/mysql) `mysql.createPool(config.mysql)` function, to initialize the connection pool.
|
24 |
|
25 | You can override this configuration in your `/app/config.js` file:
|
26 | ```js
|
27 | if (config.env === 'dev') {
|
28 | // show sql logs
|
29 | config.mysql.debugsql = true;
|
30 | }
|
31 | ```
|
32 |
|
33 |
|
34 | ## Migrations
|
35 |
|
36 | All the SQL files should be placed in the `/sql` directory, and will be played in the alphabetical order.
|
37 | The SQL files names must follow this pattern: `YYYYMMDD-*.sql`.
|
38 |
|
39 | To run the migrations, use:
|
40 | ```js
|
41 | require('igo').db.migrate();
|
42 | ```
|
43 |
|
44 | When a migration file has run successfully, it is saved in a `__db_migrations` table so it will not run again next time. (This table is automatically created by the framework.)
|
45 |
|
46 | ### CLI
|
47 |
|
48 | The Igo CLI provides convenient functions to deal with the database migrations.
|
49 |
|
50 | ```sh
|
51 | # run migrations
|
52 | igo db migrate
|
53 |
|
54 | # reset database (WARNING: data will be lost)
|
55 | igo db reset
|
56 | ```
|
57 |
|
58 | ## Model Definition
|
59 |
|
60 | ### Basics
|
61 |
|
62 | ```js
|
63 | const Model = require('igo').Model;
|
64 |
|
65 | const schema = {
|
66 | table: 'users',
|
67 | columns: [
|
68 | 'id',
|
69 | 'email',
|
70 | 'password',
|
71 | 'first_name',
|
72 | 'last_name',
|
73 | 'created_at'
|
74 | ]
|
75 | };
|
76 |
|
77 | class User extends Model(schema) {
|
78 | //override constructor if needed
|
79 | constructor(values) {
|
80 | super(values);
|
81 | }
|
82 |
|
83 | name() {
|
84 | return this.first_name + ' ' + this.last_name;
|
85 | }
|
86 | };
|
87 |
|
88 | module.exports = User;
|
89 | ```
|
90 |
|
91 | The `schema` object defines the table name and the table structure, and how this model can be associated to other models.
|
92 |
|
93 |
|
94 | ### Columns Types
|
95 |
|
96 | Column types can be specified.
|
97 |
|
98 | ```js
|
99 | const schema = {
|
100 | table: 'users',
|
101 | columns: [
|
102 | 'id',
|
103 | 'first_name',
|
104 | 'last_name',
|
105 | {name: 'is_validated', type: 'boolean'}
|
106 | {name: 'details_json', type: 'json', attr: 'details'},
|
107 | {name: 'pets_array', type: 'array', attr: 'pets'},
|
108 | ]
|
109 | };
|
110 | ```
|
111 | `boolean` will automatically be cast as boolean on instance.
|
112 |
|
113 | `json` columns will automatically be stringified on creation and update and parsed on load (on the instance, the column key is set with the `attr` attribute).
|
114 |
|
115 | `array` columns will automatically be stringified on creation and update and split on load (on the instance, the column key is set with the `attr` attribute).
|
116 |
|
117 | ### Associations
|
118 |
|
119 | Add `associations` in the schema declaration.
|
120 | Use `has_many` for one-to-many relationships, and `belongs_to` for many-to-one relationships.
|
121 |
|
122 | ```js
|
123 | const Project = require('./Project');
|
124 | const Country = require('./Country');
|
125 | const schema = {
|
126 | // ...
|
127 | columns: [
|
128 | 'id',
|
129 | 'country_id',
|
130 | // ...
|
131 | ]
|
132 | associations: [
|
133 | // [ type, attribute name, association model, model key, foreign key ('id' if not specified)]
|
134 | [ 'has_many', 'projects', Project, 'id', 'user_id'],
|
135 | [ 'belongs_to', 'country', Country, 'country_id' ],
|
136 | ]
|
137 | };
|
138 | ```
|
139 |
|
140 | `has_many` can also be used with an array of references.
|
141 | In the following example, projects_ids should be an array of projects' ids.
|
142 |
|
143 | ```js
|
144 | const schema = {
|
145 | // ...
|
146 | columns: [
|
147 | 'id',
|
148 | {name: 'projects_ids_json', type: 'json', attr: 'projects_ids'}
|
149 | // ...
|
150 | ]
|
151 | associations: () => ([
|
152 | [ 'has_many', 'projects', require('./Project'), 'projects_ids', 'id'],
|
153 | ])
|
154 | };
|
155 | ```
|
156 |
|
157 | ### Scopes
|
158 |
|
159 | Scopes can be used to specify extra queries options.
|
160 | Scopes are added to the schema declaration.
|
161 |
|
162 | ```js
|
163 | const schema = {
|
164 | // ...
|
165 | scopes: {
|
166 | default: function(query) { query.order('`created_at` DESC'); },
|
167 | validated: function(query) { query.where({ validated: true }); }
|
168 | }
|
169 | };
|
170 | ```
|
171 |
|
172 | The `default` scope will be used on all queries.
|
173 | (Use `.unscoped()` to not use the default scope.)
|
174 |
|
175 | ```js
|
176 | //Scopes usage
|
177 | User.scope('validated').list(function(err, validatedUsers) {
|
178 | //..
|
179 | });
|
180 | ```
|
181 |
|
182 | ### Callbacks
|
183 |
|
184 | Callbacks are special hooks functions called by Igo during the life cycle of an Igo Model.
|
185 |
|
186 | | Callback | Triggered |
|
187 | |----------|-----------|
|
188 | | `beforeCreate(callback)` | before object creation |
|
189 | | `beforeUpdate(values, callback)` | before object update (modified attributes are given in the `values` parameter) |
|
190 |
|
191 | Example:
|
192 | ```js
|
193 | class User extends Model(schema) {
|
194 |
|
195 | // hash password before creation
|
196 | beforeCreate(callback) {
|
197 | this.password = PasswordUtils.hash(this.password);
|
198 | callback();
|
199 | }
|
200 | ```
|
201 |
|
202 |
|
203 | ## Model API
|
204 |
|
205 | ### Create
|
206 |
|
207 | ```js
|
208 | // create default user
|
209 | User.create(function(err, user) {
|
210 | //
|
211 | });
|
212 |
|
213 | // create with specified attributes
|
214 | User.create({
|
215 | first_name: 'John',
|
216 | last_name: 'John',
|
217 | }, function(err, user) {
|
218 | console.log('Hi ' + user.first_name);
|
219 | });
|
220 | ```
|
221 |
|
222 | If the primary key is an `AUTO_INCREMENT` field, it will be set automatically in the object returned in the callback.
|
223 |
|
224 | ### Find
|
225 |
|
226 | ```js
|
227 | User.find(id, function(err, user) {
|
228 | console.log('Hi ' + user.first_name);
|
229 | });
|
230 | ```
|
231 |
|
232 | ### Update
|
233 |
|
234 | To update a specific object:
|
235 |
|
236 | ```js
|
237 | User.find(id, function(err, user) {
|
238 | user.update({
|
239 | first_name: 'Jim'
|
240 | }, function(err, user) {
|
241 | console.log('Hi ' + user.first_name);
|
242 | });
|
243 | });
|
244 | ```
|
245 |
|
246 | To update several objects:
|
247 |
|
248 | ```js
|
249 | User.where({
|
250 | country: 'France'
|
251 | }). update({
|
252 | language: 'French'
|
253 | }, function(err) {
|
254 | // Users are updated
|
255 | });
|
256 | ```
|
257 |
|
258 | To update all objects:
|
259 |
|
260 | ```js
|
261 | User.update({
|
262 | first_name: 'Jim'
|
263 | }, function(err) {
|
264 | // all users are now named Jim
|
265 | });
|
266 | ```
|
267 |
|
268 | ### Delete
|
269 |
|
270 | To delete a specific object:
|
271 |
|
272 | ```js
|
273 | User.destroy(id, function(err) {
|
274 | // user was deleted
|
275 | });
|
276 | ```
|
277 |
|
278 | ```js
|
279 | User.find(id, function(err, user) {
|
280 | user.destroy(function(err) {
|
281 | // user was deleted
|
282 | });
|
283 | });
|
284 | ```
|
285 |
|
286 | ```js
|
287 | User.destroyAll(function(err) {
|
288 | // all users were deleted
|
289 | });
|
290 | ```
|
291 |
|
292 | ```js
|
293 | User.where({first_name: 'Jim'}).destroy(function(err) {
|
294 | // all users named Jim were deleted
|
295 | });
|
296 | ```
|
297 |
|
298 | ### List
|
299 |
|
300 | ```js
|
301 | User.list(function(err, users) {
|
302 | // users is an array of User objects
|
303 | });
|
304 | ```
|
305 |
|
306 | #### Where
|
307 |
|
308 | Examples:
|
309 | ```js
|
310 |
|
311 | // filter with attribute values
|
312 | User.where({type: 'foo', sub_type: 'bar'}).list(function(err, users) { ... });
|
313 |
|
314 | // filter with sql
|
315 | User.where('`last_name` IS NOT NULL').list(function(err, users) { ... });
|
316 |
|
317 | // filter with sql and params
|
318 | User.where('`created_at` BETWEEN ? AND ?', [date1, date2]).list(function(err, users) { ... });
|
319 | ```
|
320 |
|
321 | #### Limit
|
322 |
|
323 | ```js
|
324 | User.limit(10).list(function(err, users) {
|
325 | // first ten users
|
326 | console.dir(users);
|
327 | });
|
328 | ```
|
329 |
|
330 | #### Order
|
331 |
|
332 | ```js
|
333 | User.order('`last_name` DESC').list(function(err, users) {
|
334 | console.dir(users);
|
335 | });
|
336 | ```
|
337 |
|
338 | ### Associations loading
|
339 |
|
340 | The `includes()` function is used to eager load the objects' associations
|
341 |
|
342 | ```js
|
343 | // include one association
|
344 | User.includes('country']).first( ... );
|
345 |
|
346 | // include multiple associations
|
347 | User.includes(['country', 'projects']).first( ... );
|
348 |
|
349 | // include nested associations (load projects's lead and tasks for each user)
|
350 | User.includes({projects: ['lead', 'tasks']}).first( ... );
|
351 |
|
352 | // mixed associations
|
353 | User.includes(['country', {projects: ['lead', 'tasks']}]).first( ... );
|
354 | ```
|
355 |
|
356 | ### Count
|
357 |
|
358 | `count()` allows you to count rows.
|
359 |
|
360 | ```js
|
361 | User.count(function(err, count) {
|
362 | // count all users
|
363 | console.dir(count);
|
364 | });
|
365 |
|
366 | User.where({first_name: 'john'}).count(function(err, count) {
|
367 | // count all users named John
|
368 | console.dir(count);
|
369 | });
|
370 | ```
|
371 |
|
372 | ### Distinct
|
373 |
|
374 | ```js
|
375 | User.distinct('first_name').list(function(err, first_names) {
|
376 | // list all distinct user first names
|
377 | console.dir(first_names);
|
378 | });
|
379 |
|
380 | User.distinct([ 'first_name', 'last_name' ]).list(function(err, first_names) {
|
381 | // list all distinct user first and last names combinations
|
382 | console.dir(first_names);
|
383 | });
|
384 | ```
|
385 |
|
386 | ### Select
|
387 |
|
388 | `select()` allows you to customize `SELECT` (set by default to `SELECT *`).
|
389 |
|
390 | ```js
|
391 | User.select('id, first_name').list(function(err, users) {
|
392 | // select only id and first_name columns
|
393 | console.dir(users);
|
394 | });
|
395 |
|
396 | User.select('*, YEAR(created_at) AS `year`').list(function(err, users) {
|
397 | // add year (from created_at column) in user
|
398 | console.dir(users);
|
399 | });
|
400 | ```
|
401 |
|
402 | ### Group
|
403 |
|
404 | ```js
|
405 | User.select('COUNT(*) AS `count`, YEAR(created_at) AS `year`').group('year').list(function(err, groups) {
|
406 | // return users count by creation year
|
407 | console.dir(groups);
|
408 | });
|
409 | ```
|