How to see all tables in Ecto?

Questions / Help
,
1

I launched my Phoenix app solely from the command line like this:

iex -S mix

I want to see a list of tables for the chosen database.

In Postgres it would be:

\dt

I assume there is an Ecto command like:

Ecto.showAllTables

or something.

Anyone know what it is?

Thank you

1 Like
2

There is no such Ecto function. Pg table names are stored in table, pg_schema or something like that,and you can use Ecto Repo to get that out of the db.

3

This works, though may need some tweaking depending on what you want:

{:ok, %{rows: rows}} = MyApp.Repo.query("SELECT * FROM information_schema.tables;")
for [_, "public", table | _] <- rows, do: table
7 Likes
4

I haven’t tried it but got this from chatgpt 3.5, looks pretty close :slight_smile:

YourApp.Repo.query!(
  from t in "information_schema.tables",
  where: t.table_schema == "public" and t.table_type == "BASE TABLE",
  select: t.table_name
).rows
|> Enum.map(&Map.get(&1, "table_name"))
|> IO.inspect()
7 Likes
5

Oh right, you can just use SQL to map and filter :upside_down_face:

6

EDIT

{:ok, %{rows: rows}} = MyApp.Repo.query("SELECT * FROM information_schema.tables;")
for [_, "public", table | _] <- rows, do: table

Thanks, the ideal I had was to see the field names in the tables too (Like postgres renders) . I get the idea that SQL commands can be run from Ecto, so that solves that.

Curious what would keep something like this from being added to the API as a simple one liner?
I feel like the overcomplexity has to be either inherently needed by design - or there is something seriously wrong and it should be easier.

A Phoenix app already uses a chosen database so I shouldn’t need to select it further. Phoenix already has access to the data so it knows the tables etc.

Ideally I want to be able to pop open any Phoenix App and know the shape of the table data without writing complex queries or mulling through the codebase.

Even better would be something like:

mix phx.show_tables

I feel like if this doesn’t exist - it should exist.

7

They are two commands. So hit return after the first command.

iex(1)> {:ok, %{rows: rows}} = MyApp.Repo.query("SELECT * FROM information_schema.tables;")
....
iex(2)> for [_, "public", table | _] <- rows, do: table
["schema_migrations", ...]
2 Likes
8

Ecto isn’t an ORM and thus does no form of database introspection by design. It’s more of a query builder and dispatcher, simply connecting to configured database, sending queries to it, and parsing the results. If you want to inspect tables it makes far more sense to do so in a database browsing application or using your db’s CLI client. These give lots of tools for learning about how the database is structured.

Simply listing tables, however, doesn’t even necessarily give you good information about how a Phoenix project is structured as multiple schemas can point to a single table. I think what you are after might be an entity relationship diagram. I found ecto_erd. I have not tried it myself but would be worth checking out!

6 Likes
9

This sounds like it should exist less at the Ecto level and more through ODBC, as this functionality is exposed through the driver’s SQLTables function: SQLTables Function - ODBC API Reference | Microsoft Learn

10

TBH most of the time I just use psql for this anyways; an Ecto replacement would need a lot of effort to reach a similar feature-set.

One thing I miss a little from Rails-land is rails dbconsole, which looks up the DB connection parameters in the Rails config and passes them along to the psql / mysql / sqlite3 CLI.

12 Likes
11

Also, for all its annoyances, schema.rb was a net positive.

1 Like
12

structure.sql from mix ecto.dump is arguably better for this use case because you see exactly what the database has.

2 Likes
13

Yeah, you can configure Rails to spit out SQL too, but I prefer the symmetry between schema.rb and migrations. And I like that schema.rb is produced by default each time you run a migration (notwithstanding the git/merge/conflict issues the different ordering produces).

14

Ahoy! We built a library to do this kind of thing at Vetspire called Endo.

We’re a fluent API over Postgres’ information schema as described in the comments above, but aim to support other Ecto adapters too.

For your use case you could probably just do:

Endo.list_tables(MyApp.Repo)

But you can do other things like:

Endo.list_tables(MyApp.Repo, with_column: "org_id", without_index: "org_id")
2 Likes
15

I haven’t used your library yet but I have a question. Was it as difficult to create as some of the comments in this thread infer? How difficult/easy was it?

16

Not to disagree with anyone on this thread but I don’t think it was very difficult.

Endo stitches together a bunch of data from various sources in Postgres and normalizes it to something database agnostic (for when we get other adapters).

The hardest part was figuring out a good UX for the fluent API and to dynamically generate queries from it.

I personally wouldn’t roll my own now that Endo exists (though as the author, of course id say that!). Feel free to check out our docs: Endo — Endo v0.1.17

If you do want to roll your own though, feel free to look at the source code under Endo.Adapters.Postgres (https://github.com/Vetspire/endo/blob/main/lib/endo/adapters/postgres.ex)

Happy to chat if you want to know anything more specific!

2 Likes
17

I don’t see people saying it is difficult, rather that it’s a poor fit for Ecto’s goals and other functionality.

6 Likes
18

Hey I just fired this up, it looks great. I’ll fold it into my work template moving forward.
Thank again

1 Like