ts-safeql / safeql

Validate and auto-generate TypeScript types from raw SQL queries in PostgreSQL.

SafeQL - Automatic Type Inference & Validation for PostgreSQL Queries • Get started

demo.mp4

Features

• ⚡️ Automatic Type Inference & Validation:

  SafeQL automatically infers the type of the query result based on the query itself.

• 🖖 Compatible With Popular SQL Libraries:

  SafeQL works with any PostgreSQL client, including Prisma, Sequelize, pg, Postgres.js, and more.

• 🛠️ Easy To Use

  SafeQL was built in mind to be easy to use and integrate with your existing codebase.

• 📦 Built with Monorepos & Microservices in mind:

  SafeQL was built with monorepos and microservices in mind, and it's easy to use with multiple databases.

Install

I would first recommend follow the instructions in the documentation.

npm install --save-dev @ts-safeql/eslint-plugin libpg-query

Limitations

Since libpg-query doesn't seem to support Windows, the only way to make SafeQL work on Windows is by using WSL2 (see issue).

safeql's Issues

Can't insert value with `type | null` typing

Describe the bug
SafeQL does infer correctly the return type of my query here, especially with the document_storage_id column, which has a definition of

document_storage_id INTEGER REFERENCES Public.Document_Storage(storage_id)

and SafeQL infers it correctly as number | null

However, when I try to insert or update the value, I get the following error:

SafeQL doesn't even allow to pass a straight-up null:

I don't know a lot about PostgreSQL and this most likely is a side effect of the fact that I think NULL isn't actually a PostgreSQL type. But there's gotta be a way around this, since this is a very common thing. I tried doing what the error recommended like:

{
  "connections": {
    ...,
    "overrides": {
      "types": {
        NULL: "null"
      }
    }
  }
}

But eslint crashes and I think due to my understanding that NULL isn't actually a PostgreSQL type

To Reproduce
Steps to reproduce the behavior:

1. Create a table that has a nullable column
2. Write an INSERT or UPDATE statement with the column and the type of the value you want to insert being <type> | null
3. See the error

Expected behavior
I should be able to insert or update with values that have a null or type | null typing if the column doesn't have a NOT NULL in its definition

Desktop (please complete the following information):

• OS: macOS 13.3.1
• PostgreSQL version 14.2
• Version ^1.0.2

Improve overall performance

While there are currently no known performance issues, it might be worth looking for any performance improvements.

Invalid Query: column is of type date but expression is of type text

Describe the bug
If I have a column of type DATE and I'm doing an INSERT statement with a string value of format "YYYY-MM-DD" I get the error: Invalid Query: column "<column-name>" is of type date but expression is of type text.

I don't have any INSERT statements with a TIMESTAMP column but it's worth checking if the same behavior doesn't happen in that case as well

To Reproduce
Steps to reproduce the behavior:

1. Have a table with a column of type DATE
2. Write an INSERT or UPDATE statement for it
3. See the error

Expected behavior
I should be able to insert the value since the string is an actual date

Screenshots

Desktop (please complete the following information):

• OS: macOS 13.3.1
• PostgreSQL version 14.1
• Version 1.1.3

Support array types

Is your feature request related to a problem? Please describe.
I was trying using a A[] array, inside a query, like this:

(The column type is a postgres type, called namespace_criticality_enum).

I added the next configuration:

But it doesn't seem to fix it.

Describe the solution you'd like
When adding an override to a type, support also the array form of it.

Description of migrations is missing

Describe the bug

On the Configuration page of the docs, the "2. A basic example of migrations folder" section is missing a description of what migrations are, both:

1. In a general sense (general definition of migrations)
2. In terms of SafeQL - what does SafeQL do with migrations and what behavior can the user expect / not expect?

To Reproduce

Steps to reproduce the behavior:

1. Navigate to the docs page above
2. Look for an explanation of migrations

Expected behavior

Migrations are explained

Screenshots

--

Desktop (please complete the following information):

• OS: n/a
• PostgreSQL version: n/a
• Version: n/a

Additional context

--

Unexpected type inference on array fields

Describe the bug
SafeQL infers any type on fields that returns an array, leading to a Query has incorrect type annotation error when type any is not passed to this field.
The example below will throw the above error on FoodNames because a type other than any is being passed

type AnimalWithFoodName = {
  firstName: string;
  foodNames: string[];
};

export async function queryJsonAgg() {
  return await sql<AnimalWithFoodName[]>`
    SELECT
       first_name,
       (SELECT json_agg(name) FROM foods) AS food_names
    FROM
      animals
  `;
}

To Reproduce
Steps to reproduce the behavior:

1. Setup SafeQL
2. Use this code in .ts file

type AnimalWithFoodName = {
  firstName: string;
  foodNames: string[];
};

export async function queryJsonAgg() {
  return await sql<AnimalWithFoodName[]>`
    SELECT
       first_name,
       (SELECT json_agg(name) FROM foods) AS food_names
    FROM
      animals
  `;
}

Expected behavior
A clear and concise description of what you expected to happen.
I expect that SafeQL doesn't throw an error when the correct type is being passed and should infer the correct type
Using type any is not encouraged in TypeScript and should also be discouraged in SafeQL

Screenshots
If applicable, add screenshots to help explain your problem.
Incorrect type annotation error when the correct type is passed

No error when any type is passed

Desktop (please complete the following information):

• OS: MAC OS
• PostgreSQL version 15
• Version [e.g. 22]

Additional context
Add any other context about the problem here.

explore postgres-wasm

https://github.com/snaplet/postgres-wasm - This could drastically simplify the migrations option and won't require developers to install Postgres.

Custom type with override resulting in endless loop of fixes

Describe the bug
Using cutsom types with typescript union type result in endless loop of fixes

To Reproduce
Steps to reproduce the behavior:
migration:

safeql.config.ts:

query file:

Expected behavior
after fixing with safeql - should no require to fix again

Desktop (please complete the following information):

• OS: ios
• PostgreSQL version 14
• Version 1.1.2

Additional context
this is only happening when the type is in the form of
type Type = typeof ...[number]

Unexpected error on field with type Date

Describe the bug
SafeQL throws an Invalid Query: the type "Date" is not supported when inserting a record that has a type Date resulting to the code below throwing the above error on the foodToInsert.createdTimeStamp

type FoodInsertInput = {
  name: string;
  cuisine: string;
  createdTimeStamp: Date;
};

export async function createFood(foodToInsert: FoodInsertInput) {
  return await sql`
    INSERT INTO foods
      (name, cuisine, created_timestamp)
    VALUES
      (${foodToInsert.name}, ${foodToInsert.cuisine}, ${foodToInsert.createdTimeStamp})
    RETURNING *
  `;
}

To Reproduce
Steps to reproduce the behavior:

1. Setup SafeQL
2. Use this code in .ts file
3. Insert a record with type Date

type FoodInsertInput = {
  name: string;
  cuisine: string;
  createdTimeStamp: Date;
};

