Adding that on top of Mike's approach, you may also want to create some custom functions via the @compiles decorator:
https://docs.sqlalchemy.org/en/14/core/compiler.html This would allow you to further customize the SQL emitted against Postgres vs SQLite as needed. For example, I have some examples dealing with the differences in date operations between those two backends in this file - https://github.com/aptise/peter_sslers/blob/main/peter_sslers/model/utils.py On Tuesday, October 19, 2021 at 5:20:13 PM UTC-4 Mike Bayer wrote: > > > On Tue, Oct 19, 2021, at 1:41 PM, Jonathan Brandmeyer wrote: > > We're supporting both Postgres and SQLite in our application. For the > most part, sticking close to ANSI has made this pretty seamless. However, > there have been occasions where we want to write either DDL or DML in a > dialect-specific way. > > For column data, we want to enable the use of JSON. However, when on > Postgres we'd like to use JSONB, but when on SQLite we'd use their JSON1 > extension. The generic JSON type provided by sqlalchemy defaults to the > postgres JSON type when on postgres. How can we get it to default to JSONB > instead? Using dialect-specific column types in the mapper is a > non-starter, because a mapped class (or Core table) may have to work with > both a Postgres connection and an SQLite connection in the same program. > We almost want to follow[1], except that I'm concerned that some of the > query syntax renderer might also be affected by switching to JSONB. > > > use with_variant: > > Column("data", JSON().with_variant(postgresql.JSONB(), "postgresql")) > > > https://docs.sqlalchemy.org/en/14/core/type_api.html?highlight=with_variant#sqlalchemy.types.TypeEngine.with_variant > > > > For DML there are a few cases where we'd like to use the > on_conflict_do_nothing syntax. However, it isn't available as generic > syntax, only dialect-specific syntax. It's not clear how query-generating > code can figure out which syntax to use given only a connection. > > > this was just asked yesterday and basically you can dispatch on > connection.engine.name for different database backends. Simple + more > generalized decorator approach illustrated at > https://github.com/sqlalchemy/sqlalchemy/discussions/7199#discussioncomment-1495790 > > > > SQA has some support for adding text() to larger queries, but this is a > modifier that doesn't clearly fit with the other generative methods that > accept text arguments. Is there a way to hack on some extra text into the > _post_values_clause that will be supported into the future? > > > not sure what you're looking to do here. > > > > > > Sincerely, > -- > Jonathan Brandmeyer > PlanetiQ > > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/CA%2BXzfko00UCaUn%2B8-KAfWWsqjbj6BG_kLCy0vDa-Sn%2BOteZa4g%40mail.gmail.com > > <https://groups.google.com/d/msgid/sqlalchemy/CA%2BXzfko00UCaUn%2B8-KAfWWsqjbj6BG_kLCy0vDa-Sn%2BOteZa4g%40mail.gmail.com?utm_medium=email&utm_source=footer> > . > > > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/c4edb89d-6e33-4bae-98f5-1d0abf5e4217n%40googlegroups.com.
