Queries

Squid offers a robust query system that allows you to access your data from various sources, including joining data across multiple databases with real-time query support.

Note

Squid uses RxJs for handling streams and observables. Learn more about RxJS in the RxJs documentation.

Every Squid Application has a built-in NoSQL database with an ID of built_in_db, enabling you to start querying data without setting up an integration. You can also query data from any data integration you have added using the Squid Cloud Console. To view available database integrations and setup instructions, see the database integrations documentation.

Securing your queries

Squid's backend security rules allow you to control who can perform each specific query. Set up backend security to restrict read access to specific collections or database integrations. To learn how to restrict read and write privileges for your datbase integrations, view the docs on security rules.

When querying for document(s), you have the option of consuming a single snapshot or a stream of snapshots.

When consuming a snapshot, you receive the latest version of the document(s) as a Promise.
When consuming a stream of snapshots, the result is an RxJs Observable that emits a new snapshot every time the query result changes.

The Squid Client SDK's usage of snapshots and streams of data enables you to continuously receive real-time updates from your data sources with minimal overhead and setup.

Tip

A query returns document references. You can access the data from the document reference by calling the data getter.

Querying a single document

Note

The following examples show queries using the built-in database. To tailor the examples to a different database integration, you must include the integration ID with the collection reference. For example:

const collectionRef = squid.collection('users', 'my_integration_id');

To query a single document, obtain a reference to the document and then call the snapshot or snapshots method on it.

To obtain a reference to the document, use the doc method on the collection reference and pass the document ID as a parameter. Then access the data using the data getter on the document reference.

Client code
const docRef = await squid.collection<User>('users').doc('user_id').snapshot();
if (docRef) {
  console.log(docRef.data);
}

Alternatively, subscribe to changes on this document using the snapshots method. Each time the document changes, the observable emits a new value.

Client code
squid
  .collection<User>('users')
  .doc('user_id')
  .snapshots()
  .subscribe((docRef) => {
    console.log(docRef.data);
  });

Querying multiple documents from a collection

When querying documents from a collection, use the query method to build a query. Squid provides options to consume either a single query result using the snapshot method or a stream of query results using the snapshots method. When consuming a stream of query results in this way, the observable emits a new value each time the query results change.

Here's an example of how to get a single query snapshot that returns all the admins with an age above 18:

Client code
const users = await squid
  .collection<User>('users')
  .query()
  .gt('age', 18)
  .eq('role', 'admin')
  .snapshot();

The following example receives streaming query results using the snapshots method:

Client code
const usersObs = squid
  .collection<User>('users')
  .query()
  .gt('age', 18)
  .eq('role', 'admin')
  .snapshots();

/* Subscribe to the users observable, and log the data each time a new value is received */
usersObs.subscribe((users) => {
  console.log(
    'Got new snapshot:',
    users.map((user) => user.data)
  );
});

Query also supports returning a stream of changes using the changes method. The observable returned by this method contains three different arrays that track changes made to the collection:

inserts: Contains document references to new insertions into the collection
updates: Contains document references to updates in the collection
deletes: Contains data from deleted documents

Client code
const usersObs = squid
  .collection<User>('users')
  .query()
  .gt('age', 18)
  .eq('role', 'admin')
  .changes();

usersObs.subscribe((changes) => {
  // Logs all new insertions into the collection of admins who are 18+
  console.log(
    'Inserts:',
    changes.inserts.map((user) => user.data)
  );
  // Logs new updates in the collection where the user is now an admin who is 18+
  console.log(
    'Updates:',
    changes.updates.map((user) => user.data)
  );
  // The deletes array contains the actual deleted data without a doc reference
  console.log('Deletes:', changes.deletes);
});

De-referencing document references

When querying data from a collection, you receive document references. You can access the data from the document by calling the data getter.

Client code
const usersObs = squid
  .collection<User>('users')
  .query()
  .snapshots()
  .pipe(map((user) => user.data));

To receive the document data directly without calling the data getter, call the dereference method.

Client code
const usersDataObs = squid
  .collection<User>('users')
  .query()
  .dereference()
  .snapshots();

Joining data across collections and integrations

Squid provides a powerful feature that allows you to join multiple queries and listen for result changes. This feature is made even more powerful by the ability to join data from different data sources.

For example, you can join a dept collection and employees collection with a query to return all the employees above age 18 with their departments:

Client code
const departmentCollection = squid.collection<Dept>('dept');
const employeeCollection = squid.collection<Employee>('employees');
const employeeQuery = employeeCollection.query().gt('age', 18);

