ts-safeql / safeql

Validate and auto-generate TypeScript types from raw SQL queries in PostgreSQL.
https://safeql.dev
MIT License
1.35k stars 22 forks source link

It does not take schema to distinguish tables #246

Closed TanguyFir closed 3 months ago

TanguyFir commented 4 months ago

Describe the bug If two tables have the same name but exist in different schemas, SafeQL cannot distinguish between them. This can result in false errors or missed errors when they should be flagged.

To Reproduce Steps to reproduce the behavior:

CREATE SCHEMA IF NOT EXISTS schema1;
CREATE TABLE IF NOT EXISTS schema1.table1 (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE SCHEMA IF NOT EXISTS schema2;
CREATE TABLE IF NOT EXISTS schema2.table1 (
    id SERIAL PRIMARY KEY,
    name TEXT
);
import { Client } from "pg";
import "dotenv/config";
import sql from "sql-template-tag";

async function main() {
  const client = new Client({
    user: process.env.POSTGRES_USER,
    host: process.env.POSTGRES_HOST,
    database: process.env.POSTGRES_DB,
    password: process.env.POSTGRES_PASSWORD,
    port: 5432,
  });
  await client.connect();

  // here SafeQL raises a false error
  const res = await client.query<{ name: string }>(
    sql`SELECT name FROM schema1.table1`
  );

  // here SafeQL missed an error
  const res2 = await client.query<{ name: string | null }>(
    sql`SELECT name FROM schema1.table1`
  );
}

main().catch(console.error);

Here a repository to reproduce the behaviour.

Expected behavior SafeQL should distinguish tables between schemas and raise error according to them.

Desktop (please complete the following information):