export async function createFood(foodToInsert: FoodInsertInput) {
  return await sql`
    INSERT INTO foods
      (name, cuisine, created_timestamp)
    VALUES
      (${foodToInsert.name}, ${foodToInsert.cuisine}, ${foodToInsert.createdTimeStamp})
    RETURNING *
  `;
}

Expected behavior
I expect that inserting a record with the type Date should not produce the Invalid Query error

Screenshots

Some workaround considered

1. Making the createdTimeStamp property a type string but got Invalid Query: column "created_timestamp" is of type timestamp without time zone but expression is of type text error
   

2. type casting the createdTimeStamp , all the same error like the one above
   

   

Desktop (please complete the following information):

• OS: MAC OS
• PostgreSQL version 15
• Version [e.g. 22]

Additional context
Add any other context about the problem here.

Field Name Transform Function Option?

Is your feature request related to a problem? Please describe.

When SafeQL is used with a database client including a row transformation function (eg. transforming snake case field names to camel case using Postgres.js postgres({ transform: postgres.camel })), there is no way of configuring an equivalent transformation function in SafeQL.

This leads to the error Query has incorrect type annotation. with the following code:

type Animal = {
  id: number;
  firstName: string;
};

const sql = postgres({ transform: postgres.camel });

async function query() {
  // 💥 Error on next line: Query has incorrect type annotation
  const animals = await sql<Animal[]>`
    SELECT id, first_name FROM animals
  `;
}

Describe the solution you'd like

It would be great to have ability to specify the transformation function of the type (maybe using the connections.transform configuration option?).

Alternatively, if specification of an arbitrary function is complex, maybe having some common transformations would be enough, eg. fieldTransform: 'camelcase'

Describe alternatives you've considered

Use the type with the snake case properties as is and then transform the data later manually.

Additional context

--

Unexpected error on incorrect property order

Describe the bug
SafeQL throws a Query has incorrect type annotation error when the order of the type fields defers from the database table field order.
The animals table has the fields ordered as in the screenshot below

SafeQL will throw an error on any type with no exact field order, even when all the types are correct. The example below will throw the above error because the accessory and type fields were misplaced

type QueryAnimal = {
  id: number;
  firstName: string;
  accessory: string;
  type: string;
};

export async function queryAnimals() {
  return await sql<QueryAnimal[]>`
    SELECT * FROM animals
  `;
}

To Reproduce
Steps to reproduce the behavior:

1. Setup SafeQL with the animals table structure above
2. Use this code in .ts file

type QueryAnimal = {
  id: number;
  firstName: string;
  accessory: string;
  type: string;
};

export async function queryAnimals() {
  return await sql<QueryAnimal[]>`
    SELECT * FROM animals
  `;
}

Expected behavior
I think SafeQL shouldn't care about the order of the properties as long as the types are correct, just like it is in TypeScript.
This can be very cumbersome and may require a lot of work to get correctly, especially when the database table has a lot of fields

Screenshots
If applicable, add screenshots to help explain your problem.
Incorrect type annotation error on incorrect order

No error when the properties have exact order as the database table

Desktop (please complete the following information):

• OS: MAC OS
• PostgreSQL version 15
• Version [e.g. 22]

Additional context
Add any other context about the problem here.

Cache shadow database

Currently, a shadow database in being dropped and recreated each time ESLint boots up. Even if there's no drift from the migrations

Support non-string template tag queries

TL;DR - support the following syntax:

const result = await conn.query("SELECT id FROM users WHERE first_name = $1", [firstName])

Currently, many libraries don't come with an SQL template tag out of the box (such as pg, sequelize, etc), and some devs may prefer using normal strings with parameterized variables ($1, $2, ...) with an array of values.

We should probably support that kind of syntax as well.

Unexpected error when using type aliases / interfaces

Describe the bug
SafeQL throws an error when using type aliases and interfaces, It doesn't recognise them when they are assigned as a type annotation. I am using it with postgres.js

To Reproduce
Steps to reproduce the behavior:

async function query() {
  const ids = await sql<{ id: number }[]>`SELECT id FROM animals`;

  type Animal = { id: number };
  const ids2 = await sql<Animal[]>`SELECT id FROM animals`;
}

Expected behavior
Don't throw an error when the correct type is used

Screenshots

Desktop (please complete the following information):

• OS: MAC OS
• PostgreSQL version 13
• Version [e.g. 22]

Additional context
Add any other context about the problem here.

`name` should support nested objects

@ts-safeql/check-sql rule supports only first-level objects as db connection handler object.
For example,

connections: [
          { ..., name: 'entityManager' }
]

entityManager.query('SELECT * FROM a')

Ir doesn't support nested objects:

connections: [
          { ..., name: 'this.a.entityManager' }
]

this.a.entityManager('SELECT * FROM a')

A possible solution is to support only the last object, that calls the query method:

connections: [
          { ..., name: 'entityManager' }
]

Will match this.a.entityManager.query(), a.entityManager.query(), entityManager.query()

Unexpected error when doing conditional query

Describe the bug
SafeQL throws an unsupported conditional expression error when doing a conditional query on Postgres. This is a feature in postgres.js but it seems like SafeQL doesn't allow for these kinds of queries. The queries below would throw this error Invalid Query: Unsupported conditional expression flags (true = 524288, false = 524288).

type A = { a: number | null; b: string | null; c: string | null };
export async function query(a: number) {
  return await sql<A[]>`
    SELECT
      *
    FROM
      try_safe_ql ${a ? sql`WHERE a = ${a}` : sql``}
`;
}

export async function query3(a: number) {
  return await sql<A[]>`
  SELECT
   *
  FROM
    try_safe_ql
  WHERE
    c is not null ${a ? sql`AND a = ${a}` : sql``}
`;
}

According to postgres.js, it is normal to do these kinds of queries

To Reproduce
Steps to reproduce the behavior:

1. Setup SafeQL
2. Use this code in .ts file

type A = { a: number | null; b: string | null; c: string | null };
export async function query(a: number) {
  return await sql<A[]>`
    SELECT
      *
    FROM
      try_safe_ql ${a ? sql`WHERE a = ${a}` : sql``}
`;
}

Expected behavior
I expected no error for these kinds of queries

Screenshots

Desktop (please complete the following information):

• OS: MAC OS
• PostgreSQL version 14
• Version [e.g. 22]

Additional context
Add any other context about the problem here.

Logo?

Cannot pass `null` to a nullable column

Describe the bug
SafeQL doesn't allow passing a straight-up null to a nullable column when inserting or updating:

To Reproduce
Steps to reproduce the behavior:

1. Create a table that has a nullable column
2. Write an INSERT or UPDATE statement with the column and the value you will insert being null
3. See the error

Expected behavior
I should be able to insert or update with a null if the column doesn't have a NOT NULL in its definition

Desktop (please complete the following information):

• OS: macOS 13.3.1
• PostgreSQL version 14.2
• Version ^1.1.0

Wrong type on json

Describe the bug
A clear and concise description of what the bug is.
SafeQL doesn't accept types for json, other than any. Using the json_agg aggregate function makes SafeQL think that the returning column must be any type and throws an error otherwise. The query below throws an incorrect type annotation if any other type is used for the returningJson other than any

