Services

.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: 'foo@bar.com', biography: 'blah blah', first_name: 'john', last_name: 'Doe'})
    .run();

// is equivalent to

sh
    .insert({email: 'foo@bar.com', 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();

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');

.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":"Wilhelm_Collins@Windler.name",
                "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.