Database metadata for multi-schema databases

[leenyburger]

If a database has multiple schemas, how do I build a representation of the database?

Right now I'm using db.tables to get a list of tables in the database to compose an abbreviated schema for training an LLM.
Where

  def db
    DB[key] ||= Sequel.connect(@connection_params)
  end

Running this on a multi schema database, I get just the tables in the first(?) schema?

Full example:
Schema 1

Schema 2

db call

db.tables
=> [:ticket_flights, :boarding_passes, :aircrafts_data, :flights, :airports_data, :seats, :tickets, :bookings]

where db = #<Sequel::Postgres::Database: {:adapter=>"postgres", :host=>"localhost", :database=>"demo", :user=>"", :password=>"", :port=>"5432"}>

[JonathanFrias]

I think you have to set the schema search path https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH

[jeremyevans]

Database#tables on PostgreSQL also supports a :schema option to return all tables in a given schema (by default, it gives you tables in the schema search path): https://sequel.jeremyevans.net/rdoc-adapters/classes/Sequel/Postgres/DatabaseMethods.html#method-i-tables

[leenyburger]

Perfect thank you! I was reading an old discussion while trying to solve this and you said:

Changing the search_path for a connection at runtime is possible but a very bad idea if you don't exactly understand what you are doing.

I have a similar problem, where I'm trying to create SQL on a multi-tenant database with 50+ schemas and views.

I want to confirm taht the way to do this is just always give it the fully qualified path name
db['SELECT * FROM boeing_schema.employees]

[jeremyevans]

Correct, in your queries, if the table you are referencing is not in the schema search path (or not first in the schema search path), you should qualify it. You can use raw SQL for that, as you showed, but Sequel offers multiple ways to construct qualified identifiers if you don't want to use raw SQL: https://sequel.jeremyevans.net/rdoc/files/doc/sql_rdoc.html#label-Identifiers

[leenyburger]

Great thanks!