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.

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

--

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":

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.

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

--

The configuration options on this page are confusing because they imply that Postgres.js and Sequelize can only be used with migrations instead of the simpler databaseUrl configuration.

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

--

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

--

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: https://github.com/pyramation/libpg-query-node/issues/9#issuecomment-1262491945

Thanks for #51!

Just a quick fix for the documentation of the connections.fieldTransform: 'snake' option (at least, from what I'm understanding about the option)

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

--