Native queries

Execute raw SQL or other database-specific queries directly against the database.

Why Use Native Queries

The Squid Client SDK has powerful querying functionality, but some operations require database-specific features that are not available through the standard query API. For example, SQL subselects, complex aggregations, stored procedures, or MongoDB aggregation pipelines. Native queries let you execute raw queries directly against your database connector when you need these advanced capabilities.

Overview

Native queries bypass the Squid query abstraction and run directly against your database. Squid supports two types of native queries:

• Relational queries for SQL databases (PostgreSQL, MySQL, ClickHouse, MS SQL Server) using executeNativeRelationalQuery
• MongoDB queries for MongoDB connectors using executeNativeMongoQuery

Both methods require the connector ID of the database you want to query. Find your connector ID in the connectors section of the Squid Console.

Core Concepts

Native relational queries

If using a SQL database connector, you can execute raw SQL using the executeNativeRelationalQuery method, passing your database's connector ID and the SQL query.

The following example runs a native SQL query with parameterized values:

Client code

async function runNativeQuery(queryParam: string): Promise<any> {
  const response = await this.squid.executeNativeRelationalQuery(
    'DATABASE_CONNECTOR_ID',
    'SELECT * FROM SQUIDS WHERE YEAR = ${year}',
    { year: queryParam }
  );
  return response;
}

To secure your native queries, use a Squid Service function in the Squid backend. To learn more, view the documentation on securing data access.

Native MongoDB queries

The executeNativeMongoQuery method executes a native MongoDB aggregation pipeline with the given parameters and returns a promise with the result.

The following example runs a Mongo aggregation pipeline:

Client code

async function countDocuments(): Promise<any> {
  const response = await this.squid.executeNativeMongoQuery(
    'DATABASE_CONNECTOR_ID',
    'COLLECTION_NAME',
    [{ $count: 'totalApplications' }]
  );
  return response;
}

Error Handling

Error	Cause	Solution
Invalid connector ID	The connector ID does not match any configured database connector	Verify the connector ID in the connectors section of the Squid Console
SQL syntax error	The raw SQL query contains invalid syntax	Test your query directly against the database first; check for typos and dialect-specific syntax
Invalid aggregation pipeline	The MongoDB pipeline contains invalid stages or operators	Refer to the MongoDB aggregation documentation
Security rule rejection	The query was blocked by security rules	Verify the user has permission, or run native queries from a backend executable

Best Practices

1. Use parameterized queries to prevent SQL injection. Pass values through the parameters object rather than string concatenation:

   Client code

   // Recommended: parameterized query
   const response = await this.squid.executeNativeRelationalQuery(
     'CONNECTOR_ID',
     'SELECT * FROM users WHERE name = ${name}',
     { name: userInput }
   );
   
   // Avoid: string concatenation (vulnerable to injection)
   const response = await this.squid.executeNativeRelationalQuery(
     'CONNECTOR_ID',
     `SELECT * FROM users WHERE name = '${userInput}'`
   );

2. Run sensitive native queries from backend executables rather than directly from the client, to keep query logic and credentials on the server. See executables.

3. Prefer the standard query API when it supports your use case. Native queries bypass Squid's query abstraction, real-time subscriptions, and type safety.

4. Secure native queries with backend security rules to control which queries can be executed. See securing data access.