Pin database to sql (windmill-labs#535)

hcourdent · web-flow · commit 16cdf7735772 · 2024-03-01T18:45:17.000+01:00
diff --git a/blog/2023-11-22-why-is-windmill-the-fastest-workflow-engine/index.mdx b/blog/2023-11-22-why-is-windmill-the-fastest-workflow-engine/index.mdx
@@ -134,7 +134,7 @@ hand-built workflow engine written on top of the amazing [BEAM](<https://en.wiki
 
 There are tons of workflow engines, but not many of them are self-hostable and generic enough to support arbitrary workloads of jobs defined in code,
 and even those have restrictions:
-Some like Airflow and Prefect support only one runtime (Python). Windmill on the other hand supports Typescript/Javascript, Python, Go, Bash and direct SQL queries to BigQuery, Snowflake, Mysql, Postgresql, MSSQL. And its design makes it easy to add more upon request.
+Some like Airflow and Prefect support only one runtime (Python). Windmill on the other hand supports Typescript/Javascript, Python, Go, Bash and direct SQL queries to BigQuery, Snowflake, Mysql, Postgresql, MS SQL. And its design makes it easy to add more upon request.
 Some are notoriously hard to write for (because of complex SDKs, looking at you Airflow's XCOM or Temporal idempotency primitives) and deploy to. Windmill offers an [integrated DX to build and test workflows](/docs/flows/flow_editor) in a few minutes interactively in a mix of raw code for the steps and low-code (or YAML) for the DAG itself. It is also possible to define them wholly with code and full version control using our [VS Code extension](/blog/launch-week-1/vscode-extension).
 
 One benefit of being very fast is that it makes running tests very fast too both in terms of latency to start and to run. Wasting time waiting for previews and tests to run is not fun.
@@ -303,7 +303,7 @@ json*path.map(|x| x.split(".").map(|x| x.to_string()).collect::<Vec<*>>())
 
 ## Workers efficiency
 
-In normal mode, workers pull job one at a time, identify the language used by the job (Python, TypeScript, Go, Bash, SnowFlake, PostgreSql, MySql, MSSQL, BigQquery) and then spawn the corresponding runtime then run the job.
+In normal mode, workers pull job one at a time, identify the language used by the job (Python, TypeScript, Go, Bash, SnowFlake, PostgreSql, MySql, MS SQL, BigQquery) and then spawn the corresponding runtime then run the job.
 
 Workers run jobs bare, without running containers which gives us a performance boost compared to container based workflow engines. However, for sandboxing purposes, workers themselves can be run inside containers and can run each job in an nsjail sandbox.
 
diff --git a/changelog/2023-12-17-decision-tree/index.md b/changelog/2023-12-17-decision-tree/index.md
@@ -4,7 +4,7 @@ version: v1.227.0
 title: Decision Tree
 tags: ['App Editor', 'Component']
 image: ./decision-tree.png
-description: This component allows you to create a decision tree. The decision tree is controlled by a flow-like structure. Each node in the tree represents a decision point and can lead to one or more subsequent nodes based on specified conditions.
+description: This app component allows you to create a decision tree controlled by a flow-like structure. Each node in the tree represents a decision point with a dedicated subgrid and can lead to one or more subsequent nodes based on specified conditions.
 features:
   [
     'Each node has its dedicated container',
diff --git a/changelog/2024-01-23-rich-render/index.md b/changelog/2024-01-23-rich-render/index.md
@@ -4,7 +4,7 @@ version: v1.251.1
 title: Rich results render
 tags: ['Scripts', 'Flow Editor']
 image: ./rich-render.png
-description: Added rich results render for arrays of objects and markdown.
+description: Added rich results render for arrays of objects and markdown in scripts and flows.
 features:
   [
     'Support for arrays of objects.',
diff --git a/changelog/2024-02-27-pin-database/index.md b/changelog/2024-02-27-pin-database/index.md
@@ -0,0 +1,14 @@
+---
+slug: pin-database
+version: v1.280.0
+title: Pin Database in SQL Scripts
+tags: ['Scripts', 'SQL']
+video: /videos/pin_database.mp4
+description: Pin database resource path directly within a SQL script instead of filling it through the UI.
+features:
+  [
+    'Use "-- database resource_path" in your script.',
+    'Works with PostgreSQL, MySQL, BigQuery, Snowflake, MS SQL, GraphQL.'
+  ]
+docs: /docs/getting_started/scripts_quickstart/sql
+---
diff --git a/docs/apps/4_app_configuration_settings/1_app_component_library.mdx b/docs/apps/4_app_configuration_settings/1_app_component_library.mdx
@@ -127,7 +127,7 @@ The Carousel List component enables duplication of cards or rows with consistent
 
 ### [Decision Tree](./decision_tree.mdx)
 
-This component allows you to create a decision tree. The decision tree is controlled by a flow-like structure. Each node in the tree represents a decision point and can lead to one or more subsequent nodes based on specified conditions.
+This app component allows you to create a decision tree controlled by a flow-like structure. Each node in the tree represents a decision point with a dedicated subgrid and can lead to one or more subsequent nodes based on specified conditions.
 
 ![Decision Tree](../../assets/apps/4_app_component_library/decision-tree.png.webp)
 
diff --git a/docs/apps/4_app_configuration_settings/database_studio.mdx b/docs/apps/4_app_configuration_settings/database_studio.mdx
@@ -25,14 +25,14 @@ This component allows you to create a database studio. The database studio is a
 
 The component can be configured with the following properties:
 
-- `resource`: The PostgreSQL resource to use.
+- `resource`: The [PostgreSQL](../../getting_started/0_scripts_quickstart/5_sql_quickstart/index.mdx#postgresql) / [MySQL](../../getting_started/0_scripts_quickstart/5_sql_quickstart/index.mdx#mysql) / [MS SQL](../../getting_started/0_scripts_quickstart/5_sql_quickstart/index.mdx#ms-sql) / [Snowflake](../../getting_started/0_scripts_quickstart/5_sql_quickstart/index.mdx#snowflake) / [BigQuery](../../getting_started/0_scripts_quickstart/5_sql_quickstart/index.mdx#bigquery) resource to use.
 - `table`: The table to use. Once the resource is selected, the list will be populated with the tables available in the database.
 
 Once a resource is selected, you can explore the schema: It will open a drawer the schema of the database.
 
 ## Column definition
 
-When the table is selected, the column definitions will automatically synchronise with the columns of the table.
+When the table is selected, the column definitions will automatically synchronize with the columns of the table.
 
 The column can be configured with the following properties:
 
diff --git a/docs/apps/4_app_configuration_settings/decision_tree.mdx b/docs/apps/4_app_configuration_settings/decision_tree.mdx
@@ -2,7 +2,7 @@ import DocCard from '@site/src/components/DocCard';
 
 # Decision Tree
 
-This component allows you to create a decision tree. The decision tree is controlled by a flow-like structure. Each node in the tree represents a decision point and can lead to one or more subsequent nodes based on specified conditions.
+This app component allows you to create a decision tree controlled by a flow-like structure. Each node in the tree represents a decision point with a dedicated subgrid and can lead to one or more subsequent nodes based on specified conditions.
 
 <video
 	className="border-2 rounded-xl object-cover w-full h-full dark:border-gray-800"
diff --git a/docs/core_concepts/3_resources_and_types/index.mdx b/docs/core_concepts/3_resources_and_types/index.mdx
@@ -180,6 +180,38 @@ _Python_
 	/>
 </div>
 
+### States Path
+
+States are stored in a [path](../16_roles_and_permissions/index.mdx#path) that is unique to the script, script within a flow or schedule. The state path [is defined](https://github.com/windmill-labs/windmill/blob/19547e90c681db1566559c31a508eb093eb05968/backend/windmill-common/src/variables.rs#L150) as:
+
+```rust
+    let state_path = {
+        let trigger = if schedule_path.is_some() {
+            username.to_string()
+        } else {
+            "user".to_string()
+        };
+
+        if let Some(flow_path) = flow_path.clone() {
+            format!(
+                "{flow_path}/{}_{trigger}",
+                step_id.clone().unwrap_or_else(|| "nostep".to_string())
+            )
+        } else if let Some(script_path) = path.clone() {
+            let script_path = if script_path.ends_with("/") {
+                "noname".to_string()
+            } else {
+                script_path
+            };
+            format!("{script_path}/{trigger}")
+        } else {
+            format!("u/{username}/tmp_state")
+        }
+    };
+```
+
+For example, if a script using states is used within a flow that is scheduled, the state will take a new path as: `folder/flow_path/script_path/schedule_path`, or more concretely: `u/username/u/username/flow_name/u/username/script_name/u/username/schedule_name`.
+
 ## Using Resources
 
 Resources can be used [passed as script parameters](#passing-resources-as-parameters-to-scripts-preferred) or [directly fetched](#fetching-them-from-within-a-script-by-using-the-wmill-client-in-the-respective-language) within code.
diff --git a/docs/flows/3_editor_components.mdx b/docs/flows/3_editor_components.mdx
@@ -290,7 +290,7 @@ There are special kinds of scripts, [SQL and query languages](../getting_started
 
 - Postgres
 - MySQL
-- MSSQL
+- MS SQL
 - BigQuery
 - Snowflake
 
diff --git a/docs/getting_started/0_scripts_quickstart/5_sql_quickstart/index.mdx b/docs/getting_started/0_scripts_quickstart/5_sql_quickstart/index.mdx
@@ -1,11 +1,11 @@
 ---
-title: 'Quickstart PostgreSQL, MySQL, MSSQL, BigQuery, Snowflake'
+title: 'Quickstart PostgreSQL, MySQL, MS SQL, BigQuery, Snowflake'
 slug: '/getting_started/scripts_quickstart/sql'
 ---
 
 import DocCard from '@site/src/components/DocCard';
 
-# PostgreSQL, MySQL, MSSQL, BigQuery, Snowflake
+# 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.
 
@@ -27,7 +27,7 @@ Windmill supports [PostgreSQL](https://www.postgresql.org/), [MySQL](https://www
 Windmill provides integrations with many different apps and services with the use
 of [Resources](../../../core_concepts/3_resources_and_types/index.mdx). Resources are rich objects in JSON that allow to store configuration and credentials.
 
-Each Resource has a _Resource Type_ ([PostgreSQL](https://hub.windmill.dev/resource_types/114/postgresql), [MySQL](https://hub.windmill.dev/resource_types/111/mysql), [MSSQL](https://hub.windmill.dev/resource_types/132/ms_sql_server), [BigQuery](https://hub.windmill.dev/resource_types/108/bigquery), [Snowflake](https://hub.windmill.dev/resource_types/107/snowflake))
+Each Resource has a _Resource Type_ ([PostgreSQL](https://hub.windmill.dev/resource_types/114/postgresql), [MySQL](https://hub.windmill.dev/resource_types/111/mysql), [MS SQL](https://hub.windmill.dev/resource_types/132/ms_sql_server), [BigQuery](https://hub.windmill.dev/resource_types/108/bigquery), [Snowflake](https://hub.windmill.dev/resource_types/107/snowflake))
 that defines the schema that the resource of this type needs to implement. Schemas implement the
 [JSON Schema specification](https://json-schema.org/).
 
@@ -114,7 +114,7 @@ Fill out the form with the information of your MySQL instance and "Test connecti
 | 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 |
 
-### MSSQL
+### MS SQL
 
 To be able to connect to a [Microsoft SQL Server](https://www.microsoft.com/sql-server) instance, we'll need to define a Resource with the `ms_sql_server` Resource Type first.
 
@@ -129,11 +129,11 @@ Fill out the form with the information of your MySQL instance and "Test connecti
 
 | Property | Type   | Description     | Default | Required | Where to Find                                                                         |
 | -------- | ------ | --------------- | ------- | -------- | ------------------------------------------------------------------------------------- |
-| host     | string | Instance host   |         | true     | Your hosting provider's control panel or in your server's MSSQL configuration file    |
-| port     | number | Instance port   |         | false    | Your hosting provider's control panel or in your server's MSSQL configuration file    |
-| user     | string | Username        |         | true     | Created in MSSQL (e.g., via SQL Server Management Studio) or provided by your hosting |
-| dbname   | string | Database name   |         | true     | Created in MSSQL (e.g., via SQL Server Management Studio) or provided by your hosting |
-| password | string | User's password |         | true     | Created in MSSQL (e.g., via SQL Server Management Studio) or provided by your hosting |
+| 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 |
 
 ### BigQuery
 
@@ -262,7 +262,7 @@ You can directly "Test connection" if needed.
 ## Create Script
 
 Next, let's create a script that will use the newly created Resource. From the <a href="https://app.windmill.dev/" rel="nofollow">Home</a> page,
-click on the "+Script" button. Name the Script, give it a summary, and select your preferred language, [PostgreSQL](#postgresql-1), [MySQL](#mysql-1), [MSSQL](#mssql-1), [BigQuery](#bigquery-1), [Snowflake](#snowflake-1).
+click on the "+Script" button. Name the Script, give it a summary, and select your preferred language, [PostgreSQL](#postgresql-1), [MySQL](#mysql-1), [MS SQL](#ms-sql-1), [BigQuery](#bigquery-1), [Snowflake](#snowflake-1).
 
 ![Script creation first step](../../../assets/integrations/sql_new_script.png.webp)
 
@@ -280,6 +280,16 @@ 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`.
+
+<video
+	className="border-2 rounded-xl object-cover w-full h-full dark:border-gray-800"
+	controls
+	src="/videos/pin_database.mp4"
+/>
+
+<br/>
+
 You can then write your prepared statement.
 
 <video
@@ -300,11 +310,20 @@ INSERT INTO demo VALUES (?, ?)
 
 "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`.
+
+<video
+	className="border-2 rounded-xl object-cover w-full h-full dark:border-gray-800"
+	controls
+	src="/videos/pin_database.mp4"
+/>
+
+<br/>
 You can then write your prepared statement.
 
 ![Mysql statement](./mysql_statement.png.webp)
 
-### MSSQL
+### MS SQL
 
 Arguments need to be passed in the given format:
 
@@ -316,6 +335,15 @@ INSERT INTO demo VALUES (@p1, @p2)
 
 "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`.
+
+<video
+	className="border-2 rounded-xl object-cover w-full h-full dark:border-gray-800"
+	controls
+	src="/videos/pin_database.mp4"
+/>
+
+<br/>
 You can then write your prepared statement.
 
 ![Mysql statement](./mssql_statement.png.webp)
@@ -333,6 +361,15 @@ INSERT INTO `demodb.demo` VALUES (@name1, @name2, @name3)
 
 "name1", "name2", "name3" being the names of the arguments, "default arg" the optional default value and `string`, `integer` and `string[]` the types.
 
+Database resource can be specified from the UI or directly within script with a line `-- database resource_path`.
+
+<video
+	className="border-2 rounded-xl object-cover w-full h-full dark:border-gray-800"
+	controls
+	src="/videos/pin_database.mp4"
+/>
+
+<br/>
 You can then write your prepared statement.
 
 <video
@@ -353,6 +390,15 @@ INSERT INTO demo VALUES (?, ?)
 
 "name1", "name2" being the names of the arguments, "default arg" the optional default value and `varchar` & `int` the types.
 
+Database resource can be specified from the UI or directly within script with a line `-- database resource_path`.
+
+<video
+	className="border-2 rounded-xl object-cover w-full h-full dark:border-gray-800"
+	controls
+	src="/videos/pin_database.mp4"
+/>
+
+<br/>
 You can then write your prepared statement.
 
 ![Snowflake statement](./snowflake_statement.png.webp)
diff --git a/docs/integrations/0_integrations_on_windmill.mdx b/docs/integrations/0_integrations_on_windmill.mdx
@@ -112,7 +112,7 @@ On [self-hosted instances](../advanced/1_self_host/index.mdx), integrating OAuth
 | [Matrix](./matrix.md)                             | Open standard for decentralized, real-time communication                                                                  |
 | [Microsoft Azure Blob](./microsoft-azure-blob.md) | Microsoft's cloud storage service, an alternative to S3                                                                   |
 | [MongoDB](./mongodb.md)                           | NoSQL document-oriented database                                                                                          |
-| [MSSQL](./mssql.md)                               | database management system                                                                                                |
+| [MS SQL](./mssql.md)                               | database management system                                                                                                |
 | [MySQL](./mysql.md)                               | Open-source relational database management system                                                                         |
 | [Neon.tech](./neon.md)                            | Open-source database management system                                                                                    |
 | [Nextcloud](./nextcloud.md)                       | Suite of client-server software for creating and using file hosting services                                              |
diff --git a/docs/integrations/bigquery.mdx b/docs/integrations/bigquery.mdx
@@ -10,7 +10,7 @@ Please refer to the [SQL Getting Started section](../getting_started/0_scripts_q
 
 <div className="grid grid-cols-2 gap-6 mb-4">
 	<DocCard
-		title="SQL (PostgreSQL, MySQL, MSSQL, BigQuery, Snowflake) Quickstart"
+		title="SQL (PostgreSQL, MySQL, MS SQL, BigQuery, Snowflake) Quickstart"
 		description="Learn how to build your first script in PostgreSQL, MySQL, BigQuery, Snowflake."
 		href="/docs/getting_started/scripts_quickstart/sql"
 	/>
diff --git a/docs/integrations/mssql.md b/docs/integrations/mssql.md
@@ -1,7 +1,7 @@
-# MSSQL Integration
+# MS SQL Integration
 
-Windmill provides a framework to support MSSQL databases, either with native SQL scripts or through TypeScript for raw queries.
+Windmill provides a framework to support MS SQL databases, either with native SQL scripts or through TypeScript for raw queries.
 
-![Integration between MSSQL and Windmill](../assets/integrations/windmill_and_mssql.png 'Connect a MSSQL instance with Windmill')
+![Integration between MS SQL and Windmill](../assets/integrations/windmill_and_mssql.png 'Connect a MS SQL instance with Windmill')
 
 Please refer to the [SQL Getting Started section](../getting_started/0_scripts_quickstart/5_sql_quickstart/index.mdx).
diff --git a/docs/integrations/snowflake.mdx b/docs/integrations/snowflake.mdx
@@ -6,7 +6,7 @@ Windmill natively supports Snowflake scripts:
 
 <div className="grid grid-cols-2 gap-6 mb-4">
 	<DocCard
-		title="SQL (PostgreSQL, MySQL, MSSQL, BigQuery, Snowflake) Quickstart"
+		title="SQL (PostgreSQL, MySQL, MS SQL, BigQuery, Snowflake) Quickstart"
 		description="Learn how to build your first script in PostgreSQL, MySQL, BigQuery, Snowflake."
 		href="/docs/getting_started/scripts_quickstart/sql"
 	/>
diff --git a/docs/misc/9_guides/sequin/index.mdx b/docs/misc/9_guides/sequin/index.mdx
diff --git a/sidebars.js b/sidebars.js
diff --git a/src/landing/IntergrationList.jsx b/src/landing/IntergrationList.jsx
diff --git a/src/landing/ScriptSection.tsx b/src/landing/ScriptSection.tsx
diff --git a/static/videos/pin_database.mp4 b/static/videos/pin_database.mp4

Original file line number	Diff line number	Diff line change
`@@ -4,7 +4,7 @@ version: v1.227.0`
`4`	`4`	`title: Decision Tree`
`5`	`5`	`tags: ['App Editor', 'Component']`
`6`	`6`	`image: ./decision-tree.png`
`7`		`-description: This component allows you to create a decision tree. The decision tree is controlled by a flow-like structure. Each node in the tree represents a decision point and can lead to one or more subsequent nodes based on specified conditions.`
	`7`	`+description: This app component allows you to create a decision tree controlled by a flow-like structure. Each node in the tree represents a decision point with a dedicated subgrid and can lead to one or more subsequent nodes based on specified conditions.`
`8`	`8`	`features:`
`9`	`9`	`[`
`10`	`10`	`'Each node has its dedicated container',`
Original file line number	Diff line number	Diff line change
`@@ -4,7 +4,7 @@ version: v1.251.1`
`4`	`4`	`title: Rich results render`
`5`	`5`	`tags: ['Scripts', 'Flow Editor']`
`6`	`6`	`image: ./rich-render.png`
`7`		`-description: Added rich results render for arrays of objects and markdown.`
	`7`	`+description: Added rich results render for arrays of objects and markdown in scripts and flows.`
`8`	`8`	`features:`
`9`	`9`	`[`
`10`	`10`	`'Support for arrays of objects.',`