type JsonAgg = {
  a: string;
  b: string;
  c: string;
};

type SafeqlRecordWithJson = {
  id: number;
  firstName: string | null;
  lastName: string | null;
  returningJson: JsonAgg;
};

export async function getAllTestSafeqlRecordWithJson() {
  return await sql<SafeqlRecordWithJson[]>`
      SELECT
        *,
      (
        SELECT json_agg(ingredients)::json
        FROM (
        SELECT
          *
        FROM
          try_safe_ql
        )ingredients
      ) AS returning_json
      FROM
        test_safeql;
          `;
}

To Reproduce
Steps to reproduce the behavior:

1. Setup SafeQL
2. Use the code above in .ts file

Expected behavior
A clear and concise description of what you expected to happen.
I expected that using these kinds of aggregate functions shouldn't cause an error, when i am using the type the query should return or that SafeQL would suggest types other than any

Screenshots
If applicable, add screenshots to help explain your problem.

Desktop (please complete the following information):

• OS: MAC OS
• PostgreSQL version 14
• Version [e.g. 22]

Additional context
Add any other context about the problem here.

Tuple type annotations support

Is your feature request related to a problem? Please describe.
There is no configuration for TypeScript tuple type

This leads to the error Query has invalid type annotation (SafeQL does not support it. If you think it should, please open an issue) when I use the following code

async function query() {
  type Animal = {
    id: number;
  };
  const [animal1] = await sql<[Animal]>`SELECT * FROM animals WHERE id = 1`;
  const [animal2] = await sql<[Animal | undefined]>`SELECT * FROM animals WHERE id = 1`;
}

Describe the solution you'd like
It would be great to have the ability to use tuples and arrays together. Maybe adding this configuration to the connections.transform?
eg. The following code should work without throwing an error

async function query() {
  type Animal = {
    id: number;
  };
  const animals = await sql<Animal[]>`SELECT * FROM animals`;
  const [animal] = await sql<[Animal]>`SELECT * FROM animals WHERE id = 1`;
}

Describe alternatives you've considered
Using it with the array it currently supports and get the single object using array index eg

async function query(id: number) {
  type Animal = {
    id: number;
  };
  const animals = await sql<Animal[]>`SELECT * FROM animals WHERE id = 1`;
  return animals[0];
}

But this can lead to an unexpected error, it won't prevent returning undefined index (eg. return animals[1] won't throw any error from the above code even though this index will be undefined)

Additional context
It should also have the ability to support union inside tuple (eg. [Animal | undefined] like in the following code)

async function query() {
  type Animal = {
    id: number;
  };
  const [animal] = await sql<[Animal | undefined]>`SELECT * FROM animals WHERE id = 1`;
}

Support for utility types

Hi @Newbie012 , hope you are well! 👋

Is your feature request related to a problem? Please describe.

It would be great to be able to use generic utility types such as Pick and Omit with existing types to be able to transform existing types instead of copying them throughout the codebase:

type User = {
  id: number;
  username: string;
}

export async function getUserByUsername(username: string) {
  const [user] = await sql<Pick<User, 'id'>[]>`
    SELECT
      id
    FROM
      users
    WHERE
      username = ${username}
  `;
  return user;
}

Right now, this leads to a confusing error message:

