Examples

This page contains a whole of lot examples of RapidRows configuration files. The individual files are present in the RapidRows source code repository.

RapidRows accepts configuration files either in JSON or YAML format. The examples shown below are a mix of both. To run RapidRows with either, use one of:

$ rapidrows config.json

$ rapidrows -y config.yaml

To check if a config file is valid, use one of:

$ rapidrows --check config.json

$ rapidrows --check -y config.yaml

Checking your config file prior to starting RapidRows with it is highly recommended.

Sample data and SQL queries are from the Pagila database.

Jump to a section: Endpoints, JavaScript, Streams, Scheduled Jobs, Datasources, Parameter Validation, Other features

Hello World

Here is “Hello, world”, in json:

{
  "version": "1",
  "endpoints": [
    {
      "uri": "/hello",
      "implType": "static-text",
      "script": "world!"
    }
  ]
}

Run it with (assuming the above json is put in the file static.json):

$ rapidrows static.json

Here is the output, using curl:

$ curl -i http://localhost:8080/hello
HTTP/1.1 200 OK
Content-Length: 6
Content-Type: text/plain; charset=utf-8
Date: Mon, 19 Sep 2022 13:43:57 GMT

world!

The same configuration can be written in yaml too:

version: '1'
endpoints:
- uri: /hello
  implType: static-text
  script: world!

Run it by including the -y (aka --yaml) option of rapidrows:

$ rapidrows -y static.yaml

Changing the listening port

By default RapidRows listens on port 8080. This can be changed with the listen setting:

version: '1'
listen: '127.0.0.1:8081'
endpoints:
- uri: /hello
  implType: static-text
  script: world!

Output:

$ curl -i http://localhost:8081/hello
HTTP/1.1 200 OK
Content-Length: 6
Content-Type: text/plain; charset=utf-8
Date: Thu, 22 Sep 2022 09:32:20 GMT

world!

Endpoints

Static text

Configuration:

{
  "version": "1",
  "endpoints": [
    {
      "uri": "/hello",
      "implType": "static-text",
      "script": "world!"
    }
  ]
}

Output:

$ curl -i http://localhost:8080/hello
HTTP/1.1 200 OK
Content-Length: 6
Content-Type: text/plain; charset=utf-8
Date: Mon, 19 Sep 2022 13:43:57 GMT

world!

Static json

If the response is a static JSON string, specify implType as static-json. RapidRows will validate that the script is a valid JSON string at startup, and serve it with the appropriate Content-type header.

Configuration:

version: '1'
endpoints:
- uri: /hello-json
  implType: static-json
  script: >
    { "mykey1": 100, "mykey2": [1.2, 1.3],
      "mykey3": "hello" }    

Output:

$ curl -i http://localhost:8080/hello-json
HTTP/1.1 200 OK
Content-Length: 60
Content-Type: application/json
Date: Mon, 19 Sep 2022 14:07:58 GMT

{ "mykey1": 100, "mykey2": [1.2, 1.3],   "mykey3": "hello" }

SQL select query

The SQL queries need a datasource (a PostgreSQL database) to be configured. See the examples in the section below for datasource configuration. In these examples related to endpoint, a simple datasource that assumes the default settings of PGUSER, PGHOST etc. will work – that is, just invoking psql will connect to a database without error.

Configuration:

version: '1'
endpoints:
- uri: /query-simple-select
  implType: query-json
  datasource: pagila
  script: |
    SELECT
        CONCAT(customer.last_name, ', ', customer.first_name) AS customer,
        address.phone,
        film.title
    FROM
        rental
        INNER JOIN customer ON rental.customer_id = customer.customer_id
        INNER JOIN address ON customer.address_id = address.address_id
        INNER JOIN inventory ON rental.inventory_id = inventory.inventory_id
        INNER JOIN film ON inventory.film_id = film.film_id
    WHERE
        rental.return_date IS NULL
        AND rental_date < CURRENT_DATE
    ORDER BY
        title
    LIMIT 5    
datasources:
- name: pagila
  dbname: pagila

Output:

$ curl -i http://localhost:8080/query-simple-select
HTTP/1.1 200 OK
Content-Type: application/json
Date: Mon, 19 Sep 2022 16:07:41 GMT
Content-Length: 418

