Skip to content

Services

With services, you can create builders bound to a specific table in your database. They bring before all syntactic sugar, but they also are very useful to query related data (from different tables) by composing service builders together.

Create a service

To create a service you have to pass a service definition object whose unique mandatory property is table which refers to the table your service will be bound to.

.service(definitionOrKey: ServiceDefinition | string)

If you pass a service definition you can also pass a primaryKey property (default will be 'id') and a name property. The primaryKey property is mainly used in queries with associations. The name property is used as a key in a service registry and as alias for common table expressions in some queries. If not provided it will be based on the table name putting the first letter of each word in capital.

If you pass a string as argument you are actually trying to get the service for that given key. Note that all services are singletons.

const Users = sh.service({
    table: 'test_users'
    // name will be 'TestUsers'
    // primaryKey will be 'id'
});

Users === sh.service('TestUsers'); // true

It is highly recommended to use a different name for your service name that the one used for the table. It is used as alias for some CTE and if you use the same value for both the service name and the table, some queries might have unexpected result.

Service builders

Service builders are standard ship hold builders with some light syntax differences.

.insert(...args)

A insert builder created with a service simply binds the builder to the service table. It also returns the inserted rows by default.

const Users = sh.service({
    table: 'users',
    primaryKey: 'user_id'
});

Users
    .insert({email: '[email protected]', biography: 'blah blah', first_name: 'john', last_name: 'Doe'})
    .run();

// is equivalent to

sh
    .insert({email: '[email protected]', biography: 'blah blah', first_name: 'john', last_name: 'Doe'})
    .into('users')
    .returning('*');

.update(map: objecMap)

An update builder created with a service binds the builder to the service table. You can also directly pass as argument an object map with the properties/values to update. It will return the updated rows by default.

const Users = sh.service({
    table: 'users',
    primaryKey: 'user_id'
});

Users
    .update({biography: 'blah blah blah'})
    .where('user_id', 42)
    .run();

// is equivalent to

sh
    .update('users')
    .set('biography', 'blah blah blah')
    .where('user_id', 42)
    .returning('*')
    .run();

.delete()

An delete builder created with a service binds the builder to the service table.

const Users = sh.service({
    table: 'users',
    primaryKey: 'user_id'
});

Users
    .delete()
    .where('user_id', 42)
    .run();

// is equivalent to

sh
    .delete('users')
    .where('user_id', 42)
    .run();

.select(...args)

A select builder created with a service simply binds the builder to the service table. It also has an include method. You can refer to the relations section for more details.

const Users = sh.service({
    table: 'users',
    primaryKey: 'user_id'
});

Users
    .select()
    .run();

// is equivalent to

sh
    .select()
    .from('users')
    .run();

Create Relations

The other great feature with services is the ability to combine service builders together to load related data whereas they don't belong to the same table. We usually call it eager loading.

Define a one-to-one relationship

This kind of relationship happens when a foreign key in a table references the primary key of another table. In ship-hold you define this relationship using the hasOne method on the relationship holder and the belongsTo method on the other side.

hasOne takes as arguments the service related to the target table and an alias which will be used as property when aggregating the data.

belongsTo takes as arguments the service related to the holder of the relationship, the foreign key used to reference the holder table and an alias too.

const Users = sh.service({
    table: 'users'
});

const Phones = sh.service({
    table: 'phones'
});

//Users have one phone
Users.hasOne(Phones, 'phone');

//A phone belongs to a given user
//The foreign key in the phones table which references users table is "user_id"
Phones.belongsTo(Users, 'user_id', 'owner');

Define a one-to-many relationship

This kind of relationship happens in the same condition as the one-to-one, but your data model allows an item of the holder to have several items of the target reference it.

hasMany takes as arguments the service related to the target table and an alias which will be used as property when aggregating the data.

belongsTo takes as arguments the service related to the holder of the relationship, the foreign key used to reference the holder table and an alias too.

const Users = sh.service({
    table: 'users',
    primaryKey: 'user_id'
});

const Posts = sh.service({
    table: 'posts',
    primaryKey: 'post_id'
});

//A user may have written several posts
Users.hasMany(Posts, 'articles');

//A post has only one author
Posts.belongsTo(Users, 'user_id', 'author');

Define a many-to-many relationship

Sometimes we have a many to many relationship. In that case we use a pivot table which have as rows at least a pair of key whose each member references an item in each table of the relationship.

belongsToMany takes as arguments the service related to the target table, the name of the pivot table, the key which references this side of the relationship and an alias.

const Tags = sh.service({
    table: 'tags',
    primaryKey: 'tag'
});

const Posts = sh.service({
    table: 'posts',
    primaryKey: 'post_id'
});

