5 ms·

> So what would a general purpose language look like if it took these ideas to heart? Rather than expressing SQL in each language, I'd rather mix SQL with what

> So what would a general purpose language look like if it took these ideas to heart? Rather than expressing SQL in each language, I'd rather mix SQL with whatever language I am using. There are simple affordances that can happen at the tooling level to make SQL a first-class part of other languages. E.g. instead of having to use strings for SQL fragments, or 'change language' with special preambles, the language and tools could seamlessly work out that SELECT is entering an SQL mode and so on. This brings SQL into the code-base in a much better way.

nerdponx 5y ago
JavaScript actually has a nice syntax for this, called "template literals", specifically "tagged template literals" that can be used to represent things like SQL or GraphQL queries [0]: const sql = require('mssql') const query = sql.query`select * from mytable where id = ${value}` [0]: https://github.com/tediousjs/node-mssql https://github.com/tediousjs/node-mssql
- mypalmike 5y ago
  Interesting that this gets treated as a prepared statement. I wonder if it might have the drawback, among less experienced developers, of encouraging the use of simple string substitution in queries because it looks so much like that's what's happening.
  - nerdponx 5y ago
    Possibly. In my experience, newbies use string interpolation because don't even know that parameterized queries exist. Once they know about it and why it's important, they generally start using it without hesitation. For everyone else, I think "tagged" string literals in general are a great compromise. You don't have to add extra syntax to the language (no need for regex literals like /^foo/ if you can write something like regex"^foo"), but you can statically signal to your dev tools that the string might require highlighting or other analysis. The downside is that you are passing around a string and not an AST or something comparable if you really do need to do complicated code generation type of work, but maybe the ideal world is to have both tagged string literals and an abstract representation. The prepared statement technique I think is used in Python as well, at least in the SQLite library (maybe the ODBC library as well). Whereas e.g. libpq has an API function for executing a query with parameters. So the Postgres version of this wouldn't return a prepared statement, but some kind of opaque object that specifies what exactly will be fed to libpq upon query execution.
scns 5y ago
Some IDEs by Jetbrains have this feature, Ultimate for sure. Google has added it for SQLite in Android Studio IIRC. (edit) DataGrip has it too. You connect to the database and the IDE offers autocompletion for tables and columns and checks for corectness. SQL dialect can be set in Ultimate/DataGrip.

nerdponx 5y ago

JavaScript actually has a nice syntax for this, called "template literals", specifically "tagged template literals" that can be used to represent things like SQL or GraphQL queries [0]: const sql = require('mssql') const query = sql.query`select * from mytable where id = ${value}` [0]: https://github.com/tediousjs/node-mssql https://github.com/tediousjs/node-mssql

mypalmike 5y ago
Interesting that this gets treated as a prepared statement. I wonder if it might have the drawback, among less experienced developers, of encouraging the use of simple string substitution in queries because it looks so much like that's what's happening.
- nerdponx 5y ago
  Possibly. In my experience, newbies use string interpolation because don't even know that parameterized queries exist. Once they know about it and why it's important, they generally start using it without hesitation. For everyone else, I think "tagged" string literals in general are a great compromise. You don't have to add extra syntax to the language (no need for regex literals like /^foo/ if you can write something like regex"^foo"), but you can statically signal to your dev tools that the string might require highlighting or other analysis. The downside is that you are passing around a string and not an AST or something comparable if you really do need to do complicated code generation type of work, but maybe the ideal world is to have both tagged string literals and an abstract representation. The prepared statement technique I think is used in Python as well, at least in the SQLite library (maybe the ODBC library as well). Whereas e.g. libpq has an API function for executing a query with parameters. So the Postgres version of this wouldn't return a prepared statement, but some kind of opaque object that specifies what exactly will be fed to libpq upon query execution.

scns 5y ago

Some IDEs by Jetbrains have this feature, Ultimate for sure. Google has added it for SQLite in Android Studio IIRC. (edit) DataGrip has it too. You connect to the database and the IDE offers autocompletion for tables and columns and checks for corectness. SQL dialect can be set in Ultimate/DataGrip.