{
  "rows": [
    [
      "OLVERA, DWAYNE",
      "62127829280",
      "ACADEMY DINOSAUR"
    ],
    [
      "HUEY, BRANDON",
      "99883471275",
      "ACE GOLDFINGER"
    ],
    [
      "OWENS, CARMEN",
      "272234298332",
      "AFFAIR PREJUDICE"
    ],
    [
      "HANNON, SETH",
      "864392582257",
      "AFRICAN EGG"
    ],
    [
      "COLE, TRACY",
      "371490777743",
      "ALI FOREVER"
    ]
  ]
}

SQL select query returning CSV

The same query can be made to return the results in CSV format by changing the implType from query-json to query-csv:

version: '1'
endpoints:
- uri: /query-simple-select-csv
  implType: query-csv
  datasource: pagila
  script: |
    SELECT
        CONCAT(customer.last_name, ', ', customer.first_name) AS customer,
        address.phone,
        film.title
    FROM
        rental
        INNER JOIN customer ON rental.customer_id = customer.customer_id
        INNER JOIN address ON customer.address_id = address.address_id
        INNER JOIN inventory ON rental.inventory_id = inventory.inventory_id
        INNER JOIN film ON inventory.film_id = film.film_id
    WHERE
        rental.return_date IS NULL
        AND rental_date < CURRENT_DATE
    ORDER BY
        title
    LIMIT 5    
datasources:
- name: pagila
  dbname: pagila

Output:

$ curl -i http://localhost:8080/query-simple-select-csv
HTTP/1.1 200 OK
Content-Type: text/csv; charset=utf-8
Date: Mon, 19 Sep 2022 16:09:57 GMT
Content-Length: 214

"OLVERA, DWAYNE",62127829280,ACADEMY DINOSAUR
"HUEY, BRANDON",99883471275,ACE GOLDFINGER
"OWENS, CARMEN",272234298332,AFFAIR PREJUDICE
"HANNON, SETH",864392582257,AFRICAN EGG
"COLE, TRACY",371490777743,ALI FOREVER

SQL select query with a parameter

Queries can have parameters. Here is a query that takes a single parameter – with a name descfts, of type string, and is required. The parameter should be passed in the query string (in = query).

The select statement does a full text search on the description column of films.

version: '1'
endpoints:
- uri: /query-with-param
  implType: query-json
  datasource: pagila
  script: |
        SELECT title, description FROM film WHERE fulltext @@ to_tsquery($1) ORDER BY title ASC
  params:
  - name: descfts
    in: query
    type: string
    required: true
datasources:
- name: pagila
  dbname: pagila

Here’s the output with the parameter set to the value squirrel & drama:

$ curl -i 'http://localhost:8080/query-with-param?descfts=squirrel%26drama'
HTTP/1.1 200 OK
Content-Type: application/json
Date: Tue, 20 Sep 2022 04:32:48 GMT
Content-Length: 709

{
  "rows": [
    [
      "AMELIE HELLFIGHTERS",
      "A Boring Drama of a Woman And a Squirrel who must Conquer a Student in A Baloon"
    ],
    [
      "BULL SHAWSHANK",
      "A Fanciful Drama of a Moose And a Squirrel who must Conquer a Pioneer in The Canadian Rockies"
    ],
    [
      "CREATURES SHAKESPEARE",
      "A Emotional Drama of a Womanizer And a Squirrel who must Vanquish a Crocodile in Ancient India"
    ],
    [
      "KWAI HOMEWARD",
      "A Amazing Drama of a Car And a Squirrel who must Pursue a Car in Soviet Georgia"
    ],
    [
      "WOLVES DESIRE",
      "A Fast-Paced Drama of a Squirrel And a Robot who must Succumb a Technical Writer in A Manhattan Penthouse"
    ]
  ]
}

Parameter in body

The parameter (like descfts in the previous example) can be passed either as a query parameter (in=query, like above), or as part of the URI path (in=path) or in the body (in=body).

Here is a configuration where it is in the body:

version: '1'
endpoints:
- uri: /param-in-body
  implType: query-json
  datasource: pagila
  script: |
        SELECT title, description FROM film WHERE fulltext @@ to_tsquery($1) ORDER BY title ASC
  params:
  - name: descfts
    in: body
    type: string
    required: true
