I created a VSCode extension to make developing raw SQL queries incredibly easy [TypeScript+Rust+WASM]
This is translated from the original Japanese article.
You can use the following features like this.
TypeScript with Prisma ⇩
Rust with SQLx ⇩
Not only SQL files but also other files containing raw SQL queries are supported by SQL’s Language Server Protocol (LSP).
Currently, TypeScript’s Prisma and Rust’s SQLx are supported by default. Syntax highlighting for SQL only works with Prisma.
sqlsurge Configuration
This extension is called sqlsurge. sqlsurge utilizes a Golang-based sqls for the SQL Language Server, so:
- Golang
- sqls
are required. I’ve prepared an installation guide since I believe only a limited number of people have installed sqls.
When you open TypeScript or Rust files, select “Install with command” in the popup that appears, and the installation command will run in the terminal(It will take about 30 s for the first time). After reloading the screen, auto-completion will work.
Additionally, to enable auto-completion within strings in VSCode, you need to add the following setting to settings.json:
{
"editor.quickSuggestions": {
"strings": true
}
}Mechanism of sqlsurge
Enabling SQL Language Server on Other Languages
Without this, I probably couldn’t have developed my extension.
This article doesn’t mention it, but it creates virtual files and documents (in this case, SQL files) using VSCode API’s Virtual Document.
Then, at this point, it triggers the SQL Language Server on the virtualized document:
// trigger completion on virtual file
const vDocUriString = `${originalScheme}://${sqlNode.vFileName}`;
const vDocUri = vscode.Uri.parse(vDocUriString);
return vscode.commands.executeCommand<vscode.CompletionList>(
"vscode.executeCompletionItemProvider",
vDocUri,
position,
context.triggerCharacter,
);Since the currently opened file shares the editor with the virtual SQL file, specifying the position enables SQL completion over TypeScript or Rust.
Presumably, the same mechanism enables CSS or JS completion within HTML.
Is this feature only available in VSCode? I wonder if it’s possible in Vim or other editors.
Detecting Raw SQL
For detecting raw SQL, TypeScript or Rust ASTs are analyzed to find prisma.$queryRaw or sqlx::query!, extracting the SQL arguments. TypeScript uses the official Compiler API, while Rust uses syn for AST analysis.
While dealing with ASTs in syn, I needed to obtain the start and end positions of SQL, but simply adding syn wasn’t enough; I had to specify a feature with proc-macro2.
proc-macro2 = { version = "1.0.79", features = ["span-locations"] }It’s important to read the documentation carefully, as it clearly states, “Available on crate feature span-locations only.”
LineColumn in proc_macro2 - Rust
A line-column pair representing the start or end of a `Span`.
docs.rs
By the way, astexplorer.net was useful for confirming ASTs (TypeScript is better viewed with TypeScript AST Viewer).
I intend to make customization possible to support not only Prisma and SQLx but also other libraries or custom functions in the future.
Converting Rust to WASM and Running in VSCode
To run the Rust code for detecting SQL, I convert it to WASM using wasm-pack and call it from JavaScript. Since VSCode runs on Electron with Node.js supporting WASI, WASM works.
This article about Rust + WASM was highly useful. I basically copied it.
I’m currently using Webpack as the bundler as per this article’s instructions, but I prefer esbuild due to its better development experience. However, I couldn’t get it to work after trying, so I temporarily gave up. I’ll try again later.
If anyone wants support for other languages like Go or Python, please open an Issue. And while you’re at it, submit a PR too (just kidding). (Since only the SQL range and SQL string need to be returned, contributing should be relatively straightforward.)
CI with GitHub Actions
I conduct unit tests for Rust and integration tests on VSCode with @vscode/test-electron + Jest using GitHub Actions. Tests are performed on Ubuntu and macOS. For more details, see my previous article.
I’ve optimized the caching to reduce CI time by about half (since installing wasm-pack and building Rust sometimes took over 10 minutes).
TODOs
- (Done)Support for Prisma in TypeScript
- (Done)Support for SQLx in Rust
- (Done)Install guide for sqls
- SQL formatting
- Support for custom functions for raw SQL queries, not just Prisma and SQLx
- Execute SQL queries
- Syntax highlighting for SQL
- Show quick info symbol
- Show sqls config with tree view
I plan to implement formatting and custom functions at least.
Summary
I’ve created an extension that enables auto-completion for raw SQL queries. I searched to see if something similar already existed, but it seems not. Please let me know if it does. If you’re someone who’s forced to deal with raw SQL at work or harbors resentment towards ORMs, give it a try.
I’d be extremely motivated if I could receive GitHub Stars and reviews for the extension :pray:
Additional
When I commented that I made this extension on an issue where someone wished for similar functionality in Prisma, I received a Star from someone on the Prisma team!! Many thanks! Extremely grateful! All hail me!