@squill/squill

This library currently has the following adapter librariies,

• mysql-5.7

---

Documentation

Getting Started

1. Defining Tables

2. Data Types

   1. Null-Safe Equality

3. FROM clause

   1. Correlated Subqueries

4. WHERE clause

5. SELECT clause

6. ORDER BY clause

7. LIMIT clause

8. Compound Query (UNION/INTERSECT/EXCEPT)

9. Compound Query ORDER BY clause

10. Compound Query LIMIT clause

11. .map()

12. .fetchAllXxx()

13. .fetchOneXxx()

    1. table.fetchOneXxx()

14. .fetchValueArray()

15. .fetchValueXxx()

    1. table.fetchValueXxx()

16. .paginate()

17. .emulatedCursor()

18. query.count()

19. query.exists()/query.assertExists()

    1. table.exists()/table.assertExists()

20. Derived Table

21. INSERT

22. DELETE

23. UPDATE

Schema Introspection and Validation

1. Schema Introspection

2. Schema Validation

Design Pattern

1. Log

   1. Introduction

   2. Set Up

   3. INSERT

2. Table-per-type (TODO)

---

Goals

• As much as possible, compile-time type-safety!

  • Run-time checks should also be included, as much as possible (without impacting performance too much)
  • Expressions, sub-queries, correlated sub-queries, etc. must be composable and should have compile-time checks

• Provide unified query-building

  • Write query-building code once
  • Execute on server (MySQL/PostgreSQL) and browser client (using sql.js)

• Provide query-building specific to a database and version

  • Unified query-building will have to sacrifice features not supported by some database systems
  • Tailoring code to just one database and version means no need to sacrifice features (in general)

Non-Goals

• Efficiency

  • You won't catch me writing O(2ⁿ) algorithms but I won't lose sleep over wasted CPU cycles

• Direct support for MySQL BIGINT UNSIGNED type.

  • PostgreSQL and SQLite do not support BIGINT UNSIGNED.
  • Trying to shoehorn support for it has proven too complex.
  • This may be supported by the MySQL-specific version.

---

Project Structure

This project will have multiple subprojects,

• Database-unifying subproject

  • Provides all the composable components one needs to create a compile-time safe SQL query-building library
  • Unifies query-building for,
    • MySQL
      • 5.7.26
      • It's the version I use for work and personal projects at the moment
    • SQLite
      • https://github.com/kripken/sql.js/
      • 3.28
      • One of the goals is to write a query once and execute it both on a server and browser client environment
    • PostgreSQL

      • Specific version undecided

      • Must not be a version that has been released too recently

      • An eye is kept on PostgreSQL to sanity-check the other two implementations

      • According to @webstrand ,

        9.4 still receives updates, ubuntu 16.04 expires in 2021 and only has 9.5. So at least 9.5.

        Preference is leaning towards 9.4 at the moment.

  • DOES NOT produce SQL strings; only builds an abstract syntax tree
  • DOES NOT execute SQL strings
  • Major version bumps may change which databases and versions are unified
    • A future version of this library may choose to unify MySQL 8.x, PostgreSQL 11.x

  Because it must support multiple databases, it will only support features that all three databases support. This means that many features will be excluded.

• Subprojects specific to a database and version

  • Uses composable components to implement features specific to a database and version
    • This means we do not need to sacrifice features for the sake of compatibility
  • Implements abstract syntax tree to SQL string converter
  • Implements SQL execution

---

Running on Non-node Environments

This library requires BigInt support.

If your environment does not have them, you must polyfill them before this library is loaded.

---

The simplest BigInt polyfill that should work is,

(global as any).BigInt = ((value : string|number|bigint) => {
  return {
    toString : () => {
      return String(value);
    },
  };
}) as any;

---

Notes

