Note: I have abandoned this effort. While I have worked with many less tech-y orgs that would benefit from this library in the past, I no longer have any clients that would benefit from this use-case being filled. It was always a bit of a wacky idea. Leaving it up for historical purposes.

Introduction⌗

Squeal is a library designed to allow querying raw SQL directly from the front-end without compromising security. This document intends to formalize the design for the Squeal library and eventually become a complete specification.

Writing a Squeal Queries⌗

Hello World⌗

The most basic Squeal Query is simply SQL. Let’s start with the most basic possible Query:

SELECT 1;

To use this in Squeal, you must place it into a basic.squeal file.

After running the code-gen, you will get a basic.squeal.ts file which will look like this:

interface BasicQueryVariables {}
interface BasicQueryType {
  "1": number;
}
const basicQuery = (variables: BasicQueryVariables) =>
  squeal<BasicQueryType>("42364a0", { variables });
const useBasicQuery = (variables: BasicQueryVariables) =>
  useQuery("variables", basicQuery);

It will also output a whitelist file called squeal.whitelist with an entry for the Query:

[
  {
    "Query": "SELECT 1;",
    "hash": "42364a0",
    "Scope": "default",
    "variables": {}
  }
]

This file has all the entries for each Query used by the project. It is necessary to keep the server secure from arbitrary SQL.

Variables⌗

Sometimes your SQL queries need dynamic elements for WHERE or INSERT clauses. A Query with variables would look like this:

// file: variables.squeal
#age number
SELECT name, age FROM person WHERE age > {age};

The above will generate a file like this:

interface VariablesQueryVariables {
  age: number;
}
interface VariablesQueryType {
  name: string;
  age: number;
}
const variablesQuery = (variables: VariablesQueryVariables) =>
  squeal<VariablesQueryType>("a3f45g5", { variables });
const useVariablesQuery = (variables: VariablesQueryVariables) =>
  useQuery(["variables", variables.age], variablesQuery);

Injected Variables⌗

Sometimes when running a query, you cannot trust user input. You may need to allow a user to query their records but not anyone else’s. Passing a username as a variable is easy to tamper with.

Injected variables are variables provided by the default scope at the BeforeExecute stage. You can reference them in the Query without being declared in the Query header.

Multi-Statement Queries⌗

There is currently no specification for multi-statement queries. It is unclear what the best path forward is for them.

For the moment, the recommendation is to place all the queries into a user-defined function and call it in one line.

Scopes⌗

Scopes are an optional part of Squeal, which unlocks support for cases where the Squeal server needs to do additional logic for particular queries. By default, queries are part of the default Scope. A single query can be in multiple Scopes.

You can add Scopes to a query by writing @nameOfScope before the Query.

// file: Scopes.squeal
@requireAdmin
SELECT passwords FROM users;

Scopes can use static parameters to avoid duplicating logic on the back-end. Parameters help implement granular controls in ways beyond the capabilities of SQL.

@requireAdmin
@timeout 5000
SELECT passwords FROM users;

Limiting Query Inputs⌗

Sometimes you might want to limit acceptable variables for a Query. For example, you might want to enforce a maximum page size. While you may do this using Scopes, it is better to try to solve it with pure SQL:

// Only allow queries for a maximal interval of a week
#start DateTime
#end DateTime
SELECT user FROM event WHERE time > {start} AND time < {end} AND DATEDIFF(DAY, {start}, {end}) <=7;

Scopes⌗

Scopes can intercept a query at multiple points during the execution of a Query. In that sense, you may think of Scopes as middlewares. A single Scope may perform operations at any number of points.

Built-in Scopes⌗

Squeal comes with several Scopes already built into the server.

Security Scopes⌗

For the moment, there are no plans for security Scopes. The Squeal project will release basic JWT validation Scopes as a separate project.

The Squeal Server⌗

The Squeal client depends on a Squeal-compatible server to be available. The reference Squeal server works mostly by configuration.

Schema⌗

{
  "server": {
    "connectionString": "...",
    "port": 3773
  },
  "scopes": {
    "scopeName": ["./path/to/implementation.js"]
  },
  "providedVariables": {
    "variableName": "type"
  }
}

Writing a Scope⌗

By default, a new scope is created for every request.

import { Scope } from "@squeal/server";

const name = "multiPurpose";
export default class MultiPurposeScope extends Scope {
  // The name must be consistent with the configuration
  static name = name;

  async beforeExecute({ query, variables, scopes }) {
    console.log("Logging query", query);
  }
  async duringExecute({ query, variables, scopes, executor }) {
    const timedOutSymbol = Symbol("Timeout");
    const timeout = new Promise((res) =>
      setTimeout(() => res(timedOutSymbol), 5000)
    );
    await Promise.race([timeout, executor.query]).then((result) => {
      if (result == timedOutSymbol) {
        throw new Error("Timed out!");
      }
    });

    // Alternative implementation
    // await timeout();
    // if (!executor.completed){
    //   throw new Error("Timed out!);
    // }
  }
  async afterExecute({ query, variables, scopes, results }) {
    // Remove all data from particular column
    const scopeArguments = scopes[name];
    for (let result of results) {
      result[scopeArguments] = "******";
    }
  }
  async afterError({ query, variables, scopes, error }) {
    console.error("Error", error);
  }
}

If a scope needs to be persistent, it can be marked as a singleton. Be careful of race conditions when using this:

import { Scope, singleton } from "@squeal/server";

const name = "counterLogger";
@singleton
export default class counterLoggerScope extends Scope {
  counter = 0;
  async beforeExecute() {
    console.log("query counter: ", ++counter);
  }
}

Handling Authorization⌗

Squeal forwards all the HTTP information that was sent to it to the Scopes. For a better user experience, it comes pre-processed by Koa. It can used for arbitrary logic.

import { Scope } from "@squeal/server";

const name = "cookieChecker";
export default class cookieCheckerScope extends Scope {
  async beforeExecute({ query }, ctx) {
    if (!ctx.cookies.get("token")) throw new Error("Missing cookie!");
  }
}

Providing Variables⌗

Variables are provided by Scopes during the beforeExecute stage:

import { Scope } from "@squeal/server";

const name = "CurrentTimeProvider";
export default class CurrentTimeProviderScope extends Scope {
  async beforeExecute({ variables }) {
    variables.timestamp = Date.now();
  }
}