[fork] ws4sql v0.17.0beta1

[proofrock]

This is a first pre-release of a new fork of ws4sqlite, that will "transit" the app to a new version that will support more databases than sqlite (hence the new name, if you noticed 😉). It will take some time, through improvements and, alas, breaking changes.

This is not the latest release of ws4sqlite, which is still here.

The work will take place in the fork/ws4sql branch, and you can find the changes so far, and the steps needed to migrate a regular installation in the ROAD_TO_WS4SQL.md document.

In this release:

• Renamings
• SQLite is now brovided by mattn/go-sqlite3 and CGO. Should be quite faster.
  • CGO will be needed by other, future features, so it's a good point to reintroduce it
• Target platforms (because of CGO) are now 6 (win/amd64, macos/amd64, macos/arm64, linux/amd64, linux/arm64, linux/arm6)

As always, your feedback is deeply appreciated, if you'll be so kind. Cheers! 🍻

---

This discussion was created from the release [fork] ws4sql v0.17.0beta1.

[mskyttner]

Very nice! Here is my initial attempt at using ducksql queries with the pre-release.

I used the following duck.yaml file, intending to use an in-memory database:

database:
  type: DUCKDB
  inMemory: true
  id: duckserve
#  disableWALMode: true
  readOnly: false

auth:
  mode: HTTP
  byCredentials:
    - user: ducky
      password: duckz
readOnly: false
#corsOrigin: https://myownsite.com

storedStatements:
  - id: Q0
    sql: |
      select 42
  - id: Q1
    sql: |
      select range(10) as a, 'one-to-ten' as b
  - id: Q2
    sql: >
      from read_csv_auto('https://csvbase.com/kinder/list-of-user-agents')
  - id: Q3
    sql: |
      from read_json_auto('https://api.openalex.org/works/W4388315306')
  - id: Q4
    sql: |
      install tpch; load tpch; call tpch_queries()

initStatements:
  - load json

I attempted to run using the image from ghcr, like this:

docker run --rm -p "12321:12321" -v $(pwd)/duck.yaml:/data/duck.yaml ghcr.io/proofrock/ws4sql:latest -c /data/duck.yaml

However, I probably got something wrong because I got this complaint:

exec /ws4sql: no such file or directory

I attempted another way, getting the binary into a another local container, like so:

FROM ghcr.io/proofrock/ws4sql:latest AS build

FROM debian:bookworm-slim

RUN apt-get update && apt-get install -y --no-install-recommends \
        wget \
        curl

COPY --from=build /ws4sql /usr/local/bin
RUN chmod +x /usr/local/bin/ws4sql
EXPOSE 12321
VOLUME /data
WORKDIR /data
ENTRYPOINT ["ws4sql"]
CMD ["--help"]

After building the ws4sql container locally and starting the service with docker run -it --rm -v $(pwd)/duck.yaml:/data/duck.yaml -p "12321:12321" ws4sql -db duck.yaml I could issue requests like

curl -s -X POST \
    --user ducky:duckz \
    --header 'Content-Type: application/json' \
    --data '{"transaction": [{"query": "#Q1" }]}' \
    http://localhost:12321/duckserve

It worked nicely for queries 0, 1, 2 in that .yaml file!

There were some issues with queries 3 an 4, with complaints like this one:

{"reqIdx":-1,"error":"Json: error calling MarshalJSON for type orderedmap.OrderedMap: json: unsupported type: duckdb.Map"}

When I tried to restrict the queries that uses the "json" extension to select columns of specific regular types it worked nicely, for example this statement did not complain:

  - id: Q5
    sql: |
      from read_json_auto('https://api.openalex.org/works/W4388315306')
      select #3, #2, #1

I think some duckdb types to json mapping is fairly comprehensively done here if you consider to support things like the json extension for duckdb: https://github.com/quackscience/duckdb-extension-httpserver/pull/26/files#diff-5a3853867e339ba44486cb7f7036c326dd290818cef93b6212cf715528e3e7a7