Skip to content


A ship-hold instance provides a builder for each common database operation: INSERT, UPDATE, DELETE and SELECT; but also a conditions builder to create more sophisticated conditions.

You will notice while reading this documentation that ship hold API closely mirrors the SQL syntax. This is on purpose to reduce the cognitive effort if you already know some SQL. At first, it may seem less obvious to call a select function rather than a findAll one. But with time and especially if you learn SQL, you will undoubtedly see the benefits. Usually you can write a query as you would write it in SQL but by chaining function calls instead. Moreover using functions as base abstraction brings a lot of possibilities to build on top: composition, partial application, decoration etc to name a few.

Usually the call order does not really matter but it is recommended to follow the SQL grammar syntax


Every builder comes with a build method to output the generated SQL. This is just string manipulation and does not need any database connection.'users').build();

// > { text: 'SELECT * FROM "users"', values: [] }

Parameterized queries

If you look at our previous example, you'll see the build method outputs an object whose text property has the generated SQL. But there is also a values properties. This is because ship-hold allows you to execute parameterized queries to reduce your exposure to SQL injections.

To use parameters in your request just pass pointer strings prefixed with the dollar sign. You can then pass the actual parameter values as an object map to the build method (or any other run method mentioned in the run queries section)
    .where('first_name', '$name')
        name: 'Laurent'

// > { text: 'SELECT * FROM "users" WHERE "first_name" = $1', values: [ 'Laurent' ] }


Every builder comes with a clone method which allows you to make a copy of an existing builder while leaving it untouched. It is useful to avoid side effects when you use a builder as parameter of another builder.

const b ='users');
const b2 = b.clone().where('first_name', 'Laurent');, b2);
// > false;
// > { text: 'SELECT * FROM "users"', values: [] };
// > { text: 'SELECT * FROM "users" WHERE "first_name" = 'Laurent', values: [] }

.with(alias: string, builder: Builder)

SELECT, UPDATE, DELETE and INSERT builders have the with method which lets you create Common Table Expression statements. You pass the alias as first argument and a builder as second argument

    .with('old_articles', sh
        .where('published_at', '<', new Date(2000, 1, 1))
    .where('user_id', 42)

// { text:
//     `WITH "old_articles" AS (DELETE FROM "posts" WHERE "published_at" < '2000-02-01T05:00:00.000Z'
//      RETURNING *)
//      SELECT * FROM "old_articles" WHERE "user_id" = 42`,
//   values: [] }

You can chain with calls to create several common table expressions !

Common types

The documentation often refers to some common types defined below


Pointers are SQL entities which can design a column, a table or any expression that can enter into a SELECT clause.

If a pointer is a string it will automatically be wrapped within quotes following the postgres dialect. Also, any expression marked with a dot "." will be split and every part will be wrapped as well.

A pointer can also be an object with a value property and a as property. In this case the as will be used as an alias for the value

    .select('posts.post_id', 'published_at', {
        as: 'author',
            .where('users.user_id', '"posts"."user_id"')

//> { text:
//    `SELECT "posts"."post_id", "published_at",
//     (SELECT * FROM "users" WHERE "users"."user_id" = "posts"."user_id") AS "author" FROM "posts"`,
//   values: [] }

You will see the conditions builder in details but note the first operand of the where clause is by default a pointer whereas the second is by default a value. It means if you want to set the second operand as a pointer, you will need to wrap it within quotes yourself.


Values refer to any assignable value type: string, array, number, json, subquery. They will be casted by the pg driver. They are often used as the second operand of a condition

    .where('post_id', 'IN', [123, 543, 678])
    .and('published_at', '>', new Date(2015))
    .and('user_id', 'IN', sh
        .where('last_name', 'ILIKE', 'renard')

//> { text:
//    'SELECT * FROM "posts" WHERE "post_id" IN (123,543,678)
//     AND "published_at" > '1970-01-01T00:00:02.015Z'
//     AND "user_id" IN
//          (SELECT "user_id" FROM "users" WHERE "last_name" ILIKE 'renard')',
//   values: [] }