this post was submitted on 29 Mar 2024
14 points (88.9% liked)

SQL

469 readers
1 users here now

Related Fediverse communities:

Icon base by Delapouite under CC BY 3.0 with modifications to add a gradient

founded 1 year ago
MODERATORS
[email protected]
[email protected]
14
Is there a programming language specifically designed for interacting with SQL databases? (lemmy.one)
submitted 8 months ago by [email protected] to c/[email protected]
43 comments fedilink hide all child comments
 

Is there a programming language specifically designed for interacting with SQL databases that avoids the need for Object-Relational Mappers (ORMs) to solve impedance mismatch from the start?

If such a language exists, would it be a viable alternative to PHP or Go for a web backend project?

you are viewing a single comment's thread
view the rest of the comments
[–] [email protected] 14 points 8 months ago (4 children)

ORMs are one of the worst things ever created IMO. Sure they’re great to turn structured data storage into objects and methods that developers are used to, but every single one of them scales for crap and I can’t tell you the number of projects we have to go back and fix to be straight up raw SQL once it starts growing and becomes a bottleneck.

(I’ll get off my soapbox now)

[–] [email protected] 7 points 8 months ago

It's just a performance to development time trade off. Clearly the product was successful enough to demand coming back to improve the performance. Lots of software fails before it even reaches that point.

[–] [email protected] 7 points 8 months ago (2 children)

I'm curious under what condition an ORM impacts scaling at all.

ORMs merely generate a SQL query to run on the DB, then serialize the data it gets back, how your application svales should be unimpacted by using an ORM.

I'd assume if somehow it's impeding scaling then the query itself was structured poorly, or that particular ORM was very poor.

A good ORM should be able to generate the sql query in microseconds, the actual DB query time should be typically like 99% of the response time for your API, which the ORM has no bearing on.

[–] [email protected] 4 points 8 months ago (1 children)

The SQL queries I’ve seen almost every ORM create are highly inefficient compared to a good query designed by hand. That’s where I’ve always seen the delay get introduced. You end up spending lots of cycles on the query so the delay looks like it’s within the DB but when replaced by a custom query the bottleneck goes away. Quite often a quick fix is replacing the query with a store procedure or view and letting the devs work off of that.

[–] [email protected] 6 points 8 months ago (2 children)

The SQL queries I’ve seen almost every ORM create are highly inefficient compared to a good query designed by hand.

I have to just respectfully disagree. Perhaps you had an actual database engineer on your team who was in charge of the sql (in which case 100% yes absolutely use sql!)

But typically backend devs aren't database engineers and they have no idea how to compose a good sql query, let alone how to optimize it or test its execution plan.

I've seen way way more absolute clusterfuck garbage queries that take way too long to run that were hacked together by BE devs.

Quite often a quick fix is replacing the query with a store procedure or view and letting the devs work off of that.

Views are fucking awful imo. It's yet another entire layer of abstraction that deeply obfuscates what is actually running and/or happening.

The entire principle of shit outside my codebase that I can inspect with my LSP causing side effects to my logic is just a nightmare to maintain.

The moment you can have the exact same application behave differently purely because different stuff was or was not put on the db it was pointed at, it's an absolute cluster fuck to maintain.

Stored Procedures only should be reached for under one circumstance imo, and that's when you need to use recursion on your DB.

IE if you have perhaps a parent/child self FKd table to create a hierarchy tree with unknown depth... You'd want to traverse it recursively which likely would want a stored proc.

But aside from that, I just can't get behind breaking up the backend into effectively having 2 distinct layers of truth to its behavior.

I want all my codebase in one place, and in one language, under one language, through a single LSP.

[–] [email protected] -1 points 8 months ago (1 children)

I have had Database Engineers and done it myself. If you run any ORM created SQL queries through a profiler and look at the execution plan you’ll see it’s an absolute mess. That’s why I said it doesn’t scale. Sure it’s good for small things but I’m working on projects that have millions or rows and multiple joins. At that scale it just starts to fall apart. Having good raw queries will beat out an ORM every time at scale and that’s why I hate them. You want to use it for a small quick project, go for it. You’re trying to work at enterprise scale, get a DBA to make you actual queries.

[–] [email protected] 7 points 8 months ago

If you run any ORM created SQL queries through a profiler and look at the execution plan you’ll see it’s an absolute mess.

