Query builder

Pruvious provides a fully-typed query builder for querying collection data with ease.

Instance

To create a new query builder instance, use the query function. Here's an example:

# examples/query-instance.ts

import { query } from '#pruvious/server'

const pageQuery = query('pages')
const userQuery = query('users')
const seoQuery = query('seo')

The instance is associated with the provided collection in the argument.

Raw queries

If you prefer writing your own SQL queries, you can use the rawQuery function. Here's an example:

# examples/raw-queries.ts

import { rawQuery } from '#pruvious/server'

// Example 1: Fetch products with a specific price range
await rawQuery(
  'SELECT * FROM products WHERE price BETWEEN :min AND :max',
  { min: 20, max: 50 },
)

// Example 2: Update product prices in bulk
await rawQuery(
  'UPDATE products SET price = price * :multiplier',
  { multiplier: 1.1 },
)

The replacements for SQL variables are automatically escaped.

Single-entry methods

The available methods for creating a query builder instance for single-entry collections are as follows:

getLanguage

This method returns the language code of the currently queried single-entry collection. Note that multi-entry collections use the language field for querying language specific records. Due single-entry collections are stored differently (in a single database table), they have a dedicated query builder method for defining and retrieving their translations. Here's an usage example:

# examples/query-get-language.ts

import { query } from '#pruvious/server'

const language = query('settings').getLanguage()

console.log(language)
// Output: 'en'

language

This method sets the language code for the query result. If no language is specified or the code is invalid, the primary language is used. Non-translatable collections always return results in the primary language. Here's an example:

# examples/query-set-language.ts

import { query } from '#pruvious/server'

const settings = await query('settings').language('de').read()

read

This method fetches data from a single-entry collection. Here's an example:

# examples/query-read.ts

import { query } from '#pruvious/server'

const settings = await query('settings').read()

console.log(settings)
// Output: { field1: '...', field2: '...', ... }

Multi-entry methods

Here's a summary of the query builder methods for multi-entry collections:

all

This method fetches all records from the queried collection. Here's an example:

# examples/query-all.ts

import { query } from '#pruvious/server'

const products = await query('products').all()

console.log(products)
// Output: [{ field1: '...', field2: '...', ... }, { field1: '...', field2: '...', ... }, ...]

allWithCount

This method retrieves all records from the queried collection and provides the total count of records. Here's an example:

# examples/query-all-with-count.ts

import { query } from '#pruvious/server'

const products = await query('products').limit(2).allWithCount()

console.log(products)
// Output: { count: 1337, records: [{ field1: '...', field2: '...', ... }, { field1: '...', field2: '...', ... }] }

count

This method retrieves the count of records in the queried collection based on the specified conditions. Here's an example:

# examples/query-count.ts

import { query } from '#pruvious/server'

const c1 = await query('products').count()
const c2 = await query('products').whereGt('price', 100).count()

console.log(c1) // Output: 1337
console.log(c2) // Output: 256

create

This method creates a new record in the queried collection using the provided input data. Here's an example of how to use it:

# examples/query-create.ts

import { query } from '#pruvious/server'

const result = await query('products').create({
  name: 'Magical Wand',
  price: 19.99,
  category: 2,
  description: 'A powerful wand for all your wizarding needs!',
})

if (result.success) {
  console.log('Product created successfully:', result.record)
} else {
  console.error('Product creation failed:', result.errors)
}

The method returns a Promise that resolves to a CreateResult object. If the creation is successful, the record property will contain the created record. If there are any field validation errors, they will be available in the errors property. Additionally, the result may contain a message property that holds error messages in case there were any issues during the database query.

createMany

This method creates multiple records in the collection based on the provided input array. Each input element corresponds to a record to be created. Here's an example of how to use it:

# examples/query-create-many.ts

import { query } from '#pruvious/server'

const result = await query('products').createMany([
  { name: 'Product 1', price: 10 },
  { name: 'Product 2', price: 20 },
  { name: 'Product 3', price: 'Invalid Price' }, // <- Error
])

if (result.success) {
  console.log('Records created:', result.records)
} else {
  console.log('Errors:', result.errors)
  // Output: [null, null, { price: 'Invalid input type' }]
}

If any input fails validation, no records will be created.

The method returns a Promise that resolves to a CreateManyResult object. If successful, the created records will be available in the records property. If any input has validation errors, the errors property will contain an array of error objects at the corresponding index. If there are no errors for a particular input, the value at that index will be null. Additionally, the result may contain a message property that holds error messages in case there were any issues during the database query.

delete

This method deletes records from the queried collection based on the specified conditions and returns a Promise that resolves to an array containing the deleted records. Here's a usage example:

# examples/query-delete.ts

import { query } from '#pruvious/server'

const products = await query('products')
  .select({ id: true })
  .where('category', 5)
  .delete()