const joinObs = departmentCollection
  .joinQuery('d')
  .join(employeeQuery, 'e', {
    left: 'id',
    right: 'deptId',
  })
  .snapshots();

joinObs.subscribe((joinResult) => {
  // Use the join result here
});

In the above code, each query is assigned an alias: d for the dept collection and e for the employees collection. The join condition joins the deptId field in the employees collection with the id field in the dept collection.

While this example shows joining two collections from the built-in database, you can join from separate database integrations by providing the integration IDs in the collection references. For example, if you had two database integrations with integration IDs of integrationA and integrationB, you can perform the same join as above by adding those integration IDs to their references:

Client code
const departmentCollection = squid.collection<Dept>('dept', 'integrationA');
const employeeCollection = squid.collection<Employee>(
  'employees',
  'integrationB'
);

By default, Squid performs left joins. This means that in this example, every department is included the join result, including empty departments. For example, suppose there are two people in department A over the age of 18, but no people in department B are over the age of 18. When performing the join query, the result is the following:

Client code
type ResultType = Array<{
  d: DocumentReference<Dept>;
  e: DocumentReference<Employee> | undefined;
}>;

joinResult ===
  [
    { d: { data: { id: 'A' } }, e: { data: { id: 'employee1' } } },
    { d: { data: { id: 'A' } }, e: { data: { id: 'employee2' } } },
    { d: { data: { id: 'B' } }, e: undefined },
  ];

To exclude results with undefined data, perform an inner join. To perform an inner join, pass { isInner: true } as the fourth parameter of the join method. In the following example, department B returned:

Client code
const departmentCollection = squid.collection<Dept>('dept');
const employeeCollection = squid.collection<Employee>('employees');
const employeeQuery = employeeCollection.query().gt('age', 18);

const joinObs = departmentCollection
  .joinQuery('d')
  .join(
    employeeQuery,
    'e',
    {
      left: 'id',
      right: 'deptId',
    },
    {
      isInner: true,
    }
  )
  .snapshots();

joinObs.subscribe((joinResult) => {
  // Use the join result here
});

type ResultType = Array<{
  d: DocumentReference<Dept>;
  e: DocumentReference<Employee>; // Note no `| undefined`
}>;

joinResult ===
  [
    { d: { data: { id: 'A' } }, e: { data: { id: 'employee1' } } },
    { d: { data: { id: 'A' } }, e: { data: { id: 'employee2' } } },
  ];

To write a join between three collections, add another join to the query. For example, given collections for employees, dept and company, you can perform the following join:

Client code
const departmentCollection = squid.collection<Dept>('dept');
const employeeCollection = squid.collection<Employee>('employees');
const employeeQuery = employeeCollection.query().gt('age', 18);
const companyQuery = squid.collection<Company>('company').query();

const joinObs = departmentCollection
  .joinQuery('d')
  .join(employeeQuery, 'e', {
    left: 'id',
    right: 'deptId',
  })
  .join(companyQuery, 'c', {
    left: 'companyId',
    right: 'id',
  })
  .snapshots();

The above example joins employees with dept and dept with company. The resulting object has the following type:

Client code
type Result = Array<{
  e: DocumentReference<Employee>;
  d: DocumentReference<Dept> | undefined;
  c: DocumentReference<Company> | undefined;
}>;

Choosing the left side of the join

To choose the left side of a join, pass leftAlias in the options object as the fourth parameter of the join method. For example, to join employee with dept and employee with company, choose the left side of the join for the company collection as shown in the following:

Client code
const departmentCollection = squid.collection<Dept>('dept');
const employeeCollection = squid.collection<Employee>('employees');
const employeeQuery = employeeCollection.query().gt('age', 18);
const companyQuery = squid.collection<Company>('company').query();

