#sqlmodel — Public Fediverse posts

I need to learn more about how databases work and the best way to setup relationships in SQLModel. I was working on Rack Root tonight and am still running into an issue with some circular dependencies I'm having between various tables. I need these relationships to enhance the data/utility of the app, but might need to rethink how I'm actually putting them together.

The other fun part of this is that I'm learning that the way I test the app and the way I run the app isn't always the same. The tests will run fine the first time against my test Postgres container, but subsequent runs don't work because of how I tell the test DB to drop all data before running the tests. It's complaining about the relationships, but really I think I need to tell it to either cascade delete or just ignore something.

#homelab #rackroot #programming #webdev #fastapi #sqlmodel #databases #alwayslearning

I am probably holding them wrong!

This time I tried writing a web app not in #php but #fastapi + #sqlmodel (python) and #vue3 ( javascript). It kind of works well but refactoring is a nightmare.

In the beginning, it felt like I was having the best of both worlds -- Python and Vue3. Dev speed was good. Now maintaining and refactoring make me feel like I am having the worst of both worlds.

As a single dev, I should have stayed with #php!

New blog post is up!

How I handled tracking DHCP ranges in Rack Root, some things I ran into along the way, and how I improved my pytest output.

https://medium.com/@a.j.longchamps/rack-root-devlog-tracking-dhcp-ranges-c8272001fe3a

#pytest #homelab #rackroot #devlog #blogging #fastapi #backend #databases #sqlmodel #programming #blogging

Tonight's dev log update for Rack Root

I finally finished my refactor from sqlalchemy with sqlite over to SQLModel with PostgreSQL and refactored much of my code. I had a mixture of snake_cake and camelCase which has since been fixed to be all camelCase.

I adjusted many of my imports, classes, database tables, database foreign key relationships, and testing to fit the new design. I also had to adjust some of my tests because my IDs were off by one.

This database focused refactor also gave me all the structure and space I needed to build out the basic IPAM features. When a new network is added, all the associated IP records are created, and marked as 'Available'. If a gateway is set, that gets changed to 'Gateway' and when I implement DHCP ranges, that will be set to 'DHCP'.

I still have some things to do such as make sure the IP records cascade delete when a network is deleted, and fun stuff like that.

Now that the backend is (re)designed, I can get back to the front end side of things.

If you're curious to check out my code for this refactor, it's all up on my GitHub here:

https://github.com/alongchamps/rack-root/commit/0fc99dfcf10bf3ab6a5dfdc348894fe7e6221acd

#devlog #programming #rackroot #homelab #python #fastapi #sqlmodel #pytest #refactor #postgresql #backend

I learned something about using Python today, and this might influence how I write other parts of my code. I'm also open to ideas on the best way to go about this.

I'm now most of the way through a refactor where I'm switching to Postgres for my database and also using SQLModel over SQLAlchemy. I have all my classes written/rewritten and was running into an issue on the IPAM side of things when creating a new network. See if you can spot the bug as I write it out here.

Each API call in FastAPI will map to one function, with each class having their functions in their own file, for the sake of modularity/organization. I have a subnet.py file and ipRecords.py for those respective classes.

When a new subnet is created, I make the subnet in the database and then populate all of the IP addresses in the IpAddress table before returning to the FastAPI call. This means that one function call is chained to another one. Specifically subnet.createSubnet calls Iprecords.createIpRecord.

Both functions have a Session object coming in like this. Note that Depends comes from SQLModel and getDb() returns a database session, via yield.

myFunction( input1: int, input2, str, db: Session = Depends(getDb))

This means that when a new subnet is added, and then we call iprecords.createIpRecord, that doesn't get the proper result for getDb and we can't talk to the database.

My fix was to pass the db from one function to another so I can keep using it, and not pull it in via getDb on the inner function call.

Now that I'm through that mess, I can carry on with the rest of my refactor and get back to the main branch.

#pebkac #rackroot #programming #fastapi #python #ipam #homelab #opensource #sqlmodel

After setting up separate Postgres containers, my testing and development is in a really nice spot. I have all the fields working the way I want for DeviceType and Item, all the way to the front end interoperability.

Now I need to add the subnet and IP record definitions back to the project.

I also had to keep the FastAPI validator classes, instead of using the SQLModel definitions directly. I was hoping to simplify that (define classes in less places) but this is working, so I guess I’ll take it. I might research some more how to handle that in a better way, if possible.

#programming #rackroot #homelab #backend #fastapi #postgresql #sqlmodel

Looks like I'm going with Postgres for now. It already has a container I can easily pull down to my local environment to run it. While I'm going through this refactor, I just needed to replace the connection string in database.py and install one additional python package. From there, I was able to get most of my tests passing.

Next time I'm going to be learning some more about the foreign key relationships and how those fit into this whole process.

I want to be able to pull over a property from another table, based on the foreign key, so I will be exploring that. There might be a way to do this with back_populates, which I already have in there. I will need to dig into SQLModel a little more.

I have most of my tests passing (85%!) and I did need to rewrite some of them to account for the ID parameters changing for some reason. I think Postgres and sqlite work a little differently when it comes to inserting records and auto-incrementing an ID. Now that those are fixed, at least I don't have to worry about them.

#programming #rackroot #homelab #backend #fastapi #postgresql #sqlmodel

I think I'm starting to outgrow the sqlite database I've been using. I've been able to get along with some of the foreign key stuff I want to do, but now I'm running into challenges with it accepting DateTime objects. I might need something a tad more powerful.

I'm already halfway through a SQLAlchemy -> SQLModel refactor, I guess what's one more branch on the code?! After all, that's what git is for.

I could probably get away with refactoring the other two classes, but I want all of my testing to be passing before I keep going.

#programming #rackroot #backend #databases #design #fullstackdevelopment #fastapi #sqlite #sqlmodel #pytest

So in about an hour, I refactored out SQLAlchemy and Pydantic to replace it with SQLModel – and some tests are actually passing already! I continue to appreciate pytest and how easy that makes this kind of effort.

Starting with the device type class, I'm going to be working on rewriting my "queries" to fit the SQLModel expectations vs what I was using with SQLAlchemy. I'm using the term queries in quotes there since it looks more like I'm calling methods, and I do see some warnings from SQLModel suggesting what I can replace them with.

From there, I should hopefully be able to simplify my code, classes, data types, tables, and other pieces. That'll most likely be a job for Wednesday.

#rackroot #webdev #backend #database #fastapi #sqlmodel #programming #sqlalchemy #pytest

Am I the only one that dont like other ORMs than Rails Active Record? I mean, FastAPI's SQLAlchemy, SQLModel in combination with pydantic as well as Symfonys Doctrine feels a little bit pain in the a** when it comes to complex relations. Active Record just feels smooth compared to the other ones...

#rubyonrails #activerecord #fastapi #sqlalchemy #sqlmodel #pydantic #doctrine #symfony #python #orm #database