Discussion on: Is SQL language outdated? (not relational algebra)

[Pacharapol Withayasakpunt]

Not sure if SQL drivers can return a structured object.

SQLite driver in both Python, Node and Kotlin; AFAIK, doesn't

• Know whether the column, or generated column, is JSON
• GROUP BY only returns the first column. It can be fixed with json_group_array or json_group_object, but the driver itself doesn't realize that the data is JSON, therefore will return string instead.
• $group (and $lookup) in MongoDB aggregate always return Array first, especially if you don't $unwind

I haven't tried enough PostGRES or MySQL native drivers in any of the languages I used, to tell all these aspects.

Identifier (column name) sanitization

I have tried to create a SQLite ORM with this kind of sanitization as well. However, it doesn't fix the fact that, no matter what I do, identifiers are case-insensitive.

string sanitization

Normally, I would use a prepared statement, but

• Occasionally, I need to do this in SQLite

sql.prepare(`... WHERE entry IN (${Array(entries.length).fill('?')})`).all(entries)

// Will crash if `entries.length > 999`

• This is a reference to HarperDB as well, where prepared statements are not supported.

I find no way to query this.

{
 a: '\"`@#$% SELECT'
}

I know no way to make SQLite strongly typed, with native drivers, in TypeScript and Kotlin.

IDE integration / native language implementations

[Rolf Streefkerk]

You post a number of "issues" and mention various databases. I'm not sure what the aim is of your question.
With everytihng, there are design limitations that much is a given. The question is what do you need as an end result and what are the tools that best fit that end result.

Choose a DB that fits, and work with the constraints of that database.

If you have something really concrete, then that may help much better to get to a solution