Query has incorrect type annotation.
	Expected: null[]`
	Actual: { id: number; }[]
eslint @ts-safeql/check-sql

Describe the solution you'd like

It would be great if the utility types worked

Describe alternatives you've considered

Workaround:

Use the literal object type instead of the utility type (decreasing maintainability):

export async function getUserByUsername(username: string) {
  const [user] = await sql<{ id: number }[]>`
    SELECT
      id
    FROM
      users
    WHERE
      username = ${username}
  `;
  return user;
}

Additional context

--

Array types inferred incorrectly

Describe the bug
postgres timestamp, timestamptz, time, datetime array getting inffered as null

To Reproduce
Steps to reproduce the behavior:
using postgres.js
migration:

CREATE TABLE test (
    id serial primary key,
    test_date_array date[] not null,
    test_instant_arr timestamptz[] not null,
    test_time_arr time[] not null,
    test_test_local_date_time_arr timestamp[] not null
)

Expected behavior
expected to return the right type (Date) as array (Date[])

Screenshots

Desktop (please complete the following information):

• OS: macOS 13.2.1
• PostgreSQL 14

Additional context
None

Unexpected error on sql tagged template string

Describe the bug
SafeQL throws multiple errors when using the sql tagged template string on Postgres, which leads to the errors below
Invalid Query: the type "string | PendingQuery<Row[]>" is not supported and Invalid Query: Error: syntax error at or near "DEFAULT" The query below will throw the above errors

export async function query(a: number, b: string, c: string) {
  return await sql`
    UPDATE
      try_safe_ql
    SET
      b = ${b},
      c = ${c || sql`DEFAULT`}
    WHERE
      a = ${a}
  `;
}

To Reproduce
Steps to reproduce the behavior:

1. Setup SafeQL
2. Use this code in .ts file

export async function query(a: number, b: string, c: string) {
  return await sql`
    UPDATE
      try_safe_ql
    SET
      b = ${b},
      c = ${c || sql`DEFAULT`}
    WHERE
      a = ${a}
  `;
}

Expected behavior
There shouldn't be an error when using sql tagged template string
**


Screenshots**

Desktop (please complete the following information):

• OS: MAC OS
• PostgreSQL version 14
• Version [e.g. 22]

Additional context
Add any other context about the problem here.

support db connections per relative paths in a project

When using a monorepo, each service usually uses its own db. While keeping name and operators the same between all repo's projects is required, the databaseUrl should be different.

A suggested solution - paths prop:

 connections: [
          { databaseUrl: 'X', paths: ['./apps/api-a'], operators: ['query'], name: 'entityManager' },        
          { databaseUrl: 'Y', paths: ['./apps/api-b', './apps/api-c'], operators: ['query'], name: 'entityManager' },        
 ]

Allow disabling type annotation fix

Is your feature request related to a problem? Please describe.
Slonik provides various tagged template literal primitives that allow us to write SQL. In particular, it has a sql.type to pass Zod schemas to define the shape of the query return value and sql.typeAlias to have a predefined set of Zod schemas to use with our queries. sql.type and sql.typeAlias are not normal generics because they aren't supposed to accept generic arguments, rather that they infer what it's passed to them via their arguments. When I use SafeQL with Slonik, the type annotation fix keeps appearing even though I have no need to add the annotation since I'm already using my Zod schemas:

Describe the solution you'd like
A potential solution for this would be to have an overrides.skipTypeAnnotation option in the SafeQL config that would allow us to skip this suggestion in case a person is using Slonik or another library where they don't need it. API could be something like:

{
  "connections": {
    "overrides": {
      "skipTypeAnnotation": true, // if you want to skip type annotation for every tag or wrapper you have
      //or
      "skipTypeAnnotaion": [{ "tag": "sql.type\\(*\\)" }] // if you want to skip type annotation for specific tag or wrapper
    }
  }
}

Describe alternatives you've considered
Another way would be to add the "skipTypeAnnotation" option when we are specifying the tag or wrapper in the targets array:

{
  "connections": {
    "targets": [
      {
        "tag": "sql.+(type\\(*\\)|typeAlias\\(*\\)|unsafe)",
        "skipTypeAnnotation": true
      }
    ]
  }
}

Failing interpolation

Describe the bug
When using Postgres.js, interpolation causes SafeQL error

To Reproduce
Steps to reproduce the behavior:

async function query(id: number) {
  type Animal = {
    id: number;
  };

// Works
  await sql<Animal[]>`SELECT * FROM animals WHERE id = 1`;

// Fail
  await sql<Animal[]>`SELECT * FROM animals WHERE id = ${id}`;
}

Expected behavior
Shouldn't fail with interpolation

Screenshots

Desktop (please complete the following information):

• OS: Mac OS
• PostgreSQL version 13
• Version [e.g. 22]

Additional context
Add any other context about the problem here.

Config option for SafeQL type literal transform

Hi @Newbie012, Hope you are doing great!
Is your feature request related to a problem? Please describe.
Yes, the feature request is related to how SafeQL handles type literals. When a query returns a result set that includes literal types, SafeQL throws a Query has incorrect type annotation error. When using literal types, it is necessary to manually transform them to non-literal types using a custom type, which can be cumbersome and error-prone. This feature request is related to this problem and aims to make it easier to work with literal types in SafeQL.

This is directly related to this closed request. I know that SafeQL is working as intended according to your comment/suggestion here, but the suggestion did not work in my use case, and I had to create a custom type to workaround that.
So for the types below, SafeQl will throw the Query has incorrect type annotation error

const d = [
  { id: 1, firstName: 'b', type: 'c', accessory: 'd' }
] as const;

type G = typeof d[number];

type H = {
  readonly id: 1;
  readonly firstName: 'b';
  readonly type: 'c';
  readonly accessory: 'd';
};

export async function query() {
  return await sql<G[]>`
    SELECT * FROM animals
  `;
}

Describe the solution you'd like
I would like SafeQL to support a configuration option to internally transform literal types to non-literal types during query linting. This would allow users to use literal types in their queries without having to manually transform them, making the process more efficient and less error-prone.

Describe alternatives you've considered

As a workaround, I created a conditional type Widen that would transform the type literals to none-literal types

export type Widen<T> = {
  [K in keyof T]: T[K] extends string
    ? string
    : T[K] extends number
    ? number
    : T[K] extends boolean
    ? boolean
    : T[K] extends Date
    ? Date
    : T[K] extends infer U | null
    ? U extends string
      ? string | null
      : U extends number
      ? number | null
      : U extends boolean
      ? boolean | null
      : U extends Date
      ? Date | null
      : U | null
    : T[K];
};

// Usage
export async function query() {
  return await sql<Widen<G>[]>`
    SELECT * FROM animals
  `;
}

Additional context
It would be great if this transformation could be done internally so the above type can be used like this with no error

export async function query() {
  return await sql<G[]>`
    SELECT * FROM animals
  `;
}

Explanation about Widen in this playground

ESLint server crash: `dyld[...]: missing symbol called`

Describe the bug

Installing and running SafeQL on an M1 machine (process.arch === 'arm64') causes crashes in the VS Code ESLint Output panel that look like this:

...more...
dyld[70673]: missing symbol called
[Info  - 5:42:07 PM] Connection to server got closed. Server will restart.
[Info  - 5:42:07 PM] ESLint server stopped.
[Info  - 5:42:07 PM] ESLint server is starting
[Info  - 5:42:07 PM] ESLint server running in node v16.11.0
[Info  - 5:42:07 PM] ESLint server is running.
[Info  - 5:42:07 PM] ESLint library loaded from: /Users/k/p/next-js-example-spring-2022/node_modules/eslint/lib/api.js
dyld[70680]: missing symbol called
[Error - 5:42:09 PM] The ESLint server crashed 5 times in the last 3 minutes. The server will not be restarted. See the output for more information.
[Info  - 5:42:09 PM] ESLint server stopped.

This also occurs on the command line:

$ yarn eslint . --max-warnings 0
yarn run v1.22.19
$ /Users/k/p/next-js-example-spring-2022/node_modules/.bin/eslint . --max-warnings 0
dyld[71873]: missing symbol called
error Command failed with signal "SIGABRT".
info Visit https://yarnpkg.com/en/docs/cli/run for documentation about this command.

• I also tried upgrading Node.js from 16.11.0 to 18.10.0, but encountered a similar problem.
• Switching to a Node.js v16 version that is using i386 architecture via Rosetta 2 (process.arch === 'x64') resolves the issue

To Reproduce
Steps to reproduce the behavior:

1. Install and run SafeQL on an M1 / M2 (ARM - Apple Silicon) machine
2. Open a file that causes a connection to the database
3. Observe the crash in the VS Code Output -> ESLint panel

Expected behavior

It should run on Apple Silicon machines too.

Screenshots

--

Desktop (please complete the following information):

• OS: macOS Monterey 12.6 (21G115)
• PostgreSQL version 14.2
• Version @ts-safeql/[email protected]

Additional context

Also asked about this upstream in libpg-query here: launchql/libpg-query-node#9 (comment)

Support for intersection types

Hi @Newbie012 👋 hope things are going great!

Is your feature request related to a problem? Please describe.

It would be great to be able to use intersection types with existing object type aliases to be able to transform existing types instead of copying them throughout the codebase:

type User = {
  id: number;
  username: string;
};

type UserWithPasswordHash = User & {
  passwordHash: string;
};

export async function getUserWithPasswordHashById(id: number) {
  const [user] = await sql<UserWithPasswordHash[]>`
    SELECT
      id,
      username,
      password_hash
    FROM
      users
    WHERE
      id = ${id}
  `;
  return user;
}

Right now, this leads to a crash of ESLint with a confusing error message (as seen here: https://github.com/upleveled/security-vulnerability-examples-next-js-postgres/actions/runs/3330517524/jobs/5509108053):

TypeError: Cannot read properties of undefined (reading 'members')
Occurred while linting /home/runner/work/security-vulnerability-examples-next-js-postgres/security-vulnerability-examples-next-js-postgres/database/users.ts:24
Rule: "@ts-safeql/check-sql"
    at tsTypeToText (/home/runner/work/security-vulnerability-examples-next-js-postgres/security-vulnerability-examples-next-js-postgres/node_modules/@ts-safeql/eslint-plugin/lib/utils/ts.utils.js:18:34)
    at tsTypeToText (/home/runner/work/security-vulnerability-examples-next-js-postgres/security-vulnerability-examples-next-js-postgres/node_modules/@ts-safeql/eslint-plugin/lib/utils/ts.utils.js:22:16)
    at getTypeAnnotationState (/home/runner/work/security-vulnerability-examples-next-js-postgres/security-vulnerability-examples-next-js-postgres/node_modules/@ts-safeql/eslint-plugin/lib/rules/check-sql.rule.js:271:49)
    at /home/runner/work/security-vulnerability-examples-next-js-postgres/security-vulnerability-examples-next-js-postgres/node_modules/@ts-safeql/eslint-plugin/lib/rules/check-sql.rule.js:206:37
    at /home/runner/work/security-vulnerability-examples-next-js-postgres/security-vulnerability-examples-next-js-postgres/node_modules/fp-ts/lib/Either.js:863:60
    at pipe (/home/runner/work/security-vulnerability-examples-next-js-postgres/security-vulnerability-examples-next-js-postgres/node_modules/fp-ts/lib/function.js:300:20)
    at reportCheck (/home/runner/work/security-vulnerability-examples-next-js-postgres/security-vulnerability-examples-next-js-postgres/node_modules/@ts-safeql/eslint-plugin/lib/rules/check-sql.rule.js:173:29)
    at checkConnectionByTagExpression (/home/runner/work/security-vulnerability-examples-next-js-postgres/security-vulnerability-examples-next-js-postgres/node_modules/@ts-safeql/eslint-plugin/lib/rules/check-sql.rule.js:236:16)
    at /home/runner/work/security-vulnerability-examples-next-js-postgres/security-vulnerability-examples-next-js-postgres/node_modules/@ts-safeql/eslint-plugin/lib/rules/check-sql.rule.js:160:16
    at e.run (/home/runner/work/security-vulnerability-examples-next-js-postgres/security-vulnerability-examples-next-js-postgres/node_modules/ts-pattern/dist/index.cjs:1:4927)
error Command failed with exit code 2.

Describe the solution you'd like

It would be great if the intersection types worked

Describe alternatives you've considered

Workaround:

Copy the properties from the other type to the new type (decreasing maintainability):

type User = {
  id: number;
  username: string;
};

type UserWithPasswordHash = {
  id: number;
  username: string;
  passwordHash: string;
};

export async function getUserWithPasswordHashById(id: number) {
  const [user] = await sql<UserWithPasswordHash[]>`
    SELECT
      id,
      username,
      password_hash
    FROM
      users
    WHERE
      id = ${id}
  `;
  return user;
}

Additional context

--

Drops database table on ESLint restart

Describe the bug
SafeQL executes the queries each time the ESLint is restarted which causes an unexpected dropping of the database table and leads to the error Internal error: relation "animals" does not exist immediately after restarting the ESLint.

To Reproduce
Steps to reproduce the behavior:
It executes the queries below.
e.g

exports.up = async (sql) => {
  await sql`
    CREATE TABLE animals (
      id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    )
  `;
};

exports.down = async (sql) => {
  await sql`
    DROP TABLE animals
  `;
};

restart ESLint: cmd + shift + P => ESLint: Restart ESLint Server

Expected behavior
Should not execute any SQL queries

Screenshots
If applicable, add screenshots to help explain your problem.
Before restarting the ESLint

After restarting the ESLint

Desktop (please complete the following information):

• OS: macOS
• PostgreSQL version 14
• Version [e.g. 22]

Additional context
As far as i can tell, SafeQL tries to avoid executing the query by using `sql``.describe()

