The syntax follows https://github.com/krisajenkins/yesql/ which works with Clojure. So then the DB queries can be defined in a language agnostic way, so you don't need a gateway API to access the DB :)
There are many opportunities to grow this, e.g. transforming the SQL files into stored procedures, and being able to lint and check against a DB schema definition. However, I'm not sure that the indirection step of hiding the SQL away from the code actually makes sense in the long run. A tight coupling allows filters and other optimisations to be easily added to the end of a query, and saves one lookup that is not yet supported in your editor.
Hey, thanks for the opportunity ideas! You're totally right about the editor lookup problem. I wrote this project, I use this project in a production setting and not being able to jump to the SQL from code does bother me.
Seems similar in inspiration to https://pugsql.org/ which is a python reimplementation of hugSQL, more or less.
I'd say an advantage of pugSQL is that it's sqla-core under the hood, so lots of stuff will Just Work and you can drop down to core to commit war crimes if you have to.
I haven't used pug, but I read through it a bit and like their approach. I think they have a more intricate way of parsing SQL than we have in aiosql. We're doing it quick and dirty with some regular expressions.
[hp]ugSQL seems to have been inspired by[1] Yesql, a library with ``a similar take on using SQL'' that has also inspired this[2] rom-rb adapter; not a Ruby guy so there may be something a lot better/closer, but this is what I came up with after a couple minutes of searching.
My initial thought is I'd rather just have a module that would find project-related .sql files and parse into named blocks based on the comments, treating them as named strings. No db entanglement required, just a simple way to organize queries in sql files. You would use it more like this:
import sqlite3
import project_queries as queries # a module that loads queries from local .sql files as strings in module namespace
conn = sqlite3.connect('myapp.db')
cursor = conn.cursor(queries.get_all_users)
# ^^^^^^^^^^^^^^^^^^^^^
users = cursor.fetchall()
# >>> [(1, "nackjicholson", "William", "Vaughn"), (2, "johndoe", "John", "Doe"), ...]
aiosql may add some features to help with passing values to the query. I see `^` used with `:users` in an example but don't quite get it.
-- name: get-user-by-username^
I often need to generate DDL details like table and column names in the query, which you don't want escaped, along with data like values and ids where you would want escaping (:users or %(users)s).
It works nicely to use string formatting (`CREATE TABLE {table_name} ...`) for DDL along with interpolation (`WHERE user_id IN %(user_ids)s`) in the same query.
Nice idea, but this pattern hides your queries away from the developer. In practice, developers need the query transparent and not hidden in a file somewhere away from the code they're reading. Otherwise you're building a pattern for using queries without knowing the query's cost. If you need raw SQL, use SQLAlchemy Core and just don't use the ORM features.
I use roughly this approach in Java, and I would argue that it makes the queries more available to the developer.
Yes, they're in a different file. But it's another file in the same codebase, and it's directly referenced by the code that's using it. This means it's not really any harder to track down than the code to a function that lives in a different file. It's really NBD.
And, in return for putting your SQL in .sql files, you get all sorts of nice things. Syntax highlighting for your SQL, for starters. And it's somewhere that autoformatters and linters and SQL unit testing tools and the like can get at easily. And you get SQL code that isn't being forced through whatever laundry wringer and or cheese grater is imposed by your language's string literal syntax. (This last one admittedly isn't such a problem in Python.)
This really isn't too far off from why your JavaScript code is generally happiest in .js files and your CSS is generally happiest in .css files, rather than making them all inline in your HTML.
> And, in return for putting your SQL in .sql files, you get all sorts of nice things. Syntax highlighting for your SQL, for starters.
Until recently I was looking for a good scheme to keep my sql in .sql files primarily for this reason. But some time in the past few releases, pycharm has started detecting SQL in strings, and if I add an instance of my DB as a data source to the project, it autocompletes table/column names, etc.
I still see some appeal to putting my SQL in .sql files, but good IDE support has given me most of the benefit while keeping it in local strings, plus some help with prepared statements that would have required some added effort if I'd moved it to SQL files.
Sure, with SQLAlchemy Core you might start out by adding some `conn.execute` statements where necessary. But, inevitably, you'll want to use some queries in multiple places. Some of them you'll even want to use in multiple different files. Your schema will surely change over time, and your queries along with them. It would be a shame if you needed to hunt for queries across all of your files and make the necessary changes to each copy of your queries which need to be change.
It seems much more convenient to have some part of your code base, a separate module, which is dedicated to communication with the database.
The rest of your code communicates with that module through function calls and doesn't need to worry about how it's implemented. Realistically, you'll probably always use a database, but this does make it much easier to switch between databases, or decide you want to use a flat-file instead, or even use dynamodb for some high-traffic part which doesn't need much consistency.
Once you have a module, it's incredibly natural to de-duplicate queries. Instead of writing out each query once for each time it's called, you can stick it into a helper function and call that function from many places. You can organize queries by their purpose or which tables they use, making it easier to change all the queries impacted by each schema change.
In a world where you have large queries SQLAlchemy lets you give meaningful names to subqueries and different expressions in the larger queries, making them easier to understand. If you share some subqueries between queries... that module dedicated to working with your db starts to make a lot of sense!
In all, I can think of a lot of situations where "hides your queries away from the developer" is a cost out-weighed by many other benefits.
> It seems much more convenient to have some part of your code base, a separate module, which is dedicated to communication with the database.
That module doesn't have to be .sql file. You can organize your code in whatever language to achieve what you are describing. And have IDE help with Jump/Peek definition.
How is this different from having a repository of queries somewhere - this is basically a query repository but not in the active language in fact.
Your queries should be hidden away from the developer - I mean obfuscation is never the goal but separation is... If your query is written inline in some business logic that section of code has poor tests and is unreliable. SQL is complex, I absolutely adore it but it's not simple - keep it isolated from the logic of your system and, if possible, use a layered architecture approach that allows the entire persistence layer to be detachable.
> How is this different from having a repository of queries somewhere - this is basically a query repository but not in the active language in fact.
Looks like they do fancy stuff like template substitution &etc instead of just opening a text file and feeding it to the sql engine.
I was looking at it and thinking "why not just use jinga?" but then you wouldn't get the 'query manager' object and name spacing (and probably other features I've missed).
It actually seems a fairly good way to mix languages, I haven't looked at the code to see what they're really up to but I like the concept.
Sorry - is it because you don't analyze the queries even though you write them (and thus are responsibly for optimization) that is the issue? Or do you have the same complaints about .NETs Entity Framework?
Yes, this feels like it reinvents prepared statements in a less portable way. It kind of goes against the spirit of their opening justification for using it: "SQL is code, you should be able to write it, version control it, comment it, and run it using files." If that's the goal encourage the use of a way to leverage native SQL that could run on any platform or engine.
Looks kinda nice, but can it handle "IN (...)" statements correctly? Or conditionally adding some WHERE param? If not, this is rather simplistic and couldn't replace writing inline queries, and then I think I'd rather not mix together 2 approaches.
I've built pre-parsing layers for queries that can explode out params properly for IN() handling - though depending on your SQL variant you can also get away with string passing an array to get around that.
Conditional WHERE seems unapproachable with this tool - which is one of my doubts about the need of a tool like this.
If you instead approach your SQL by having each query wrapped in a function in an isolated part of your code base you, as a company/team/whatever, can choose how much logic to let reside inside of those functions - building up conditional WHERE clauses is a very common thing to need to do.
Take a look at JinjaSQL (https://github.com/hashedin/jinjasql). Supports conditional where clauses as well as in statements. It uses Jinja templates, so you get a complete template language to create the queries.
Readit News
There are many opportunities to grow this, e.g. transforming the SQL files into stored procedures, and being able to lint and check against a DB schema definition. However, I'm not sure that the indirection step of hiding the SQL away from the code actually makes sense in the long run. A tight coupling allows filters and other optimisations to be easily added to the end of a query, and saves one lookup that is not yet supported in your editor.
I'd say an advantage of pugSQL is that it's sqla-core under the hood, so lots of stuff will Just Work and you can drop down to core to commit war crimes if you have to.
Happy user of PugSQL here. I just wish there was a VSCode extension for autocompletion.
Plus it’s got a cool name
[1] https://www.hugsql.org/#faq-yesql
[2] https://github.com/rom-rb/rom-yesql (documentation: https://www.rubydoc.info/gems/rom-yesql)
It works nicely to use string formatting (`CREATE TABLE {table_name} ...`) for DDL along with interpolation (`WHERE user_id IN %(user_ids)s`) in the same query.
https://nackjicholson.github.io/aiosql/defining-sql-queries/...
Deleted Comment
Deleted Comment
Yes, they're in a different file. But it's another file in the same codebase, and it's directly referenced by the code that's using it. This means it's not really any harder to track down than the code to a function that lives in a different file. It's really NBD.
And, in return for putting your SQL in .sql files, you get all sorts of nice things. Syntax highlighting for your SQL, for starters. And it's somewhere that autoformatters and linters and SQL unit testing tools and the like can get at easily. And you get SQL code that isn't being forced through whatever laundry wringer and or cheese grater is imposed by your language's string literal syntax. (This last one admittedly isn't such a problem in Python.)
This really isn't too far off from why your JavaScript code is generally happiest in .js files and your CSS is generally happiest in .css files, rather than making them all inline in your HTML.
Until recently I was looking for a good scheme to keep my sql in .sql files primarily for this reason. But some time in the past few releases, pycharm has started detecting SQL in strings, and if I add an instance of my DB as a data source to the project, it autocompletes table/column names, etc.
I still see some appeal to putting my SQL in .sql files, but good IDE support has given me most of the benefit while keeping it in local strings, plus some help with prepared statements that would have required some added effort if I'd moved it to SQL files.
Right. It’s not much different than a constants file.
https://marketplace.visualstudio.com/items?itemName=bbsimonb...
It seems much more convenient to have some part of your code base, a separate module, which is dedicated to communication with the database.
The rest of your code communicates with that module through function calls and doesn't need to worry about how it's implemented. Realistically, you'll probably always use a database, but this does make it much easier to switch between databases, or decide you want to use a flat-file instead, or even use dynamodb for some high-traffic part which doesn't need much consistency.
Once you have a module, it's incredibly natural to de-duplicate queries. Instead of writing out each query once for each time it's called, you can stick it into a helper function and call that function from many places. You can organize queries by their purpose or which tables they use, making it easier to change all the queries impacted by each schema change.
In a world where you have large queries SQLAlchemy lets you give meaningful names to subqueries and different expressions in the larger queries, making them easier to understand. If you share some subqueries between queries... that module dedicated to working with your db starts to make a lot of sense!
In all, I can think of a lot of situations where "hides your queries away from the developer" is a cost out-weighed by many other benefits.
That module doesn't have to be .sql file. You can organize your code in whatever language to achieve what you are describing. And have IDE help with Jump/Peek definition.
For example https://github.com/cashapp/sqldelight for Kotlin integrates with IntelliJ to provide ctrl+click navigation (see gif in their readme), and https://github.com/simolus3/moor/ for Dart has similar features that integrate with VSCode (https://moor.simonbinder.eu/docs/using-sql/sql_ide/).
(...since everyone is linking their favorite libraries with a similar approach!)
Your queries should be hidden away from the developer - I mean obfuscation is never the goal but separation is... If your query is written inline in some business logic that section of code has poor tests and is unreliable. SQL is complex, I absolutely adore it but it's not simple - keep it isolated from the logic of your system and, if possible, use a layered architecture approach that allows the entire persistence layer to be detachable.
Looks like they do fancy stuff like template substitution &etc instead of just opening a text file and feeding it to the sql engine.
I was looking at it and thinking "why not just use jinga?" but then you wouldn't get the 'query manager' object and name spacing (and probably other features I've missed).
It actually seems a fairly good way to mix languages, I haven't looked at the code to see what they're really up to but I like the concept.
How heavy is that?
Users care.
Conditional WHERE seems unapproachable with this tool - which is one of my doubts about the need of a tool like this.
If you instead approach your SQL by having each query wrapped in a function in an isolated part of your code base you, as a company/team/whatever, can choose how much logic to let reside inside of those functions - building up conditional WHERE clauses is a very common thing to need to do.