datasources:
- name: pagila
  dbname: pagila

RapidRows understands parameters with bodies of content type application/x-www-form-urlencoded (POST form) and application/json (body is a JSON object).

Here is how to call it with a POST form:

$ curl -i -X POST http://localhost:8080/param-in-body \
> -H 'Content-Type: application/x-www-form-urlencoded' \
> -d 'descfts=squirrel%26drama'
HTTP/1.1 200 OK
Content-Type: application/json
Date: Tue, 20 Sep 2022 05:50:42 GMT
Content-Length: 709

{
  "rows": [
    [
      "AMELIE HELLFIGHTERS",
      "A Boring Drama of a Woman And a Squirrel who must Conquer a Student in A Baloon"
    ],
    [
      "BULL SHAWSHANK",
      "A Fanciful Drama of a Moose And a Squirrel who must Conquer a Pioneer in The Canadian Rockies"
    ],
    [
      "CREATURES SHAKESPEARE",
      "A Emotional Drama of a Womanizer And a Squirrel who must Vanquish a Crocodile in Ancient India"
    ],
    [
      "KWAI HOMEWARD",
      "A Amazing Drama of a Car And a Squirrel who must Pursue a Car in Soviet Georgia"
    ],
    [
      "WOLVES DESIRE",
      "A Fast-Paced Drama of a Squirrel And a Robot who must Succumb a Technical Writer in A Manhattan Penthouse"
    ]
  ]
}

And here it is called with the parameter in a JSON body:

$ curl -i -X POST http://localhost:8080/param-in-body \
> -H 'Content-Type: application/json' \
> -d '{ "descfts": "squirrel & drama" }'
HTTP/1.1 200 OK
Content-Type: application/json
Date: Tue, 20 Sep 2022 05:55:18 GMT
Content-Length: 709

{
  "rows": [
    [
      "AMELIE HELLFIGHTERS",
      "A Boring Drama of a Woman And a Squirrel who must Conquer a Student in A Baloon"
    ],
    [
      "BULL SHAWSHANK",
      "A Fanciful Drama of a Moose And a Squirrel who must Conquer a Pioneer in The Canadian Rockies"
    ],
    [
      "CREATURES SHAKESPEARE",
      "A Emotional Drama of a Womanizer And a Squirrel who must Vanquish a Crocodile in Ancient India"
    ],
    [
      "KWAI HOMEWARD",
      "A Amazing Drama of a Car And a Squirrel who must Pursue a Car in Soviet Georgia"
    ],
    [
      "WOLVES DESIRE",
      "A Fast-Paced Drama of a Squirrel And a Robot who must Succumb a Technical Writer in A Manhattan Penthouse"
    ]
  ]
}

Caching

To cache the query results for a while, add the cache setting to the endpoint. The value is the number of seconds to cache the result.

The caching is done on server-side. The cache entries are different for the same endpoint but with different values for parameters.

Configuration:

version: '1'
endpoints:
- uri: /param-in-body
  implType: query-json
  datasource: pagila
  script: |
        SELECT title, description FROM film WHERE fulltext @@ to_tsquery($1) ORDER BY title ASC
  params:
  - name: descfts
    in: body
    type: string
    required: true
  cache: 3600
datasources:
- name: pagila
  dbname: pagila

Timeout

A timeout can be specified for a query, by including the timeout setting for an endpoint. The value is specified in seconds.

Configuration:

version: '1'
endpoints:
- uri: /query-timeout
  implType: query-json
  datasource: pagila
  script: SELECT pg_sleep(60)
  timeout: 5
datasources:
- name: pagila
  dbname: pagila

The RapidRows server returns a 500 with a body, on timeout errors.

Output:

$ curl -i http://localhost:8080/query-timeout
HTTP/1.1 500 Internal Server Error
Content-Type: application/json
Date: Wed, 21 Sep 2022 03:28:03 GMT
Content-Length: 68

{
  "rows": null,
  "error": "timeout: context deadline exceeded"
}

Non-select SQL statements

SQL queries that do not return rows (like insert, update, delete, call etc.) are specified using implType=exec. The endpoint only returns the number of rows affected, and no rows itself.

