Question about adding database-specific functionality to Django ORM

Using Django
1

I’m in the process of trying to get some level of integration between the Django ORM and Sql Server Temporal Tables. By overriding a 3rd party SQL Server backend, I am now able to create (migrate) temporal tables. Next step is to implement .as_of() so that I can “time-travel” to a specific state of my models. Ultimately, this means appending something to the effect of
FOR SYSTEM_TIME AS OF %s' ;
to the select statements, and now my goal is to add this functionality as cleanly as possible. IE, I don’t want to simply make a function in a custom manager that runs cursor.execute – I’d like to be able to be able to use QuerySets and chaining if at all possible. From my understanding, this should be possible by somehow creating a new clause – I just can’t seem to nail down how that’s accomplished

I realize this is a very general question that requires understanding of many different parts of the ORM, but I was hoping someone could point me in the direction of some source code that might shed some light on the proper way to accomplish this.

2

Hi @techknowfile

Welcome to the forum!

Injecting an extra SQL clause is a bit tricky. You need to do it in the SQLCompiler or to rewrite the query after it’s made.

My extension library Django-MySQL uses the latter technique to add MySQL-specific query hints: https://django-mysql.readthedocs.io/en/latest/queryset_extensions.html#query-hints . But I think if I were to recreate it, I’d try the former technique. Additionally it should be doing the rewriting not through monkey-patching cursor.execute, but through database instrumentation.

Additionally, temporal tables are an SQL standard feature and supported also in at MariaDB and Oracle (at laest). If you’d like to find or create a ticket to add support for them in Django core, that would be exciting to see.

Thanks,

Adam

3

It made my morning to see that you were the one who replied! I probably had the Django-MySQL github open while I was initially writing this post. It was just a glance through, but I saw enough to understand that you were exploiting comments that would later be parsed in order to modify the running sql command (though I didn’t see how that last step gets “hooked” into the process).

I’m going to take another pass through your library to see if I can tease the rest of it apart, and I’ll probably take a swing at getting an idea of how I’d override the sql compiler.

This definitely sounds like it could be a useful feature to have built out for the community. Will consider it for my first post-college open source contribution if I can get some momentum on my own project. This ticket highlighted the first useful feature that I was hoping already existed for adding to the CREATE TABLE clause during migration (needed to add a flag to the meta class to mark the table as temporal). Due to nothing being implemented to allow for this, my current workaround to create a temporal table is very hacky.

4

At startup, it applies monkey patches that intercept and rewrite queries. As I said, the database instrumentation feature would be a cleaner implementation.

I found a way to make a database instrumentation hook that always applies (rather than as a context manager) in scout-apm, that I’ll copy over to Django-MySQL some time.

Yeah that involves a few moving parts, iirc you’ll need to first create a custom database backend through subclassing.

Idk what way you’ve taken, but the “least hacky” way would be:

  1. Create model
  2. Create migration to add it
  3. Run sqlmigrate and sqlmigrate --backwards on it to get forwards and reverse SQL for your database
  4. Put that SQL into a RunSQL operation’s sql and reverse_sql with the RunSQL’s state_operations set to contain the intiail CreateModel
  5. Change sql to include the clause for system versioning