console.log(products)
// Output: [{ id: 30 }, { id: 144 }, { id: 145 }]

every

This method applies a logical AND operation to a set of filtering conditions on the query. All conditions must be satisfied for a record to be included in the result. Here's an example:

# examples/query-every.ts

import { query } from '#pruvious/server'

query('products').every(
  (products) => products.where('price', '>', 100),
  (products) => products.where('status', '>=', 0.1),
)

// Same as:
query('products').where('price', '>', 100).where('status', '>=', 0.1)

Chained filter operations are automatically combined using logical AND by default. Therefore, using this method is syntactic sugar.

exists

This method checks whether there is at least one record that matches the current query. Here's an example:

# examples/query-exists.ts

import { query } from '#pruvious/server'

const exists = await query('products').whereGt('price', 100).exists()

console.log(exists)
// Output: true

first

This method fetches the first record from the queried collection. Here's an example:

# examples/query-first.ts

import { query } from '#pruvious/server'

const product = await query('products').first()

console.log(product)
// Output: { field1: '...', field2: '...', ... }

group

This method groups the query results based on a specific collection field. You can chain multiple group calls to apply multiple grouping criteria. The grouping will be applied in the order they are called. Here's an example:

# examples/query-group.ts

import { query } from '#pruvious/server'

await query('products').select({ category: true }).group('category').all()

limit

This method sets the maximum number of records that the query will return. Here's an example:

# examples/query-limit.ts

import { query } from '#pruvious/server'

await query('products').limit(10).all()

// Same as:
const { records } = await query('products').paginate(1, 10)

max

This method retrieves the maximum value of a specific field in the queried collection. Here's an example:

# examples/query-max.ts

import { query } from '#pruvious/server'

const max = await query('products').max('price')

console.log(max)
// Output: 9001

min

This method retrieves the minimum value of a specific field in the queried collection. Here's an example:

# examples/query-min.ts

import { query } from '#pruvious/server'

const min = await query('products').min('price')

console.log(min)
// Output: 0.36

offset

This method sets the offset (starting position) for the query results. Here's an example:

# examples/query-offset.ts

import { query } from '#pruvious/server'

await query('products').limit(10).offset(10).all()

// Same as:
const { records } = await query('products').paginate(2, 10)

order

This method sets a sorting order for the query results based on a specific collection field. By default, the sorting is done in ascending order (asc). You can chain multiple order calls to apply multiple sorting criteria. The sorting will be applied in the order they are called. Here's a usage example:

# examples/query-order.ts

import { query } from '#pruvious/server'

await query('products').order('price', 'desc').all()

If the field argument starts with a colon (:), it is considered a search structure key. For example, order(':default') is equivalent to calling the orderBySearchRelevance() method.

orderBySearchRelevance

This method sets the sorting order for query results based on search relevance within a specific search structure. By default, the sorting is in ascending order (asc), showing the most relevant results first. You can chain multiple order calls to apply multiple sorting criteria. The sorting will be applied in the order they are called. Here's a usage example:

# examples/query-order-by-search-relevance.ts

import { query } from '#pruvious/server'

await query('products')
  .search('NVMe SSD')
  .orderBySearchRelevance()
  .order('price').all()

// Same as:
await query('products')
  .search('NVMe SSD')
  .order(':default')
  .order('price')
  .all()

In order to perform a search on a collection, you need to specify the search parameter while defining the collection.

paginate

This method retrieves a specific page of records and includes pagination-related information. Here's an example.

# examples/query-paginate.ts

import { query } from '#pruvious/server'

const result = await query('products').paginate(1, 10)

console.log(result)
// Output: {
//   currentPage: 1,
//   lastPage: 134,
//   perPage: 10,
//   records: [...],
//   total: 1337
// }

search

This method performs a case-insensitive search in the queries collection using specified keywords and search structure. Here's how to use it:

# examples/query-search.ts

import { query } from '#pruvious/server'

await query('products').search('NVMe SSD').all()
await query('products', 'internal').search('tbd').all()

In order to perform a search on a collection, you need to specify the search parameter while defining the collection. Here's an example:

# collections/products.ts

import { defineCollection } from '#pruvious'

export default defineCollection({
  name: 'products',
  mode: 'multi',
  fields: {
    name: { ... },
    description: { ... },
    price: { ... },
    comment: { ... },
  },
  search: {
    default: [{ field: 'name', reserve: 30 }, 'description'],
    internal: ['comment'],
  }
})

// Example record:
// { id: 1, name: 'Product name', description: 'xy...' }
// Generated keywords for the 'default' structure:
// 'Product name                   xy...'

// Example record:
// { id: 2, name: 'Product with long name', description: 'yx...' }
// Generated keywords for the 'default' structure:
// 'Product with long name         yx...'