Configuration:

version: '1'
endpoints:
- uri: /exec
  implType: exec
  methods:
  - POST
  datasource: pagila
  script: UPDATE rental SET return_date = now() WHERE rental_id = $1
  params:
  - name: rental_id
    in: body
    type: integer
    minimum: 1
    required: true
datasources:
- name: pagila
  dbname: pagila

Output:

$ curl -i -X POST http://localhost:8080/exec -H 'Content-Type: application/json' -d '{ "rental_id": 3 }'
HTTP/1.1 200 OK
Content-Type: application/json
Date: Wed, 21 Sep 2022 04:13:33 GMT
Content-Length: 24

{
  "rowsAffected": 1
}

Transaction options

The SQL statements of an endpoint (typically for exec ones, but also allowed for query-*) can be run in a transaction. Use the tx setting to set the transaction type (read only or read write), the ISO level (serializable, repeatable read or read committed) and deferrable (true or false).

Configuration:

version: '1'
endpoints:
- uri: /exec
  implType: exec
  methods:
  - POST
  datasource: pagila
  script: UPDATE rental SET return_date = now() WHERE rental_id = $1
  params:
  - name: rental_id
    in: body
    type: integer
    minimum: 1
    required: true
  tx:
    access: 'read write'
    level: 'serializable'
    deferrable: false
datasources:
- name: pagila
  dbname: pagila

Here is the output when the transaction fails due to a serialization conflict:

$ curl -i -X POST http://localhost:8080/exec -H 'Content-Type: application/json' -d '{ "rental_id": 3 }'
HTTP/1.1 500 Internal Server Error
Content-Type: application/json
Date: Wed, 21 Sep 2022 04:27:22 GMT
Content-Length: 105

{"rowsAffected":0,"error":"ERROR: could not serialize access due to concurrent update (SQLSTATE 40001)"}

JavaScript

Extra validation

Set implType to javascript to run JavaScript code when the endpoint is hit. The code itself is set in script. Here is an endpoint that performs extra validation on its input.

version: '1'
endpoints:
- uri: /exec-js
  implType: javascript
  methods:
  - POST
  datasource: pagila
  script: |
    // get a connection to a datasource
    let conn = $sys.acquire("pagila");

    // perform a query
    let genreResult = conn.query(`
      select C.name
        from rental R
        join inventory I on R.inventory_id = I.inventory_id
        join film_category FC on I.film_id = FC.film_id
        join category C on C.category_id = FC.category_id
      where R.rental_id = $1
    `, $sys.params.rental_id);

    // check the result
    if (genreResult.rows.length != 1)
      throw "Rental not found";
    const genre = genreResult.rows[0][0];

    // further checks
    let today = (new Date()).getDay();
    if (genre == "Horror" && today == 3)
      throw "Cannot return Horror DVDs on Wednesdays!"

    // exec a SQL without a resultset
    conn.exec("UPDATE rental SET return_date = now() WHERE rental_id = $1",
      $sys.params.rental_id)    
  params:
  - name: rental_id
    in: body
    type: integer
    minimum: 1
    required: true
datasources:
- name: pagila
  dbname: pagila

Output on success:

$ curl -i -X POST http://localhost:8080/exec-js -H 'Content-Type: application/json' -d '{ "rental_id": 3 }'
HTTP/1.1 204 No Content
Date: Wed, 21 Sep 2022 06:07:17 GMT

Output on an invalid rental ID:

$ curl -i -X POST http://localhost:8080/exec-js -H 'Content-Type: application/json' -d '{ "rental_id": 17000 }'
HTTP/1.1 500 Internal Server Error
Content-Type: text/plain; charset=utf8
Date: Wed, 21 Sep 2022 06:07:24 GMT
Content-Length: 16

Rental not found

Output (comes only on Wednesdays):

$ curl -i -X POST http://localhost:8080/exec-js -H 'Content-Type: application/json' -d '{ "rental_id": 4 }'
HTTP/1.1 500 Internal Server Error
Content-Type: text/plain; charset=utf8
Date: Wed, 21 Sep 2022 06:07:14 GMT
Content-Length: 40

Cannot return Horror DVDs on Wednesdays!

Customized response

You can return a JavaScript object or text by setting the result property on the $sys object from your script:

