feat: Global default sql dialect

[KronosTheLate]

Is your feature request related to a problem?

No response

What is the motivation behind your request?

No response

Describe the solution you'd like

If this option exists please correct me. I looked for it a lot, and found nothing.

There is no option to set a global sql dialect. Having such an option would be very useful, as it would allow switching a backend without adding the dialect keyword argument to each call to the sql function. This would allow even more seamless swapping of backends, and also allow a low-friction option for those that want to use a specific dialect that differs from the dialect of their backend.

What version of ibis are you running?

None

What backend(s) are you using, if any?

No response

Code of Conduct

• I agree to follow this project's Code of Conduct

[cpcloud]

Are you asking for the ability to interpret any and all dialect arguments (and effectively therefore all SQL strings) as a single configurable dialect?

So you could do something like this:

con = ibis.postgres.connect()
ibis.sql.dialect = "mysql"
con.sql("SELECT `b` FROM `t`")  # <- automatically transpiles mysql -> postgres

[KronosTheLate]

Oh, that seems like exactly what I am looking for! Well in that case, I spent a good 10-15 minuts looking through the documentation without finding this option.

I just searched the docs for "dialect", and I can still not find this option, even when I know what I am looking for. It surely seems like mention of this option is appropriate under sql-query-execution, right? In any case, this is then just a documentation issue, and not a feature request.

Also, it does not seem to work for me:

󰍲 dennis.bal  ~   v3.13.3   15:48 
 pip show ibis-framework
Name: ibis-framework
Version: 10.5.0
Summary: The portable Python dataframe library
Home-page: https://ibis-project.org
Author:
Author-email: Ibis Maintainers <maintainers@ibis-project.org>
License: Apache-2.0
Location: C:\Users\dennis.bal\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.13_qbz5n2kfra8p0\LocalCache\local-packages\Python313\site-packages
Requires: atpublic, parsy, python-dateutil, sqlglot, toolz, typing-extensions, tzdata
Required-by:

󰍲 dennis.bal  ~   v3.13.3   15:48 
 python
Python 3.13.3 (tags/v3.13.3:6280bb5, Apr  8 2025, 14:47:33) [MSC v.1943 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import ibis
>>> ibis.sql.dialect = "duckdb"
Traceback (most recent call last):
  File "<python-input-1>", line 1, in <module>
    ibis.sql.dialect = "duckdb"
    ^^^^^^^^
  File "C:\Users\dennis.bal\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.13_qbz5n2kfra8p0\LocalCache\local-packages\Python313\site-packages\ibis\__init__.py", line 142, in __getattr__
    return load_backend(name)
  File "C:\Users\dennis.bal\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.13_qbz5n2kfra8p0\LocalCache\local-packages\Python313\site-packages\ibis\__init__.py", line 66, in load_backend
    raise AttributeError(msg)
AttributeError: module 'ibis' has no attribute 'sql'.

[cpcloud]

Sorry, that was a question about a hypothetical API, not something that currently exists!

[KronosTheLate]

Oh, right! Well in that case yes, that looks exactly like what I am looking for. If a script relies on a lot of SQL, I in fact feel like this is key for true portability 🚀

[KronosTheLate]

In fact, I can see that there already is a global sql dialect config here:

class SQL(Config):
    """SQL-related options.

    Attributes
    ----------
    fuse_selects : bool
        Whether to fuse consecutive select queries into a single query where
        possible.
    default_limit : int | None
        Number of rows to be retrieved for a table expression without an
        explicit limit. [](`None`) means no limit.
    default_dialect : str
        Dialect to use for printing SQL when the backend cannot be determined.

    """

    fuse_selects: bool = True
    default_limit: Optional[PosInt] = None
    default_dialect: str = "duckdb"

In regard to your previous question, I changed my mind when looking into the existing code: I think it would be confusing to introduce config.sql.dialect, given that sql.default_dialect exists. So my wish would be for this default_dialect to (a) determine the default execution dialect, (b) determine the default printing dialect (and not just the fallback), and (c) be set to None by default. The current meaning of config.sql.default_backend could be moved to a new config option config.sql.fallback_printing_dialect, which IMO is a lot less confusing. Is there appetite among the core developers for such a breaking implementation?

Given that the setting as it is only changes the fallback printing dialect, I guess it is not actually used in most codebases, which would mean that the breakage is not very significant 🤞 Also, I suppose that syntax errors will be raised rather quickly in the truly breaking case (where a user has set the printing backend explicitly, and written sql queries that use an sql dialect different from that), making it a loud and visible breaking change. So all in all, it might not be that bad...

Followup-question: Would an implementation of this be as simple as adding something like the following to the sql function on this line?

if dialect is None:
    dialect = config.sql.default_dialect

Just for the new implementation that is - there would ofc also be related cleanup work if my suggestion about redefining the meaning of the config options is taken seriously.

Alternatively, a new config could be introduced in a non-breaking version that would determine the default excecution and printing dialect. And then, for the next breaking release, that new config could move to default_dialect, and the current default_dialect could move to a new config option. But that is all up to the core devs, and how you/they prefer to make breaking changes. I am biased towards "move fast and breaking things", and making breaking changes if they are agreed upon to be good, without too much reservation. After all, that is what we have versioning for!