Using raw SQL query fragments

raw() helper

When you want to use a raw SQL fragment as part of your query, you can use the raw() helper. It creates a raw SQL query fragment instance that can be assigned to a property or part of a filter. This fragment is represented by RawQueryFragment class instance that can be serialized to a string, so it can be used both as an object value and key. When serialized, the fragment key gets cached and only such a cached key will be recognized by the ORM. This adds runtime safety to the raw query fragments.

raw() helper is required since v6 to use a raw SQL fragment in your query, both through EntityManager and QueryBuilder.

// as a value
await em.find(User, { time: raw('now()') });

// as a key
await em.find(User, { [raw('lower(name)')]: name.toLowerCase() });

// with operators
await em.find(User, { [raw('lower(name)')]: { $like: name.toLowerCase() } });

// value can be empty array to skip operator
await em.find(User, { [raw('(select 1 = 1)')]: [] });

The raw helper supports several signatures, you can pass in a callback that receives the current property alias:

await em.find(User, { [raw(alias => `lower(${alias}.name)`)]: name.toLowerCase() });

Raw fragments in filters

When using raw query fragment inside a filter, you might have to use a callback signature to create new raw instance for every filter usage - namely when you use the fragment as an object key, which requires its serialization.

@Filter({ name: 'long', cond: () => ({ [raw('length(perex)')]: { $gt: 10000 } }) })

Raw fragments in indexes and uniques

The raw helper can be used within indexes and uniques to write database-agnostic SQL expressions. In that case, you can use '??' to tag your database identifiers (table name, column names, index name, ...) inside your expression, and pass those identifiers as a second parameter to the raw helper. Internally, those will automatically be quoted according to the database in use:

// On postgres, will produce: create index "index custom_idx_on_name" on "library.author" ("country")
// On mysql, will produce: create index `index custom_idx_on_name` on `library.author` (`country`)
@Index({ name: 'custom_idx_on_name', expression: (table, columns) => raw(`create index ?? on ?? (??)`, ['custom_idx_on_name', table, columns.name]) })
@Entity({ schema: 'library' })
export class Author { ... }

You can also use the quote tag function to write database-agnostic SQL expressions. The end-result is the same as using the raw function regarding database identifiers quoting, only to have a more elegant expression syntax:

@Index({ name: 'custom_idx_on_name', expression: (table, columns) => quote`create index ${'custom_idx_on_name'} on ${table} (${columns.name})` })
@Entity({ schema: 'library' })
export class Author { ... }

sql tagged templates

You can also use the sql tagged template function, which works the same, but supports only the simple string signature:

// as a value
await em.find(User, { time: sql`now()` });

// as a key
await em.find(User, { [sql`lower(name)`]: name.toLowerCase() });

// value can be empty array
await em.find(User, { [sql`(select ${1} = ${1})`]: [] });

sql.ref()

When you want to refer to a column, you can use the sql.ref() function:

await em.find(User, { foo: sql.ref('bar') });

sql.now()

When you want to define a default value for a datetime column, you can use the sql.now() function. It resolves to current_timestamp SQL function, and accepts a length parameter.

@Property({ default: sql.now() })
createdAt: Date & Opt;

sql.lower() and sql.upper()

To convert a key to lowercase or uppercase, you can use the sql.lower() and sql.upper() functions

const books = await orm.em.find(Book, {
  [sql.upper('title')]: 'TITLE',
});

Aliasing

To select a raw fragment, we need to alias it. For that, we can use sql`(select 1 + 1)`.as('<alias>').