How to syntax highlight/autocomplete/autoformat sql command inside javascript / node.js file in vscode / visual studio code?

Question

Right now, the SQL command is treated like a string and have to be manually formatted by capslock if it is inside a JavaScript function. For example:

const sqlite3 = require('sqlite3').verbose();

let db = new sqlite3.Database('../db/test.db');
 
let sql = `create table person (
    id BIGSERIAL NOT NULL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    gender VARCHAR(6) NOT NULL,
    date_of_birth DATE NOT NULL,
    email VARCHAR(150));
insert into person (first_name, last_name, gender, date_of_birth) values ('Anne', 'Smith', 'Female', '1988-01-09');
insert into person (first_name, last_name, email, gender, date_of_birth) values ('Jake', 'Jones', '[email protected]', 'Male', '1990-12-31');`;

The long SQL command will show as a block of green text in my VS Code (following my color scheme for string).

The desired color scheme is as if I put it in an sql file, eg:

create table person (
    id BIGSERIAL NOT NULL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    gender VARCHAR(6) NOT NULL,
    date_of_birth DATE NOT NULL,
    email VARCHAR(150));
enter image description hereinsert into person (first_name, last_name, gender, date_of_birth) values ('Anne', 'Smith', 'Female', '1988-01-09');
insert into person (first_name, last_name, email, gender, date_of_birth) values ('Jake', 'Jones', '[email protected]', 'Male', '1990-12-31');

has the colors:

• orange for words like create table, insert into, not null, primary key, varchar, etc
• white for person, id, first_name, last_name, gender, etc
• green for 'Anne', 'Smith', '[email protected]', etc
• purple for bigserial (for some reason the color does not show on stackoverflow's codeblock)

Maybe through an extension or through vscode settings?

Answer 1

For just syntax highlighting, this seems to work well: https://marketplace.visualstudio.com/items?itemName=forbeslindesay.vscode-sql-template-literal

It works by activating SQL syntax highlighting for template literals preceded by sql, like

const query = sql`SELECT * FROM articles;`

You'll need to then use a library that has an sql tag function, or make a dummy one like the following to pass through your template string contents like expected:

// TypeScript
const sql = (strings: TemplateStringsArray, ...expr: any[]) =>
  strings
    .map((str, index) => str + (expr.length > index ? String(expr[index]) : ''))
    .join('');

// ES6
const sql = (strings, ...expr) =>
  strings
    .map((str, index) => str + (expr.length > index ? String(expr[index]) : ''))
    .join('')

There's an alternative extension with more bells and whistles (syntax checking for Postgres), but the same template literal approach. I haven't tried it out myself, though.