SQL¶
Structured Query Language (SQL) is a language that facilitates relational database interactions and queries. Working with a SQL database typically takes the form of entering in statements—made up of clauses, expressions, queries, and predicates—to accomplish a task.
Some statements create or update the database itself, as well as its general structure:
CREATE DATABASE anewdatabase;
USE anewdatabase;
CREATE TABLE newtable (
column1 varchar(7),
column2 varchar(7),
column3 varchar(3)
);
And other statements query and manipulate that data:
SELECT * FROM newtable;
INSERT INTO table (column1, column2, column3)
VALUES ("bibbidi","bobbidi","boo")
Because of its flexibility and relative ease-of-use, SQL has been widely adopted by some of the largest database services in the industry.
Supported SQL dialects¶
Much like any other language, SQL’s ubiquity has led to distinct branches off the language, each with their own unique features and expectations. These are known as SQL dialects.
Each of these dialects has their own idiosyncrasies and unique syntax requirements, so Skuid contains separate data source types to connect to unique SQL instances.
All of that is to say: using a Skuid data source type (DST) to connect to a SQL database resolves the need to code individual SQL statements for a particular SQL dialect.
Skuid currently supports:
Oracle DB
Microsoft SQL Server
MySQL
PostgreSQL
Amazon Redshift
Warning
Amazon Redshift is considered a data warehouse in contrast to a database. As such, it should be considered read-only.
While technically possible to use DML operations when connecting to an Amazon Redshift instance, it is strongly discouraged due to Redshift’s pricing structure.
For more information, see the following resources:
- The Data Transfer section of AWS Redshift pricing docs
- Updating tables with DML commands from AWS Redshift docs
Prerequisites¶
Setup the Database¶
While Skuid makes interacting and creating new data within a PostgreSQL database much simpler, you must create your database schema (tables, columns, and general structure) before creating the SQL data source in Skuid. Skuid creates data source objects as references to these preexisting database elements, but they must already exist.
Understand the Database¶
Since SQL databases can be stored in various cloud services, or on-premise, there are two important points to note when creating a SQL data source:
- The port number, which must be appended to the database URL / host field. Different SQL dialects may require different port numbers.
- The use and configuration of Secure Sockets Layer—better known as SSL—to connect to the database. Some cloud services require that this option be enabled.
Understand how these concepts relate to your database before proceeding.
Allowlist IPs¶
Some databases, whether in the cloud or on-premise, use a firewall to block IPs as a security measure. However, Skuid may try to access the database from a few different IPs, depending on a Skuid site’s region. After entering the required information for the Skuid data source, Skuid displays a list of IPs your Skuid site may use.
If the database host employs a firewall, allowlist all of these IPs to ensure Skuid can properly connect. (Contact your network administrator if necessary.)
(Skuid SFX) Add Skuid’s SQL Service to CSP trusted sites¶
Skuid must query its own SQL service to properly communicate with SQL data sources. This means, in order to utilize SQL data sources in Skuid SFX, users must add the URL for that service (https://gateway-us.skuid.net
) to their trusted sites.
In Salesforce
- Navigate to Salesforce Setup.
- Enter CSP in the Setup pane’s Quick Find bar.
- Click CSP Trusted Sites.
- Click New Trusted Site.
- Enter the following properties for the trusted site definition:
- Trusted Site Name: Enter a recognizable name, like SkuidSQLService
- Trusted Site URL:
https://gateway-us.skuid.net
- Click Save.
(Skuid SFX) Create and add a JWT signing certificate¶
Because Skuid must communicate with its own data services to properly parse SQL data, you must make a JWT signing certificate within Salesforce and then update Skuid’s certificate in the Data Services tab.
- Navigate to Configure > Data Sources > Data Services.
- Click Modify JWT Signing Certificate.
- Follow the instructions listed in the popup to create a certificate and paste its contents in the field.
- Click Save.
Hosted Databases¶
Hosted SQL databases, such as Amazon Web Services RDS or Microsoft Azure, may require SSL to connect. Consult the database’s documentation to determine if and how the connection must be configured for proper usage. Azure, for example, requires SSL.
However, even if the database host doesn’t require SSL, Skuid strongly recommends using SSL to ensure the most secure connection.
Note
Using SSL may require configuration within the hosting provider.
Create a SQL Data Source¶
Select the SQL dialect for your database within the New Data Source form.
Note
Once created, SQL data sources cannot be renamed.
Then, enter these base-level data source properties:
- Database Host / IP Address: The hostname or IP address of the system hosting the database to be accessed, e.g.
ea2-54-345-12-137.server-1.sqlhost.com
or71.122.33.423
. - Database Port: The port on which the database is accessible. Common default port values include
5423
for PostgresSQL instances or3306
for MySQL instances, but database ports may vary depending on configuration. - Database Name: The name of the individual database to be accessed. This is necessary, since several unique databases can be stored on the same host.
- Database Username: The username used to login and access the database.
- Database Password: The password used to login and access the database.
The names of these properties may not match your database’s terminology exactly, but they will have always have corresponding details.
After creating the Skuid data source for your database, verify the data source’s details by clicking
Test Connection.Database SSL Configuration¶
If you use SSL to connect to your cloud database service, some additional configuration is required after creating the data source. (You may need to consult the database’s documentation and work with your database administrator to properly set these fields.)
Use SSL to Connect: Determines whether or not to use SSL when connecting to the database. Using SSL provides an increased level of security to the data connection.
Note
This property must be enabled if hosting Microsoft SQL via Azure.
- Server CA: The URL for the certificate authority (CA) that issues certificates for SSL connection.
- Client Key and Client Certificate: A set of credentials—two certificate-signed blocks—used for SSL connections, authentication, and encryption.
Data Source Object Configuration¶
Data source objects (DSOs) are Skuid’s way of translating the various tables and columns of a database into the declarative objects that Skuid models can work with. Without DSOs, Skuid models are totally unable to access the tables and records within the SQL database; essentially, DSOs are the foundation for SQL data source use.
After creating the SQL data source, you must always import or manually create DSOs for the SQL tables you wish to access.
The fastest way to get started is to click
Import Data Source Objects.Using a SQL Data Source¶
After the prerequisite configuration (data source creation and data source object management), you can use the SQL data source within a Skuid page by creating a Skuid model with properties pointing to the other elements configured thus far.
Model properties¶
In addition to several standard Skuid model properties, SQL models have some unique characteristics worth noting:
- Basic
- Data Source Type: Select the DST for the SQL dialect you’ve connected to.
- Data Source: Select the data source you created.
- Entity Name: Select the data source object pointing to the table you wish to access.
- Model Behavior: Determines whether a model will function as a basic model (the default Skuid behavior) or as an aggregate model, which summarizes multiple rows of data into aggregations.
- Advanced
- Request data in the same transfer as other Models: Determines whether or not this model’s request will be batched with any other requests to the same data source on page load. Any models on the same data source with this property checked will have their requests batched and returned at the same time after the data source’s server has resolved the request and returned all requested data. If unchecked, this model will be queried and returned first. It can be useful to disable this property if a model’s data is not dependent on another object from the same data source.
Configure fields, conditions, and model actions as you would with most other models.
Note
Data source object conditions will supersede page-level conditions.
Troubleshooting¶
As SQL data sources totally rely on data source object configuration, the best place to start troubleshooting SQL errors is verifying all DSOs are accurate and up-to-date.
My page doesn’t load data and displays this error: JWT Authorization error. Error: could not retrieve public key from microservice [[]]¶
This error indicates Skuid may have issues with the JWT signing certificate used to facilitate communication between Salesforce and Skuid’s data services. Ensure your JWT signing certificate settings are properly configured.
My page seems to break with this error: Timeout exceeded while awaiting headers. [[]]¶
You may have manually added either a DSO or a DSO field that does not exist within the database. Verify that all DSOs and all of their fields exist with the database.
My page does not load at runtime and I receive this error: DSO Provider returned zero Data Source Objects. [[]]¶
This error can occur on page load if there is a Skuid model connected to a DSO that no longer exists. Verify that all models are pointed to the correct DSOs and remove any models without corresponding DSOs.
I’m using a Microsoft SQL database hosted through Azure and getting errors with little or no information about the error. [[]]¶
After creating a Microsoft SQL database, set the SSL configuration to Use SSL to Connect. If this is not checked, the database will be unable to connect.
My SQL data source is behaving in unexpected ways. [[]]¶
Skuid must contact its cloud data service to provide accurate data for a SQL data source. This service may be updated as a part of a Skuid Platform release, and those updates can affect the page.
- Ensure that the version of Skuid SFX you are using is current. This helps your org stay compliant with Skuid’s cloud data service.
- Review any Skuid Platform release notes that have been posted since the issue began for possible causes. Also, consult the Skuid community.