Table Definitions

Let’s assume you have created the tables users and posts as show in the example in the section on Migrations.

The next step is to define these tables for the ORM. This is done in the file orm/tables.js show in the Getting Started section.

orm/tables.js

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
module.exports = (orm) => {
orm.defineTable({
name: 'users',

props: {
autoId: true,
timestamps: true
}
});

orm.defineTable({
name: 'posts',

props: {
autoId: true,
timestamps: true
}
});
};

Now you can use these tables anywhere in your applications. For example:

1
2
3
4
5
6
7
8
const app = module.exports = require('express')();

const {table} = require('orm');

app.get('/posts', async (req, res) => {
const posts = await table('posts').all();
res.send({posts});
});


Full Config

Full configuration that can be provided when defining a table on an orm object is given below, along with defaults:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
orm.defineTable({
// the table's name, is required, should be a string
name: null,

// table properties
props: {
key: 'id',
// default key column, can be ['user_id', 'post_id'] for composite keys

autoId: false,
// set this to true if you want the orm to generate a 36 character
// uuid for your inserts. The orm checks for uniqueness of the uuid
// when its generating it.
// Can generate composite keys.
// While using autoId on postgresql, use uuid column for your key(s)

perPage: 25,
// standard batch size per page used by `forPage` method
// table.forPage(page, perPage) method uses offset
// avoid that and use a keyset in prod (http://use-the-index-luke.com/no-offset)

timestamps: false
// set to `true` if you want auto timestamps or
// timestamps: ['created_at', 'updated_at'] (these are defaults when `true`)
// will be assigned in this order only
},

// predefined scopes on the table. Will talk about them more in scopes and joints section.
// `this` will be bound to the table instance.
scopes: {},

// predefined joints on the table. Will talk about them more in scopes and joints section.
// `this` will be bound to the table instance.
joints: {},

// relations definitions for the table. Will talk about them more in relations section.
// `this` will be bound to the table instance.
relations: {}
});

Scopes

Suppose you have a particular where clause that you need to apply on posts table several times when fetching data. You can add this where clause as a scope in the table-definition, and re-use it when working with posts table in your application. Example shown below.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
orm.defineTable({
name: 'posts',

props: {
autoId: true,
timestamps: true
},

scopes: {
whereTitle(search='') {
return this.whereRaw('lower(title) like ?', [search.toLowerCase()]);
}
}
});

Joints

Suppose you have another table comments and posts-hasMany-comments, and say you want to join users to comments through posts, you can do something like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
orm.defineTable({
name: 'users',

props: {
autoId: true,
timestamps: true
},

joints: {
joinComments() {
return this.joinPosts().join('comments', 'comments.post_id', '=', 'posts.id');
},

joinPosts() {
return this.join('posts', 'users.id', '=', 'posts.author_id')
}
}
});

This way, suppose because of some reason you need to do something like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
function getUsers(params={}) {
const t = table('users');

if (params.foo) {
t.joinComments();
}

if (params.bar) {
t.joinPosts();
}

...

return t.all();
}

The table posts will be joined only once, even when the joinPosts method gets called twice. Once under the if statement, and once in the joint joinComments(). Because of this nature of joints, they don’t accept any arguments. Even if you define arguments on joints, and call joints with some values for those arguments, the arguments will receive undefined as their value.

However, unless you are doing something really complicated, you won’t need to use joints to join tables. Just define your relations properly, and you can use a very powerful feature named relation joins.


Relations

The table posts contains a column named author_id. This column references to the id column in the users table. This is a relation that exists in the database schema. You need to describe these relations to the ORM. In the example shown below, we define a users-hasMany-posts relation and a reverse relation posts-belongsTo-users:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
orm.defineTable({
name: 'users',

props: {
autoId: true,
timestamps: true
},

relations: {
posts() {
return this.hasMany('posts', 'author_id');
}
}
});

orm.defineTable({
name: 'posts',

props: {
autoId: true,
timestamps: true
},

relations: {
author() {
return this.belongsTo('users', 'author_id');
}
}
});

Relations are covered in more detail in the Relationships Section.


On this

Notice the use of this when defining scopes, joints, and relations, and the syntax used for the function that declares the (scope|joint|relation) in the table definitions. This syntax is important as these functions are bound to their respective table objects internally by tabel ORM. These functions should not be declared using arrow functions (DO NOT USE THIS SYNTAX: () => {}).


Next > Query Building