SQL

Configuration References

SQL Components are a special type of component that allow you to define operations as SQL queries to a remote database. A SQL operation takes inputs like any other operation and it uses those inputs as bound parameters to prepared queries. The results of the query are returned as the operation’s output.

Supported Databases

Postgres with connection strings that start with postgres://
MS SQL Server with connection strings that start with mssql://
SQLite with connection strings that start with sqlite://

Global configuration

See the SQL component documentation for more details. Important options include:

`resource`

SQL Operations require a url resource to use as the connection string.

Per-operation configuration

See the SQL operation definition documentation for more details.

`inputs`

The inputs are defined by the SQL operation definition configurations.

`outputs` (unconfigurable)

output - JSON-like representation of each returned row (may be nothing).

Example

Every component starts off with the common Component kind and an optional name.

kind: wick/component@v1
name: my_component

This example defines two operations, get_user and set_user that are backed by queries to a database.

operations:
- inputs:
  - name: id
    type: i32
  name: get_user
  query: SELECT * FROM users WHERE id = ${id}
- inputs:
  - name: name
    type: string
  - name: email
    type: string
  name: set_user
  query: INSERT INTO users(name, email) VALUES (${name}, ${email}) RETURNING *
- arguments:
  - input...
  inputs:
  - name: input
    type: string[]
  name: set_user_with_columns
  query: INSERT INTO users(name, email) VALUES ($1, $2) RETURNING *

The full configuration is located at examples/db/postgres-component.wick.

Running on the CLI with `wick invoke`

Run this component on the command line by cloning the wick repository and using wick invoke.

Note: Use cargo run -p wick-cli -- invoke to run wick from source.

Clone the repository with

git clone https://github.com/candlecorp/wick && cd wick

Notice: the arguments to the operation come after the --.

Invoke the operation get_user with the following command:

wick invoke examples/db/postgres-component.wick get_user -- --id=[...]

Invoke the operation set_user with the following command:

wick invoke examples/db/postgres-component.wick set_user -- --name=[...] --email=[...]

Invoke the operation set_user_with_columns with the following command:

wick invoke examples/db/postgres-component.wick set_user_with_columns -- --input=[...]

Complete Example

The full example configuration is included below,

name: my_component
kind: wick/component@v1
resources:
  - name: DBADDR
    resource:
      kind: wick/resource/url@v1
      url: postgres://postgres:{{ ctx.root_config.password }}@{{ ctx.root_config.host }}:{{ ctx.root_config.port }}/wick_test
component:
  kind: wick/component/sql@v1
  resource: DBADDR
  tls: false
  with:
    - name: password
      type: string
    - name: host
      type: string
    - name: port
      type: string
  operations:
    - name: get_user
      inputs:
        - name: id
          type: i32
      query: SELECT * FROM users WHERE id = ${id}
    - name: set_user
      inputs:
        - name: name
          type: string
        - name: email
          type: string
      query: INSERT INTO users(name, email) VALUES (${name}, ${email}) RETURNING *
    - name: set_user_with_columns
      inputs:
        - name: input
          type: string[]
      query: INSERT INTO users(name, email) VALUES ($1, $2) RETURNING *
      arguments:
        - input... # This is special "spread" syntax that expands the input array into individual positional arguments
tests:
  - with:
      password: '{{ctx.env.TEST_PASSWORD}}'
      host: '{{ctx.env.TEST_HOST}}'
      port: '{{ctx.env.POSTGRES_PORT}}'
    cases:
      - operation: set_user
        inputs:
          - name: name
            value: TEST_NAME
          - name: email
            value: TEST_EMAIL@example.com
        outputs:
          - name: output
            assertions:
              - operator: Contains
                value:
                  email: TEST_EMAIL@example.com
                  name: TEST_NAME

SQL