• typed-orm supported RIGHT JOINs. Support is removed in this rewrite.

  • All RIGHT JOINs can be rewritten as LEFT JOINs

  • Using RIGHT JOIN on a LATERAL derived table introduces problems, https://dev.mysql.com/doc/refman/8.0/en/lateral-derived-tables.html

    If the table is in the left operand and contains a reference to the right operand, the join operation must be an INNER JOIN, CROSS JOIN, or RIGHT [OUTER] JOIN.

    It is possible to use a column before it even exists in the query. This complicates compile-time type checking code.

  • Maybe keep support for RIGHT JOIN but not support RIGHT JOIN LATERAL?

---

TODO

• Important, create sub-packages for different MySQL versions/different DBMSs

• Support for other DBMS'

  random 2cents: if you're going for this, I say ideally support every sql offered by aws rds (aurora, postgres, my, maira, oracle & MS)

• Refactor FromXxx to FromXxxArray if it is meant to distribute?

• Monitor this issue, https://github.com/microsoft/TypeScript/issues/32824

  • Remove all "unnecessary" parentheses from top-level type aliases

• Read https://stackoverflow.com/questions/41312641/is-it-possible-to-have-a-datetime-mapper-for-every-underlaying-rdbms

• Reduce lib and target until we reach es2015?

  • What would be a good value for these things?
  • Browser-compatibility?

• List of OIDs for built-in data types,

  • https://github.com/brianc/node-pg-types/blob/master/index.d.ts#L1
  • https://github.com/brianc/node-pg-types/blob/master/lib/builtins.js

• https://github.com/gajus/eslint-plugin-sql

  To be used with https://github.com/brianc/node-postgres

• https://okbob.blogspot.com/2009/08/mysql-functions-for-postgresql.html

• Consider forcing users of library to allow library to create a library-specific schema for stored procedures/functions to unify behaviour of built-in functions/operators.

  • Personal opinion is that this should only be a last resort.
  • Should try to unify behaviour without it.

• More goals and non-goals

• Examples of compile-time type-safety you won't get from other libraries

• Remove usages of "I" and replace with "we"?

• WTF, Postgres!?

  • https://stackoverflow.com/questions/43111996/why-postgresql-does-not-like-uppercase-table-names
  • https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

Quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to lower case. For example, the identifiers FOO, foo, and "foo" are considered the same by PostgreSQL, but "Foo" and "FOO" are different from these three and each other. (The folding of unquoted names to lower case in PostgreSQL is incompatible with the SQL standard, which says that unquoted names should be folded to upper case. Thus, foo should be equivalent to "FOO" not "foo" according to the standard. If you want to write portable applications you are advised to always quote a particular name or never quote it.)

• https://stackoverflow.com/questions/41936403/mysql-ieee-floating-point-nan-positiveinfinity-negativeinfinity

  • NaN, +Infinity, -Infinity are not valid DOUBLE values according to the SQL standard

• Investigate and compare against,

  • https://github.com/prisma/photonjs
  • https://github.com/jawj/mostly-ormless/blob/master/README.md
  • https://news.ycombinator.com/item?id=21031187
  • https://github.com/vincit/objection.js
  • https://github.com/typeorm/typeorm
  • https://github.com/sequelize/sequelize
  • https://github.com/hediet/ts-typed-sql
  • https://github.com/mikro-orm/mikro-orm

• This library will not handle migrations but it's good to keep an eye on promising migration libraries

  • https://github.com/graphile/migrate
  • https://sqitch.org/

• Remove static use of potentially polyfilled functions like BigInt, etc.

  • Stick to lazy initialization

• Investigate https://www.postgresql.org/docs/9.2/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS-ESCAPE

  • The escaping rules seem to have changed a lot over the different versions
  • An escape string constant is specified by writing the letter E (upper or lower case) just before the opening single quote, e.g., E'foo'.
  • If the configuration parameter standard_conforming_strings is off, then PostgreSQL recognizes backslash escapes in both regular and escape string constants. However, as of PostgreSQL 9.1, the default is on

• Add subqueries to certain callbacks (For example, the .where() method)

  • The .select() and .selectValue() methods already have this

---

TODO Feature Parity with typed-orm

• Emulated FULL OUTER JOIN (MySQL does not have it)

---

More TODO

• Application schema generation from DB schema?
  • Requires data type from information_schema