Data Connector Agent for SQLite

This directory contains an SQLite implementation of a data connector agent. It can use local SQLite database files as referenced by the "db" config field.

Capabilities

The SQLite agent currently supports the following capabilities:

•  GraphQL Schema
•  GraphQL Queries
•  GraphQL Mutations
•  Relationships
•  Aggregations
•  Prometheus Metrics
•  Exposing Foreign-Key Information
•  Subscriptions
•  Streaming Subscriptions

Note: You are able to get detailed metadata about the agent's capabilities by GETting the /capabilities endpoint of the running agent.

Requirements

• NodeJS 16
• SQLite >= 3.38.0 or compiled in JSON support
  • Required for the json_group_array() and json_group_object() aggregate SQL functions
  • https://www.sqlite.org/json1.html#jgrouparray

Build & Run

npm install
npm run build
npm run start

Or a simple dev-loop via entr:

echo src/**/*.ts | xargs -n1 echo | DB_READONLY=y entr -r npm run start

Options / Environment Variables

Note: Boolean flags {FLAG} can be provided as 1, true, yes, or omitted and default to false.

Agent usage

The agent is configured as per the configuration schema.

The only required field is db which specifies a local sqlite database to use.

The schema is exposed via introspection, but you can limit which tables are referenced by

• Explicitly enumerating them via the tables field, or
• Toggling the include_sqlite_meta_tables to include or exclude sqlite meta tables.

Docker Build & Run

> docker build . -t dc-sqlite-agent:latest
> docker run -it --rm -p 8100:8100 dc-sqlite-agent:latest

You will want to mount a volume with your database(s) so that they can be referenced in configuration.

Dataset

The dataset used for testing the reference agent is sourced from:

• https://raw.githubusercontent.com/lerocha/chinook-database/master/ChinookDatabase/DataSources/Chinook_Sqlite.sql

Testing Changes to the Agent

Run:

cabal run graphql-engine:test:tests-dc-api -- test --agent-base-url http://localhost:8100 --agent-config '{"db": "db.chinook2.sqlite"}'

From the HGE repo.

TODO

•  Prometheus metrics hosted at /metrics

•  Pull reference types from a package rather than checked-in files

•  Health Check

•  DB Specific Health Checks

•  Schema

•  Capabilities

•  Query

•  Array Relationships

•  Object Relationships

•  Ensure everything is escaped correctly - https://sequelize.org/api/v6/class/src/sequelize.js~sequelize#instance-method-escape

•  Or... Use parameterized queries if possible - https://sequelize.org/docs/v6/core-concepts/raw-queries/#bind-parameter

•  Run test-suite from SDK

•  Remove old queries module

•  Relationships / Joins

•  Rename resultTT and other badly named types in the schema.ts module

•  Add ENV Variable for restriction on what databases can be used

•  Update to the latest types

•  Port back to hge codebase as an official reference agent

•  Make escapeSQL global to the query module

•  Make CORS permissions configurable

•  Optional DB Allowlist

•  Fix SDK Test suite to be more flexible about descriptions

•  READONLY option

•  CREATE option

•  Don't create DB option

•  Aggregate queries

•  Verbosity settings

•  Cache settings

•  Missing WHERE clause from object relationships

•  Reuse find_table_relationship in more scenarios

•  ORDER clause in aggregates breaks SQLite parser for some reason

•  Check that looped exist check doesn't cause name conflicts

•  NOT EXISTS IS NULL != EXISTS IS NOT NULL, Example: sqlite> create table test(testid string); sqlite> .schema CREATE TABLE test(testid string); sqlite> select 1 where exists(select * from test where testid is null); sqlite> select 1 where exists(select * from test where testid is not null); sqlite> select 1 where not exists(select * from test where testid is null); 1 sqlite> select 1 where not exists(select * from test where testid is not null); 1 sqlite> insert into test(testid) values('foo'); sqlite> insert into test(testid) values(NULL); sqlite> select * from test; foo

  sqlite> select 1 where exists(select * from test where testid is null); 1 sqlite> select 1 where exists(select * from test where testid is not null); 1 sqlite> select 1 where not exists(select * from test where testid is null); sqlite> select 1 where exists(select * from test where testid is not null); 1

Known Bugs

Tricky Aggregates may have logic bug

Replicate by running the agent test-suite.