//A tag can be applied to many posts
Tags.belongsToMany(Posts, 'posts_tag', 'tag', 'posts');

//A posts may be tagged with multiple tags
Posts.belongsToMany(Tags, 'posts_tag', 'post_id', 'tags');

Queries with associations

Once you have defined relationships between your services, you can combine service builders together to include related association data when fetching a given type of data. This is particularly helpful as you avoid the trouble of making many joins or sub queries yourself. It will also aggregate the data based on the nature of the relationship and the aliases you have provided.

All the examples below will use our test database with the following service definitions

const Users = sh.service({
    name: 'Users',
    table: 'users',
    primaryKey: 'user_id'
});

const Posts = sh.service({
    name: 'Posts',
    table: 'posts',
    primaryKey: 'post_id'
});

const Comments = sh.service({
    name: 'Comments',
    table: 'comments',
    primaryKey: 'comment_id'
});

const Tags = sh.service({
    name: 'Tags',
    table: 'tags',
    primaryKey: 'tag'
});

Users.hasMany(Posts);
Posts.belongsTo(Users, 'user_id', 'author');

Users.hasMany(Comments);
Posts.hasMany(Comments);
Comments.belongsTo(Users, 'user_id', 'author');
Comments.belongsTo(Posts, 'post_id', 'article');

Posts.belongsToMany(Tags, 'posts_tags', 'post_id');
Tags.belongsToMany(Posts, 'posts_tags', 'tag');

.include(...associations: InclusionInput)

The include method lets you query for as many related table data as you want. An inclusion input can be one of the following:

  • Service select builder
  • Service: in this case it will be equivalent to Service.select()
  • An alias string as specified when you have created your relations. Again it will be equivalent in this case to Service.select()
// Find all the posts including their author, tags and comments
Posts
    .select()
    .include(Tags.select(), Comments, 'author')
    .run()
    .then(posts => {
        /* posts will be something
        [{
            "post_id":3,
            "title":"Fugit facilis facilis",
            "content":"Veniam aliquam aut ...",
            "published_at":"2014-03-08T05:00:00.000Z",
            "user_id":26365,
            "tags":[{
                    "tag":"nobis",
                    "description":"Et rerum ..."
                },
                ...],
            "comments":[{
                    "comment_id":31110,
                    "content":"Culpa dolores ...",
                    "published_at":"1979-03-08T00:00:00",
                    "user_id":59043,
                    "post_id":3
                },
                ...],
            "author":{
                "user_id":26365,
                "email":"[email protected]",
                "biography":"Et sit neque aliquid... ",
                "first_name":"Felix",
                "last_name":"Stokes"
                }
        },
        ...]
        */
    });

If you use the version with select builders, you have to understand that any clause will be scoped to the given builder. It makes it particularly easy to reason when you are building your query, especially if you want to paginate or filter your result: you don't have to worry whether you are going to miss some data if you do not apply the limit clause to the appropriate builder for example. It just translates what you would say in plain English.

I want to get the ten latest posts including for each the author name and the three last published comments.

It may seem pretty easy, but it is actually not trivial SQL. Worst, some ORMs you think you could rely on would give you wrong result. With ship-hold, it is easy. You just have to write your builders how you would say it in plain English.

const authors = Users.select('user_id', 'first_name', 'last_name');

const lastThreeComments = Comments
    .select()
    .orderBy('published_at', 'desc')
    .limit(3);

Posts
    .select()
    .orderBy('published_at', 'desc')
    .limit(10)
    .include(authors, lastThreeComments)
    .run()
    .then(lastTenPosts => {

    });

You have to include in your select clauses the required primary keys and foreign keys. ship-hold does not know much about your table columns, it just does what you are asking for.

You can also perform nested include. However the cost on the database will be higher although ship-hold performs quite well.

One last things good to know is that you can overwrite the aliases defined by your relations providing instead of a builder an object with the value property and the as property. It is particularly handy if you want to include several times the same service with different filter clauses

I want to get details for user Laurent Renard including its comments split into two collections: an old one with comments older than 2010, and a recent one with comments published after 2010. All comments should come with the title and the id of the post and should be sorted by publication date.

Easy !

const postReferences = Posts.select('post_id', 'title');

const getComments = (operator = '<') => Comments
    .select()
    .where('published_at', operator, new Date(2010, 1, 1))
    .orderBy('published_at', 'desc')
    .include(postReferences.clone());

const [user] = Users
    .select()
    .where('first_name', 'Laurent')
    .and('last_name', 'Renard')
    .include(
        {as: 'oldComments', value: getComments()},
        {as: 'recentComments', value: getComments('>')})
    .run()
    .then(laurentRenard => {
        //
    });

If the included builders finish on a condition clause, remember to call noop method in order to revoke any ongoing proxy.