.describe() should not execute the query according to Postgres.js

But it seems SafeQL is actually executing the query for DROP TABLE which is a big problem

Add documentation for CI

I should probably add basic documentation of how to run SafeQL in CI

SafeQL installation fails on Windows

Describe the bug
SafeQL can not be installed on Windows 10 / 11. This is due to one of the dependencies, libpg-query that doesn't currently support installation on windows machines according to the issue here launchql/libpg-query-node#22

Running yarn add --dev @ts-safeql/eslint-plugin libpg-query throws the following errors

$ yarn add --dev @ts-safeql/eslint-plugin libpg-query
yarn add v1.22.19
[1/4] �  Resolving packages...
warning Resolution field "[email protected]" is incompatible with requested version "eslint-plugin-jest@^25.3.0"
[2/4] �  Fetching packages...
[3/4] �  Linking dependencies...
warning "@emotion/react > @emotion/[email protected]" has unmet peer dependency "@babel/core@^7.0.0".
warning "@emotion/react > @emotion/babel-plugin > @babel/[email protected]" has unmet peer dependency "@babel/core@^7.0.0-0".
warning " > @babel/[email protected]" has unmet peer dependency "@babel/core@>=7.11.0".
warning " > [email protected]" has unmet peer dependency "@babel/plugin-syntax-flow@^7.14.5".
warning " > [email protected]" has unmet peer dependency "@babel/plugin-transform-react-jsx@^7.14.9".
[4/4] �  Building fresh packages...
[-/4] ⠁ waiting...
[-/4] ⠁ waiting...
[-/4] ⠂ waiting...
error C:\Users\Victor\projects\debugging-programs\next-js-ecommerce-store\node_modules\libpg-query: Command failed.
Exit code: 1
Command: node-pre-gyp install --fallback-to-build
Arguments:
Directory: C:\Users\Victor\projects\debugging-programs\next-js-ecommerce-store\node_modules\libpg-query
Output:
node-pre-gyp info it worked if it ends with ok
node-pre-gyp info using [email protected]
node-pre-gyp info using [email protected] | win32 | x64
node-pre-gyp info check checked for "C:\Users\Victor\projects\debugging-programs\next-js-ecommerce-store\node_modules\libpg-query\build\Release\queryparser.node" (not found)
node-pre-gyp http GET https://supabase-public-artifacts-bucket.s3.amazonaws.com/libpg-query-node/queryparser-v13.2.5-node-v108-win32-x64.tar.gz
node-pre-gyp ERR! install response status 404 Not Found on https://supabase-public-artifacts-bucket.s3.amazonaws.com/libpg-query-node/queryparser-v13.2.5-node-v108-win32-x64.tar.gz
node-pre-gyp WARN Pre-built binaries not installable for [email protected] and [email protected] (node-v108 ABI, unknown) (falling back to source compile with node-gyp)
node-pre-gyp WARN Hit error response status 404 Not Found on https://supabase-public-artifacts-bucket.s3.amazonaws.com/libpg-query-node/queryparser-v13.2.5-node-v108-win32-x64.tar.gz
gyp info it worked if it ends with ok
gyp info using [email protected]
gyp info using [email protected] | win32 | x64
gyp info ok
gyp info it worked if it ends with ok
gyp info using [email protected]
gyp info using [email protected] | win32 | x64
gyp info find Python using Python version 3.9.6 found at "C:\Users\Victor\AppData\Local\Programs\Python\Python39\python.exe"
gyp info find VS using VS2017 (15.9.28307.1585) found at:
gyp info find VS "C:\Program Files (x86)\Microsoft Visual Studio\2017\BuildTools"
gyp info find VS run with --verbose for detailed information
gyp info spawn C:\Users\Victor\AppData\Local\Programs\Python\Python39\python.exe
gyp info spawn args [
gyp info spawn args   'C:\\Users\\Victor\\projects\\debugging-programs\\next-js-ecommerce-store\\node_modules\\node-gyp\\gyp\\gyp_main.py',
gyp info spawn args   'binding.gyp',
gyp info spawn args   '-f',
gyp info spawn args   'msvs',
gyp info spawn args   '-I',
gyp info spawn args   'C:\\Users\\Victor\\projects\\debugging-programs\\next-js-ecommerce-store\\node_modules\\libpg-query\\build\\config.gypi',
gyp info spawn args   '-I',
gyp info spawn args   'C:\\Users\\Victor\\projects\\debugging-programs\\next-js-ecommerce-store\\node_modules\\node-gyp\\addon.gypi',
gyp info spawn args   '-I',
gyp info spawn args   'C:\\Users\\Victor\\AppData\\Local\\node-gyp\\Cache\\18.5.0\\include\\node\\common.gypi',
gyp info spawn args   '-Dlibrary=shared_library',
gyp info spawn args   '-Dvisibility=default',
gyp info spawn args   '-Dnode_root_dir=C:\\Users\\Victor\\AppData\\Local\\node-gyp\\Cache\\18.5.0',
gyp info spawn args   '-Dnode_gyp_dir=C:\\Users\\Victor\\projects\\debugging-programs\\next-js-ecommerce-store\\node_modules\\node-gyp',
gyp info spawn args   '-Dnode_lib_file=C:\\\\Users\\\\Victor\\\\AppData\\\\Local\\\\node-gyp\\\\Cache\\\\18.5.0\\\\<(target_arch)\\\\node.lib',
gyp info spawn args   '-Dmodule_root_dir=C:\\Users\\Victor\\projects\\debugging-programs\\next-js-ecommerce-store\\node_modules\\libpg-query',
gyp info spawn args   '-Dnode_engine=v8',
gyp info spawn args   '--depth=.',
gyp info spawn args   '--no-parallel',
gyp info spawn args   '--generator-output',
gyp info spawn args   'C:\\Users\\Victor\\projects\\debugging-programs\\next-js-ecommerce-store\\node_modules\\libpg-query\\build',
gyp info spawn args   '-Goutput_dir=.'
gyp info spawn args ]
gyp info ok
gyp info it worked if it ends with ok
gyp info using [email protected]
gyp info using [email protected] | win32 | x64
gyp info spawn C:\Program Files (x86)\Microsoft Visual Studio\2017\BuildTools\MSBuild\15.0\Bin\MSBuild.exe
gyp info spawn args [
gyp info spawn args   'build/binding.sln',
gyp info spawn args   '/clp:Verbosity=minimal',
gyp info spawn args   '/nologo',
gyp info spawn args   '/p:Configuration=Release;Platform=x64'
gyp info spawn args ]
Building the projects in this solution one at a time. To enable parallel build, please add the "/m" switch.
  nothing.c
  win_delay_load_hook.cc
  nothing.vcxproj -> C:\Users\Victor\projects\debugging-programs\next-js-ecommerce-store\node_modules\libpg-query\build\Release\\nothing.lib
  prebuild_dependencies
  The system cannot find the path specified.
