Adding a database integration

Squid offers the capability to seamlessly integrate with multiple databases to allow easy access to your data from your client application. Each database can be configured with different authorization and access controls to ensure that only the data that you want to expose can be accessed by your users.

Squid's database integrations are incredible for their power and simplicity. Once you add a database integration, whether NoSQL or SQL, you can access and interact with your data using a single streamlined SDK. Rather than a watered-down version of data access with a few high-level features, Squid goes beyond what you can do with your database alone, providing endlessly customizable security functions written in TypeScript for easy coding and interpreting by your team, Query with AI for asking ad-hoc questions about your data in natural language, and fine-grained data querying that can even join results from separate database integrations. All of this is available without the need to spin up and manage your own middle tier. Squid handles the business logic so you can focus on building the best user experiences.

Connecting to a database

1. Navigate to the Integrations section in the console.
2. Click on "Available Integrations" and select the database type.
3. Enter the necessary connection details and test the connection.
4. Click "Next" and verify that the displayed schema is accurate. If necessary, remove any tables that are not intended for use with Squid (these can always be added back later).
5. Click "Save" to finalize the integration.

Squid's IP address

To prevent denial-of-service attacks, brute force password attacks, and other forms of malicious activity, some providers recommend restricting your network to allow access only from specific IP addresses. This procedure is commonly known as allowlisting, and it limits access to your resources by only accepting connections from a specific list of endpoints. Refer to your resource provider to determine if you need to add Squid's IP addresses to your access list. If allowlisting is required, use the following Squid IP addresses found in the Squid Cloud Console.

Query with AI

Query with AI lets you ask questions about your data in natural language format and get answers back in natural language format along with the database queries and steps taken to get the answer. It allows you to explore your data or ask ad-hoc questions without writing SQL or NoSQL queries. Query with AI is found on the Schema tab in your database integration.

1. If you're using Query with AI for the first time or after you've edited your schema, click the Save schema button if you haven't done so already.
2. Click on Query with AI to open up a chat UI. Ask a question and Squid AI will use the data found in your database collections to answer your question.
3. For better results, add brief descriptions about the collection by clicking on the pencil icon or about a field by clicking on the three dots next to a field and choosing Edit field.

   For example, you could add in "Two-letter abbreviation for US state" in the description for a state field that always specified US states in that format. This description would let Squid AI know to translate user questions about "California" as "CA" when querying against the state field, as an example. Descriptions can also come in handy for specifying date and time formats.

Query with AI returns an answer, the query it executed, and a walkthrough of each step that was taken. You can use these to tweak your question or add descriptions to fields so that queries and answers are more accurate.

Learn more about what you can do with Squid AI for Your Data using the Squid Client SDK.

Using a connected database

To access data from the client, you need your integration ID. The integration ID is the value you provided when creating the integration. You can view your integration ID on the integrations page of the Squid Cloud Console.

Client code

await squid
  .collection('COLLECTION_NAME', 'YOUR_INTEGRATION_ID')
  .query()
  .eq('field', 'value')
  .snapshots()
  .subscribe((data) => {
    // Do something with your data
  });

Securing your database

To secure your database integration, write one or more functions that use a database security decorator, @secureDatabase or @secureCollection. To learn more about securing your database integrations, view the documentation on securing data. As a more complex example, you can add the following function to your backend project to make sure users cannot change the userId field in a document:

Backend code

export class ExampleService extends SquidService {
  @secureCollection('table_name', 'update', 'integration_id')
  secureUpdate(context: MutationContext): boolean {
    // If the user is not authenticated, the update is not allowed.
    const auth = this.getUserAuth();
    if (!auth) return false;

    // If the userId has changed, the update is not allowed.
    const { after, before } = context.beforeAndAfterDocs;
    if (after.userId !== before.userId) return false;

    // The update is only allowed if the user making the request
    // matches the user being updated.
    return after.userId !== auth.userId;
  }
}

To publish local changes, deploy your function using the Squid CLI.

The following command deploys your Squid backend to the prod environment:

squid deploy --apiKey YOUR_PROD_API_KEY --environmentId prod

The following command deploys your backend to the dev environment:

squid deploy

Database schema

Upon registration, Squid automatically generates the database schema based on the existing schema of data in your database. To view your schema, navigate to your datbase integration in the Squid Cloud Console, click the '...' button, and select Schema from the dropdown menu. Use this interface to verify schema structures, make schema changes, and manage validation of rules, ensuring data correctness.

Understanding collections and fields

Collections are the main organizational units in your database, with their underlying structure varying based on the database integration type. In SQL databases, a collection represents a table, while in NoSQL databases a collection represents a collection of documents.

Collections are identified by a unique name within an integration. They comprise one or more documents, with a document representing a row in SQL databases and representing a document in NoSQL databases. A document contains one or more fields that denote different data elements. Each field within a collection is characterized by a specific set of attributes, facilitating streamlined data management and integration.

Here's a closer look at the general structure and attributes of collections and fields:

Collections:

• Name: A unique identifier within an integration that helps in grouping and referencing a set of related rows or documents.

Documents:

• ID: A unique identifier within a collection. IDs can be random, but can also be a user ID or other significant data point.

Fields:

• Name: The name of the field. The name should match the document field name when inserting/updating/querying.
• Type: Specifies the data category a field belongs to, encompassing types like string, number, boolean, date, map, and array. The chosen type may dictate additional validation rules:
  • String: Allows the setting of minimum and maximum lengths.
  • Number: Allows the setting of minimum and maximum values.
• Primary Key: Indicates if the field serves as a unique identifier within the collection, aiding in distinguishing records.
• Required: Marks whether the data entry in the field is mandatory.
• Default Value: Assigns a preset value to the field.

Allowing extra fields

Some database integrations--often those with a NoSQL data structure--support dynamic schmas. A dynamic schema changes as you add data, so you don't have to define a schema in advance. When data is inserted, updated, or removed, the database builds a schema automatically. Refer to your database integration's documentation to see if dynamic schema is supported.

For integrations that support a dynamic schema, extra fields are allowed by default. To prevent schema changes on a collection, navigate to the Schema tab for the datbase integration, select the desired collection, and then toggle off Allow extra fields. Once disabled, document insertions or updates with extra fields are rejected.

When a previously unknown field becomes part of the known schema after clicking Rediscover schema, the new field is accepted seamlessly, even if Allow extra fields is disabled.

Note

The Allow extra fields option is only available for database integrations that support a dynamic schema. Refer to your database's documentation to determine the schema attributes.