version: '1'
endpoints:
- uri: /rental/{rental_id}
  implType: javascript
  datasource: pagila
  script: |
    // get a connection to a datasource
    let conn = $sys.acquire("pagila");

    // perform a query
    let queryResult = conn.query(`
      select C.first_name || ' ' || C.last_name
        from rental R
        join customer C on R.customer_id = C.customer_id
      where R.rental_id = $1
    `, $sys.params.rental_id);

    // check the result
    if (queryResult.rows.length != 1)
      throw "Rental not found";
    const custname = queryResult.rows[0][0];

    // return a result
    $sys.result = { 'custname': custname };    
  params:
  - name: rental_id
    in: path
    type: integer
    minimum: 1
    required: true
datasources:
- name: pagila
  dbname: pagila

Output on success:

$ curl -i http://localhost:8080/rental/4
HTTP/1.1 200 OK
Content-Type: application/json
Date: Wed, 21 Sep 2022 14:36:56 GMT
Content-Length: 33

{
  "custname": "ANDREW PURDY"
}

Streams

Streams allow you forward notifications sent to a PostgreSQL channel via WebSockets or Server Sent Events to all connected clients.

WebSockets

Forward all notifications on channel payment_received to a WebSocket with URI /new_payments_ws:

version: '1'
streams:
- uri: '/new_payments_ws'
  type: 'websocket'
  datasource: 'pagila'
  channel: 'payment_received'
datasources:
- name: 'pagila'
  dbname: 'pagila'

WebSocket client code:

<!doctype html>
<html lang="en-US">
  <head>
    <meta charset="utf-8"/>
  </head>
  <body>
    <textarea id="messages" cols="80" rows="40" style="margin: 10px; border: 1px solid #888"></textarea>

    <script type="text/javascript">
      let messages = document.getElementById("messages");
      let socket = new WebSocket("ws://localhost:8080/new_payments_ws");

      socket.onopen = (e) => {
        messages.value += "event: open\n";
      };
      socket.onclose = (e) => {
          messages.value += `event: close; wasClean=${e.wasClean}, code=${e.code}, reason=${e.reason}`;
      };
      socket.onerror = (e) => {
        messages.value += `event: error; message=${e.message}`;
      };
      socket.onmessage = (e) => {
        messages.value += `event: data; data=${e.data}\n`;
      };
    </script>
  </body>
</html>

Send notifications using psql:

pagila=> notify payment_received, 'MARY SMITH paid us $10.35';
NOTIFY
pagila=> notify payment_received, 'PATRICIA JOHNSON paid us $4.99';
NOTIFY
pagila=>

Output in the web page’s textarea:

event: open
event: data; data=MARY SMITH paid us $10.35
event: data; data=PATRICIA JOHNSON paid us $4.99

Server Sent Events

Use server sent events instead, change the type to sse:

version: '1'
streams:
- uri: '/new_payments_sse'
  type: 'sse'
  datasource: 'pagila'
  channel: 'payment_received'
datasources:
- name: 'pagila'
  dbname: 'pagila'

Output using curl:

$ curl -i http://localhost:8080/new_payments_sse
HTTP/1.1 200 OK
Cache-Control: no-cache
Connection: keep-alive
Content-Type: text/event-stream
Date: Thu, 22 Sep 2022 06:31:54 GMT
Transfer-Encoding: chunked

:

data: MARY SMITH paid us $10.35

data: PATRICIA JOHNSON paid us $4.99

Scheduled Jobs

Jobs can be scheduled to run at specified intervals. The jobs themselves can be written as SQL statements or JavaScript code.

SQL statements

Here is a job scheduled to run every hour, on the hour. It executes one SQL statement (for multiple SQL statements separate them using ;). The schedule is specified in the CRON syntax.

{
  "version": "1",
  "jobs": [
    {
      "name": "refresh-sales",
      "schedule": "0 * * * *",
      "type": "exec",
      "script": "REFRESH MATERIALIZED VIEW rental_by_category",
      "datasource": "pagila",
      "debug": true
    }
  ],
  "datasources": [
    {
      "name": "pagila",
      "dbname": "pagila"
    }
  ]
}

Setting debug to true tells RapidRows to emit more logs during the execution of the job to help with debugging. Note that errors are always logged.