// Search example 1
// await query('products').select({ id: true }).search('name')
// Output: [{ id: 1}, { id: 2 }]
// The keyword 'name' is found earlier in the record with ID 1

// Search example 2
// await query('products').select({ id: true }).search('y')
// Output: [{ id: 2}, { id: 1 }]
// The keyword 'y' is found earlier in the record with ID 2

some

This method applies a logical OR to a set of filtering conditions on the query. At least one condition must be satisfied for a record to be included in the result. Here's an example:

# examples/query-some.ts

import { query } from '#pruvious/server'

query('products').some(
  (products) => products.where('price', '<', 100),
  (products) => products.where('discount', '>=', 0.5),
)

sum

This method retrieves the sum of a specific numeric field in the queried collection. Here's an example:

# examples/query-sum.ts

import { query } from '#pruvious/server'

const sum = await query('products').sum('quantity')

console.log(sum)
// Output: 5417

where

The where method enables you to filter queries with a wide range of available operators:

# examples/query-where.ts

import { query } from '#pruvious/server'

// Apply a filtering condition: status = 'active'
query('products').where('status', 'active')
query('products').where('status', '=', 'active')
query('products').whereEq('status', 'active')

// Apply a filtering condition: discount != null
query('products').where('discount', '!=', null)
query('products').whereNe('discount', null)

// Apply a filtering condition: price > 100
// Supported by number and string field types
query('products').where('price', '>', 100)
query('products').whereGt('price', 100)

// Apply a filtering condition: price >= 100
query('products').where('price', '>=', 100)
query('products').whereGte('price', 100)

// Apply a filtering condition: price < 100
query('products').where('price', '<', 100)
query('products').whereLt('price', 100)

// Apply a filtering condition: price <= 100
query('products').where('price', '<=', 100)
query('products').whereLte('price', 100)

// Apply a filtering condition: price >= 20 and price <= 50
query('products').where('price', 'between', [20, 50])
query('products').whereBetween('price', [20, 50])

// Apply a filtering condition: price < 20 or price > 50
query('products').where('price', 'notBetween', [20, 50])
query('products').whereNotBetween('price', [20, 50])

// Apply a filtering condition: id = 1 or id = 3 or id = 5
query('products').where('id', 'in', [1, 3, 5])
query('products').whereIn('id', [1, 3, 5])

// Apply a filtering condition: id != 1 and id != 3 and id != 5
query('products').where('id', 'notIn', [1, 3, 5])
query('products').whereNotIn('id', [1, 3, 5])

// Apply a filtering condition: name starts with 'P' (case sensitive in PG)
query('products').where('name', 'like', 'P%')
query('products').whereLike('name', 'P%')

// Apply a filtering condition: name does not start with 'P' (case sensitive in PG)
query('products').where('name', 'notLike', 'P%')
query('products').whereNotLike('name', 'P%')

// Apply a filtering condition: name contains 'phone' (case insensitive)
query('products').where('name', 'iLike', '%phone%')
query('products').whereILike('name', '%phone%')

// Apply a filtering condition: name does not contain 'phone' (case insensitive)
query('products').where('name', 'notILike', '%phone%')
query('products').whereNotILike('name', '%phone%')

Feel free to choose your preferred where annotation, whether it involves using operators as arguments or explicit methods like whereNe, whereGt, etc. Both options function in the same way, but utilizing explicit annotations can result in clearer TypeScript suggestions, as the operator is determined from the method name right from the beginning.

The records field offers specific extra methods for querying relations:

# examples/query-where.ts

import { query } from '#pruvious/server'

// Select products with `tags` that contain the record with ID 1
query('products').whereRecordsIn('tags', 1)

// Select products with `tags` containing records of IDs 1, 2, or 3
query('products').whereRecordsIn('tags', [1, 2, 3])
query('products').whereRecordsIn('tags', [1, 2, 3], 'some')

// Select products with `tags` containing all records of IDs 1, 2, and 3
query('products').whereRecordsIn('tags', [1, 2, 3], 'every')

// Select products with `tags` that do not contain the record with ID 4
query('products').whereRecordsNotIn('tags', 4)

// Select products with tags not containing any of the records with IDs 4, 5, or 6
query('products').whereRecordsNotIn('tags', [4, 5, 6])
query('products').whereRecordsNotIn('tags', [4, 5, 6], 'some')

// Select products with `tags` not containing records of IDs 4, 5, and 6
query('products').whereRecordsNotIn('tags', [4, 5, 6], 'every')

Shared methods

This section explains the query builder methods that are available in both single-entry and multi-entry collections.

applyQueryStringParams

This method applies query string parameters to the current query. Here's an example how to use it with the getQueryStringParamsutility:

# server/api/example.get.ts

