Compile-time checking without duplicating the database schema

[ozgb]

Hi,

On the project roadmap you discuss being able to describe database schemas in PRQL:

Currently PRQL compiles into SQL with no understanding of the underlying tables. We plan to introduce database schema declarations into the language, so PRQL compiler and tooling can enrich the developer experience with autocomplete and early error messages.

The goal here is to catch all errors at PRQL compile time, instead of at the database’s PREPARE stage.

Have you considered instead taking a similar approach to SQLX, and sending the compiled queries to the database backend then parsing any error messages?
https://github.com/launchbadge/sqlx#sqlx-is-not-an-orm

[max-sixty]

V interesting suggestion @oscarbailey-tc

It's even better if we can produce errors without doing a query — it means we can get errors on every keystroke — but this would let us bootstrap lots of errors without building all the logic within PRQL.

At the moment we're trying to remain a fairly thin layer and defer to other tools for querying the DB. That's not a hard requirement, and we could issue queries, or allow tools to pass back error messages.

Ofc open to any other thoughts and ideas...

[ozgb]

It's even better if we can produce errors without doing a query — it means we can get errors on every keystroke — but this would let us bootstrap lots of errors without building all the logic within PRQL.

I was doing some experiments with SQL queries + templating recently and was exposing types of tables/function returns to the templating engine to create aliases e.g.:

DROP VIEW IF EXISTS submissions_view;
CREATE VIEW submissions_view AS SELECT 
{{#submissions_view}}
    posts.{{name}} as "{{name}}!",
{{/submissions_view}}
    (SELECT post_id from comments_on_posts WHERE comments_on_posts.post_id = posts.id) as "parent_id",
{{#accounts_viewfn_columns}}
    author."{{{name}}}" as "author${{{name}}}"{{^last}},{{/last}}
{{/accounts_viewfn_columns}}

FROM posts
INNER JOIN accounts_viewfn (null) as author ON author."id!" = posts.author_id;

Result:

DROP VIEW IF EXISTS submissions_view;
CREATE VIEW submissions_view AS SELECT 
    posts.id as "id!",
    posts.author_id as "author_id!",
    posts.content as "content!",
    posts.created_at as "created_at!",
    posts.updated_at as "updated_at!",
    (SELECT post_id from comments_on_posts WHERE comments_on_posts.post_id = posts.id) as "parent_id",
    author."id!" as "author$id!" ,
    author."alias!" as "author$alias!" ,
    author."public_key!" as "author$public_key!" ,
    author."name!" as "author$name!" ,
...

FROM posts
INNER JOIN accounts_viewfn (null) as author ON author."id!" = posts.author_id;

So I guess something similar could be done by feeding this data into an LSP to get errors on every keystroke - not sure if there's a way to subscribe to the db and get notified of schema changes? But if there was you could cache this type information locally and update when anything changes.

This would get you some level of error checking without sending a query.

This was for postgres, using their CLI tool:

$ psql $DATABASE_URL -c "\df" # functions
$ psql $DATABASE_URL -c "\d+" # list all tables
$ psql $DATABASE_URL -c "\d+ accounts" # type info for specific table

[ozgb]

Another thought:
Prisma allows users to "pull" from the database to a schema file, might be a useful reference:
https://www.prisma.io/docs/reference/api-reference/command-reference#db-pull

PgTyped grabs type information from postgresql to generate Typescript types from SQL source:

[max-sixty]

Super interesting — this is exactly what we were thinking for PRQL to do.

As well as the latency being much lower relative to requiring a live DB connection, it's also easier a more modular design, with less shared context, which is easier to abstract across databases, and test and maintain.

We would need to decide when to update the current schema. That said, schemas generally don't change that frequently.