Builders
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
.build()
Every builder comes with a build method to output the generated SQL. This is just string manipulation and does not need any database connection.
sh.select().from('users').build();
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)
sh.select()
.from('users')
.where('first_name', '$name')
.build({
name: 'Laurent'
});
.clone()
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 = sh.select().from('users');
const b2 = b.clone().where('first_name', 'Laurent');
Object.is(b, b2);
b.build();
b2.build();
.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
sh
.select()
.with('old_articles', sh
.delete()
.from('posts')
.where('published_at', '<', new Date(2000, 1, 1))
.returning('*')
)
.from('old_articles')
.where('user_id', 42)
.build();
You can chain with calls to create several common table expressions !
Common types
The documentation often refers to some common types defined below
Pointers
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
sh
.select('posts.post_id', 'published_at', {
as: 'author',
value: sh.select('*')
.from('users')
.where('users.user_id', '"posts"."user_id"')
.noop()
})
.from('posts')
.build();
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
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
sh
.select()
.from('posts')
.where('post_id', 'IN', [123, 543, 678])
.and('published_at', '>', new Date(2015))
.and('user_id', 'IN', sh
.select('user_id')
.from('users')
.where('last_name', 'ILIKE', 'renard')
.noop()
)
.build();
