MySQL script - Argument support list

Hello, I have a MySQL script, and I wish to used named arguments :

-- database f/main/my_database
-- :ids (string[]) <-- Doesn't work, same for (int[])
-- :begin_date (string)
-- :end_date (string)

select *
from my_table t
where t.id in (:ids)
  and t.date between :begin_date and :end_date

-- database f/main/my_database
-- :ids (string[]) <-- Doesn't work, same for (int[])
-- :begin_date (string)
-- :end_date (string)

select *
from my_table t
where t.id in (:ids)
  and t.date between :begin_date and :end_date

My issue is I don't know how to name my type in order to support arrays (or list), Can you help me ? I can't find the official documentation in this page https://www.windmill.dev/docs/getting_started/scripts_quickstart/sql#mysql-1 Thank you for your time !

Quickstart PostgreSQL, MySQL, MS SQL, BigQuery, Snowflake | Windmill

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.

16 Replies

Hugo•4mo ago

Hi, sadly MySQL does not support array parameters in prepared statements

CitizenOP•4mo ago

Thank you for your reply, I didn't know that, because using node-mysql and node-mysql2 always allowed to pass arrays. I just learn that under the hood, they convert list into a single string ! So I should be able to replicate this, with something like .map() & .join() before calling the SQL script Thanks for your time :)

Hugo•4mo ago

@Etienne do you think we could have a special interpolated argument instruction which joins a list with a comma?

wendrul•4mo ago

We can probably follow how they do in node-mysql and add it to our list of interpolated arguments as we had discussed in the past

CitizenOP•4mo ago

Oh great ! Can I contribute and implement it if you want ? I could try ^^

Hugo•4mo ago

Yes sure! we just need to agree on a format maybe smth like -- %%myarrarg%% (list) @Etienne wdyt?

wendrul•4mo ago

currently we have the two following formats: - -- %%myarg%% - -- %%myarg%% enum_variant1/enum_variant2 To make it safer and robust we could type check the elements of the list, so we could maybe have straight up types like: - -- %%my_intlist%% (int[])
- -- %%my_stringlist%% (string[]) The %% format lets the user know this is not normal arguments but string interpolation, but we add some "type safety" to it @Citizen, you can check the PR where we implemented the interpolated arguments in the first place as reference: https://github.com/windmill-labs/windmill/pull/5488/files

CitizenOP•4mo ago

Nice, thank you, I will try to implement it, and test it I've started the implementation, but I'm a first time user of Rust, so I wanted to validate with you the taken direction : https://github.com/windmill-labs/windmill/commit/9e4dbcaa75e50df544665cd13bc6018cd1eb3187 Currently, the notation -- %%param%% (type) seems not supported in the RE_ARG_SQL_INTERPOLATION variable. So I have added this part \((\w+(?:\([\w, ]+\))?(?:\[\])?)\), in order to support type declaration inside sanitized interpolation. It allows -- %%param%% (type) and add the list support (-- %%param%% (type[])) with this optional non capturing group (?:\[\])?

wendrul•4mo ago

Once it's ready for review I'll double check and test the regex, but yes it currently only matches the existing syntaxes we support. So yes, modifying the regex as you did / using another regex is required for this change

CitizenOP•4mo ago

I'm trying to start a local dev windmill in order to debug locally, but I am facing 404 when accessing the container (via Caddy or directly via exposing the 8000 port of container windmill_server) :

CitizenOP•4mo ago

I've edited docker-compose file by replacing the windmill_server "image" line by a "build", in order to use the local Dockerfile, it seems to compile, but once booted, I can't access to it

  windmill_server:
#    image: ${WM_IMAGE}
    build: .

  windmill_server:
#    image: ${WM_IMAGE}
    build: .

Do you have a guide in order to debug locally ? In the contributor guide (https://www.windmill.dev/docs/misc/contributing) I don't find info about it Thank you !

develoco•3mo ago

Is the local Dockerfile exposing the port which compose file is remapping to 8000? It would be helpful to see both Dockerfile and compose file.

Hugo•3mo ago

Hi @develoco for dev you should run the backend directly with cargo run so you don't have the build the docker image everytime here's the guide: https://github.com/windmill-labs/windmill?tab=readme-ov-file#backend--frontend you can skip number 2 and 3

develoco•3mo ago

Ah didn’t see that it is about local dev env, sorry

wendrul•3mo ago

If you have nix with flakes enabled, you should be able to run nix develop and get a shell with the environment setup. There are two of us working like that Should probably add that as an alternative in the readme

CitizenOP•3mo ago

Thank you for the information, I will try soon !

MySQL script - Argument support list

Did you find this page helpful?