The syntax follows <a href="https://github.com/krisajenkins/yesql/" rel="nofollow">https://github.com/krisajenkins/yesql/</a> 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 :)<p>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.
Seems similar in inspiration to <a href="https://pugsql.org/" rel="nofollow">https://pugsql.org/</a> which is a python reimplementation of hugSQL, more or less.<p>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.
Go has something similar to this <a href="https://github.com/kyleconroy/sqlc" rel="nofollow">https://github.com/kyleconroy/sqlc</a>
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:<p><pre><code> 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"), ...]
</code></pre>
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.<p><pre><code> -- name: get-user-by-username^
</code></pre>
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).<p>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.
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.
SQL should have been in .sql files from the start. SQL in string literals is an industry-wide decades long aberration. I fixed this problem for C# with QueryFirst. I'm delighted to see all the support here the approach, which is clearly the way forward for data access.<p><a href="https://marketplace.visualstudio.com/items?itemName=bbsimonbb.QueryFirst&ssr=false#overview" rel="nofollow">https://marketplace.visualstudio.com/items?itemName=bbsimonb...</a>
I really like this approach because as you said, it gets really messy to do string replacements inline in a python file.<p>I'm curious if any people here are comfortable using stored procedures as an alternative to this.<p>Stored procs give you the benefit of your sql being easy to change in a sql editor, but you also get query planner caching of the results which means it will likely execute faster than string replacement inline sql.
I'm relatively new to Python and I was looking for something like this. I do wonder if there is a well-known Python equivalent of Dapper: <a href="https://github.com/StackExchange/Dapper" rel="nofollow">https://github.com/StackExchange/Dapper</a>
Looks like a cool idea. I wonder if I can make it to work easily with this other lovely python library <a href="https://github.com/pudo/dataset" rel="nofollow">https://github.com/pudo/dataset</a>
I'm a fan of using raw sql like this but curious if anyone has solved deduplicating common statements and dynamic queries in a good way.<p>For example:<p>1) Imagine lots of queries having to show the results in the context of a user and therefore use the same JOIN and WHERE clause all over. Not being DRY, this breaks down when having to change the clause at all.<p>2) Imagine a reporting page that allows for filtering and ordering by different columns and therefore need some way to compose the final sql.
I've always wanted to build a larger app based on this approach. I quite like Massive, and 5 years (!) ago I wrote <a href="https://gist.github.com/grncdr/4555208#file-sss-py" rel="nofollow">https://gist.github.com/grncdr/4555208#file-sss-py</a> as a one-off response to a tweet from Ted Dziuba (is he still around?)
Nice approach with annotations in the comments.<p>I wrote something a little more tounge in cheek a while ago -- I modified SQL syntax to include functions with arguments<p><a href="https://github.com/scientifichackers/sql2code" rel="nofollow">https://github.com/scientifichackers/sql2code</a>
Anyone who is attracted to this approach should look into stored procedures instead: <a href="https://www.postgresqltutorial.com/postgresql-create-procedure/" rel="nofollow">https://www.postgresqltutorial.com/postgresql-create-procedu...</a>
I like this idea a lot actually. I've been pondering of doing the same thing in Go.<p>There are some edges though... for example what if you want to do further composition based on if/else clause.
Funny to see on HN just started building a version of this that actually generates functions from sql queries so you get all the IDE goodness and sightly smaller runtime overhead
This could go one step further by implementing an import hook, then you could do import users; users.get_all_users(conn)<p>It's something I've been meaning to try with other languages.