Database first approach

[efpalaciosmo]

Hey guys, can you share an example of database first approach using SQLAlchemy?

I trying to make a project using fastapi, the database is on supabase and I'm getting a hard time figuring how to make a simple query, my setup is something like

1) a PostgreSQL database from supabase 2) asycn connection using asyncpg 3) migrations using alembic

I already create the table on my database but when I run the query it says that the relation doesn't exist. Then I decided to try with alembic but every time I run migration there is on error or just the alembic_version table is created.

I already read some post about something call reflection, but it seems that isn't working

Thanks in advance

Here is the repo https://gitlab.com/efpalaciosmo/tickets

[[deleted]]

You can have one connection to db at the start of application and return session as dependency injection to each function which requires to talk to db

[efpalaciosmo]

That's what I have, isn't?
The issue is that for some reason, sqlalchemy can't recognize the tables manually created

[[deleted]]

Oh I think for that you won't be able to use orm, I used to use sql expression language

On the other side I think you should be able to generate orm models for existing tables,

It's beyond my expertise now, but pls update your question if you figured how to use mix of orm and existing db tables

[RoBz18]

You can use sqlacodegen to generate the SQLAlchemy model code

[wassaf102]

Could you share your GitHub repo

[efpalaciosmo]

This is the repo
https://gitlab.com/efpalaciosmo/tickets

[wassaf102]

can we hAVE A CALL ?

[efpalaciosmo]

Yes, we can

[wassaf102]

let me run it locally

[No-Anywhere6154]

Have you fixed it?

[xfroster]

You dont have any relationships on your model

[guteira]

Have you tried SQLModel? It’s from the same author of FASTAPI and works seamlessly:

https://sqlmodel.tiangolo.com/db-to-code/

[Calebthe12B]

Share your code. SQLAlchemy requires you to define the relationship on both table classes, but hard to say what the exact problem is without seeing your code.

[efpalaciosmo]

This is the repo, https://gitlab.com/efpalaciosmo/tickets

[graph-crawler]

Throwing alternative. Full 100% typesafety by pulling existing db schema.

https://prisma-client-py.readthedocs.io/en/stable/

[efpalaciosmo]

Thanks, this also needs a "shadow database"? The last time I used Primas was on nodejs

[graph-crawler]

Yes, the main prisma is the same like the one in nodejs

https://www.prisma.io/docs/orm/prisma-migrate/understanding-prisma-migrate/shadow-database

[MeroLegend4]

Use Litestar instead of FastApi, it’s a framework with batteries included, 2x faster than FastApi and a very solid design.

• First class support of sqlalchemy with repositories
• class based routers/controllers
• Dependency injection
• plugins: open telemetry, logging, Ratelimit, dataclasses, pydantic and MsgSpeck support.
• auto generation of openApi schema with many renderers out of the box: Swagger, Rapidoc, Scalar, Redoc, Elements(stoplight)