I've never had this experience with Entity Framework, full stop.

It sounds like either the devs were abusing the ORM and using it wrong to make it generate garbage, or, you were just using some very poorly written ORM.

[–] [email protected] -2 points 8 months ago

But typically backend devs aren’t database engineers and they have no idea how to compose a good sql query, let alone how to optimize it or test its execution plan.

Should the BE Dev be touching the data if they don't, you know, know how to work with the data? No: SQL Developers or Data Engineers should create data they can access and use. The design of the database is entirely separate from the design of the application. They do not need to be related, and usually should not.

I’ve seen way way more absolute clusterfuck garbage queries that take way too long to run that were hacked together by BE devs.

Again, should the BE Devs be touching the data, then? No. If they don't know what they're doing (which is writing queries to handle data), then they shouldn't be messing with the data. The Data team should be messing with the data, and serving it to the BE Devs.

Views are fucking awful imo. It’s yet another entire layer of abstraction that deeply obfuscates what is actually running and/or happening.

You must not be a SQL Developer or Data Engineer.

Stored Procedures only should be reached for under one circumstance imo, and that’s when you need to use recursion on your DB.

You're definitely not a SQL Developer or Data Engineer.

IE if you have perhaps a parent/child self FKd table to create a hierarchy tree with unknown depth… You’d want to traverse it recursively which likely would want a stored proc.

I had to read this several times to make sure I understood, because I would absolutely and immediately fire any Data Engineer working for me who developed something like this, and I would shun and or quit the company or project team that insisted on developing an application or process that required it, especially if they did so without consulting their Data team.

I want all my codebase in one place, and in one language, under one language, through a single LSP.

This is completely unrealistic. It speaks to a lack of industry experience and is down-right selfish. No hiring manager will ever take you seriously if you say you only use one language. You need to know several to get by. And that's because each one has a different purpose. HTML renders web content, CSS makes it pretty, JavaScript makes it think, PHP makes it explode, Go makes it go, JSON helps it share data, SQL manipulates the data, Shell/Bash updates systems, etc. Some of these are markup languages, some are stylers, some compute and run functions, some explain data, some manipulate data, some give system instructions. They all have a different purpose, are used in a different layer of the stack, and require a different expert (with the exception of some Full-Stack Wizards) to implement.

Saying you want one language is the most bonkers thing I've read on the internet this month. Don't travel the world; you're gonna have a bad time.

[–] [email protected] 1 points 8 months ago (1 children)

The classic example is the N+1 query pattern, where the number of generated queries is linear in the number of rows returned by the first query.

[–] [email protected] 6 points 8 months ago

This is not a problem for a modern ORM, JOINs are supported by most ORMs I've worked with for many years.

Var cars = await db.Cars
    .Include(c => c.Wheels)
    ToListAsync();

foreach (var car in cars)
{
    Console.WriteLine(car.Model);
    foreach (var wheel in car.Wheels)
    {
        Console.WriteLine(wheel.Id);
    }
}

This will get all the cars and their associated wheels in 1 single query by performing a JOIN operation using Entity Framework Core, assuming there's a FK for Wheel to Car.

[–] [email protected] 3 points 8 months ago (1 children)

ORMs should be made illegal. They make you learn sql then how to translate that to the ORM.

[–] [email protected] 2 points 8 months ago

The absolutely worst thing about them is they claim to be database engine agnostic. Surey the most basic stuff maybe, but make your queries a tad bit advanced and it stops working on different database engine. Also, how often do you switch to a database engine that is so much different from your existing one? I hate using Doctrine, stupid query builders make me develop twice as slow. On the other hand, Eloquent is kind of nice and has an easy way to write raw SQL queries.

[–] [email protected] 2 points 8 months ago

Argh, your comment is such a pet peeve of mine, especially since my SQL knowledge is kinda arse (it's #1 of my todo list to learn): "just use raw SQL" is a terrible answer, because newbies now still don't know how to not use an ORM (and building classes is what they tend to know, so using a wrapper like Django or SQL Alchemy is ez pz).

How do I learn to use raw SQL, as a way to not use ORMs. Yes, learning SQL is step 1, but what is step 2? How am I going to do migrations, without having to manually run stuff, because manual work is faulty work. How am I going to track changes in my model, over time?

Sorry if I sound frustrated, because I am.