SQLite Support via Adapter Libraries

[AnyhowStep]

trafficstars

Related to https://github.com/AnyhowStep/tsql/issues/16

This issue should track work items related to adding SQLite 3 support.

I don't expect this to be done soon. There's a lot of other stuff to do first. But having a proper place to track progress is nice.

---

Here are some SQLite3 libaries,

• https://github.com/kripken/sql.js/
• https://github.com/kriasoft/node-sqlite
• https://www.npmjs.com/package/sqlite3
• https://github.com/JoshuaWise/better-sqlite3 (@phiresky)

---

• [ ] AST-to-SQL-string library

  While those libraries may have different APIs, they still all parse and execute SQLite's dialect of SQL.

  As such, it makes sense to have a separate AST-to-SQL-string library.

  The ./test directory already has an AST-to-SQL-string implementation, https://github.com/AnyhowStep/tsql/blob/master/test/sqlite-sqlfier.ts

  So, all we really need to do is clean it up, add in support for all existing expressions, and publish as a separate package.

• [ ] Support for https://github.com/kripken/sql.js/

  This is mostly so we can bring this library to web browsers. I would personally benefit from it, for personal web projects. (IndexedDB :-1: , SQL :+1: )

  The adapter library for sql.js will use the above AST-to-SQL-string implementation.

  It also needs a lot of hacking and custom code to add support for bigint, custom functions, custom aggregate functions, etc.

  The ./test directory already hacks and wraps sql.js to implement its query execution tests, https://github.com/AnyhowStep/tsql/blob/master/test/run-time/input/sql-web-worker/promise.sql.ts

  The code is very ugly, because features were prioritized over readability. A lot of clean up and refactoring will be needed.

• [ ] Support for https://github.com/JoshuaWise/better-sqlite3

  Mostly for @phiresky

  Will use the above AST-to-SQL string implementation.

  No code written for it yet. Should require less hack-ery than support for sql.js

• [ ] Document process for writing adapter libraries

  Should document the process so future attempts at adapter libraries are easier (like MySQL/PostgreSQL/etc.)

  Maybe even convince others to write their own adapter libraries?

[AnyhowStep]

Hey, @phiresky , it's been a long time but I wanted to update you.

https://anyhowstep.github.io/tsql-sqlite3-browser/test-playground/public/

I don't have an integration with better-sqlite3 yet. BUT! I have a Playground that runs both raw SQLite queries and TS code that can interact with a SQLite database on the browser.

I know there isn't any documentation yet but if you have the time, maybe you'd like to give it a spin?

[phiresky]

Hey @AnyhowStep that looks amazing!

I'll definitely try this out next time i need to do something with sqlite in TypeScript!

Regarding the playground, is there a method to get the SQlite SQL string from a TS query (instead of executing it)?

[AnyhowStep]

That's a good question, actually.

So, there are two kinds of SQL strings one can get.

• SQL string (fast-ish to build)
• Prettified SQL string (slow-ish to build)

Internally, the library always uses the unprettified SQL string. Otherwise, queries 600k+ characters long take a long time to prettify, when the SQL server doesn't even care if your SQL looks nice!

I'll admit there isn't an intuitive API to get it yet (maybe I should add it...)

---

Another thing is that the @squill/squill library (currently known as tsql) doesn't actually build any SQL strings. It only builds ASTs. The adapter libraries handle the AST-to-string conversions.

So, in the Playground, @squill/sqlite3-browser needs to be used to get the string.

---

To get the unprettified string from the Playground,

    console.log(sql.AstUtil.toSql(
        peopleNamedWithLetterIQuery, //This can be found in the Playground
        sqlite3.sqlfier
    ));

You should see something like,

SELECT "person"."bio" AS "person--bio" , "person"."description" AS "person--description" , "person"."name" AS "person--name" , "person"."personId" AS "person--personId" , ('Introduction is ' || COALESCE(CAST(LENGTH("person"."description") AS VARCHAR), (SELECT SUM(9223372036854775807) FROM (SELECT NULL UNION ALL SELECT NULL))) || ' characters long') AS "$aliased--descriptionMeta" FROM "person" WHERE "person"."name" LIKE '%i%' ESCAPE '\' ORDER BY "person"."name" ASC

---

Sometimes, you may see query strings that look kind of... weird

For example, COALESCE(expr, (SELECT SUM(9223372036854775807) FROM (SELECT NULL UNION ALL SELECT NULL)), this particular string comes from the sql.throwIfNull() function. It is not a native SQL function. But, rather, I've found it useful as an escape hatch for type-safety. So, I've sort of polyfilled it.

There's usually a good reason for why the SQL string has weird expressions. Usually. It's also possible that it may be a bug, though!

---

Getting "pretty" output is more challenging and will definitely benefit from having an intuitive API exposed.

What does it actually mean to prettify a SQL string? There are so many different rules. Also, different SQL dialects have different string constructs and key words and whatnot. So, prettification has to be done by each adapter library, and can't be done by the unified library.

And this is pretty config-heavy at the moment. For SQLite, the config is,

    console.log(sql.AstUtil.toSqlPretty(
        peopleNamedWithLetterIQuery,
        sqlite3.sqlfier,
        {
            stringTypes : [/*`""`,*/ "N''", /*"''",*/ "``", "[]", "X''", "pascal-single", "pascal-double"],
            /**
            * These `undefined` values should be ignored,
            * and should not overwrite.
            */
            openParens : undefined,
            closeParens : undefined,
        }
    ));

You should get the following output,

SELECT
  "person"."bio" AS "person--bio",
  "person"."description" AS "person--description",
  "person"."name" AS "person--name",
  "person"."personId" AS "person--personId",
  (
    'Introduction is ' || COALESCE(
      CAST(LENGTH("person"."description") AS VARCHAR),
      (
        SELECT
          SUM(9223372036854775807)
        FROM
          (
            SELECT
              NULL
            UNION ALL
            SELECT
              NULL
          )
      )
    ) || ' characters long'
  ) AS "$aliased--descriptionMeta"
FROM
  "person"
WHERE
  "person"."name" LIKE '%i%' ESCAPE '\'
ORDER BY
  "person"."name" ASC

[AnyhowStep]

If you have more questions, fire away! I need to write documentation and all that other "fun" stuff

[AnyhowStep]

All right, if you go to the Playground again and hit the "Reset" button, you should see this in the TS editor,

    //Slower performance but pretty output.
    //See sqlite3.toSql() for faster performance but ugly output.
    console.log(
        sqlite3.toSqlPretty(peopleNamedWithLetterIQuery)
    );

Then, just hit "Run" and it should console.log() a "pretty" SQL string.

[phiresky]

Nice. Thanks again for your awesome work.

The throwIfNull thing is pretty interesting. Weird that there's no way to do that "cleanly" in most PostgreSQL and SQLite, they both have RAISE statements but only allow them in functions / triggers.