Auto-discovering table columns/automate schema creation

Questions / Help
1

I wonder if there is a proper way to create Ecto schemas based on the existing DB tables in a Firebird database.

Rails ActiverRecord makes this kind of auto-discovery possible, we can juts override table name or column when needed directly in a model class.

Tables of a legacy app are too huge with more than 50 columns each or even more.
Typing in all the columns and create a schema one-by-one looks a bit tedious task.

Any ideas about this? Thank you!

2

That is what I’m trying to solve with GitHub - elixir-dbvisor/sql: Brings an extensible SQL parser and sigil to Elixir, confidently write SQL with automatic parameterized queries.

2 Likes
3

Ah, nice, I’ll take a look. The point is that in the Rails world the existing adapter is too old and and I could not even make it work with Ruby 3.4 and Rails 8 :frowning: hoping to just create schema files with Ruby :slight_smile: .

4

Yeah, we’re not quite there with abstractions like schemas as we’re focusing on the fundamentals, but you can use it with Ecto today and it will be a pinch faster then Repo.query.

5

Does this help: GitHub - solnic/drops_relation: 🔋-included relation abstraction on top of Ecto with schema inference and composable query API + more ✨

2 Likes
6

Awesome, thank you for the link, I’ll take a try :clap:.
Oh, Peter Solnica is the author of the library, it promises to be something very promising then.

7

Well, after playing a bit with provided examples, none of them really worked s expected. The impression is that the main ida was rather provide something similar to the (Rails) ActiveRecord pattern, where you call the entity methods directly on the entity (ActiveRecord Model) class instead of calling the Repo’s (context) one:

# Use it like Ecto.Repo
{:ok, user} = MyApp.Users.insert(%{name: “John”, email: “john@example.com”})

user = MyApp.Users.get(1)
users = MyApp.Users.all()`

I’m using the Firebird DB and tried to call the `schema/0` function both on an existing module which already contains all the long list of available fields, and on a new one which has nothing but the using of the Drops.Relation module:


defmodule IcTrace.Core.Payment do

@moduledocmoduledoc false

use Drops.Relation, otp_app: :ic_trace

schema(“t_payment”, infer: true)

end

Here is the simplest call to check the schema:
```
Interactive Elixir (1.18.2) - press Ctrl+C to exit (type h() ENTER for help)
iex(1)> IcTrace.Core.Payment.schema()
%Drops.Relation.Schema{
source: :t_payment,
fields: ,
primary_key: nil,
foreign_keys: ,
indices:
}
```
I expected all the available `t_payment` columns to be listed, but that’s not the case here.
I might be doing something wrong, will ask Peter some questions directly then.

8

I have done a similar thing (not with Firebird, but with SQLite and PostgreSQL) three times this year just by dumping the schema to an LLM (Gemini Pro). Have you tried that? Agents are pretty good at this kind of stuff.

9

Well, yeah, being too tedious task (the DB schema has more than 300 tables), It was actually put in stand-by mode.
The initial implementation I started was based on creating a mix task which parses the current schema and extracts columns, data types, etc. Sure it is enough verbose and complicated.