C:\Program Files (x86)\Microsoft Visual Studio\2017\BuildTools\Common7\IDE\VC\VCTargets\Microsoft.CppCommon.targets(209,5): error MSB6006: "cmd.exe" exited with code 1. [C:\Users\Victor\projects\debugging-programs\next-js-ecommerce-store\node_modules\libpg-query\build\queryparser.vcxproj]
gyp ERR! build error
gyp ERR! stack Error: `C:\Program Files (x86)\Microsoft Visual Studio\2017\BuildTools\MSBuild\15.0\Bin\MSBuild.exe` failed with exit code: 1
gyp ERR! stack     at ChildProcess.onExit (C:\Users\Victor\projects\debugging-programs\next-js-ecommerce-store\node_modules\node-gyp\lib\build.js:194:23)
gyp ERR! stack     at ChildProcess.emit (node:events:537:28)
gyp ERR! stack     at ChildProcess._handle.onexit (node:internal/child_process:291:12)
gyp ERR! System Windows_NT 10.0.22000
gyp ERR! command "C:\\Program Files\\nodejs\\node.exe" "C:\\Users\\Victor\\projects\\debugging-programs\\next-js-ecommerce-store\\node_modules\\node-gyp\\bin\\node-gyp.js" "build" "--fallback-to-build"
"--module=C:\\Users\\Victor\\projects\\debugging-programs\\next-js-ecommerce-store\\node_modules\\libpg-query\\build\\Release\\queryparser.node" "--module_name=queryparser" "--module_path=C:\\Users\\Victor\\projects\\debugging-programs\\next-js-ecommerce-store\\node_modules\\libpg-query\\build\\Release" "--napi_version=8" "--node_abi_napi=napi" "--napi_build_version=0" "--node_napi_label=node-v108"
gyp ERR! cwd C:\Users\Victor\projects\debugging-programs\next-js-ecommerce-store\node_modules\libpg-query
gyp ERR! node -v v18.5.0
gyp ERR! node-gyp -v v8.4.1
gyp ERR! not ok
node-pre-gyp ERR! build error
node-pre-gyp ERR! stack Error: Failed to execute 'C:\Program Files\nodejs\node.exe C:\Users\Victor\projects\debugging-programs\next-js-ecommerce-store\node_modules\node-gyp\bin\node-gyp.js build --fallback-to-build --module=C:\Users\Victor\projects\debugging-programs\next-js-ecommerce-store\node_modules\libpg-query\build\Release\queryparser.node --module_name=queryparser --module_path=C:\Users\Victor\projects\debugging-programs\next-js-ecommerce-store\node_modules\libpg-query\build\Release --napi_version=8 --node_abi_napi=napi --napi_build_version=0 --node_napi_label=node-v108' (1)
node-pre-gyp ERR! stack     at ChildProcess.<anonymous> (C:\Users\Victor\projects\debugging-programs\next-js-ecommerce-store\node_modules\@mapbox\node-pre-gyp\lib\util\compile.js:89:23)
node-pre-gyp ERR! stack     at ChildProcess.emit (node:events:537:28)
node-pre-gyp ERR! stack     at maybeClose (node:internal/child_process:1091:16)
node-pre-gyp ERR! stack     at ChildProcess._handle.onexit (node:internal/child_process:302:5)
node-pre-gyp ERR! System Windows_NT 10.0.22000
node-pre-gyp ERR! command "C:\\Program Files\\nodejs\\node.exe" "C:\\Users\\Victor\\projects\\debugging-programs\\next-js-ecommerce-store\\node_modules\\@mapbox\\node-pre-gyp\\bin\\node-pre-gyp" "install" "--fallback-to-build"
node-pre-gyp ERR! cwd C:\Users\Victor\projects\debugging-programs\next-js-ecommerce-store\node_modules\libpg-query
node-pre-gyp ERR! node -v v18.5.0
node-pre-gyp ERR! node-pre-gyp -v v1.0.10
node-pre-gyp ERR! not ok
Failed to execute 'C:\Program Files\nodejs\node.exe C:\Users\Victor\projects\debugging-programs\next-js-ecommerce-store\node_modules\node-gyp\bin\node-gyp.js build --fallback-to-build --module=C:\Users\Victor\projects\debugging-programs\next-js-ecommerce-store\node_modules\libpg-query\build\Release\queryparser.node --module_name=queryparser --module_path=C:\Users\Victor\projects\debugging-programs\nex

