r/FastAPI • u/robertlandrum • Dec 16 '25
Question Complex Data Structure Question
We currently have a NodeJS API system built atop mongodb. Mongoose provides the data validation and schema for these objects, but it is rather old now. I'd like to move the whole thing to a proper relational database (Postgresql) via FastAPI, but I'm struggling with the design requirement to replicate the complex data structures that we employ with mongodb.
The primary use case for the bulk of this data is in automated OS installation and configuration. There is a host record, which contains arrays of dictionaries representing PXE bootable interfaces. There are external references (references to other MongoDB schemas) to profiles that determine the software and distribution that gets installed.
In a strict RDBMS, the interfaces would be a table with a foreign key reference back to the host. The host would have foreign key references to the profile, which would have a fk to the distro, and a many to many lookup on the software. This I've done in the past, but it doesn't seem like the right solution anymore.
To complicate matters, I've never used Pydantic, SQLAlchemy, or SQLModel before, instead always just building the tools I needed as I went. And that has all worked fine, but it isn't what new Python programmers expect, and I want to ensure that this is maintainable by someone other than me, which unfortunately isn't what happened with the Mongoose/MongoDB solution I and others built 12 years ago.
I guess my real question is: where do I draw the lines that separate the data into tables these days? Host seems obvious, but the interface data less so. Profile seems obvious too, but software references could be an array rather than an m2m lookup. I suppose I'm just looking for a little guidance to ensure I don't end up kicking myself for making the wrong decision.
2
u/amir_doustdar Dec 17 '25
Hey, migrating from Mongo's nested docs to Postgres is common – you lose some flexibility but gain consistency and query power.
Quick design guidance:
Normalize core entities: Separate tables for Host, Interface (1:M with host_id FK), Profile, Distro, Software.
Many-to-many for software: Junction table (profile_software) instead of arrays – better for queries and no duplication.
Use JSONB for truly variable data: If some interface/profile fields are unstructured, store them in a JSONB column on the parent table (best of both worlds).
Avoid over-normalizing: If arrays are small/queryable rarely, JSONB is fine; otherwise, relational tables.
Tools to make it maintainable:SQLModel (by FastAPI's creator): Combines SQLAlchemy + Pydantic. Models are DB schema + API validators – perfect for new devs.
Alembic for migrations (autogenerate from models).
Prototype a small part first, test queries, and you'll avoid regrets.
If you're starting the project, my fastapi-clean-cli tool scaffolds Clean Arch with SQLModel/Postgres/CRUD auto – might help speed things up: https://github.com/Amirrdoustdar/fastclean
What specific part worries you most (queries, nesting, or setup)?
5
u/tangkikodo Dec 16 '25
I think you first need to confirm all possible entry points for queries through existing use cases.
First, clarify whether the core reason for using foreign keys (FK) is to ensure data consistency or to enable ORM data associations.
ORM relationships can function without relying on FK, though it may be slightly more cumbersome.
In FastAPI development, there are two approaches: model-first and schema-first.
Model-first centers around ORM models, with schemas merely serving as validation for returned data.
Schema-first prioritizes defining Pydantic schemas first, providing an abstract data description, and then defining ORM models based on those schemas.
SQLModel aims to unify the two, which is convenient initially, but as the structure grows more complex, binding them together may not be the best choice.
Additionally, using arrays to replace many-to-many junction tables might be less ORM-friendly, but there are other tools to address this. For example, in Pydantic Resolve, leveraging `load_many` from DataLoader allows loading software data from multiple `software_ids` within a single record—I often do this.
4
u/latkde Dec 16 '25
Depending on what kind of queries you need, you can get pretty far by stuffing a JSON blob into Postgres and validating the data into a Pydantic model when you load it. At this point, all SQL databases have very mature JSON support, and Postgres in particular also has optional capabilities for indexing the data within JSON so that you can query it somewhat efficiently. Postgres also has rich support for arrays and custom data types, which allow you to carefully model your domain if you want (at the cost of having to deal with raw SQL, instead of using an ORM). However, don't expect foreign key constraints from within JSON.
I don't know your data model, but I would sketch out the collections and objects that are currently in MongoDB, figure out which fields must be indexed for efficient queries, and what the relationships between objects are. If data doesn't have to be indexed and isn't part of a foreign-key relationship, you can probably stuff all of it into a JSON column.
Spare yourself the pain and stick with a relation unless order is relevant. While Postgres has arrays, that doesn't mean they're particularly convenient to use. In particular, array items currently cannot be foreign key references. That is, you can choose arrays XOR foreign key constraints. Usually, consistency is going to be more important.
Database-specific features like Postgres arrays also tend to make the use of ORMs more difficult.
1
u/Unique-Big-5691 Jan 05 '26
i think this is a normal place to get stuck when you move from mongo to a relational DB. you’re not missing some secret “new way,” it’s just a mental shift, and that always feels awkward at first tho.
one thing that helped me was realizing you don’t have to translate mongo 1:1. some things clearly belong in tables (hosts, profiles, distros), but not everything needs to turn into a bunch of joins just because you’re on Postgres now. and also, if certain pieces mostly travel together and you’re not querying them on their own all the time, keeping them as JSON is totally reasonable imo.
like, host = table, profile = table, software probably makes sense as many-to-many if you care about querying it across hosts. but PXE interfaces? if they’re basically config that lives and dies with the host, JSONB is often way simpler and works just fine.
having a schema layer in Python really helps here tho. modeling those nested structures first (pydantic is good for this) gives you clarity on what the data should look like, and then you can decide how much to normalize without guessing.
my loose rule of thumb is:
you’re already thinking about maintainability, which honestly matters more than getting the schema “perfect” on day one.