Is there a way to have SQL checked against existing database schema while it being written inside of .js or any other file type?

Hi! How are you?

You might already get an idea of what I'd like to achieve, but I'll try my best to describe it anyway.

I have a web server written in typescript (but the language is not the point, it also very well might be .rs or .kt) and I am playing with the idea of writing SQL manually, but, obviously, without IDE helping me out it's gonna be rough.

Is there a way to have the following code checked:

function runQuery(db) {
return db.query(`
SELECT id, name, nonExistingColumn FROM users WHERE id = 123;

with the result being

1. IDE should highlight with red that `nonExistingColumn` column is, in fact, not existing in the `users` table
2. IDE should also highlight with red that `id` has a type of `uuid`, therefore integer there is an issue
3. IDE should autocomplete and show types for columns and tables as I type the query (`Database Tools and SQL` kind of gives it already)

So the real problem is the point number #1 and #2. `Database Tools and SQL` doesn't check embedded queries, although provides autocompletion. It also has basic type checks in the console, but it's not enough. Some things could be resolved only at runtime.

I am looking for experience close with Android Studio and its developer experience with writing SQLs for SQLLite.

Especially painful after migration that changed some tiny detail in the schema.

Thanks in advance!

Please sign in to leave a comment.