2022-09-22 08:57:57.265 INF successfully connected to datasource datasource=pagila
2022-09-22 08:57:57.266 INF API server started successfully listen=:8080
2022-09-22 09:00:00 DBG job starting job=refresh-sales
2022-09-22 09:00:00.099 DBG job completed successfully elapsed=99.330877 job=refresh-sales

JavaScript code

Similar to endpoints, the jobs can also be written in JavaScript. Here is one that is scheduled to run on the 28th of every month at 10am, and creates a new partition for the next month.

version: '1'
jobs:
- name: create-monthly-partition
  type: javascript
  schedule: '0 10 28 * *'
  datasource: pagila
  script: |
    // find the YYYY and MM for the next month and the one after
    const now = new Date();
    const nextMonth  = new Date(now.getFullYear(), now.getMonth()+1, 1);
    const next2Month = new Date(nextMonth.getFullYear(), nextMonth.getMonth()+1, 1);
    const y1 = nextMonth.getFullYear(), m1 = nextMonth.getMonth() + 1;
    const y2 = next2Month.getFullYear(), m2 = next2Month.getMonth() + 1;
    const m1s = (m1 < 10 ? '0' : '') + m1, m2s = (m2 < 10 ? '0' : '') + m2;
    
    // make the sql to create a partition for next month
    const sql = `
    CREATE TABLE public.payment_p${y1}_${m1s} (
      payment_id integer DEFAULT nextval('public.payment_payment_id_seq'::regclass) NOT NULL,
      customer_id integer NOT NULL,
      staff_id integer NOT NULL,
      rental_id integer NOT NULL,
      amount numeric(5,2) NOT NULL,
      payment_date timestamp with time zone NOT NULL
    );
    
    ALTER TABLE ONLY public.payment
    ATTACH PARTITION public.payment_p${y1}_${m1s}
    FOR VALUES FROM ('${y1}-${m1s}-01 00:00:00+00') TO ('${y2}-${m2s}-01 00:00:00+00');`
    
    // run the sql
    $sys.acquire('pagila').exec(sql)    
datasources:
- dbname: pagila
  name: pagila

Repeat interval syntax

If you don’t care exactly when a job runs, only that it should run at a specific frequency, you can use the simpler schedule syntax @every X where X is a duration string like 10h or 10h5m or 10h5m15s.

Note that if the schedule is say @every 5m, the job will be started every 5 minutes, irrespective of how long the job takes to complete. Specifically, it is NOT the duration between jobs.

Here is the first example job written using this syntax:

{
  "version": "1",
  "jobs": [
    {
      "name": "refresh-sales-every-format",
      "schedule": "@every 1h",
      "type": "exec",
      "script": "REFRESH MATERIALIZED VIEW rental_by_category",
      "datasource": "pagila",
      "debug": true
    }
  ],
  "datasources": [
    {
      "name": "pagila",
      "dbname": "pagila"
    }
  ]
}

Datasources

Here is a full-featured configuration to connect to a PostgreSQL database over the network using SSL and a passfile:

{
  "version": "1",
  "datasources": [
    {
      "name": "pagila",
      "host": "pg-staging-3.corp.example.com",
      "dbname": "pagila",
      "user": "webappuser",
      "passfile": "/home/deploy/.pgpass",
      "sslmode": "require",
      "params": {
        "application_name": "rapidrows",
        "search_path": "pagila,public"
      },
      "timeout": 10
    }
  ]
}

Role

Use the role setting to make RapidRows do a “SET ROLE” just after every connection:

{
  "version": "1",
  "datasources": [
    {
      "name": "pagila",
      "user": "postgres",
      "role": "readonlyuser"
    }
  ]
}

Pool

To use connection pool, use the pool setting. Here is a pool of at least 5 connections, expanding automatically up to 10 on demand, and with connections closing after 10 minutes of inactivity:

{
  "version": "1",
  "datasources": [
    {
      "name": "pagila",
      "pool": {
        "minConns": 5,
        "maxConns": 10,
        "maxIdleTime": 600
      }
    }
  ]
}

Lazy connections

By default RapidRows will establish the minimum number of connections specified for each datasource at startup. To do this on first demand, set the lazy option for the pool:

