Is SQL language outdated? (not relational algebra)

I mean, I am not sure if SQL is well-supported in some domains,

• Database denormalization
  • Not sure if SQL drivers can return a structured object. (It seems that node-postgres can return both JSON and Date, and of course, HarperDB can.)
• IDE integration / native language implementations / stored procedures
• Identifier (column name) sanitization / string sanitization, especially when used without prepared statements
  • This is also the case when using SQLite with more than 999 bindings.

Comments

[Alex Antra]

No not at all.

It's been around for 50 years and the pendulum swings every couple of years.

We've just been through our 'SQL is old no one wants to use it phase' now we are back to the 'SQL is amazing it's the perfect data tool'

Your questions sounds like your trying to use SQL in a non standard way, ie through Python or something, maybe check out some of the other articles here through the SQL tab

Here's one of my new favs

4 SQL Tips For Data Scientists And Data Analysts

SeattleDataGuy ・ Aug 8 ・ 7 min read

#sql #database #datascience #postgres

[Pacharapol Withayasakpunt]

Thank you for understanding my concerns.

Your questions sounds like your trying to use SQL in a non standard way, ie through Python or something

Hmm... Even if I use web server, or serverless, if it mostly likely comes from SQL drivers, that is the interface, anyway; not the SQL language itself.

[Alex Antra]

I’m sorry why do you keep talking about SQL drivers? What are you connecting with a driver?

[Pacharapol Withayasakpunt]

Usually web server's API.

[Rolf Streefkerk]

You're mentioning a lot of loosely related topics to SQL, what is it you actually want to ask here?

[Pacharapol Withayasakpunt]

How well are SQL dialects supported in various domains? Especially for the sake of (1) conveniences without ORM, (2) security.

[Rolf Streefkerk]

What is the context? You provide to little information to give any meaningful answer.

SQL dialects by definition are vendor dependent, by definition ORM's and Server products are vendor dependent. By definitio security issues are again inherent to those implementations.

The question is too broad

[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

[Pacharapol Withayasakpunt]

Maybe not that bad, but dialect-dependent.

datanami.com/2017/11/01/sqls-co-cr...