[FEATURE REQUEST] Analogous Functionality for `SELECT * FROM table` in `SQLTable`

[TheCedarPrince]

Hi all,

I was experimenting with FunSQL today - super easy to work with by the way! - and ran into this personally irritating situation. What do you all think?

Is your feature request related to a problem? Please explain.

Currently, I need to specify what are the exact columns I need to query when creating a SQLTable entity like so:

const person = SQLTable(:person, columns = [:person_id, :year_of_birth, :location_id])

I find having to define each column I need to be painful as sometimes I do not know all the columns for a given table easily when I write a query.

Describe the solution you'd like

Ideally, I would like to be able to provide nothing to the columns variable and have it default to select all columns in the query - analogous to SELECT * FROM person. I was wondering if the syntax could like something like this:

const person = SQLTable(:person)

and a dispatch or underlying functionality to look like:

SQLTable(name; schema = nothing, columns = nothing) =
    SQLTable(schema = schema, name = name, columns = columns)

Describe alternatives you've considered

I could have the data dictionary at hand but that is quite tedious for data analysis on the fly.

Additional context

N/A

[xitology]

A FunSQL query analogous to SELECT * FROM table is just From(table). FunSQL does not require Select to be a part of the query object. Unfortunately, it does not mean that you could omit the columns from SQLTable constructor. FunSQL really needs to know the columns of a table in order to construct valid SQL queries.

However, it's relatively easy to automatically generate SQLTable objects for an existing database. Here is an example how it could be done for PostgreSQL. The code uses ODBC.jl, but it should be trivial to port it to LibPQ.jl if that's what you are using. Other database engines could also be supported, but they will require a different introspection query.

using FunSQL: SQLTable, As, From, Fun, Get, Join, Order, Select, Where, render
using Tables
using ODBC

const pg_namespace =
    SQLTable(schema = :pg_catalog,
             name = :pg_namespace,
             columns = [:oid, :nspname])
const pg_class =
    SQLTable(schema = :pg_catalog,
             name = :pg_class,
             columns = [:oid, :relname, :relnamespace, :relkind])
const pg_attribute =
    SQLTable(schema = :pg_catalog,
             name = :pg_attribute,
             columns = [:attrelid, :attname, :attnum, :attisdropped])

Introspect(; schema = :public) =
    From(pg_class) |>
    Where(Fun.in(Get.relkind, "r", "v")) |>
    Where(Fun.has_table_privilege(Get.oid, "SELECT")) |>
    Join(From(pg_namespace) |>
         Where(Get.nspname .== String(schema)) |>
         As(:nsp),
         on = Get.relnamespace .== Get.nsp.oid) |>
    Join(From(pg_attribute) |>
         Where(Fun.and(Get.attnum .> 0, Fun.not(Get.attisdropped))) |>
         As(:att),
         on = Get.oid .== Get.att.attrelid) |>
    Order(Get.nsp.nspname, Get.relname, Get.att.attnum) |>
    Select(Get.nsp.nspname, Get.relname, Get.att.attname)

function introspect(conn; schema = :public)
    q = Introspect(schema = schema)
    sql = render(q, dialect = :postgresql)
    ts = Pair{Symbol, SQLTable}[]
    s = n = nothing
    cs = Symbol[]
    rows = Tables.rows(DBInterface.execute(conn, sql))
    for (nspname, relname, attname) in rows
        s′ = Symbol(nspname)
        n′ = Symbol(relname)
        c′ = Symbol(attname)
        if s === s′ && n === n′
            push!(cs, c′)
        else
            if s !== nothing
                t = SQLTable(schema = s, name = n, columns = cs)
                push!(ts, n => t)
            end
            s = s′
            n = n′
            cs = [c′]
        end
    end
    if !isempty(cs)
        t = SQLTable(schema = s, name = n, columns = cs)
        push!(ts, n => t)
    end
    return NamedTuple(ts)
end

To use this code, you first need to create a connection object, then call the introspect() function, e.g.,

const conn = ODBC.Connection(ENV["DSN"])
const db = introspect(conn)

The function returns a named tuple, where each attribute is a SQLTable object generated from the database metadata. You could use it to build FunSQL queries like this:

q = From(db.person) |>
    Select(Get.person_id, Get.year_of_birth)

sql = render(q, dialect = :postgresql)

res = DBInterface.execute(conn, sql)
#...

Ideally, introspect() should be a part of FunSQL, but it's hard to integrate because the implementation needs to execute a database query, and there is no engine-independent way to do it in Julia. We'd like to avoid adding a dependency on every database library in existence.

[clarkevans]

It's important to have a clean "out of the box" experience. We could make modules for each database which included the SQL needed for introspection, and perhaps a function that'd build the right catalogue object given result-sets from those introspection queries. However, making the database connection itself is still a bit uneasy: some databases have multiple Julia client libraries, a native wrapper or ODBC, for example. I suppose we could have a monorepo including several packages which combine the SQL introspection logic together with the database connection library?

[xitology]

A possible FunSQL interface for introspect() could be

conn = ...
schema = FunSQL.introspect(dialect = :postgresql) do sql, params
    return DBInterface.execute(conn, sql, params)
end

or

conn = ...
schema = FunSQL.introspect((sql, params) -> DBInterface.execute(conn, sql, params), dialect = :postgresql)

[TheCedarPrince]

Hey @xitology and @clarkevans - thanks for the help and thoughts! Yea, I was using LibPQ.jl and I ported over what you did to work with LibPQ as otherwise, your proposed idea for introspect works perfectly and is rather generic @xitology!

With what @clarkevans is saying, I wonder if it makes sense to provide the connection interface options yourself? Like you said, there are many connection libraries and supporting them all might be tough. I like the idea of just sticking with ODBC.jl for my connection interface or any connection type that satisfies the DBInterface interface.

[TheCedarPrince]

Just a bump here, but the more I have worked with FunSQL, I can completely say that having the introspect functionality is immensely important. In my experience it leads to compact code and less cognitive load having it. Without having introspect available, my code looks much more verbose and complicated - and I have to always be conscious of knowing what all the columns are all the time.

Thanks all!

[xitology]

As a stopgap, I added introspection snippets to Examples.

[TheCedarPrince]

That is perfect as a stopgap! Thanks @xitology ! 😄

[xitology]

Database introspection and DBInterface compatibility are implemented in https://github.com/MechanicalRabbit/FunSQL.jl/pull/23.