import { getQueryStringParams, query } from '#pruvious/server'

export default defineEventHandler((event) => {
  const qs = getQueryStringParams(event, 'products')

  if (qs.errors.length) {
    setResponseStatus(event, 400)
    return qs.errors.join('\n')
  }

  return query('products').applyQueryStringParams(qs.params).all()
})

clone

This method creates a new query builder instance with the same settings as the current one.

# examples/clone-query.ts

import { query } from '#pruvious/server'

const q1 = query('pages').select({ path: true })
const q2 = q1.clone()

console.log(q2.getOptions().selectedFields)
// Output: ['path']

contextLanguage

This method sets the language for the validation messages returned by the query builder. By default, the language is set to the language code defined in the module option language.primary.

# nuxt.config.ts

export default defineNuxtConfig({
  modules: ['pruvious'],
  pruvious: {
    language: {
      supported: [
        { code: 'en', name: 'English' },
        { code: 'de', name: 'Deutsch' },
      ],
      primary: 'en',
    },
  },
})

Ensure that you provide the translated strings before the server can respond with messages in languages other than English. To learn more about translatable strings, click here.

# examples/query-context-language.ts

import { query } from '#pruvious/server'

const result = await query('pages').contextLanguage('de').create({})

console.log(result)
// Output: { "success": false, "errors": { "path": "Dieses Feld ist erforderlich" } }

deselect

This method excludes specific fields from the query result. Here's an example of how to use it:

# examples/query-deselect.ts

import { query } from '#pruvious/server'

const product = await query('settings').deselect({ secret: true }).read()

console.log(product.secret)
// Output: undefined

getOptions

This method returns a copy of the current query builder options. Here's an example of how to use it:

# examples/get-query-options.ts

import { query } from '#pruvious/server'

const options = query('pages').getOptions()

console.log(options)
// Output: {
//   table: string;
//   selectedFields: string[];
//   whereOptions: Record<any, any>;
//   searchOptions: Partial<Record<any, string[]>>;
//   orderOptions: [Sortable, "ASC NULLS LAST" | "DESC NULLS LAST"][];
//   groupOptions: SelectableFieldName[];
//   offsetOption: number | undefined;
//   limitOption: number | undefined;
//   populateOption: boolean;
//   fallbackOption: boolean;
//   contextLanguageOption: SupportedLanguage;
// }

populate

This method enables field population to retrieve populated field values in the query results. By default, the query builder returns the casted field values without populating related data. Here's an example:

# examples/populate-query.ts

import { query } from '#pruvious/server'

// Without population:
await query('settings').select({ blogLandingPage: true }).read()
// { blogLandingPage: 1 }

// With population:
await query('settings').select({ blogLandingPage: true }).populate().read()
// { blogLandingPage: { id: 1, path: '/blog' } }

reset

This method resets the query builder options to their default values. Here's an example of how to use it:

# examples/reset-query.ts

import { query } from '#pruvious/server'

const q1 = query('pages').populate()
const q2 = q1.clone().reset()

const o1 = q1.getOptions()
const o2 = q2.getOptions()

console.log(o1.table === o2.table)
// Output: true

console.log(o1.populateOption === o2.populateOption)
// Output: false

select

The select method specifies the collection fields to be returned in the query results. Here's an example:

# examples/query-select.ts

import { query } from '#pruvious/server'

const product = await query('products')
  .select({ name: true, price: true })
  .first()

console.log(product)
// Output: { name: '...', price: '...' }

By default, all public fields are selected.

selectAll

The method selects all public fields from the queried collection. Here's an example:

# examples/query-select-all.ts

import { query } from '#pruvious/server'

const product = await query('products').selectAll().first()

console.log(product)
// Output: { field1: '...', field2: '...', ... }

update

The update method modifies existing records in the queried multi-entry collection using specified conditions. In the case of single-entry collections, it simply updates the fields of the queried collection and language. Here's an example:

# examples/query-update.ts

import { query } from '#pruvious/server'

// Multi-entry collection
const resultMulti = await query('products').where('id', 47).update({
  name: 'Updated Product',
  price: 15,
  category: 3,
  description: 'This product has been updated!',
})

if (resultMulti.success) {
  console.log('Records updated:', resultMulti.records)
} else {
  console.error('Update failed:', resultMulti.errors)
}

// Single-entry collection
const resultSingle = await query('settings').update({
  logo: 2,
  blogLandingPage: 15,
  copyright: '2077',
})

if (resultSingle.success) {
  console.log('Updated record:', resultSingle.record)
} else {
  console.error('Update failed:', resultSingle.errors)
}

The method returns a Promise that resolves to an UpdateResult object. If there are any field validation errors, they will be available in the errors property. Additionally, the result may contain a message property that holds error messages in case there were any issues during the database query.