{
  "version": "1",
  "datasources": [
    {
      "name": "pagila",
      "pool": {
        "minConns": 5,
        "maxConns": 10,
        "maxIdleTime": 600,
        "lazy": true
      }
    }
  ]
}

Parameter Validation

Required or optional

A parameter can be set as required, in which case the endpoint will fail (with a 400 status code) if the parameter is not supplied by the client. If required is not set and the parameter is not supplied, it will be passed as a SQL NULL to queries.

version: '1'
endpoints:
- uri: /params-required
  implType: query-json
  datasource: pagila
  script: |
        SELECT $1, $2
  params:
  - name: required_param
    in: body
    type: string
    required: true
  - name: optional_param
    in: body
    type: string
datasources:
- name: pagila
  dbname: pagila

Enumerated list

The values of parameters of type string, number or integer can be limited to a specific list of allowed values using the enum setting:

version: '1'
endpoints:
- uri: /params-enum
  implType: query-json
  datasource: pagila
  script: |
        SELECT $1, $2
  params:
  - name: lang
    in: body
    type: string
    required: true
    enum: [ english, german, japanese ]
  - name: year
    in: body
    type: integer
    required: true
    enum: [ 2022, 1950 ]
datasources:
- name: pagila
  dbname: pagila

Minimum and maximum

Minimum and maximum can be set for values of type integer or number:

version: '1'
endpoints:
- uri: /params-minmax
  implType: query-json
  datasource: pagila
  script: |
        SELECT $1
  params:
  - name: year
    in: body
    type: integer
    required: true
    minimum: 1972
    maximum: 2022
datasources:
- name: pagila
  dbname: pagila

Pattern and maxlength for strings

Strings can be specified not to exceed a certain number of characters using maxLength, and to satisfy a regular expression set using pattern.

Pattern must match the whole string, and will be used as an anchored expression.

version: '1'
endpoints:
- uri: /params-strings
  implType: query-json
  datasource: pagila
  script: |
        SELECT $1
  params:
  - name: lang
    in: body
    type: string
    maxLength: 10
    pattern: '.*(ish|an|ese)'
datasources:
- name: pagila
  dbname: pagila

Arrays

Arrays (type=array) must have an element type specified (using elemType). The minimum and maximum items in the array can optionally be set with minItems and maxItems.

version: '1'
endpoints:
- uri: /params-arrays
  implType: query-json
  datasource: pagila
  script: |
        SELECT $1
  params:
  - name: genres
    in: body
    type: array
    elemType: string
    minItems: 1
    maxItems: 5
datasources:
- name: pagila
  dbname: pagila

Other features

CORS

CORS can be enabled and configured at server-level using the cors setting.

version: '1'
cors:
  allowedOrigins: [ 'http://foo.com' ]
  allowedMethods: [ 'GET' ]
  allowedHeaders: [ 'X-Pingother', 'Content-Type' ]
  debug: true
endpoints:
- uri: /hello
  implType: static-text
  script: world!

A successful pre-flight request with allowed origin, method and headers:

$ curl -X OPTIONS -i http://localhost:8080/hello -H 'Origin: http://foo.com' -H 'Access-Control-Request-Method: GET' -H 'Access-Control-Request-Headers: Content-type, X-PingOTHER'
HTTP/1.1 204 No Content
Access-Control-Allow-Headers: Content-Type, X-Pingother
Access-Control-Allow-Methods: GET
Access-Control-Allow-Origin: http://foo.com
Vary: Origin
Vary: Access-Control-Request-Method
Vary: Access-Control-Request-Headers
Date: Thu, 22 Sep 2022 09:16:35 GMT

An unsuccessful pre-flight request with allowed origin but disallowed method:

$ curl -X OPTIONS -i http://localhost:8080/hello -H 'Origin: http://foo.com' -H 'Access-Control-Request-Method: POST'
HTTP/1.1 204 No Content
Vary: Origin
Vary: Access-Control-Request-Method
Vary: Access-Control-Request-Headers
Date: Thu, 22 Sep 2022 09:16:51 GMT

Common prefix

A common prefix for all URIs can be specified:

