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 | ### Associations
|
94 |
|
95 | Add `associations` in the schema declaration.
|
96 | Use `has_many` for one-to-many relationships, and `belongs_to` for many-to-one relationships.
|
97 |
|
98 | ```js
|
99 | const Project = require('./Project');
|
100 | const Country = require('./Country');
|
101 | const schema = {
|
102 | // ...
|
103 | columns: [
|
104 | 'id',
|
105 | 'country_id',
|
106 | // ...
|
107 | ]
|
108 | associations: [
|
109 | // [ type, attribute name, association model, model key, foreign key ('id' if not specified)]
|
110 | [ 'has_many', 'projects', Project, 'id', 'user_id'],
|
111 | [ 'belongs_to', 'country', Country, 'country_id' ],
|
112 | ]
|
113 | };
|
114 | ```
|
115 |
|
116 | ### Scopes
|
117 |
|
118 | Scopes can be used to specify extra queries options.
|
119 | Scopes are added to the schema declaration.
|
120 |
|
121 | ```js
|
122 | const schema = {
|
123 | // ...
|
124 | scopes: {
|
125 | default: function(query) { query.order('`created_at` DESC'); },
|
126 | validated: function(query) { query.where({ validated: true }); }
|
127 | }
|
128 | };
|
129 | ```
|
130 |
|
131 | The `default` scope will be used on all queries.
|
132 | (Use `.unscoped()` to not use the default scope.)
|
133 |
|
134 | ```js
|
135 | //Scopes usage
|
136 | User.scope('validated').list(function(err, validatedUsers) {
|
137 | //..
|
138 | });
|
139 | ```
|
140 |
|
141 | ### Callbacks
|
142 |
|
143 | Callbacks are special hooks functions called by Igo during the life cycle of an Igo Model.
|
144 |
|
145 | | Callback | Triggered |
|
146 | |----------|-----------|
|
147 | | `beforeCreate(callback)` | before object creation |
|
148 | | `beforeUpdate(values, callback)` | before object update (modified attributes are given in the `values` parameter) |
|
149 |
|
150 | Example:
|
151 | ```js
|
152 | class User extends Model(schema) {
|
153 |
|
154 | // hash password before creation
|
155 | beforeCreate(callback) {
|
156 | this.password = PasswordUtils.hash(this.password);
|
157 | callback();
|
158 | }
|
159 | ```
|
160 |
|
161 |
|
162 | ## Model API
|
163 |
|
164 | ### Create
|
165 |
|
166 | ```js
|
167 | // create default user
|
168 | User.create(function(err, user) {
|
169 | //
|
170 | });
|
171 |
|
172 | // create with specified attributes
|
173 | User.create({
|
174 | first_name: 'John',
|
175 | last_name: 'John',
|
176 | }, function(err, user) {
|
177 | console.log('Hi ' + user.first_name);
|
178 | });
|
179 | ```
|
180 |
|
181 | If the primary key is an `AUTO_INCREMENT` field, it will be set automatically in the object returned in the callback.
|
182 |
|
183 | ### Find
|
184 |
|
185 | ```js
|
186 | User.find(id, function(err, user) {
|
187 | console.log('Hi ' + user.first_name);
|
188 | });
|
189 | ```
|
190 |
|
191 | ### Update
|
192 |
|
193 | To update a specific object:
|
194 |
|
195 | ```js
|
196 | User.find(id, function(err, user) {
|
197 | user.update({
|
198 | first_name: 'Jim'
|
199 | }, function(err, user) {
|
200 | console.log('Hi ' + user.first_name);
|
201 | });
|
202 | });
|
203 | ```
|
204 |
|
205 | To update several objects:
|
206 |
|
207 | ```js
|
208 | User.where({
|
209 | country: 'France'
|
210 | }). update({
|
211 | language: 'French'
|
212 | }, function(err) {
|
213 | // Users are updated
|
214 | });
|
215 | ```
|
216 |
|
217 | To update all objects:
|
218 |
|
219 | ```js
|
220 | User.update({
|
221 | first_name: 'Jim'
|
222 | }, function(err) {
|
223 | // all users are now named Jim
|
224 | });
|
225 | ```
|
226 |
|
227 | ### Delete
|
228 |
|
229 | To delete a specific object:
|
230 |
|
231 | ```js
|
232 | User.destroy(id, function(err) {
|
233 | // user was deleted
|
234 | });
|
235 | ```
|
236 |
|
237 | ```js
|
238 | User.find(id, function(err, user) {
|
239 | user.destroy(function(err) {
|
240 | // user was deleted
|
241 | });
|
242 | });
|
243 | ```
|
244 |
|
245 | ```js
|
246 | User.destroyAll(function(err) {
|
247 | // all users were deleted
|
248 | });
|
249 | ```
|
250 |
|
251 | ```js
|
252 | User.where({first_name: 'Jim'}).destroy(function(err) {
|
253 | // all users named Jim were deleted
|
254 | });
|
255 | ```
|
256 |
|
257 | ### List
|
258 |
|
259 | ```js
|
260 | User.list(function(err, users) {
|
261 | // users is an array of User objects
|
262 | });
|
263 | ```
|
264 |
|
265 | #### Where
|
266 |
|
267 | Examples:
|
268 | ```js
|
269 |
|
270 | // filter with attribute values
|
271 | User.where({type: 'foo', sub_type: 'bar'}).list(function(err, users) { ... });
|
272 |
|
273 | // filter with sql
|
274 | User.where('`last_name` IS NOT NULL').list(function(err, users) { ... });
|
275 |
|
276 | // filter with sql and params
|
277 | User.where('`created_at` BETWEEN ? AND ?', [date1, date2]).list(function(err, users) { ... });
|
278 | ```
|
279 |
|
280 | #### Limit
|
281 |
|
282 | ```js
|
283 | User.limit(10).list(function(err, users) {
|
284 | // first ten users
|
285 | console.dir(users);
|
286 | });
|
287 | ```
|
288 |
|
289 | #### Order
|
290 |
|
291 | ```js
|
292 | User.order('`last_name` DESC').list(function(err, users) {
|
293 | console.dir(users);
|
294 | });
|
295 | ```
|
296 |
|
297 | ### Associations loading
|
298 |
|
299 | The `includes()` function is used to eager load the objects' associations
|
300 |
|
301 | ```js
|
302 | // include one association
|
303 | User.includes('country']).first( ... );
|
304 |
|
305 | // include multiple associations
|
306 | User.includes(['country', 'projects']).first( ... );
|
307 |
|
308 | // include nested associations (load projects's lead and tasks for each user)
|
309 | User.includes({projects: ['lead', 'tasks']}).first( ... );
|
310 |
|
311 | // mixed associations
|
312 | User.includes(['country', {projects: ['lead', 'tasks']}]).first( ... );
|
313 | ```
|
314 |
|
315 | ### Distinct
|
316 |
|
317 | ```js
|
318 | User.distinct('first_name').list(function(err, first_names) {
|
319 | // list all distinct user first names
|
320 | console.dir(first_names);
|
321 | });
|
322 |
|
323 | User.distinct([ 'first_name', 'last_name' ]).list(function(err, first_names) {
|
324 | // list all distinct user first and last names combinations
|
325 | console.dir(first_names);
|
326 | });
|
327 | ```
|
328 |
|
329 | ### Select
|
330 |
|
331 | `select()` allow you to customize `SELECT` (set by default to `SELECT *`).
|
332 |
|
333 | ```js
|
334 | User.select('id, first_name').list(function(err, users) {
|
335 | // select only id and first_name columns
|
336 | console.dir(users);
|
337 | });
|
338 |
|
339 | User.select('*, YEAR(created_at) AS `year`').list(function(err, users) {
|
340 | // add year (from created_at column) in user
|
341 | console.dir(users);
|
342 | });
|
343 | ```
|
344 |
|
345 | ### Group
|
346 |
|
347 | ```js
|
348 | User.select('COUNT(*) AS `count`, YEAR(created_at) AS `year`').group('year').list(function(err, groups) {
|
349 | // return users count by creation year
|
350 | console.dir(groups);
|
351 | });
|
352 | ```
|