Parameterised Raw SQL for bulk statements

[pavdwest]

I have looked at the docs and whilst this is useful, I can't quite figure out how to adapt this to a bulk use case. Specifically I'm trying to write some raw SQL to use Postgres' MERGE statement introduced in v15.

Specifically I'd like to know:

• If there is a function that I can use to escape values directly? I thought QueryString does that but it doesn't seem to escape quotes, at least in the way I'm using it - entirely possible I'm not understanding how to use it correctly.
• When using Model.raw(query, *vals) it seems to compile the query in some scope of the model - I can't quite figure out what's happening here but I get syntax errors when the SQL executes
• Is there a better recommended way to handle bulk inserts/updates/upserts?

Excuse the work in progress but here's my current function. It works but doesn't escape quotes, leaving it vulnerable to SQL injection:

@classmethod
    async def upsert_many_with_merge(cls, dtos: list[CreateDTOClassType]) -> AppBulkActionResultDTO:
        """Generates a statement of the following form:
        WITH updates (title, description, price) AS
        (
            VALUES
                ('New Product A', NULL, 9.99),
                ('AAAAAA', 'Updated description', NULL),
                ('Brand New Product', 'Fresh!', 19.99)
        )
        MERGE INTO product AS p
        USING updates AS u
        ON p.title = u.title
        WHEN MATCHED THEN
            UPDATE SET
                title = COALESCE(u.title, p.title),
                description = COALESCE(u.description, p.description),
                price = COALESCE(u.price, p.price)
        WHEN NOT MATCHED THEN
            INSERT (title, description, price)
            VALUES (u.*);
        """
        colnames = [c for c in cls._all_column_names() if c not in cls._internal_column_names()]
        colnames_clause = f"({",".join(colnames)})"

        primary_key_cols = cls._primary_key_column_names()
        primary_key_cols_clause = ",".join([f"t.{c} = u.{c}" for c in primary_key_cols])

        # Values
        value_placeholders = ",".join(["{}"] * len(colnames))
        value_placeholders = f"({value_placeholders})"
        values = [
            str(
                QueryString(
                    value_placeholders,
                    *[getattr(d, c) for c in colnames],
                )
            )for d in dtos
        ]
        values_clause = ",\n".join(values)

        q = f"""
        WITH updates {colnames_clause} AS
        (
            VALUES
            {values_clause}
        )
        MERGE INTO {cls._meta.tablename} AS t
        USING updates AS u
        ON {primary_key_cols_clause}
        WHEN MATCHED THEN
            UPDATE SET
                {", ".join([f"{c} = COALESCE(u.{c}, t.{c})" for c in colnames])}
        WHEN NOT MATCHED THEN
            INSERT {colnames_clause}
            VALUES (u.*)
        RETURNING id;
        """
        logger.warning(f"Query: {q}")
        res = await cls._meta.db.run_querystring(QueryString(q))

        return AppBulkActionResultDTO(
            ids=[res["id"] for res in res]
        )

[dantownsend]

This looks OK to me.

Is there a better recommended way to handle bulk inserts/updates/upserts?

You can try the on_conflict clause - I use this quite a lot for upserts:

https://piccolo-orm.readthedocs.io/en/latest/piccolo/query_clauses/on_conflict.html#on-conflict

The advantage of the Postgres MERGE command is it does insert/update/delete (i.e. can also do deletes). I think it would be cool if we could add it to Piccolo somehow.

If there is a function that I can use to escape values directly? I thought QueryString does that but it doesn't seem to escape quotes, at least in the way I'm using it - entirely possible I'm not understanding how to use it correctly.

QueryString doesn't do any escaping of the strings you pass it. It just lets you compose queries which contain parameters.

QueryString("select * from some_table where {}", some_other_querystring)

It doesn't do anything to the select * from some_table where bit if it contained quotes or something.

Does that make sense?