To Reproduce
You can try this installation on this repo
A computer running windows OS is needed to reproduce this error

clone project

git clone https://github.com/Eprince-hub/libpg-query-installation-error.git

cd into project

cd libpg-query-installation-error

install SafeQL

yarn add --dev @ts-safeql/eslint-plugin libpg-query
or
npm install --save-dev @ts-safeql/eslint-plugin libpg-query

Expected behaviour
Install SafeQL on a windows computer without an issue

Screenshots
If applicable, add screenshots to help explain your problem.

Desktop (please complete the following information):

• OS: Windows 10 / 11
• PostgreSQL version 14
• Version [e.g. 22]

Additional context
Add any other context about the problem here.

Watch for migration folder changes for database invalidation

Currently, there's a need to restart ESLint each time

Switch from `Nullable` generic to `<type> | null`

Is your feature request related to a problem? Please describe.

It was confusing to me that this does not work:

type Animal = {
  id: number;
  name: string | null;
}

// error: type of name `string | null` does not match `Nullable<string>`
await sql<Animal[]>`
  SELECT * FROM animals
`

With this schema:

CREATE TABLE animals (
  id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  name varchar(40)
)

Describe the solution you'd like

Support the x | null union type when using a nullable database field

Describe alternatives you've considered

Apparently as shown in the examples, it shows that users can define their own Nullable generic type to achieve this:

type Nullable<T> = T | null;

type Animal = {
  id: number;
  name: Nullable<string>;
}

// no error
await sql<Animal[]>`
  SELECT * FROM animals
`

But this seems less natural and discoverable than allowing | null unions

Additional context

--

Document about the "transform" property

Copying from the upcoming release notes:

---

{
  // ...
  "connections": [
    {
      // ...
      "transform": "${type}[]"
    }
  ]
}

${type} will be replaced by the original type that was generated. In this case, we are transforming it into an array:

// before transformation
const rows = conn.query<{ id: number }>(conn.sql`SELECT id FROM users`)
// after transformation
const rows = conn.query<{ id: number }[]>(conn.sql`SELECT id FROM users`)
                                      ^^

transform accepts the following pattern: string | (string | [(from) string, (to) string])[].

For example:

• "transform": "${type}[]" (add [] to the end)
• "transform": ["${type}[]"] (identical to the previous one)
• "transform": [["Nullable", "Maybe"]] (replaces Nullable with Maybe)

Support custom type mapping

Support literal args

client.queryOne(sql`SELECT * FROM comments where body = ${Math.random() > 0.5 ? 'a' : 'b'}`);

error:

Invalid Query: the type ""a" | "b"" is not supported

`connections.transform` option conflicts with `no-template-curly-in-string`

Describe the bug

When linting .eslintrc.js or .eslintrc.cjs, the built in no-template-curly-in-string rule reports a problem with the connections.transform syntax with a dollar sign in a non-template string:

module.exports = {
  plugins: ['@ts-safeql/eslint-plugin'],
  rules: {
    '@ts-safeql/check-sql': [
      'error',
      {
        connections: [
          {
            databaseUrl: `postgres://postgres:postgres@localhost:5432/postgres`,
            tagName: 'sql',
            transform: '${type}[]', // 💥 Unexpected template string expression. (`no-template-curly-in-string`)
          },
        ],
      },
    ],
  },
};

To Reproduce

Steps to reproduce the behavior:

1. Enable the no-template-curly-in-string to be a warning or error
2. Lint a .eslintrc.js file with the connections.transform option as specified in the code block above
3. Observe the problem reported by ESLint

Expected behavior

The recommended connections.transform configuration shouldn't cause an error with a built-in ESLint rule.

Maybe an alternative syntax that doesn't use the dollar?

Screenshots

--

Desktop (please complete the following information):

• OS: macOS Monterey 12.6 (21G115)
• PostgreSQL version 14.2
• Version @ts-safeql/[email protected]

Additional context

--

Opt-in for branded types (TableId rather than - number).

Support for readonly Indexed Access Type

Is your feature request related to a problem? Please describe.
Yes, the SafeQL plugin currently throws an error when a readonly TypeScript type is used in it. This leads to a Query has incorrect type annotation. error when using Indexed Access Types.

The setup below would throw the above error on the <A[]> because the type A is a readonly type

const a = [{ id: 1, firstName: 'b', type: 'c', accessory: 'd' }] as const;

type A = typeof a[number];

export async function query() {
  return await sql<A[]>`
    SELECT * FROM animals
  `;
}

Describe the solution you'd like
SafeQL should support the readonly Typescript types when it is being indexed, like in the example above. So, it should not throw an error when a readonly Indexed Access Type is used as in the example below

const a = [{ id: 1, firstName: 'b', type: 'c', accessory: 'd' }] as const;

type A = typeof a[number];

export async function query() {
  return await sql<A[]>`
    SELECT * FROM animals
  `;
}

Describe alternatives you've considered
I tried to convert the readonly type to writable again using some of these methods below, but none of the method I tried worked for me

I saw this example of creating a type that would convert it from readonly here

type DeepMutable<T> = { -readonly [P in keyof T]: DeepMutable<T[P]> };

export async function query() {
  return await sql<DeepMutable<A>>`
    SELECT * FROM animals
  `;
}

Using the Writable type from the toolbelt utility after i tried it in this simple example as shown in this sandbox and it seems to work there but doesn't work when i use it in SafeQL in my application

export async function query() {
  return await sql<Writable<A>>`
    SELECT * FROM animals
  `;
}

** None of the above method I tried worked

Additional context
Add any other context or screenshots about the feature request here.

Explore the idea of SQL views and SQL fragments

It would probably require a custom ORM.

Create a default value for the shadow database name

Can probably be safeql_shadow_{escaped_package_name}, but it might cause confusion among the community

Support Idiomatic Postgres.js

Hi @Newbie012 👋 First of all, thanks again for this library, so amazing!

Is your feature request related to a problem? Please describe.

Reading the first Before we begin section on the Postgres.js docs page, it becomes clear that idiomatic Postgres.js is not supported:

SafeQL supports by design only the following pattern: ...

It would be nice to be able to keep the code simple when using SafeQL and not have to create an extra wrapper for every project using Postgres.js + SafeQL.

For us, we would like to avoid any extra code / boilerplate because we are teaching beginners to programming.

Describe the solution you'd like

It would be great to remove this limitation and allow for idiomatic Postgres.js as in their docs:

// db.js
import postgres from 'postgres'

const sql = postgres({ /* options */ }) // will use psql environment variables

export default sql

// users.js
import sql from './db.js'

async function getUsersOver(age) {
  const users = await sql`
    select
      name,
      age
    from users
    where age > ${ age }
  `
  // users = Result [{ name: "Walter", age: 80 }, { name: 'Murray', age: 68 }, ...]
  return users
}

If the "by design" limitation is about the ESLint AST, I guess there are a few different ways this could be approached, so that it can support await sql()...