{
  "version": "1",
  "commonPrefix": "/api/v1",
  "endpoints": [
    {
      "uri": "/hello",
      "implType": "static-text",
      "script": "world!"
    }
  ]
}

The URI in this case is effectively /api/v1/hello:

$ curl -i http://localhost:8080/api/v1/hello
HTTP/1.1 200 OK
Content-Length: 6
Content-Type: text/plain; charset=utf-8
Date: Thu, 22 Sep 2022 08:35:58 GMT

world!

and not just /hello:

$ curl -i http://localhost:8080/hello
HTTP/1.1 404 Not Found
Content-Type: text/plain; charset=utf-8
X-Content-Type-Options: nosniff
Date: Thu, 22 Sep 2022 08:35:53 GMT
Content-Length: 19

404 page not found

Methods

If methods is set, the endpoint responds only to those HTTP methods:

{
  "version": "1",
  "endpoints": [
    {
      "uri": "/hello",
      "methods": [ "POST", "PUT" ],
      "implType": "static-text",
      "script": "world!"
    }
  ]
}

Here it responds to POST:

$ curl -i -X POST http://localhost:8080/hello
HTTP/1.1 200 OK
Content-Length: 6
Content-Type: text/plain; charset=utf-8
Date: Thu, 22 Sep 2022 08:38:03 GMT

world!

but not a GET:

$ curl -i http://localhost:8080/hello
HTTP/1.1 405 Method Not Allowed
Date: Thu, 22 Sep 2022 08:37:57 GMT
Content-Length: 0

Compression

Compression of responses using gzip or deflate can be enabled at server-level by setting compression:

version: '1'
compression: true
endpoints:
  - uri: '/hello-compressed'
    implType: 'static-text'
    script: |
      Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod
      tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam,
      quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo
      consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse
      cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non
      proident, sunt in culpa qui officia deserunt mollit anim id est laborum.      

Uncompressed response:

$ curl -i http://localhost:8080/hello-compressed
HTTP/1.1 200 OK
Content-Length: 447
Content-Type: text/plain; charset=utf-8
Date: Thu, 22 Sep 2022 09:03:07 GMT

Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod
tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam,
quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo
consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse
cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non
proident, sunt in culpa qui officia deserunt mollit anim id est laborum.

Compressed response (curl with --compressed flag):

$ curl -i --compressed http://localhost:8080/hello-compressed
HTTP/1.1 200 OK
Content-Encoding: gzip
Content-Type: text/plain; charset=utf-8
Vary: Accept-Encoding
Date: Thu, 22 Sep 2022 09:03:15 GMT
Content-Length: 288

Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod
tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam,
quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo
consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse
cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non
proident, sunt in culpa qui officia deserunt mollit anim id est laborum.

Debug

The boolean setting debug can be set in endpoints, streams, scheduled jobs, and CORS. RapidRows will emit debug-level logs to help with debugging the relevant endpoint, stream or job, or with all CORS-related decisions.

An endpoint configured with debug:

version: '1'
endpoints:
- uri: /exec-debug
  implType: exec
  methods:
  - POST
  datasource: pagila
  script: UPDATE rental SET return_date = now() WHERE rental_id = $1
  params:
  - name: rental_id
    in: body
    type: integer
    minimum: 1
    required: true
  debug: true
datasources:
- name: pagila
  dbname: pagila

produces these logs (the “DBG” ones are because of debug=true):

$ /data/mdevan/rapidrows/rapidrows -y exec-debug.yaml
2022-09-22 14:28:02.527 INF successfully connected to datasource datasource=pagila
2022-09-22 14:28:02.527 INF API server started successfully listen=:8080
2022-09-22 14:29:08.178 DBG handler start endpoint=/exec-debug ip=[::1] params=[3]
2022-09-22 14:29:08.197 DBG exec completed successfully elapsed=18 endpoint=/exec-debug
2022-09-22 14:29:08.197 DBG handler end elapsed=18.837755 endpoint=/exec-debug

when called with:

$ curl -i -X POST http://localhost:8080/exec-debug -H 'Content-Type: application/json' -d '{ "rental_id": 3 }'
HTTP/1.1 200 OK
Content-Type: application/json
Date: Thu, 22 Sep 2022 08:59:08 GMT
Content-Length: 24

{
  "rowsAffected": 1
}