Nesting Query Type

[kayvaninvemo]

Is there a way to generate the type for nesting queries?

    const q1 = sql`select * from my_table`;
    const q2 = sql`
      select q1.id, q1.my_col
      from (${q1}) q1
    `;

It gives me this error:

Query: "select q1.id, q1.my_col from ($1) a \\gdesc"
Result: "psql:<stdin>:1: ERROR:  syntax error at or near \"$1\"\nLINE 1: select q1.id, q1.my_col from ($1) a \n          

which the reason is quite obvious.

If it is inevitable, is there a way to tell the type generator not to try for that nesting query. I will generate the type manually for nesting queries, but if it gives error, it will be hard to manage the code. (It will cause us to ignore the errors)

    const q1 = sql<queries.MyTable>`select * from my_table`;
    const q2 = sql<my_queries.Q2>`
      select q1.id, q1.my_col
      from (${q1}) q1
    `;

  // this should be created by my self, but as I am using Pick<Type, Keys> it should be type safe enough
  export declare namespace my_queries {
    export type Q2 = Pick<queries.MyTable, "id" | "my_col">;
  }

  export declare namespace queries {
    // Generated by @slonik/typegen

    /** - query: `select * from my_table` */
    export interface MyTable {
      /** column: `public.my_table.id`, not null: `true`, regtype: `bigint` */
      id: number;

      /** column: `public.my_table.my_col`, not null: `true`, regtype: `text` */
      my_col: string;

      /** column: `public.my_table.my_col2`, not null: `true`, regtype: `text` */
      my_col2: string;
    }
  }

[mmkal]

You're right, types can't be generated for queries like this at the moment. I'm hoping that #315 will improve this somewhat. For now, see the readme "Limitations" section. You should find that since the tool doesn't know how to get a type for the query with a nested subquery, you can add a manual type and it won't be removed. But you will see an error message. To make the tool skip the query entirely, use a variable other than sql;

const q1 = sql`select * from my_table`;
const _sql = sql;
const q2 = _sql`
  select q1.id, q1.my_col
  from (${q1}) q1
`;

I'll leave this open until I add a case along these lines to the limitations section, but keep an eye on #315 for a fix that doesn't require manually adding a type.

[kayvaninvemo]

Thanks for the fast reply.

I want just to ask that, "vscode-sql-template-literal" does not do syntax highlighting when I use "_sql" is there a work around?

[mmkal]

You would have to ask the maintainer to support underscores too. Looks like it'd be as easy as adding _? here. Feel free to point to this issue as an explanation if you create a pull request or issue. I think it's a reasonable request so hopefully would be accepted. If not, you could try vscode-sql-template-literal which officially supports this library (see #269) so hopefully would be willing to make this small change.

[mmkal]

I'm also open to other ways of skipping codegen for certain queries like a comment directive if there are issues with the above suggestion for some reason, but I like const _sql = sql because

1. it's so intuitive why it works, it doesn't really require documentation outside of a "tips and tricks" section
2. There's no implementation or maintenance cost on the library side

[kayvaninvemo]

I used SQL tagged template literals and it works as expected.

    const sqlFragment = sql;

It also support typed sql fragments.

  sql<queries.MyTable>`select * from my_table`

You might consider change the readme to suggest this plugin instead