Describe alternatives you've considered

Staying with the wrapper

Additional context

This would also help the plugin with its claim of "easy to use and integrate with your existing codebase":

Unexpected error on sql helper function

Describe the bug
SafeQL throws an error when using the sql() helper in Postgres. This is a feature that is supported in postgres.js but it seems like SafeQL doesn't allow using this helper in writing queries. The queries below would throw this error Invalid Query: the type "Helper<number[], []>" is not supported".

export async function query(a: number[]) {
  return await sql`
    SELECT
      *
    FROM
      try_safe_ql
    WHERE
      id IN ${sql(a)}
  `;
}

To Reproduce
Steps to reproduce the behavior:

1. Setup SafeQL
2. Use this code in .ts file

export async function query(a: number[]) {
  return await sql`
    SELECT
      *
    FROM
      try_safe_ql
    WHERE
      id IN ${sql(a)}
  `;
}

Expected behavior
Usage of sql() helper should not throw errors

Screenshots

Desktop (please complete the following information):

• OS: MAC OS
• PostgreSQL version 14
• Version [e.g. 22]

Additional context
Add any other context about the problem here.

Adding `migrationsDir` to config throws ESLint error

Describe the bug
Adding migrationsDir to the plugin's rules config results in an error:

Error: backend/.eslintrc.js:
	Configuration for rule "@ts-safeql/check-sql" is invalid:
	Value {"migrationsDir":"./lib/migrations","databaseUrl":"postgres://postgres:postgres@localhost:5432","name":"unstoppable_website_development","operators":["queryRunner.query"]} should NOT have additional properties.
	Value {"migrationsDir":"./lib/migrations","databaseUrl":"postgres://postgres:postgres@localhost:5432","name":"unstoppable_website_development","operators":["queryRunner.query"]} should NOT have additional properties.
	Value {"migrationsDir":"./lib/migrations","databaseUrl":"postgres://postgres:postgres@localhost:5432","name":"unstoppable_website_development","operators":["queryRunner.query"]} should NOT have additional properties.
	Value {"migrationsDir":"./lib/migrations","databaseUrl":"postgres://postgres:postgres@localhost:5432","name":"unstoppable_website_development","operators":["queryRunner.query"]} should NOT have additional properties.
	Value {"migrationsDir":"./lib/migrations","databaseUrl":"postgres://postgres:postgres@localhost:5432","name":"unstoppable_website_development","operators":["queryRunner.query"]} should match some schema in anyOf.

To Reproduce
Steps to reproduce the behavior:

add migrationsDir to the config.

Expected behavior
A clear and concise description of what you expected to happen.

For the configuration not to error.

Screenshots
If applicable, add screenshots to help explain your problem.

Desktop (please complete the following information):

• OS: MacOS
• PostgreSQL version 11
• Version 0.0.7

Additional context
Add any other context about the problem here.

MySQL support

Is MySQL support planned?

Wrong types generated for full joins

Consider the following example:

Users have skills. There is 1 user in total. There are 7 skills in total. The user has 3 skills. Therefore there are 3 records in users_skills join table.

Consider the following code:

  const innerJoin = await sql<
    { id: string; user_id: string }[]
  >`SELECT S.id, US.user_id FROM skills S INNER JOIN users_skills US ON S.id = US.skill_id`;

  const leftJoin = await sql<
    { id: string; user_id: string | null }[]
  >`SELECT S.id, US.user_id FROM skills S LEFT JOIN users_skills US ON S.id = US.skill_id`;

  const rightJoin = await sql<
    { id: string; user_id: string }[]
  >`SELECT S.id, US.user_id FROM skills S RIGHT JOIN users_skills US ON S.id = US.skill_id`;

  const fullJoin = await sql<
    { id: string; user_id: string }[]
  >`SELECT S.id, US.user_id FROM skills S FULL JOIN users_skills US ON S.id = US.skill_id`;

  console.log({ innerJoin, leftJoin, rightJoin, fullJoin });

And the following logs:

{
  innerJoin: Result(3) [
    { id: '3', user_id: '6fdcb391-1485-452b-8da5-4c0aced00158' },
    { id: '4', user_id: '6fdcb391-1485-452b-8da5-4c0aced00158' },
    { id: '5', user_id: '6fdcb391-1485-452b-8da5-4c0aced00158' }
  ],
  leftJoin: Result(7) [
    { id: '1', user_id: null },
    { id: '2', user_id: null },
    { id: '3', user_id: '6fdcb391-1485-452b-8da5-4c0aced00158' },
    { id: '4', user_id: '6fdcb391-1485-452b-8da5-4c0aced00158' },
    { id: '5', user_id: '6fdcb391-1485-452b-8da5-4c0aced00158' },
    { id: '6', user_id: null },
    { id: '7', user_id: null }
  ],
  rightJoin: Result(3) [
    { id: '3', user_id: '6fdcb391-1485-452b-8da5-4c0aced00158' },
    { id: '4', user_id: '6fdcb391-1485-452b-8da5-4c0aced00158' },
    { id: '5', user_id: '6fdcb391-1485-452b-8da5-4c0aced00158' }
  ],
  fullJoin: Result(7) [
    { id: '1', user_id: null },
    { id: '2', user_id: null },
    { id: '3', user_id: '6fdcb391-1485-452b-8da5-4c0aced00158' },
    { id: '4', user_id: '6fdcb391-1485-452b-8da5-4c0aced00158' },
    { id: '5', user_id: '6fdcb391-1485-452b-8da5-4c0aced00158' },
    { id: '6', user_id: null },
    { id: '7', user_id: null }
  ]
}

As you can see the user_id type for the full join query does not include the null value, whereas there are such records in the results.

.eslintrc.json

{
  "parser": "@typescript-eslint/parser",
  "plugins": ["@typescript-eslint", "@ts-safeql/eslint-plugin"],
  "parserOptions": {
    "project": "./tsconfig.json"
  },
  "rules": {
    "@ts-safeql/check-sql": [
      "error",
      {
        "connections": [
          {
            "databaseUrl": "xxx", // hidden url
            "tagName": "sql",
            "transform": "{type}[]"
          }
        ]
      }
    ]
  }
}

SELECT version();

PostgreSQL 14.1 on aarch64-unknown-linux-gnu, compiled by gcc (Ubuntu 10.3.0-1ubuntu1~20.04) 10.3.0, 64-bit

Don't lint .js files

This could probably be solved by using ESLint's overrides

Support INTERVAL type

Is your feature request related to a problem? Please describe.

Describe the solution you'd like
I would like interval '${number} minutes/hours/etc..' to act like a valid interval type.

Postgres TIME inferred as Date

Describe the bug
selecting a TIME column inferred by sqfeql as js Date

To Reproduce
Steps to reproduce the behavior:
using postgres.js
migration:

CREATE TABLE test
(
    id serial primary key,
    test_time time not null
);

Expected behavior
TIME column should be inferred as string

Screenshots

Desktop (please complete the following information):

• OS: macOS
• PostgreSQL 14

Additional context
None