Show HN: Sqlbind a Python library to compose raw SQL

"Is ORM still an anti-pattern?" (2023)

> In truth the best way to do the data layer is to use stored procs with a generated binding at the application layer. This is absolutely safe from injection, and is wicked fast as well.

[Who must keep stored procedures in sync with migrations,]

https://news.ycombinator.com/item?id=36497613#36503998

If you use postgres, psycopg provides `cursor.mogrify(query, params)` which returns the actual query that will be executed . For example:

    cursor.mogrify(*queryset.query.sql_with_params())

Alternatively, you can set the log level of the `django.db.backends` logger to DEBUG to see all executed queries

I really wish Django would let you write raw queries, but wrap them in an object that still allows `.annotate()` and `.filter`, etc. I know you can do Model.objects.raw(), but I'm talking about a more complex query with nested joins, etc.

I understand why it doesn't allow that, and probably never can, but it would be nice when you need author OLAP queries but want to continue to use as much of the ORM as you can.

there Queryset.query but it's not obvious unless you read every line of the docs

> but I have no idea what the SQL will look like

Why don't you print QuerySet.query ?

> but I have no idea what the SQL will look like except in the simplest cases

that's really on you as Django makes it very easy to find the answer to that because Python makes it very easy to inspect objects at runtime

I don’t use Python so can’t be bothered to read the source but the general solution here is to make regular string interpolation fail.

e.g. You must use the Q mechanism and doesn’t work if you forget it.

Any string interpolation would open you up to sql injection without a lot of care. It generally recommended to used prepared statements.

I guess the q part is the query parameter for the prepared statement. I’d still be a bit antsy here given the ease of a mistake.

I just don’t get what it’s providing over using my db’s module. Usually the dialect is close but different enough to require changes to queries.

Proposed safe(r) API via decorated methods:

    QParams = sqlbind.Dialect.some_dialect

    @QParams
    def make_my_query(value1: str, value2: int):
        return f'SELECT * FROM table WHERE field1 = {value1} AND field2 > {value2}'

    data = connection.execute(*make_my_query(foo, bar))

Obviously this wouldn't work as-is, QParams would need to be modified to support this. The decorator would wrap the method, sanitising the args before they're even passed to the wrapped function.

Edit: actually, I might've misunderstood what sqlbind is doing internally, maybe this approach doesn't quite make sense

Edit 2: Like so: https://github.com/baverman/sqlbind/issues/1

It's a little difficult to parse through the README, but I believe placeholders and placeholder structure is being rendered in the SQL, not placeholder values.

    ps = f'SELECT * FROM table WHERE field1 = {q/value1} AND field2 > {q/value2}'
    # SELECT * FROM table WHERE field1 = ? AND field2 > ?

However, I would imagine that if any external input is passed through to this framework, then there might still be the possibility of SQL injection attacks passing through this framework and ending up in the prepared statement SQL.

It's my biggest concern as well. For real world application I tend to use out of line filters:

    filters = [q.field == value1, q.field2 > value2]
    sql = f'SELECT * FROM table {WHERE(*filters)}'

It's a little harder to misuse.

It's not obvious how to make SQL with nested queries with own WHERE in Norm. One of design goals of sqlbind was an ability to write complete high level structure of a big query. And to eliminate `if` statements as much as possible.

Norm:

    def get_users(cursor, user_ids, only_girls=False, minimum_age=0):
        s = (SELECT('first_name', 'age')
             .FROM('people')
             .WHERE('user_ids IN :user_ids')  # doesn't work in sqlite
             .bind(user_ids=user_ids))
        if only_girls:
            s = s.WHERE(gender='f')
        if minimum_age:
            s = (s.WHERE('age >= :minimum_age')
                 .bind(minimum_age=minimum_age))
        return cursor.run_query(s)

VS sqlbind:

    def get_users(cursor, user_ids, only_girls=False, minimum_age=0):
        q = QParams()
        filters = [
            q.user_ids.IN(user_ids), # renders into FALSE if user_ids are empty and supports SQlite.
            q.cond(only_girls, "gender = 'f'"),
            q.age >= truthy/minimum_age,
        ]
        sql = f'''\
            SELECT first_name, age
            FROM people
            {WHERE(*filters)}
        '''
        return cursor.execute(sql, q)

If you are allowed to create new schema objects then you can create views over the tables. Give the views a consistent name, but create the view to reference the actual table you want. Then update the view.

I came across this yesterday for golang: https://sqlc.dev which is somewhat like what you want, maybe.

Not sure it allows you to parameterize table names but the basic idea is codegen from sql queries so you are working with go code (autocompletion etc).

> I want to write a sql query I can deploy against test and prod, so I need to be able to parameterize table names

… are your “test" and “prod" different tables in the same database? :S

I wish there was a way to get around the syntax highlighting, checking, and completion issue. Pandas has a nice `query` method, which is hard to use for the same reason.

https://pandas.pydata.org/docs/reference/api/pandas.DataFram...

Unfortunately straightforward code leads to spaghetti mess in my domain: big reporting queries. API solves an issue of binding a value in exact place of raw SQL query.

It's not a lot of magic, but it's pretty opaque magic. The q object overloads the division operator and returns a ? and stores the "passed" value inside itself. The q object extends either a list or a dictionary to act as the set of values to pass to the parametrized query.

So in effect, the slash behavior is just a spicy way to append or set (depending on which dialect option you choose).

Yes, but to get the exact query with interpolated parameters that can be copy pasted into e.g. pgadmin for a nice explain analyze visualization is a bit more involved. As I could never remember it I always copy pasted that function in any project. But other than that minor complaint I have always been thoroughly impressed by both SQLAlchemy ORM and the Core query builder, and how they can be combined to have a best of both worlds situation. I have written some very complex queries that remained maintainable thanks to composition with cte's, sub queries and computed properties. Doing that in pure SQL would also have been possible, but using using SQLlachemy allowed me to name and reuse smaller components, avoiding the wall of text SQL statements.

If you have a lot of values to add to a query, they're physically distant from the place you're using them. With the unnamed version like you used, you're relying on your ability to count how many values are used so you get the list correct. With named parameters, you're adding another layer of indirection. Use a value more than once? If you change it or want to remove it, you'd better be sure you do it for each usage of the name in your query!