feat: compile datatypes to native types

[NickCrews]

Is your feature request related to a problem?

I am writing my own alter_table function to adjust the types of a physical table. I want to be able to hand it an ibis.DataType and then generate the proper SQL that can be passed to connection.raw_sql.

For this to work, I need to be able to convert the ibis String datatype to eg VARCHAR for duckb.

What is the motivation behind your request?

I want a more ibis-y way of writing my database migrations.

Describe the solution you'd like

I would hope that ibis.to_sql(ibis.dtype("string"), dialect=...) would work.

Here is my current workaround:

import re
from ibis.expr import datatypes as dt


def compile_dtype(type: str | dt.DataType, *, dialect: str | None = None) -> str:
    """Compile an ibis.DataType to SQL."""
    # need to ensure nullable so that we can make a literal NULL of this type.
    # No backends that I know of treat nullable and non-nullable types
    # differently, they only care about this during a column definition.
    t = ibis.dtype(type).copy(nullable=True)
    e = ibis.null(t).name("foobar")
    raw = ibis.to_sql(e, dialect=dialect)
    pattern = re.compile(r"^SELECT\s+CAST\(NULL AS (.*)\) AS .+$")
    match = pattern.match(raw)
    assert match, f"Could not match {raw} to SQL type"
    return match.group(1)


compile_dtype("!struct<foo: int64, bar: string>", dialect="duckdb")
compile_dtype("struct<foo: int64, bar: string>", dialect="postgres")

What version of ibis are you running?

main

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

duckdb, as well as postgres, and I want the same function to work for both.

Code of Conduct

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

[gforsyth]

There's machinery in ibis.backends.sql.datatypes for converting to sqlglot DataType and from there you can .sql() to get the rendered type.

For multiple backends, you'll want to use the appropriate <backend>Type class, since there are backend-specific conversion methods to try to provide consistent behavior.

[ins] In [20]: from ibis.backends.sql.datatypes import DuckDBType

[ins] In [21]: s = ibis.dtype('string')

[ins] In [22]: DuckDBType.from_ibis(s).sql()
Out[22]: 'VARCHAR'

[NickCrews]

That looks great. Does the public API I propose of ibis.to_sql() look good to you? Do you know how to map from dialect="duckdb" to the DuckDBType?

EDIT: see #11100, I figured it out.