const joinObs = departmentCollection
  .joinQuery('d')
  .join(employeeQuery, 'e', {
    left: 'id',
    right: 'deptId',
  })
  .join(companyQuery, 'c', {
    { left: 'companyId', right: 'id' },
    { leftAlias: 'e' }
  )
  .snapshots();

Grouping the join results

To group your join results so that repeat entries are combined, call the grouped() method on the join query. For example, when joining employees with dept and to join dept with company, use grouped to receive only one entry user.

Client code
const departmentCollection = squid.collection<Dept>('dept');
const employeeCollection = squid.collection<Employee>('employees');
const employeeQuery = employeeCollection.query().gt('age', 18);
const companyQuery = squid.collection<Company>('company').query();

const joinObs = departmentCollection
  .joinQuery('d')
  .join(employeeQuery, 'e', {
    left: 'id',
    right: 'deptId',
  })
  .join(companyQuery, 'c', {
    left: 'companyId',
    right: 'id',
  })
  .grouped()
  .snapshots();

Without the grouped() method, this query returns results of this type:

Client code
type Result = Array<{
  // The same user may return more than once (one for each department and company)
  e: DocumentReference<Employee>;
  // The same department may return more than once (one for each company)
  d: DocumentReference<Dept> | undefined;
  c: DocumentReference<Company> | undefined;
}>;

With the grouped() method, the query returns results of this type:

Client code
type Result = Array<{
  e: DocumentReference<Employee>;
  d: Array<{
    d: DocumentReference<Dept>;
    c: Array<DocumentReference<Company>>;
  }>;
}>;

You can use the grouped() method with dereference() to get the result data without the DocumentReference.

Client code
const departmentCollection = squid.collection<Dept>('dept');
const employeeCollection = squid.collection<Employee>('employees');
const employeeQuery = employeeCollection.query().gt('age', 18);
const companyQuery = squid.collection<Company>('company').query();

const joinObs = departmentCollection
  .joinQuery('d')
  .join(employeeQuery, 'e', {
    left: 'id',
    right: 'deptId',
  })
  .join(companyQuery, 'c', {
    left: 'companyId',
    right: 'id',
  })
  .grouped()
  .dereference()
  .snapshots();

This query returns results of this type:

Client code
type Result = Array<{
  e: Employee;
  d: Array<{
    d: Dept;
    c: Array<Company>;
  }>;
}>;

Tip

The backend security rules allow you to control the queries that are permitted for each user. These rules receive the QueryContext as a parameter, which contains the query. You can read more about Security Rules to understand how to write them.

Limits and sorting

Squid provides the ability to sort and limit queries, which can be useful for optimizing the performance of your application and improving the user experience.

To sort a query, use the sortBy method and specify the field to sort on, as well as an optional parameter to specify the sort order. If no sort order is provided, the query defaults to ascending order.

Here's an example of sorting a query by age in descending order:

Client code
const users = await squid
  .collection<User>('users')
  .query()
  .gt('age', 18)
  .eq('role', 'admin')
  .sortBy('age', false)
  .snapshot();

To limit the number of results returned by a query, use the limit method and specify the maximum number of results to return. If no limit is provided, the query defaults to 1000, which is also the largest maximum allowed.

Here's an example of limiting a query to 10 results:

Client code
const users = await squid
  .collection<User>('users')
  .query()
  .gt('age', 18)
  .eq('role', 'admin')
  .limit(10)
  .snapshot();

You can also use sorting and limiting together in the same query, as shown in the following example:

Client code
const users = await squid
  .collection<User>('users')
  .query()
  .gt('age', 18)
  .eq('role', 'admin')
  .sortBy('age', false)
  .limit(10)
  .snapshot();

Note

In order to prevent misuse by users, the system limits the maximum number of results that can be returned by a query to 1000.

Another feature is limitBy. This returns only the first limit documents which have the same values in each field in fields. This enables queries such as "return the 5 youngest users in each city" (see example).

Client code
const users = await squid
  .collection<User>('users')
  .query()
  .sortBy('state')
  .sortBy('city')
  .sortBy('age')
  .sortBy('name')
  .limitBy(5, ['state', 'city'])
  .snapshot();

The returned query will have a maximum of 5 documents for each state and city combination. Effectively, this query returns the 5 youngest users in each city (with age ties broken by name).

Note

All fields in the limitBy clause must appear in the first n sortBy clauses for the query (where n is the number of fields). In the above example, state and city (which appear in the limitBy) must have a sortBy() in the query (and their sortBy must be before the ones age or name).

Pagination

Squid provides a powerful way to paginate query results through the paginate method on the query. The paginate method accepts a PaginationOptions object as a parameter, which contains the following properties:

pageSize: A number that defaults to 100.
subscribe: A boolean that indicates whether to subscribe to real-time updates on the query. By default, this is set to true.

Upon invocation, the pagination method returns a Pagination object with the following properties:

observeState: An observable that emits the current pagination state, defined as PaginationState.
next: A function that resolves a promise with the succeeding page state.
prev: A function that resolves a promise with the preceding page state.
waitForData: A function that returns a promise resolving with the current pagination state, once the loading process has finished.
unsubscribe: A function that instructs the pagination object to unsubscribe from the query and clear its internal state.

The PaginationState object contains the following properties:

data: An array holding the current page's data.
hasNext: A boolean indicating the availability of a next page.
hasPrev: A boolean indicating the availability of a previous page.
isLoading: A boolean indicating whether the pagination is in the process of loading data.

The following is a sample usage of pagination:

Client code
const pagination = (this.query = squid
  .collection<User>('users')
  .query()
  .gt('age', 18)
  .eq('role', 'admin')
  .sortBy('age', false)
  .dereference()
  .paginate({ pageSize: 10 }));

let data = await pagination.waitForData();
console.log(data); // Outputs the first page of data
data = await pagination.next();
console.log(data); // Outputs the second page of data
pagination.unsubscribe();

When requested, the pagination object will actively subscribe to real-time updates on the query to maintain the most current state. This means that the PaginationState object is updated with the latest data as it changes.

Note

To preserve real-time updates or to accommodate edge cases such as receiving empty pages due to server updates, the pagination object may execute more than a single query to display a page.

Note

Our pagination interface does not provide page numbers or the current page number. This design choice is deliberate due to the dynamic nature of data.

Data can often change on the server side without the client's knowledge. This fluidity can impact the accuracy of page numbers and render the concept of a "current page" ambiguous. For instance, when data gets deleted from previous pages, the sequential order of page numbers may no longer represent the accurate state of data distribution.

Moreover, maintaining the current page number involves significant overhead, which could affect the performance of your application, especially when dealing with large data sets.

By prioritizing data availability and up-to-dateness, we ensure our pagination system remains efficient and robust, adapting to real-time changes while ensuring optimal performance.

Query helpers

In addition to using the helper functions, queries can be constructed using the where function. The where function accepts three parameters: the field to query, the operator to use, and the value to compare against.

Helper	Before	After	Explanation
eq	`where('foo', '==', 'bar')`	`eq('foo', 'bar')`	Checks if `foo` is equal to `bar`
neq	`where('foo', '!=', 'bar')`	`neq('foo', 'bar')`	Checks if `foo` is not equal to `bar`
in	`where('foo', 'in', ['bar'])`	`in('foo', ['bar'])`	Checks if `foo` is in the specified list
nin	`where('foo', 'not in', ['bar'])`	`nin('foo', ['bar'])`	Checks if `foo` is not in the specified list
gt	`where('foo', '>', 'bar')`	`gt('foo', 'bar')`	Checks if `foo` is greater than `bar`
gte	`where('foo', '>=', 'bar')`	`gte('foo', 'bar')`	Checks if `foo` is greater than or equal to `bar`
lt	`where('foo', '<', 'bar')`	`lt('foo', 'bar')`	Checks if `foo` is less than `bar`
lte	`where('foo', '<=', 'bar')`	`lte('foo', 'bar')`	Checks if `foo` is less than or equal to `bar`
like (case sensitive)	`where('foo', 'like_cs', '%bar%')`	`like('foo', '%bar%')`	Checks if `foo` matches pattern `%bar%` (CS)
like	`where('foo', 'like', '%bar%')`	`like('foo', '%bar%', false)`	Checks if `foo` matches pattern `%bar%` (CI)
notLike (case sensitive)	`where('foo', 'not like_cs', '%bar%')`	`notLike('foo', '%bar%')`	Checks if `foo` does not match pattern `%bar%` (CS)
notLike	`where('foo', 'not like', '%bar%')`	`notLike('foo', '%bar%', false)`	Checks if `foo` does not match pattern `%bar%` (CI)
arrayIncludesSome	`where('foo', 'array_includes_some', ['bar'])`	`arrayIncludesSome('foo', ['bar'])`	Checks if `foo` array includes some of the values
arrayIncludesAll	`where('foo', 'array_includes_all', ['bar'])`	`arrayIncludesAll('foo', ['bar'])`	Checks if `foo` array includes all of the values
arrayNotIncludes	`where('foo', 'array_not_includes', ['bar'])`	`arrayNotIncludes('foo', ['bar'])`	Checks if `foo` array does not include any value

Queries

Squid offers a robust query system that allows you to access your data from various sources, including joining data across multiple databases with real-time query support.​

Querying a single document​

Querying multiple documents from a collection​

De-referencing document references​

Joining data across collections and integrations​

Choosing the left side of the join​

Grouping the join results​

Limits and sorting​

Pagination​

Query helpers​

Squid offers a robust query system that allows you to access your data from various sources, including joining data across multiple databases with real-time query support.

Querying a single document

Querying multiple documents from a collection

De-referencing document references

Joining data across collections and integrations

Choosing the left side of the join

Grouping the join results

Limits and sorting

Pagination

Query helpers