SQLDelight: Would it be possible to make the `.sq` syntax closer to Kotlin and allow for trailing co...

# squarelibraries

[Adam S]

SQLDelight: Would it be possible to make the .sq syntax closer to Kotlin and allow for trailing commas in table definitions? Or is the syntax not under SQLDelight’s control?

CREATE TABLE Players(
  player_number INTEGER PRIMARY KEY NOT NULL,
  full_name TEXT NOT NULL,
); -- ERROR <column def real> or <table constraint real> expected, got ')'

[Adam S]

or are trailing commas allowed? I just spotted an example in the docs with a trailing comma https://cashapp.github.io/sqldelight/2.0.0-rc02/jvm_sqlite/types/, but this shows an error in my IDE

(I have SQLDelight IJ plugin v2.0.0-SNAPSHOT-1688987704086)

[mbonnin]

I'd say it depends the dialect? Some might support it and other might not?

[mbonnin]

If you look at SQLite docs , looks like it's not supported

[Adam S]

thanks Martin, so it’s not supported by SQLite. But SQDelight uses a different syntax for the .sq files, right? That’s how it can support MySQL and Postgres too, and also custom column types https://cashapp.github.io/sqldelight/2.0.0-rc02/jvm_postgresql/types/#custom-column-types

I presume that SQLDelight is parsing the .sq files and then converting it to valid SQLite sytanx.

[mbonnin]

Ooo yea good call. As a user I think I prefer the syntax to be as close to what is sent to the actual DB though (to avoid surprises)

[mbonnin]

Even if SQL (or is it SQLite?) not supporting trailing commas is annoying here

[Adam S]

mmm true, that’s one way of looking at it. Personally I’d prefer it if I didn’t have to write any SQL at all!

[mbonnin]

<side rant> Love it when you need to pay 187CHF to access the SQL spec

[mbonnin]

If you don't want to write SQL, maybe look into Room? Unless you need multiplatform?

[Adam S]

isn’t Room for Android?

[mbonnin]

Yea, mostly

[Adam S]

I’m writing JVM, so I’d use Exposed, but I want Coroutine/Async support

[mbonnin]

Writing SQL is actually what I like about SQLDelight. All my previous attempts at non-SQL DB things failed at some point when writing migrations or needing a specific pragma or what not. By making SQL a requirement, SQLDelight has a steeper learning curve but also removes the surprises down the road when the app is deployed on multiple devices. If your DB supports it, SQLDelight should support it.

[hfhbd]

Yes exactly, the whole idea is to force you to write sql, mostly do fine tune your queries for each use-case by only fetching the data you really need. If you don't want to write sql or want a ORM framework, I would recommend Exposed. When using ORM, you often use it in DAO mode and fetch the whole row and do the actual filtering etc in your app logic.

regarding trailing comma, we could support it in dialects that also support it, because ideally you should be able to copy pasta the sql files to you db/get them from your db engineers.

[hfhbd]

Exposed also supports coroutines, but not R2DBC, but sqldelights R2DBC implementation could be improved though.

[Adam S]

regarding trailing comma, we could support it in dialects which also support it, but ideally you should be able to copy pasta the sql files to you db/get them from your db engineers.

That makes sense, thanks. So if trailing commas are optional, like in Kotlin, then that would still work. Or does the reverse have to be true, so the requirement is that I can copy+paste SQL from .sq files to DB engineers and it should just work?

[Adam S]

Exposed also supports coroutines

afaik Exposed doesn’t support async, or subscribing with a flow https://github.com/JetBrains/Exposed/issues/1560 (unlike SQDelight https://cashapp.github.io/sqldelight/2.0.0-rc02/jvm_sqlite/coroutines/)

[hfhbd]

I would prefer both ways to minimize any changes when doing some debugging/analyzing. This would allow postgres to use trailing commas, while sqlite not.

[Adam S]

alright, that makes sense, thanks @hfhbd

[hfhbd]

BTW I tested the trailing comma in postgres and mysql, and both don't support it.