PostgreSQL, MySQL, MS SQL, BigQuery, Snowflake
In this quick start guide, we will write our first script in SQL. We will see how to connect a Windmill instance to an external SQL service and then send queries to the database using Windmill Scripts.
This tutorial covers how to create a simple script through Windmill web IDE. See the dedicated page to Develop Scripts Locally.
Windmill supports PostgreSQL, MySQL, Microsoft SQL Server, BigQuery and Snowflake. In any case, it requires creating a dedicated resource.
Although all users can use BigQuery, Snowflake and MS SQL through resources and community-available languages (TypeScript, Python, Go, Bash etc.), only instances under Enterprise edition and cloud workspaces can use BigQuery, Snowflake and MS SQL runtimes as a dedicated language.
Create Resource
Windmill provides integrations with many different apps and services with the use of Resources. Resources are rich objects in JSON that allow to store configuration and credentials.
Each Resource has a Resource Type (PostgreSQL, MySQL, MS SQL, BigQuery, Snowflake) that defines the schema that the resource of this type needs to implement. Schemas implement the JSON Schema specification.
You can find a list of all the officially supported Resource Types on Windmill Hub.
You can pin a resource to an SQL query by adding a -- database resource_path
line to your script. The query will automatically use the resource without having to specify it in the autogenerated user interface.
PostgreSQL
To be able to connect to a PostgreSQL instance (Supabase, Neon.tech, etc.), we'll need to define a Resource with the PostgreSQL
Resource Type first.
Head to the Resources page in the Windmill app, click on
"Add a resource/API" in the top right corner and select the PostgreSQL
type.
Fill out the form with the information of your PostgreSQL instance and "Test connection" if needed.
For testing purposes, you can use the sample PostgreSQL Resource provided to
every user. It is available under the path f/examples/demo_windmillshowcases
.
PostgreSQL: Add a Supabase Database
Windmill provides a wizard to easily add a Supabase database through PostgreSQL.
When creating a new PostgreSQL resource, just "Add a Supabase DB". This will lead you to a Supabase page where you need to pick your organization. Then on Windmill pick a database, fill with database password and that's it.
Use SQL to build on external APIs using Sequin
With Sequin, developers can build on top of third-party services like Salesforce or HubSpot using SQL. More details at:
MySQL
To be able to connect to a MySQL instance, we'll need to define a Resource with the MySQL
Resource Type first.
Head to the Resources page in the Windmill app, click on
"Add a resource/API" in the top right corner and select the MySQL
type.
Fill out the form with the information of your MySQL instance and "Test connection" if needed.
Property | Type | Description | Default | Required | Where to Find |
---|---|---|---|---|---|
host | string | Instance host | false | Your hosting provider's control panel or in your server's MySQL configuration file | |
port | number | Instance port | 3306 | false | Your hosting provider's control panel or in your server's MySQL configuration file |
user | string | Username | true | Created in MySQL (e.g., via phpMyAdmin or MySQL Workbench) or provided by your hosting | |
database | string | Database name | true | Created in MySQL (e.g., via phpMyAdmin or MySQL Workbench) or provided by your hosting | |
password | string | User's password | true | Created in MySQL (e.g., via phpMyAdmin or MySQL Workbench) or provided by your hosting |
MS SQL
To be able to connect to a Microsoft SQL Server instance, we'll need to define a Resource with the ms_sql_server
Resource Type first.
Head to the Resources page in the Windmill app, click on
"Add a resource/API" in the top right corner and select the ms_sql_server
type.
Fill out the form with the information of your MySQL instance and "Test connection" if needed.
Property | Type | Description | Default | Required | Where to Find |
---|---|---|---|---|---|
host | string | Instance host | true | Your hosting provider's control panel or in your server's MS SQL configuration file | |
port | number | Instance port | false | Your hosting provider's control panel or in your server's MS SQL configuration file | |
user | string | Username | true | Created in MS SQL (e.g., via SQL Server Management Studio) or provided by your hosting | |
dbname | string | Database name | true | Created in MS SQL (e.g., via SQL Server Management Studio) or provided by your hosting | |
password | string | User's password | true | Created in MS SQL (e.g., via SQL Server Management Studio) or provided by your hosting |
To specify the application intent for read-only requests, add -- ApplicationIntent=ReadOnly
to the script.
BigQuery
To be able to connect to a BigQuery instance, we'll need to define a Resource with the BigQuery
Resource Type first.
Head to the Resources page in the Windmill app, click on
"Add a resource/API" in the top right corner and select the BigQuery
type.
Property | Type | Description | Required |
---|---|---|---|
auth_provider_x509_cert_url | string | Auth provider X.509 certificate URL. | false |
client_x509_cert_url | string | Client X.509 certificate URL. | false |
private_key_id | string | ID of the private key used for authentication. | false |
client_email | string | Email associated with the service account. | false |
private_key | string | Private key used for authentication. | false |
project_id | string | Google Cloud project ID. | true |
token_uri | string | OAuth 2.0 token URI. | false |
client_id | string | Client ID used for OAuth 2.0 authentication. | false |
auth_uri | string | OAuth 2.0 authorization URI. | false |
type | string | Type of the authentication method. | false |
Here's a step-by-step guide on where to find each detail.
-
Service Account Creation:
- Go to the Google Cloud Console.
- Select the appropriate project from the top menu.
- In the left navigation pane, go to "IAM & Admin" > "Service accounts".
- Click on the "+ CREATE SERVICE ACCOUNT" button.
- Provide a name and optional description for the service account.
- Click "Create".
-
Assign Roles:
- After creating the service account, you'll be prompted to grant roles to it. Select "BigQuery" roles such as "BigQuery Admin" or "BigQuery Data Editor" based on your needs.
- Click "Continue" and "Done" to create the service account.
-
Generate Key:
- In the "Service accounts" section, find the newly created service account in the list.
- Click on the three dots on the right and select "Manage keys", then "Add Key".
- Choose the key type as "JSON" and click "Create".
-
Properties Details:
Once you've generated the key, the downloaded JSON file will contain all the required properties.
You can directly "Test connection" if needed.
Snowflake
To be able to connect to a Snowflake instance, we'll need to define a Resource with the Snowflake
Resource Type first.
Head to the Resources page in the Windmill app, click on
"Add a resource/API" in the top right corner and select the Snowflake
type.
Property | Type | Description | Required |
---|---|---|---|
account_identifier | string | Snowflake account identifier in the format <orgname>-<account_name> . | true |
private_key | string | Private key used for authentication. | true |
public_key | string | Public key used for authentication. | true |
warehouse | string | Snowflake warehouse to be used for queries. | false |
username | string | Username for Snowflake login. | true |
database | string | Name of the Snowflake database to connect to. | true |
schema | string | Schema within the Snowflake database. | false |
role | string | Role to be assumed upon connection. | false |
Here's a step-by-step guide on where to find each detail.
-
Account Identifier:
The account identifier typically follows the format:
<orgname>-<account_name>
. You can find it in the Snowflake web interface:- Log in to your Snowflake account.
- The account identifier can often be found in the URL or at the top of the Snowflake interface after you log in (in the format
https://app.snowflake.com/orgname/account_name/
).
-
Username:
The username is the Snowflake user you will use to connect to the database. You will need to create a user if you don't have one:
- In the Snowflake web interface, go to the "ACCOUNT" tab.
- Select "Users" from the left navigation pane.
- Click the "+ CREATE USER" button to create a new user with a username and password.
-
Public Key and Private Key:
To create the public and private keys, you will need to generate them using a tool like OpenSSL:
- Open a terminal window.
- Use OpenSSL to generate a public and private key pair. The exact commands may vary based on your operating system.
- For example, to generate a public key:
openssl rsa -pubout -in private_key.pem -out public_key.pem
Once you have the keys, you can copy the content and paste them into the respective fields in your configuration.
Snowflake Documentation on Key Pair Authentication & Key Pair Rotation
-
Warehouse, Schema, Database, and Role:
These parameters are specific to your Snowflake environment and will depend on how your Snowflake instance is configured:
warehouse
: The name of the Snowflake warehouse you want to connect to.schema
: The name of the Snowflake schema you want to use.database
: The name of the Snowflake database you want to connect to.role
: The role you want to use for authentication.
You can find these details in the Snowflake web interface:
- Log in to your Snowflake account.
- You can find the names of warehouses, schemas, databases, and roles in the interface or by running SQL queries.
You can directly "Test connection" if needed.
Amazon Redshift
To connect to an Amazon Redshift instance, we need to add the corresponding resource. Redshift is compatible with Windmill's PostgreSQL resources and scripts, so we'll start by adding a new PostgrSQL resource type.
Get the required values from the AWS console under CLUSTERS > your Redshift cluster.
Find the value named 'endpoint' it should look like this:
default-workgroup.475893240789.us-east-1.redshift-serverless.amazonaws.com:5439/dev
From there you can deduce your host, port and database name:
- host: default-workgroup.475893240789.us-east-1.redshift-serverless.amazonaws.com
- port: 5439
- dbname: dev
Now you can fill those values in Windmill, fill also the user and password for the db and press "Test connection" to check that it's working.
Once it's working press save and you have succesfully added your Redshift instance as a PostgreSQL resource!
Create Script
Next, let's create a script that will use the newly created Resource. From the Home page, click on the "+Script" button. Name the Script, give it a summary, and select your preferred language, PostgreSQL, MySQL, MS SQL, BigQuery, Snowflake.
You can also give more details to your script, in the settings section, you can also get back to that later at any point.
PostgreSQL
Arguments need to be passed in the given format:
-- $1 name1 = default arg
-- $2 name2
INSERT INTO demo VALUES ($1::TEXT, $2::INT) RETURNING *
"name1", "name2" being the names of the arguments, and "default arg" the optional default value.
Database resource can be specified from the UI or directly within script with a line -- database resource_path
.
You can then write your prepared statement.