Postgres Language Server: JS + TS Template Literal Support

[karlhorky]

Originally opened as issue and a discussion in the organization repo (probably the wrong locations):

• Issue JS + TS Template Literal Support #20
• Organization-level discussion https://github.com/orgs/supabase/discussions/16286

---

Hi there 👋 First of all, thanks for all of the effort that Supabase is putting into the PostgreSQL community, including tooling, really amazing 🙌

I recently encountered Postgres Language Server via the recent tweet from @kiwicopple and it looks amazing!

I have been chasing the "holy grail" of tooling for SQL template literals in JavaScript + TypeScript for a while now:

• syntax highlighting (works, with the VS Code extension SQL tagged template literals (syntax only))
• linting (works, using SafeQL)
• formatting, some options
  • prettier-plugin-embed and prettier-plugin-sql - uses sql-formatter
  • kind of works, with eslint-plugin-sql and eslint-plugin-unicorn, but very opinionated / not so configurable - uses pg-formatter
  • @potygen/prettier-plugin-pgsql by @ivank - uses custom Prettier AST formatter

Wasn't even looking for inline documentation yet, but that's a great addition too!

It seems that the plan will be for Postgres Language Server to support all of these use cases, which is amazing!

I would love to have everything handled by one highly-battle-tested tool, which could maybe also deal with some of the shortcomings from the tools listed above.

However, for Postgres Language Server to work for any of our projects, it would need a feature which I'm not sure it currently has (can't tell, from a quick glance at the docs and the code):

• Support for tagged template literals in JavaScript and TypeScript

There is precedent for SQL tagged template literals in JS+TS, some of the more popular ones being:

• Postgres.js
• @vercel/postgres

Eg. I would love for all of the features of Postgres Language Server to be available in the following code block:

import { createClient } from '@vercel/postgres';
 
async function queryPosts() {
  const client = createClient();
  await client.connect();
 
  try {
    const likes = 100;
    const { rows, fields } =
      await client.sql`SELECT * FROM posts WHERE likes > ${likes};`;
  } finally {
    await client.end();
  }
}

Or this code block:

// db.js
import postgres from 'postgres'

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

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
}


async function insertUser({ name, age }) {
  const users = await sql`
    insert into users
      (name, age)
    values
      (${ name }, ${ age })
    returning name, age
  `
  // users = Result [{ name: "Murray", age: 68 }]
  return users
}

[psteinroe]

hey @karlhorky, sorry for the late reply!

I have been doing some research on this in the meanwhile and it seems like that it's the client who has to support multiple language servers for a single file. You can find a related discussion here. for vscode as a client it is described here. I also found a neovim plugi that implements something similar. In summary, it will require some changes to the typescript plugin / extension. I am quite surprised myself that almost all editors (exception being Jetbrains products) only support a single language server per file.

[remorses]

This would be amazing

It's possible to add completions for SQL inside Typescript and Js files with a typescript plugin, this is an example: https://github.com/runem/lit-analyzer

(notice that there is another similar extension but autocompletion is not working there)

[karlhorky]

Nice find! I guess this works via ts-lit-plugin created by @runem and co-maintained by @rictic:

Typescript plugin that adds type checking and code completion to lit-html

• https://github.com/runem/lit-analyzer/tree/master/packages/ts-lit-plugin

[karlhorky]

Oh I also found one that does completions inside SQL inside of tagged template literals in JS/TS, by @xialvjun and @darky:

• https://github.com/xialvjun/ts-sql-plugin

[remorses]

No lit-analyzer is different from ts-lit-plugin, as i said completions in ts-lit-plugin don't work

Also lit-analyzer has the same interface as a typical LSP so it should be easy to use as starting point

[karlhorky]

Ah do you mean completionsAtOffset() in packages/lit-analyzer/src/lib/analyze/document-analyzer/html/completion/completions-at-offset.ts? I'm not as familiar as I would like to be with LSPs...