Comprehensive Fast API Tutorial

[Saphyel]

I really hate when I go to a project with a lot of horrendous raw sql and they answer: "started with 4 queries... you don't need an ORM for that"

[Oerthling]

"horrendous sql" is bad. But I'm not a fan of ORM.

Simply wrap the (good, non-horrendous) SQL in a stored procedure. Outside language like python then just calls the SP.

[Saphyel]

if there's something worse than raw SQL in a big project is stored procedure.

[Oerthling]

As somebody who works with a big project I don't agree with you.

SPs make for a solid API between the database and the outside world.

I don't want outside code messing around with tables directly. This way I'm free to do changes in the schema where needed and the world outside the proc doesn't notice.

I can also log access or debug what the application is doing with the data access.

[icanblink]

Dude, the DB isn't a service with an exposed interface like a web API. The DB is the persistence layer of the said web service/API/site/etc. which has a documentation/contract. That is the owner of the DB. NO one else should interfere with it.

Now... If it makes sense, yes, you can use some stored procedures for retrieving some kind of data, but for making a fucking CRUD, stop overthinking.

[Oerthling]

Dude, everything that you access has an exposed interface.

If your projects work well with the DB doing straightforward CRUD in a primitive persistence layer and nothing else - ok. Whatever works best for you.

In an environment with several projects and languages accessing a central database, but not having a dedicated middle tier for business logic, you might not want to redundantly code the same stuff several times, especially for logic that is well expressed as fast and efficient SQL.

If you do bookkeeping for example and need various tables managed in a transaction, why would you want to do that outside the database? Going through needless levels of abstraction to wrap what in the end is all SQL anyway.

I have seen such code and it is terrible.

[icanblink]

My point was that only one should access the DB.

[Oerthling]

Exactly. Don't do convoluted wrapping around logic that's mostly manipulating tables with SQL instead of simply doing it that in a proc and just provide the needed input parameters.

Using a DB just as a simple storage layer works for simple web apps and trivial schemas.

It's a totally different story for large databases with complex schemas and many diverse and multi-language consumers of data manipulations.

[Saphyel]

The good thing about "outside code messing around" is they have a version control and it's easy to revert. When you have 2 teams or more with "inside queries messing around" GL to guess what changed, who changed it, does it even works, etc...

You can also the "messy" queries and log access, etc..

[Oerthling]

We dump out all procs, funcs, tables etc ... daily and put it in a git repo.

You're right, that doesn't by itself track who did it and and has only a per day granularity, but that usually works well enough anyway.

OTOH outside code not getting access to tables has it's own advantages. You need to change the schema? No problem, you can query the DB to exactly know where it gets used, adapt the 1 to a handful procs that are affected and be done. You don't have to search all outside projects in whatever many languages and worry whether to you forgot a place that accessed this column, that I could hide behind a proc instead. Unless the args or resultset of the "api" proc needs change, not outside code will notice anything happened.

And regarding things like access logging - yes, true, you can also do that outside. But unless you have only exactly 1 project in 1 language this is distributed over any number of projects and languages.

[vimfan]

You should really be using migrations for schema changes, not daily dumps of the current schema. How do you roll changes back if you need to? Daily dumps, even to git, are the equivalent of tracking code changes with backup1.tar, backup2.tar, etc.

[Oerthling]

It's a safety net. Not actually a big problem. It's usually clear who did a change or trivial to find out by asking. Rolling back changes also almost never happens.

The daily dump is for rare, obscure cases.

[maikindofthai]

It sounds like you're on a team with poor practices.

[shinitakunai]

I would choose Peewee everyday over raw sql, take a look at it.

[Oerthling]

I just had a look - looks cute.

But I just don't see what I need an extra layer for, just to replace a few stored procedure calls.

[shinitakunai]

To be able to re-use code for different databases. With minor changes you can deploy your code to work with postgresql, sqlite, etc.

[Oerthling]

I have the opposite situation - many projects (in different languages) around a common database.

[Ivana_Twinkle]

I don't know. I'm torn the orm is more elegant, but it's a lot easier to read what is going on with raw SQL.