SafeQL installation fails on Windows about safeql

[karlhorky]

@gregnr opened a PR using emnapi + WebAssembly to get libpg_query support on Windows 👀 🔥 (in the libpg-query npm package)

• launchql/libpg-query-node#34

[Newbie012]

I'll take a look at https://github.com/taozhi8833998/node-sql-parser as well

[Newbie012]

@swax done - https://github.com/ts-safeql/safeql#limitations

[karlhorky]

The PR has been merged and published as [email protected] - installable on Windows, finally!

launchql/libpg-query-node#45 (comment)

[Eprince-hub]

@karlhorky
SafeQL linting error message on Windows Computer

Because of the issue with a long path not being found by node on Windows: nodejs/node#50753

I needed to run the two scripts below to get it working

echo 'node-linker=hoisted' > ./.npmrc

pnpm install --force

I will be closing this issue as it is now confirmed that SafeQL can be installed and used on Windows Machines

[Newbie012]

I ran some tests, and it looks like node-sql-parser isn't working as expected. It fails to parse many syntaxes such as:

SELECT ((colname + INTERVAL '1 month')::DATE - 1) as x

If I change it to this (which I believe will result in a runtime error; haven't checked):

SELECT (colname + INTERVAL '1 month')::DATE - 1 as x

it will throw an error.

I believe we should stick with libpg-query since it uses the real PostgreSQL query parser. Let's see how the conversation in launchql/libpg-query-node#22 evolves.

[karlhorky]

Just trying out the new [email protected] Windows version on a GitHub Actions windows-latest runner, and it looks like there's a new ERR_UNSUPPORTED_ESM_URL_SCHEME error with SafeQL regardless of whether a linting problem is encountered or not:

pnpm eslint . --max-warnings 0
  
Oops! Something went wrong! :(
ESLint: 8.57.0
Error [ERR_UNSUPPORTED_ESM_URL_SCHEME]: Only URLs with a scheme in: file, data, and node are supported by the default ESM loader. On Windows, absolute paths must be valid file:// URLs. Received protocol 'd:'
    at throwIfUnsupportedURLScheme (node:internal/modules/esm/load:239:11)
    at defaultLoad (node:internal/modules/esm/load:130:3)
    at ModuleLoader.load (node:internal/modules/esm/loader:409:13)
    at ModuleLoader.moduleProvider (node:internal/modules/esm/loader:291:56)
    at new ModuleJob (node:internal/modules/esm/module_job:65:26)
    at #createModuleJob (node:internal/modules/esm/loader:303:17)
    at ModuleLoader.getJobFromResolveResult (node:internal/modules/esm/loader:260:34)
    at ModuleLoader.getModuleJob (node:internal/modules/esm/loader:241:17)
    at async ModuleLoader.import (node:internal/modules/esm/loader:328:23)
Error: Process completed with exit code 1.

This appears to be related to the Node.js requirement on Windows to "need to write file:///c:/x/y/z instead of c:/x/y/z"

There are no ESM errors or warnings from the ESLint run on Windows when SafeQL is disabled:

So this appears to be a SafeQL-specific bug with Windows support.

cc @Newbie012

[karlhorky]

@Eprince-hub if you could do one more check from your side on your Windows system, then I think this issue can be closed.

[Newbie012]

That's not exactly an issue of SafeQL, but libpg-query.

One of my machines is using Windows, but all of the development occurs in WSL (Windows Subsystem for Linux) and it seems to work fine.

Another workaround that I can think of is using Docker.

[swax]

Any chance you could mention that this doesn't work on Windows on your website and/or the readme file? Just spent a good amount of time trying to figure out why this wasn't installing.

I am looking forward to using it someday!

[Newbie012]

Hopefully should be resolved if Hilzu/libpg-query-node#11 will be resolved as well.

[karlhorky]

Thanks to @lfittl [email protected] now has support for compiling on Windows too 🎉 🚀

• pganalyze/libpg_query#44 (comment)
• pganalyze/libpg_query#233

[karlhorky]

There's a PR by @aquariuslt open in libpg-query now 👀 🙌

• launchql/libpg-query-node#45

[karlhorky]

@Eprince-hub can you confirm that SafeQL performs checking and shows errors and warnings on Windows now?

[karlhorky]

Actually, looking at this closer and trying to create a reproduction, it looks like it's something else in eslint-config-upleveled that's causing it, since a simple SafeQL config seems to work:

upleveled/preflight-test-project-next-js-passing#156 (comment)

Edit: Yes confirmed, the problem with ERR_UNSUPPORTED_ESM_URL_SCHEME was an error in our own eslint-config-upleveled package:

It seems like using process.cwd() in a dynamic import() doesn't work on Windows, have to use pathToFileURL:

+import { pathToFileURL } from 'node:url';

-await import(`${process.cwd()}/package.json`, {
+await import(pathToFileURL(`${process.cwd()}/package.json`).href, {
  assert: { type: 'json' },
});

[karlhorky]

I have now successfully been able to receive a SafeQL error on GitHub Actions, on a Windows runner 🎉

  await sql`
    INSERT INTO
      loogos (name)
    VALUES
      ('Next.js'),
      ('Next.js'),
      ('Next.js'),
      ('Next.js')
  `;

SafeQL error:

$ pnpm eslint . --max-warnings 0
(node:2004) ExperimentalWarning: Importing JSON modules is an experimental feature and might change at any time
(Use `node --trace-warnings ...` to show where the warning was created)

D:\a\preflight-test-project-next-js-passing\preflight-test-project-next-js-passing\migrations\00000-create-table-logos.ts
Error:   25:7  error  Invalid Query: relation "loogos" does not exist  @ts-safeql/check-sql

✖ 1 problem (1 error, 0 warnings)

Error: Process completed with exit code 1.

• Workflow run: https://github.com/upleveled/preflight-test-project-next-js-passing/actions/runs/8566529267/job/23476453817?pr=156

---

While testing the Windows SafeQL issue, I created a cross-platform PostgreSQL + SafeQL reproduction repo (Windows, macOS and Linux runners).

Now that I solved it, I thought this may also be a useful thing for users of the SafeQL project longer term (eg. for users demonstrating their bugs):

• https://github.com/karlhorky/safeql-github-actions-repro-template

cc @Newbie012 , in case we should integrate this into the issue workflow on GitHub.