Idea: Using Query Builder / ORM for database access like SQLAlchemy or Peewee

[petercsiba]

Is your feature request related to a problem? Please describe.
Thanks for putting this together!

One thing which annoys me about Supabase-Py is the lack of Query Builder / ORM which feels like makes the queries fragile OR you have to have a full CRUD test for it.

Describe the solution you'd like
Did you consider generating models out of the Postgres Database with say sqlacodegen?
I.e. instead of doing

supabase.table("countries").select("*").eq("country", "IL").execute()

you could with SQLAlchemy or Peewee ORMs do like:

Countries.get(country == "IL")

which catches query problems at build time.

Describe alternatives you've considered
Current solution feels good enough.

Additional context
I have started executing on this idea with Peewee, but it ain't yet fully production ready. Just wanted to give you an idea
https://github.com/petercsiba/supawee?tab=readme-ov-file#example-models

[AtticusZeller]

Sorry to have u waiting for few weeks! busy for the bottom of the year. i am very appreciated for your instructive suggestion.

One thing which annoys me about Supabase-Py is the lack of Query Builder / ORM which feels like makes the queries fragile OR you have to have a full CRUD test for it.

you are definitely right dude! i did find the discussion about ORM in the supabase-py issues, and looks no plan for integrating supabase with ORM style query, but i think it deserves a try. causes i genuinely prefer the object-oriented mode, and i thinks it should have a plan for it.

this idea with Peewee
the idea will help us reduce too much time on writing ORM model manually.sounds great!
tiangolo who created the FastAPI also build the ORM tools called sqlmodel , it looks like a good high-level abstraction for SQLAlchemy with pydantic so i think this one maybe suitable for fastapi, sqlacodegen suit for it i think

in short, the following works:

• SqlModel ORM instead of supabase.table("countries").select("*").eq("country", "IL").execute()
• sqlacodegen for sqlmodel

your ideas do help a lot!

[petercsiba]

NP thanks for the thoughtful response and research Atticuszz!

Good point +1, yes for a Fastapi library like yours sqlmodel w/ sqlacodegen feels like the best integration,
since it is most likely to match "vibes" of FastAPI.

(Just rambling lol)
Was thinking if adding sqlmodel to this template si too "opinionated",
but then this is a template, so from a projects lifespan perspective forking this template might be the best point-of-time to commit to a way to interact with the database.

That said, this is just an idea, thanks again for putting this repo together!

[AtticusZeller]

Hey, I really appreciate your thoughtful response about adding SQLModel to the template. You've made an excellent point about the timing - this early stage of template development is indeed the perfect moment to make such a foundational decision.

I've been pondering your input, and I think you're right that this is a good direction to explore. However, I'd like to suggest a slightly different approach that might offer even more flexibility:

What if we use Supabase primarily as a database UI tool, while handling core database operations through SQLModel? This way, we get the best of both worlds - Supabase's excellent admin interface, and SQLModel's versatility for actual data interactions.

SQLModel is particularly appealing because it supports both object-oriented and SQL-like query syntax. This flexibility could be crucial for a template meant to serve various project needs. Plus, with async support on their roadmap, it seems like a forward-thinking choice.

I believe this approach could provide a more universal and adaptable foundation, without tying us too closely to any single ecosystem.

[AtticusZeller]

it starts on #143 now ,coming soon together with self-host supabase and sqlmodel orm 😆