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+unsubscr...@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/de4863ac-73d3-4d1b-8f67-e070e10e62fb%40www.fastmail.com.