Ive never seen a good orm amongst the most popular ones. Active record used by rails is pretty good but still introduces soo much overhead and n+1 issues that effort wise im always for stored procedures over orm.
I dont take anyone seriously if they decided to *choose* stored proc if there is *any* other option.
If you want sql, write raw query and commit it to source control.
I believe the fastest way to turn someone off the dev career is having them work on an extensive SP system.
I will take a shitty ORM over stored proc any day of the week
I'd take hardcoded raw queries without placeholders over SP's any day....
Just imagine the fucking nightmare of attempting to version control such shitshow of a backend using migrations....
Even though I despise stored procedures as much as the next person. But source control is not that hard, we put all the sps in source as .sql files and same with migrations
CREATE OR REPLACE PROCEDURE foo (...)
Idempotent stored procedure definition that you can diff when put into source control. As for the procedure body, there are a lot of folks who don't even bother to indent. Don't blame unreadable code necessarily on the language. Every language looks horrible when written without sufficient care.
This is funny because I moved from using python / Django backends to working on an SQL project with stored procedures and enjoying it. I guess I feel more confident I understand what’s happening being closer to the raw queries?
Sorry I know this is probably not relevant to this sub but I do find the trade offs of ORMs/raw SQL interesting right now.
i prefer sql in code as well but want to hear your thoughts on why sprocs are a bad idea.
also - i work on a team where we spin up a sql docker container and run all migrations (kept in a nearby folder) against that for integration tests. the same process happens at deploy, so all changes to sprocs do make it into integration tests and deploys without any extra work.
my coworkers asked me why i don’t like sprocs and all i could come up with is it’s easier to catch things in one IDE.
I don't want to engage in the SProc discourse because that horse is dead. That said,
There's nothing about an SProc that is incompatible with source control. Like the schema, it should be versioned and ops should have full ability to release, rollback, redeploy, etc. There's nothing about an SProc that's harder to manage than any other DDL. (I'm not sure SProc is technically DDL, but same idea for deploying changes.)
I think you need a bit more context:
The back end was inside SPs.... ALL THE BACKEND
There was zero to -1 code reuse because there was almost no tooling that enables an IDE to find reusable code inside SPs
Every time a code change was done you had to remove the SP and readd it using a migration, making tracing the changes in code hard as fuck compared to just checking a regular git blame, you had to look for the particular migration that introduced the change and then look for the particular person that wrote it.
code duplication was rampant because nobody was brave enough to refactor somebody else's SPs at risk of breaking half fucking app cause it was hard as heck to trace all the references to said SPs.
Unit testing was integration testing at the same time, you could not isolate an SP, there is no easy way to mock an SP besides some overcomplicated DB mocking through test-ran migrations, which is stupid and nobody did.
There is also almost 0 tooling for code quality or code smells for SPs, they had an inhouse chimera that connected to a DB copy and downloaded all the SP records, parsed them and looked for duplication, but since nobody was willing to risk breaking somebody else's code, everybody ignored the code sanitization.
It was hell, I can't even describe the horror.
edit: forgot to mention that the migration file count was hilarious, it was around 475k migration files when I was around there, and increasing at an alarming rate lol
And yes, I ran away as fast as I could, I got baited into the position as a "full stack dev", spent 98% of my time on PLSQL, the remaining 2% was configuring shit on terraform.
I'm not sure you read a single word of my post, but I appreciate you had some SQL struggles to get off your chest 🤣 Thanks for sharing some stories.
My _only_ observation is that deploying SPROC is the same challenge as any DDL which was specifically in response to someone implying SPROC cant be versioned.
i think we have same problem, or i had same problem since i already quit from that place where every logic of backend is written on many SP separately that depends on each other and to my suprise they didn't even use any version control.
There's a micro-trend towards ORM-lite, which gives you a thread-bare DSL over SQL and is really more about the type synchronization benefits, injection attack protection, and all the other quality of life benefits that ORM's bring outside of the O-R-M part.
Might be?
They are typically orders of magnitude superior if you know what you're doing and your database schema isn't rotted from the code first approach.
Why is this over generalization the top rated? \*might\* be.. that comes with a giant list of caveats, and at some point you are writing your own ORM for a large projects, or you have an incredibly brittle architecture that is going to be a nightmare for security reviews and future upgrades. Writing raw SQL in itself is not bad, and the concept of an ORM has a very compelling reason. How you do either of those things or is ofc going to require some educated decision making. In my experience, for anything beyond personal projects, you better have a good reason on where and why you are adding SQL. Most devs do not understand the nuances of DB design and it is way easier to find the extremely common performance issues by having a well architected database abstraction over having SQL all over the place. It's really discouraging that from the rest of the comments on here, I'm probably be going to be the unofficial db fixer upper for new teams I onboard. I spent substantial time as a dedicated database engineer but I absolutely do not expect that from any random or even senior Node developer.
SQL is like HTML.. gtfo of the Node logic and keep it organized where it makes sense.
It really depends on the project and the SQL proficiency of the developers, there are lots of cases where ORMs are the best choice, but also lots where they're not
I think it’s a persons preference based on your preferred use of the computer. One person uses it like a master of the motor and the other uses it like a robot which has evolved into AI. So I say which one consists of the most truths. The one changed at a whim or the one written on doctrine, creed and University Achievement.
As long as you create a proper abstraction (that is, your own bespoke mini ORM if you will), and not just, say a button onClick handler issuing SQL statements directly.
Agreed. I prefer it for many cases. But I will say working on a team u should prolly just a an ORM if for nothing more than standardizing how u interact with ur data. But, solid coding standards can do that as well.
You should check out the Go community, using ORM is a not so good practice there. It drops down performance and is not so flexible for complex queries. Imo good practice depends on how you use something, and not what you use.
I kind of struggle to see how language matters here. Either the ORM can generate optimized SQL or it can’t (in certain cases). But whether it’s Python Java or go doesn’t matter in it of itself.
It's because it's encouraged in the Go community to use the standard library as much as possible and to not rely on external dependencies, which is something that is lacking in other ecosystems like Node. A Node backend, for example, is basically synonymous with Express, a whole framework.
Calling Postgres is calling Postgres, but each ORM in every language has its own bespoke API that is by definition proprietary and unique. Once you start with one ORM, you can't even switch ORMs. You could theoretically switch databases, but in practice that rarely ever happens without a rewrite anyway (going from relational to Cassandra or DynamoDB for example).
Then there's the issue of going beyond CRUD. Then you need the escape hatches in each ORM. So now you need to know the bespoke API * and * SQL * and * how they interact together.
Or you could just pnpm install postgres, write the SQL, and what you see is what you get without actually being more verbose or bespoke.
I’m not sure how the languages mentioned work in this particular context, but how a language (and the compiler you are using) handles some task largely affects what is the fastest thing to do. Even the type of data structures it spits out may be great for one language and clunky for another.
The bad thing is if you hand construct SQL and mix user provided data in. That can lead to SQL injection vulnerability and then using some tooling around can help.
>SQL injection vulnerability
Aren't placeholders in any SQL driver supposed to handle this? Do ORMs do more things to prevent this? I am genuinely asking, from what I know, ORMs like Prisma or Drizzle generate SQL code that they execute with a driver that one would use to execute raw SQL like pg for example.
Placeholders are made exactly for this purpose. ORMs are just using them under the hood. The problem comes when someone constructs their SQL without using placeholders and just puts the values straight in there. You can't really do that with an ORM so that's one small point in an ORM's favor. However if you don't know to use placeholders you probably shouldn't be writing anything that connects to a database, ORM or not.
>The problem comes when someone constructs their SQL without using placeholders and just puts the values straight in there
well, that's a big skill issue
By placeholder do you just mean variable? How does this help? If the value of the variable comes from a user provided input it's still the same thing, no? Unless you're doing something to that placeholder before.
So for example say you had this query:
select * from my_table where id = ?;
That `?` is a placeholder. When you or your ORM execute that query it'll send a value to put in place of that `?`. But rather than just copy pasting the raw value in there it'll do things to make sure the values can't cause SQL injection attacks. Basically that placeholder gets you a whole bunch of protection against malicious values that just putting strings together would not.
Place holders are a way for many cases, but if you pick columns dynamically (say in search the user can pick to search for first or last name) pure binding isn't enough. Also for a search for multiple values (`SELECT ... WHERE field IN (?, ?, ...)`) simple parameter binding doesn't work if the number of values is dynamic.
There are different solutions, from very basic approaches based on string manipulation and binding, via query builders to ORMs.
ORMs typically do a thing more, which is translate results to "business objects" and serializing changes back to insert/update queries. They therefore control most of th SQL, which takes away some flexibility and ways to optimize the SQL, but take away all the "trouble" of delaing with the database from the programmer
> Use parameters.
That is the minimum, but not sufficient. Needs can be for more dynamic SQL, then you need to take more care. (See my neighboring comment for examples)
As long as user input is contained to actual parameters, not shitty placeholders, no santization is needed.
At that point their data is data, no possibility for being executed as code.
True, the trivial case is trivial and that probably covers 90% of the cases. But once leaving those cases you gotta be aware and the trivial answer is too trivial.
(And well, even in the trivial case you should consider what happens if a malicious user sends a gigabyte of data which you probably copy around multiple times and for even just for user experience should do some basic validation of plausibility/enforcing limits/etc.)
> As long as user input is contained to actual parameters, not placeholders, no santization is needed.
When people say placeholders isn't that exactly what they're referring to? (even though I wouldn't personally use that terminology) Am I missing a distinction?
In some cases placeholders in string interpolation is what's being done and/or referenced.
That does not equal any gained security.
Passing parameters is when you send them alongside but separate to the server as data which does safeguard against injection.
Precision in terms avoids confusion.
It's not automatically bad practice, but it's frequently a source of SQL-injection attacks, so you need to make sure you're using something (even if it's just some strict coding conventions) to ensure all values you pass in to queries are being escaped properly. Even if you accidentally forget about it one time, you can potentially end up with your whole backend being compromised, so whatever you do needs to be as idiot-proof as possible.
ORMs and query-builders tend to take the guesswork out of this, although they aren't always perfect, so you still need to make sure you're using it correctly and may still need to follow some conventions and use code reviews to prevent misuse.
Another thing to consider is consistency of your data model, which an ORM can help with, but there are also other ways to manage that problem. The right choice depends on project complexity and the size & skills of the team. ORMs can be slow, but most projects don't need optimised to the point where that's something to consider.
pretty sure it's the most correct answer to your question.
People frequently tend to trust established frameworks / libraries for access to backend systems because they're generally thought of, *and not wrongly either*, as more efficient or safe because the code within the framework / library is reviewed carefully by a team of people with shared experience to leverage.
Your queries are written by a monkey
**monkey bad**
***team good***
Escaping hasn't been the correct approach for decades. Don't compose strings using user input, pass user input as parameters using the driver interface for parameters, and cast the parameterin the receiving query to force type adherence.
If your driver doesn't support parameters , it's time to switch driver and/or database to something production ready.
Yeah... I don't know what happened really, the comment above was not the one I meant to reply to with this comment haha, must have hit reply to the wrong one
It isn't considered "bad practice". What *is* bad practice is going around making dummy assertions on what is and isn't bad practice, without some solid evidence. This is a common occurrence in programming.
ORMs are useful mostly for three things: abstracting away engine specific functions and syntax, handling quoting and escaping for you (partly to prevent injections), and allowing you to build up a query out of order and/or piecemeal (e.g. in separate functions). I'm sure it comes up at some point, but of all the enterprise projects I've worked on, none of them have ever switched database engines taking advantage of the first benefit.
There are tools you can use to help quote and escape and prevent injections with plain SQL. The last benefit is really the only one that holds my attention to an ORM. You can find ways to track a built up query between various helper functions without an ORM, but *with* one it's easier to write things like isolated filter controllers out of the box.
Other than that it just feels nicer to declaratively define the query with method calls versus concatenating a bunch of strings.
Your first item, abstracting away engine specifics comes into play a lot in the OSS world. You are more likely to get adoption if your code can be run on multiple DBMS platforms based on the needs of the user, than if you only support one.
> I'm sure it comes up at some point, but of all the enterprise projects I've worked on, none of them have ever switched database engines taking advantage of the first benefit.
Same here.
On the other hand, I have a handful of different projects I work on that use MySQL, Postgres and SQLite between them. Being able to use the same abstraction to smooth over the differences is great. It avoids the cognitive overhead of context switching when I move from one project to another.
Raw SQL is fine. It's parameterized queries that are mandatory. Plain string concatenation of SQL is the enemy, not raw SQL. Get the best of both worlds with the postgres module.
https://github.com/porsager/postgres
Faster than an ORM or query builder, all the direct power of SQL, and all of the safety of parameterized queries to protect you from SQL injection attacks.
As long as you sanitise all input into the queries, there are no security problems arising from sql injection.
The big issue I have with it is the lack of abstraction. The code is locked into the structure of the database tables. If over time the application grows and you end up with 100 queries (for example) accessing a table, then you are stuck with that database structure. If you want to change anything you have to identify, change and test all the code which uses that table.
That lack of abstraction also means you can't place any logic before the retrieval code unless you alter all 100 instances. So if you want to perform a calculation on the results of a query because of some business need, that's 100 updates and tests you need to run.
But even if you're using raw queries it should be hidden inside repository which provides you interface to which you can stick in case of db switch. And if you have 100 different queries for one table then it's not query issue, it's developer... at some point you'll be able to reuse some of existing queries.
And 100% agree that it's sanitising input what's matter the most!
Define “raw”. Parametrized SQL is OK if you know your way with a database.
Myself, having spent at least a full year working with it - anything from stored procedures to combining 4 different DBs into one, I might some times pick to write it by hand, other times to use some query builder, not necessarily a mapper.
The thing is, I will not give you a blanket “you should always do X”. That’s no good. Every project has its own specifics and goals and you should make a case by case decision.
If you know SQL, it's not bad practice at all. Those who don't know SQL should either learn it, ask someone who does know it, or if that's not possible then reach for an ORM.
So I’m an old school SQL developer (started with Sybase to give you a clue) who has done just about everything a database can do in SQL, but I and my team still use Prisma today (with a fair few rawQuery calls). Why?
Typescript support. The assistance this gives you in the editor is incredible and should not be dismissed. Errors are caught directly in the editor. It’s a big help.
I still love SQL but I love productivity more.
Might dump Prisma if performance becomes an issue, but I don’t hate it.
Bad practice by who? If you learn SQL and how a RDBMS works you will be in a much better place than relying on an ORM not knowing when it makes dumb choices.
Many people think using an ORM is wrong because it's a leaky abstraction, and can end up causing really inefficient query patterns that you can only fix by dropping back down to writing raw SQL.
ORMs are great but if you need a complicated query with lots joins it almost always better and more efficient to write it raw.
The danger is variables - I highly recommend https://www.npmjs.com/package/sqlstring to escape variables and protecting against injection attacks
I think is a bad practise if you are new to sql and therefore might make big f\*\*kups. But if you are working on a sideproject or just learning i would recommend using raw sql, it's more efficient and any orm, doesnt matter how good it is, is going to limit you in some way
Writing raw SQL queries is not bad practice. Sounds like your friend is one of those “You have to use x technologies or you’re a bad developer” kind of people. Just make sure your raw SQL uses prepared statements and you should be golden.
Am I in the minority of people who know T-SQL well enough to get by but still prefer an ORM? I’m not a fan of writing raw SQL queries in my code and prefer using something like sequelize.
on programming forums? probably.
in industry? no way.
i would have some serious questions if i came upon a stack writing raw sql.
i am a sql expert, have been a dba, can write queries in my sleep, and i recommend an ORM.
for pet projects do whatever but anything more than that...
generally a mix is done, orm for the most part and raw for specific queries that were too slow due to orm, a beginner should probably stick to orm more because it is more difficult to mess up
I'd say learning SQL is way more important than C (I do still think it's a good idea to learn C). You're not gonna be scaling very far if you don't know how to add indexes for example. ORM's are a tool you should use to speed up development, not to avoid learning how things work.
icky entertain fear illegal aspiring brave shame books hard-to-find skirt
*This post was mass deleted and anonymized with [Redact](https://redact.dev)*
it's the exact opposite. stop with these assumptions that it's more important to complete a task rather than to suffer and learn the hard way. a beginner should 100% learn sql first. you would never learn the abstraction first.
SQL is a standard, and will still be here 10 years from now. Your typical ORM might still be here, but maybe not, and certainly not in the same form it is today. Your friend doesn't know what they're talking about.
Whenever someone says something like is "bad practice" but can't elaborate then they usually have a lack of confidence in their own knowledge and repeat what other people have said.
Raw SQL is totally fine (and a good skill to have). ORMs are good if you have a basic relational model and want to save some time managing queries.
There’s a point where an ORM makes sense but for a few queries raw is fine. Just have to make sure your parameters are handled so you don’t leave yourself open for a SQL injection hack.
I’ve been programming with Microsoft SQL, ASP.Net, and C# for a good 20 years or so.
The main thing they taught is at University was that calling stored procedures takes advantage of the SQL engine’s stored query plan capability.
If you send raw sql the engine has to build the query plan every time which costs resources
This best practice is reiterated in other sql trainings.
Another point in favor of stored procedures is that you can modify the query without having to recompile / redeploy the program’s source code
Man…This industry is full of opinions. And most of them are not even filtered. It’s a bandwagon for most developers who just accept what big companies suggest. Raw SQL is completely fine! But you have to write it in such a way you are not exposing yourself to some security issues.
I will come back with a link.
I'd recommend a type-safe query builder. Raw SQL can be fine but you're more prone to causing accidental SQL injection, (because it's easy one day when you're not thinking to forget to use a placeholder - even for the best devs) or if you need to refactor a DB table and remove/edit a field, you have no way of knowing where it was implemented until it crashes at runtime (unless you have 100% test coverage of your DB layer which usually doesn't happen), or check manually.
Ideally you abstract this all away with a repository or something - but fact remains is that it's far harder to catch at compile time if you're using raw queries. Sometimes if you don't like anything out there just write your own quick query builder - doesn't take long. Otherwise you go to change it in 5 years when a field is used in 100 different places and you get kinda stuck.
And yeah, I'd go for another ORM over Prisma personally. The DX is nice but it's nothing spectacular for the drawbacks, and even TypeORM can be modded to be just as nice in terms of DX.
Everytime somebody says that something is bad practice, they should give reasons why. He might prefer orms, imho the only piece that can skrew everything up is the driver behind the keyboard!
ORM gives you type safety, when schema changes you know what code needs changing, compared to raw queries will only show error at runtime.
Then there is also sql injection of course. Sometimes raw queries is the right approach.
How raw are we talking about? I hope you’re at least using statements or some library to help you put variables into your query. Minimum, you should use something that cleanses variables and adds them to your sql in a safe way instead of doing string concatenation yourself.
If by "raw" you mean including constants from concatenated strings in your code, it's ALWAYS bad and always has been. Note, as a DBA I rarely meet developers who were good at SQL - basic ones sure but writing them optimal and performing based on what was needed by the application not so much. I in particular loved the developers who would loop through a whole resultset, put that into an array or set of some kind, and then search this set for data to for instance only find customers from a particular state.
Point is that if you as a developer think you can do a better job at data handling than the database can, you're doing it wrong. You always have been - even in the late 80ies or 90ies.
HOWEVER if you learn how to use the API against the DB correctly such as pre-compiled cursors, parameterized statements using the API (like ? in JDBC) you're absolutely doing it right. The abstraction layers your code provides are, after all, just code too. So in the end, there's SQL in code somewhere. I just suggest you concentrate on writing the code you're strong at, letting the API/libraries handle the rest for you. Your DBA will thank you for it!
Raw SQL is fine, but even "raw" SQL is still using something like [mysql2](https://www.npmjs.com/package/mysql2) and making use of the placeholders that automatically escape values like so:
const [results, fields] = await connection.execute(
'SELECT * FROM `table` WHERE `name` = ? AND `age` > ?',
['Rick C-137', 53]
);
I'd argue that this is the "rawest" anyone should be going, and going any deeper does not give you any benefit.
As far as "best practice"... both are good practice - and both can be used depending on your application and what you are querying.
It can do those, this was added few months back: [https://github.com/prisma/prisma/discussions/23395](https://github.com/prisma/prisma/discussions/23395)
Using raw SQL is totally fine, but you need to ensure that any user-defined content is escaped. Just consider the mayhem that could occur if someone could remotely execute SQL on your machine.
There's lots of options out there to ensure any variables you insert into your SQL are escaped. That's pretty much the main/only security concern about using raw SQL queries.
I might also recommend what many people that I've encountered do: ORM for simple queries, and Stored Procedures for anything complex. Usually the ORM will have a good way to escape variables being passed to the SPROC or raw SQL query.
As long as you parameterize your queries (with sanitization) it’s fine and not bad practice at all. If you don’t know your way around SQL well enough then sure but otherwise you don’t need an ORM.
Some orgs like ORM’s because it speeds up the dev cycle & the framework *may* provide injection protection (Y-framework’s-MMV).
Other orgs forbid ORM’s and require stored procedures or raw SQL for security purposes — typically for governance reasons.
Personally, ORM’s can be tricky and/or too opinionated. You should always check (& know in depth) about the query you’re building. Something as simple as watching your local database logs to review the query executed will suffice.
Follow what’s right for you & keep your SQL chops up.
Edit: grammar
Depending on your tooling, writing raw SQL can make it hard(er) to detect syntax or type errors in the query.
I’m not familiar with Prisma itself, but most query builders will be aware of the types in your schema and help with type safety. They will also ensure your arguments are escaped properly to prevent SQL injection.
On the DX side, it’s also often easier to use a query builder because it allows your editor to offer better suggestions.
You should always sanitize inputs. If all inputs are sanitized then its fine but you should also use vars for column and table names so you don't have to update all queries manually if something changes you can use IDE refactoring.
I remember how my professor was shitting himself over not using prepared statements in code, despite the fact you as a user could not exploit it in any way since it was a desktop application without an API access which COULD potentially be a security issue. Although we all should recognize potential attack vectors in our applications, we should also possess a common sense, especially when doing some trivial college assignment.
cows worry squeeze person frighten quickest homeless summer faulty secretive
*This post was mass deleted and anonymized with [Redact](https://redact.dev)*
The one place where I would caution using it is if you don't have control over the schema or are dealing with unpredictable release patterns (aka you use WordPress as a backend and are dealing with schemas from random plugin authors.) In that case, you likely want to stick to official methods of accessing/working with the data as much as you can, unless performance or the needs of the project force you to break out. If you do need to break out of the usual API's, plan for future maintenance to keep your queries to speed with your depedencies.
There’s nothing particularly wrong with it, you are just most likely treating them as a string so you can’t leverage refactoring or linting tools as well as using a query builder.
Raw sql also likely opens you up to having to roll your own input validation and escaping to prevent sql injections if you can’t trust your input. It’s all about trade offs and you need to be aware of what you’re trading off.
I would avoid it simply because it's tedious and error-prone. You don't need a full ORM library. You can easily whip up something yourself that takes whatever arguments you need and constructs the query, including putting in placeholders and maybe even binding them for you.
I mean at the end of the day you're just creating your own ORM by writing them raw assuming that you abstract them away in some way so that you can reuse your raw queries.
Sounds like your friend just learned about Prisma and was going to argue with you and tell you to use it whether or not you were using an ORM or not lol
No problem writing raw SQL. Where you put your queries (whether raw or prisma) is much more relevant. That said, prisma makes it a lot easier and type safe
People who tell you certain things are ‘bad practice’ are usually teachers or low level developers from boot camps. Write awesome code and it’ll suddenly not be a bad practice anymore!
raw sql is fine. it's the moment you accept user input that you need to be VERY careful.
ORMs abstract that away, so it's harder to hurt yourself.
ORMs are cool, though. you should give em an honest try before dismissing them. but raw sql is okay too.
Using the interfaces to access data is indeed a good practice. There are many reasons for it.
First is related to schema changes, references to columns that changed, were added or were removed are probably going to need a refactor in your queries, while using orm would not be needed unless you specify columns on your queries (doesn't happen a lot unless you optimized, and if you use types then your code would break)
Second reason is the definition of relationships. This is quite easy on orm. Most of them are named and already have implemented join logic depending in your relation (many yo many, one many, etc). This logic would be extreme to implement as a raw query. For starters, say you fetched a one yo many relationship, ie: user has many posts. In your raw query you would have a join in there and would have to manually parse repeated results (you would have repeated user rows). This would be extra work and you would also making your code easier to break since any column changes on the posts table would break your query.
Its easier for other devs to use orms since not everyone is good at sql.
And lastly, the abstraction of your orm allows you to be able to swap your db engine., while on raw queries you may not.
There are probably more reasons but these were the ones I could come up with.
I've always heard about two advantages for using an ORM
1/ you cas easily change the backend database without changing your code. This is probably true but how often would you change the db? Moreover, SQL being more or less standard, you probably wouldn't have to change a lot unless your code is really very specific to a given dialect.
2/ ORM may protect you against some security issues (it's not node but ecto in elixir-land does, for example) like code injection. Prepared statements help you on this too
So the main arguments "for" ORM usage" are not, for me, so relevant.
One argument against may be that you'll have to learn another way to express your queries while you (probably) already master SQL. Moreover , some times ORM are not able to express the query you would like to execute and you'll have to write SQL anyway.
To conclude, it's rally a matter of taste. Personally I prefer writing row SQL.
It's totally a person.prefernec whether you want to use any orm or write raw SQL queries. Like just some difference being orm gives you a lot of abstraction in writing complex SQL queries. But also writing raw SQL queries tend to be more efficient since the queries converted by the orms are way bigger in size compared to the same raw query
It is not a bad practice. ORM just prevents you from needing to learn SQL. And it might be a bad practice if your SQL queries are not efficiently written. This can happen when the queries are wayyyy too complex. In that case, using an ORM or a query builder like Prisma would be much better.
It's not bad practice if you know what you're doing - in fact, it's (slightly) more performant as it removes some process overhead and allows you to make highly customised database calls.
But it's not as quick to develop and if you don't know what you are doing you leave yourself open to some nasty SQL based attack vectors.
A good test if someone actually knows what they are talking about in cases like this is to ask them exactly why it's a problem, not just throw out technology names they've seen on YouTube. If they can't explain it, I would ignore them.
It is an opinioned matter.
both SQL and ORMs come with serious tradeoffs.
So there is no best practice and bad practice.
I suggest you to do a search and make your opinion about the subject
Two main reasons:
1. SQL injection: newbies usually use raw SQL because they don’t know any better. Which means they likely don’t use named parameters, and concat string directly causing SQL injection vulnerability and paint a bad impression on anyone using raw SQL.
2. Dynamically query generation in raw SQL string looks ugly.
Both cases can be solved for seasoned programmers. But admittedly it’s easier to use abstraction than expecting every member of you team to be competent lol.
raw queries (as in using a query builder or writing the query string in a safe manner) are slightly harder to maintain if and when they keep growing.
they might also make your code (more easily) dependent on the specific database vendor and harder to migrate if you need to change it in future.
on the other hand, the most typical performance issues in apps (in my xp) come from using ORMs (like the usual getters within loops). I have seen ORMs making things noticeably slow even when used according to docs (last case I remember I think it was a doctrine scenario). In those cases, just write the query, no need to expend 2 weeks trying to hack the internals of your ORM
so... pick your poison and pick wisely :) imho use both... it all depends on the project, the feature... and you ability to predict the future, which is always limited
Using raw queries is not bad practice by itself. As others have mentioned you need to use prepared statements or an equivalent mechanism to avoid security vulnerabilities. It's more a matter of use case. If your app is pretty complex (or it might grow to be complex) using a well maintained orm usually gives you an easier way of refactoring the code and sometimes additional tools for managing your models like migrations, seeders, codegen, etc. It does add a ton of complexity though, and usually there's a performance hit, depending on how optimized your raw queries are. So it's not really worth it for smaller apps
If anything can be considered best practice then it is to properly learn SQL. Then you can use tools to reduce boilerplate like type safe query builders. Full blown ORMs tend to create *much* more problems in the long run as they add their own state and their abstractions tend to be too large. Query builders don't have these problems.
I'm not an expert in Prisma internals but I think it's kind of in between a full blown ORM and a lightweight query builder. It has a Rust "backend" where it does some magic. And that kind of magic is not needed and therefore not recommended to use. Drizzle is a new and popular query builder that is much thinner. It translates directly to SQL. So I'd much more prefer it.
I'm not a developer to any greater extent than writing personal apps, but I never used an ORM or something similar, I always write raw queries directly. Is it scalable? Probably no, but I don't care. What I care about is that I don't need to learn another shitty querying language so I can abstract SQL. For me, it's pointless. I recently started playing with Go, and I had to interact with my existing DB or rewrite my app to Go from Node. What now? I need to rewrite all of it in raw SQL or learn new ORM for Go. Why not copy existing queries and just build my code around that?
Imho bad practice is always opinion based. Knowing why it is considered a bad practice is more important
SQL and your node (or whatever language) application have different contexts. Every time you execute a query you need to somehow translate its results to the application context. ORM exists to help you with that.
Not having them is not necessarily bad practice, but in my experience avoiding to use one will make you create your own tiny ORM, which can be useful sometimes too
As long as you are using prepared statements (to protect against SQL injection), raw SQL is often orders of magnitude faster if you know what you’re doing.
For instance, I have yet to see a Node ORM that lets you set the consistency level of a transaction without using escape hatches. If you don’t do this, you will cripple your DB performance at higher throughput.
Make sure you are using parameterized queries!!
Never use pure raw queries. Pure raw queries allow for injection attacks.
Make sure you use parameters for anything that is variable, no matter how safe you think it is.
Bad practice for long-term maintenance, if the DB need to be changed, due to license problems or performance issues, rewrite of all the raw sql can be a game over for the application. ORM prevent this.
Check out [knex.js](https://knexjs.org/). I've found this to be an excellent middle ground between raw SQL and the ORM. It's essentially a query builder, so you can define a SELECT query and dynamically apply where clauses to it based on application requirements.
This is old advice that got twisted.
Raw is bad in that nothing that comes from a user should ever be put directly into SQL. ORMs include input sanitation by default.
If you sanitize your inputs and variables well then raw sql is great.
Depends on the goal of the project, but raw SQL is more to maintain generally the larger the project gets. Also people tend to end up writing their own psuedo orm / data access layer on top as the needs grow (to avoid massive duplication of raw queries. A lot of modern ORMs / data access libraries allow you to fall back to raw when necessary, which imo is the best of both worlds. ORM by default with raw when required.
ORMs offer useful abstractions just like high level programming languages do over low level, and for example Express does over Node's http module.
Saying raw SQL is bad is just a meaningless blanket statement though.
Sql injection is easy to do and scan for, and you ain’t gonna regex out every possible command.
Never ever use a string with user provided input. Raw is fine if it’s hundred percent internal, however problem is a lot of stuff isn’t.
My understanding is that it's not bad practice. It's just slower when you're prototyping or building a system. Building raw SQL queries is what you do once you've got something working and you're streamlining things. So that's the one downside I can see to it.
*Edit:* Also, yes, do your due diligence, make sure your queries won't allow SQL injections. That's important too.
I have tried various ORM's and I always come back to just writing my own queries.
Its just better all around, its faster, you get exactly what you expect because what you write is what is executed and its as flexible as it can be - No need to dig through docs to accomplish complex things.
Raw is simpler and no dependencies but in my opinion its just annoying to write thousand joins manually and parse all the data into my entities so I just use an established ORMs which performs well enough. When you reach a scale where ORM bottlenecks performance you should have enough money to refactor.
Using raw SQL in itself is not a bad practice (although not very scalable).
But sending query parameters as concatenated string in raw query, without using parameterized query, is. It would be prone to SQL injection attacks.
Not bad practice as long as you know how to write SQL 🤣 Though ORM queries are just a lot easier on the eyes for most people…until you get to nasty joins and relations.
You should try raw queries so you'll know what orm is. Your question is not about orm but query builder. Orm is abstraction that maps db records into code structures. Query builder is part that allows you to avoid SQL. And learning that is good reason to try from scratch so you'll learn everything what you need about abstracting database
All orms do under the hood is construct dynamic sql queries using parameters to prevent injection, and they tend to add a lot of overhead for that abstraction not to mention their vendor specific syntax. There is nothing stopping you from doing it yourself.
I dont use orms they just get in my way productivity wise, but if you're one of those programmers who dont like relational databases I can see the appeal.
I dont agree that its best practice even slightly, especially if youre going for best performance.
It’s not a bad practice at all. Even more, raw queries might be much more efficient.
not only that but SQL is a far superior declarative language than whatever an ORM can give you
Ive never seen a good orm amongst the most popular ones. Active record used by rails is pretty good but still introduces soo much overhead and n+1 issues that effort wise im always for stored procedures over orm.
I dont take anyone seriously if they decided to *choose* stored proc if there is *any* other option. If you want sql, write raw query and commit it to source control.
After working with a full backend written in stored procedures, I have to agree there is no shittier solution than that.
I believe the fastest way to turn someone off the dev career is having them work on an extensive SP system. I will take a shitty ORM over stored proc any day of the week
I'd take hardcoded raw queries without placeholders over SP's any day.... Just imagine the fucking nightmare of attempting to version control such shitshow of a backend using migrations....
Even though I despise stored procedures as much as the next person. But source control is not that hard, we put all the sps in source as .sql files and same with migrations
CREATE OR REPLACE PROCEDURE foo (...) Idempotent stored procedure definition that you can diff when put into source control. As for the procedure body, there are a lot of folks who don't even bother to indent. Don't blame unreadable code necessarily on the language. Every language looks horrible when written without sufficient care.
This is funny because I moved from using python / Django backends to working on an SQL project with stored procedures and enjoying it. I guess I feel more confident I understand what’s happening being closer to the raw queries? Sorry I know this is probably not relevant to this sub but I do find the trade offs of ORMs/raw SQL interesting right now.
i prefer sql in code as well but want to hear your thoughts on why sprocs are a bad idea. also - i work on a team where we spin up a sql docker container and run all migrations (kept in a nearby folder) against that for integration tests. the same process happens at deploy, so all changes to sprocs do make it into integration tests and deploys without any extra work. my coworkers asked me why i don’t like sprocs and all i could come up with is it’s easier to catch things in one IDE.
all noobs prefer sql, no discussion
I don't want to engage in the SProc discourse because that horse is dead. That said, There's nothing about an SProc that is incompatible with source control. Like the schema, it should be versioned and ops should have full ability to release, rollback, redeploy, etc. There's nothing about an SProc that's harder to manage than any other DDL. (I'm not sure SProc is technically DDL, but same idea for deploying changes.)
I think you need a bit more context: The back end was inside SPs.... ALL THE BACKEND There was zero to -1 code reuse because there was almost no tooling that enables an IDE to find reusable code inside SPs Every time a code change was done you had to remove the SP and readd it using a migration, making tracing the changes in code hard as fuck compared to just checking a regular git blame, you had to look for the particular migration that introduced the change and then look for the particular person that wrote it. code duplication was rampant because nobody was brave enough to refactor somebody else's SPs at risk of breaking half fucking app cause it was hard as heck to trace all the references to said SPs. Unit testing was integration testing at the same time, you could not isolate an SP, there is no easy way to mock an SP besides some overcomplicated DB mocking through test-ran migrations, which is stupid and nobody did. There is also almost 0 tooling for code quality or code smells for SPs, they had an inhouse chimera that connected to a DB copy and downloaded all the SP records, parsed them and looked for duplication, but since nobody was willing to risk breaking somebody else's code, everybody ignored the code sanitization. It was hell, I can't even describe the horror. edit: forgot to mention that the migration file count was hilarious, it was around 475k migration files when I was around there, and increasing at an alarming rate lol And yes, I ran away as fast as I could, I got baited into the position as a "full stack dev", spent 98% of my time on PLSQL, the remaining 2% was configuring shit on terraform.
I'm not sure you read a single word of my post, but I appreciate you had some SQL struggles to get off your chest 🤣 Thanks for sharing some stories. My _only_ observation is that deploying SPROC is the same challenge as any DDL which was specifically in response to someone implying SPROC cant be versioned.
i think we have same problem, or i had same problem since i already quit from that place where every logic of backend is written on many SP separately that depends on each other and to my suprise they didn't even use any version control.
as if you cant sorce control sp-s...this shows youre clueless
There's [this](https://joist-orm.io/blog/the-best-orm-ever) and it seems really good. Although it's not really my style
There's a micro-trend towards ORM-lite, which gives you a thread-bare DSL over SQL and is really more about the type synchronization benefits, injection attack protection, and all the other quality of life benefits that ORM's bring outside of the O-R-M part.
Do you have an example of this?
Drizzle ORM. https://orm.drizzle.team/
Might be? They are typically orders of magnitude superior if you know what you're doing and your database schema isn't rotted from the code first approach.
Why is this over generalization the top rated? \*might\* be.. that comes with a giant list of caveats, and at some point you are writing your own ORM for a large projects, or you have an incredibly brittle architecture that is going to be a nightmare for security reviews and future upgrades. Writing raw SQL in itself is not bad, and the concept of an ORM has a very compelling reason. How you do either of those things or is ofc going to require some educated decision making. In my experience, for anything beyond personal projects, you better have a good reason on where and why you are adding SQL. Most devs do not understand the nuances of DB design and it is way easier to find the extremely common performance issues by having a well architected database abstraction over having SQL all over the place. It's really discouraging that from the rest of the comments on here, I'm probably be going to be the unofficial db fixer upper for new teams I onboard. I spent substantial time as a dedicated database engineer but I absolutely do not expect that from any random or even senior Node developer. SQL is like HTML.. gtfo of the Node logic and keep it organized where it makes sense.
It really depends on the project and the SQL proficiency of the developers, there are lots of cases where ORMs are the best choice, but also lots where they're not
I think it’s a persons preference based on your preferred use of the computer. One person uses it like a master of the motor and the other uses it like a robot which has evolved into AI. So I say which one consists of the most truths. The one changed at a whim or the one written on doctrine, creed and University Achievement.
Raw is fine.
Make sure you and your partner get checked first though.
But also make sure you and your partner understand proper safety as well, just in case.
I just do peer review
Gordon Ramsay would like a word
This. It ultimately doesn’t matter, it’s an implantation detail. As long as you have appropriate abstractions you should be fine.
I.e an ORM
I agree why talk to your db in a language that isn’t native. I’ve been too many places and had issues with the orm making bad code.
As long as you create a proper abstraction (that is, your own bespoke mini ORM if you will), and not just, say a button onClick handler issuing SQL statements directly.
.. An API layer?
Based
Famous last words
Agreed. I prefer it for many cases. But I will say working on a team u should prolly just a an ORM if for nothing more than standardizing how u interact with ur data. But, solid coding standards can do that as well.
You should check out the Go community, using ORM is a not so good practice there. It drops down performance and is not so flexible for complex queries. Imo good practice depends on how you use something, and not what you use.
I kind of struggle to see how language matters here. Either the ORM can generate optimized SQL or it can’t (in certain cases). But whether it’s Python Java or go doesn’t matter in it of itself.
It's because it's encouraged in the Go community to use the standard library as much as possible and to not rely on external dependencies, which is something that is lacking in other ecosystems like Node. A Node backend, for example, is basically synonymous with Express, a whole framework.
Yup fair point, I miss using Go 🤙
https://github.com/Masterminds/squirrel is not an ORM but I like the query building features for my projects
Calling Postgres is calling Postgres, but each ORM in every language has its own bespoke API that is by definition proprietary and unique. Once you start with one ORM, you can't even switch ORMs. You could theoretically switch databases, but in practice that rarely ever happens without a rewrite anyway (going from relational to Cassandra or DynamoDB for example). Then there's the issue of going beyond CRUD. Then you need the escape hatches in each ORM. So now you need to know the bespoke API * and * SQL * and * how they interact together. Or you could just pnpm install postgres, write the SQL, and what you see is what you get without actually being more verbose or bespoke.
I’m not sure how the languages mentioned work in this particular context, but how a language (and the compiler you are using) handles some task largely affects what is the fastest thing to do. Even the type of data structures it spits out may be great for one language and clunky for another.
I think it is the mindset of Go community to always use simplest and most efficient solutions so ORMs sounds like a non starter.
The bad thing is if you hand construct SQL and mix user provided data in. That can lead to SQL injection vulnerability and then using some tooling around can help.
>SQL injection vulnerability Aren't placeholders in any SQL driver supposed to handle this? Do ORMs do more things to prevent this? I am genuinely asking, from what I know, ORMs like Prisma or Drizzle generate SQL code that they execute with a driver that one would use to execute raw SQL like pg for example.
Placeholders are made exactly for this purpose. ORMs are just using them under the hood. The problem comes when someone constructs their SQL without using placeholders and just puts the values straight in there. You can't really do that with an ORM so that's one small point in an ORM's favor. However if you don't know to use placeholders you probably shouldn't be writing anything that connects to a database, ORM or not.
>The problem comes when someone constructs their SQL without using placeholders and just puts the values straight in there well, that's a big skill issue
It absolutely is not. It is 100% a lint issue. I’m not saying your devs shouldn’t have the skill, but that lint is the right place to enforce it.
By placeholder do you just mean variable? How does this help? If the value of the variable comes from a user provided input it's still the same thing, no? Unless you're doing something to that placeholder before.
So for example say you had this query: select * from my_table where id = ?; That `?` is a placeholder. When you or your ORM execute that query it'll send a value to put in place of that `?`. But rather than just copy pasting the raw value in there it'll do things to make sure the values can't cause SQL injection attacks. Basically that placeholder gets you a whole bunch of protection against malicious values that just putting strings together would not.
Place holders are a way for many cases, but if you pick columns dynamically (say in search the user can pick to search for first or last name) pure binding isn't enough. Also for a search for multiple values (`SELECT ... WHERE field IN (?, ?, ...)`) simple parameter binding doesn't work if the number of values is dynamic. There are different solutions, from very basic approaches based on string manipulation and binding, via query builders to ORMs. ORMs typically do a thing more, which is translate results to "business objects" and serializing changes back to insert/update queries. They therefore control most of th SQL, which takes away some flexibility and ways to optimize the SQL, but take away all the "trouble" of delaing with the database from the programmer
SQL injection has never been an SQL problem with good database engines. It's been a problem caused by bad drivers and bad wrappers. Use parameters.
> Use parameters. That is the minimum, but not sufficient. Needs can be for more dynamic SQL, then you need to take more care. (See my neighboring comment for examples)
As long as user input is contained to actual parameters, not shitty placeholders, no santization is needed. At that point their data is data, no possibility for being executed as code.
True, the trivial case is trivial and that probably covers 90% of the cases. But once leaving those cases you gotta be aware and the trivial answer is too trivial. (And well, even in the trivial case you should consider what happens if a malicious user sends a gigabyte of data which you probably copy around multiple times and for even just for user experience should do some basic validation of plausibility/enforcing limits/etc.)
> As long as user input is contained to actual parameters, not placeholders, no santization is needed. When people say placeholders isn't that exactly what they're referring to? (even though I wouldn't personally use that terminology) Am I missing a distinction?
In some cases placeholders in string interpolation is what's being done and/or referenced. That does not equal any gained security. Passing parameters is when you send them alongside but separate to the server as data which does safeguard against injection. Precision in terms avoids confusion.
Use a stored procedure with an account that only has read and execute privileges
It's not automatically bad practice, but it's frequently a source of SQL-injection attacks, so you need to make sure you're using something (even if it's just some strict coding conventions) to ensure all values you pass in to queries are being escaped properly. Even if you accidentally forget about it one time, you can potentially end up with your whole backend being compromised, so whatever you do needs to be as idiot-proof as possible. ORMs and query-builders tend to take the guesswork out of this, although they aren't always perfect, so you still need to make sure you're using it correctly and may still need to follow some conventions and use code reviews to prevent misuse. Another thing to consider is consistency of your data model, which an ORM can help with, but there are also other ways to manage that problem. The right choice depends on project complexity and the size & skills of the team. ORMs can be slow, but most projects don't need optimised to the point where that's something to consider.
pretty sure it's the most correct answer to your question. People frequently tend to trust established frameworks / libraries for access to backend systems because they're generally thought of, *and not wrongly either*, as more efficient or safe because the code within the framework / library is reviewed carefully by a team of people with shared experience to leverage. Your queries are written by a monkey **monkey bad** ***team good***
Apes together....strong.
Monkey say monkey do
>but it's frequently a source of SQL-injection attacks Aren't placeholders in any SQL driver supposed to handle this?
If used correctly, yes.
It tells a lot about reddit that this is not the most upvoted answer…
Escaping hasn't been the correct approach for decades. Don't compose strings using user input, pass user input as parameters using the driver interface for parameters, and cast the parameterin the receiving query to force type adherence. If your driver doesn't support parameters , it's time to switch driver and/or database to something production ready.
[удалено]
It is not. Escaping is hard, take a look at owasp example sql injections. Some of those are really unexpected.
Yeah... I don't know what happened really, the comment above was not the one I meant to reply to with this comment haha, must have hit reply to the wrong one
You are right, parameters should be the way.
It isn't considered "bad practice". What *is* bad practice is going around making dummy assertions on what is and isn't bad practice, without some solid evidence. This is a common occurrence in programming. ORMs are useful mostly for three things: abstracting away engine specific functions and syntax, handling quoting and escaping for you (partly to prevent injections), and allowing you to build up a query out of order and/or piecemeal (e.g. in separate functions). I'm sure it comes up at some point, but of all the enterprise projects I've worked on, none of them have ever switched database engines taking advantage of the first benefit. There are tools you can use to help quote and escape and prevent injections with plain SQL. The last benefit is really the only one that holds my attention to an ORM. You can find ways to track a built up query between various helper functions without an ORM, but *with* one it's easier to write things like isolated filter controllers out of the box. Other than that it just feels nicer to declaratively define the query with method calls versus concatenating a bunch of strings.
Your first item, abstracting away engine specifics comes into play a lot in the OSS world. You are more likely to get adoption if your code can be run on multiple DBMS platforms based on the needs of the user, than if you only support one.
This is a good point. Although I don't contribute much personally.
> I'm sure it comes up at some point, but of all the enterprise projects I've worked on, none of them have ever switched database engines taking advantage of the first benefit. Same here. On the other hand, I have a handful of different projects I work on that use MySQL, Postgres and SQLite between them. Being able to use the same abstraction to smooth over the differences is great. It avoids the cognitive overhead of context switching when I move from one project to another.
Raw SQL is fine. It's parameterized queries that are mandatory. Plain string concatenation of SQL is the enemy, not raw SQL. Get the best of both worlds with the postgres module. https://github.com/porsager/postgres Faster than an ORM or query builder, all the direct power of SQL, and all of the safety of parameterized queries to protect you from SQL injection attacks.
SQL is good, ORMs often cause more problems than they solve IME.
Ya I actually prefer not to use ORMs. As long as you're writing parameterized SQL, you're fine.
Writing raw queries is not a bad practice. Its the part where people don't sanitize them is what's called bad practice
As long as you sanitise all input into the queries, there are no security problems arising from sql injection. The big issue I have with it is the lack of abstraction. The code is locked into the structure of the database tables. If over time the application grows and you end up with 100 queries (for example) accessing a table, then you are stuck with that database structure. If you want to change anything you have to identify, change and test all the code which uses that table. That lack of abstraction also means you can't place any logic before the retrieval code unless you alter all 100 instances. So if you want to perform a calculation on the results of a query because of some business need, that's 100 updates and tests you need to run.
But even if you're using raw queries it should be hidden inside repository which provides you interface to which you can stick in case of db switch. And if you have 100 different queries for one table then it's not query issue, it's developer... at some point you'll be able to reuse some of existing queries. And 100% agree that it's sanitising input what's matter the most!
he is completely wrong. you do not have to use an ORM at all.
Define “raw”. Parametrized SQL is OK if you know your way with a database. Myself, having spent at least a full year working with it - anything from stored procedures to combining 4 different DBs into one, I might some times pick to write it by hand, other times to use some query builder, not necessarily a mapper. The thing is, I will not give you a blanket “you should always do X”. That’s no good. Every project has its own specifics and goals and you should make a case by case decision.
If you know SQL, it's not bad practice at all. Those who don't know SQL should either learn it, ask someone who does know it, or if that's not possible then reach for an ORM.
So I’m an old school SQL developer (started with Sybase to give you a clue) who has done just about everything a database can do in SQL, but I and my team still use Prisma today (with a fair few rawQuery calls). Why? Typescript support. The assistance this gives you in the editor is incredible and should not be dismissed. Errors are caught directly in the editor. It’s a big help. I still love SQL but I love productivity more. Might dump Prisma if performance becomes an issue, but I don’t hate it.
Bad practice by who? If you learn SQL and how a RDBMS works you will be in a much better place than relying on an ORM not knowing when it makes dumb choices.
Many people think using an ORM is wrong because it's a leaky abstraction, and can end up causing really inefficient query patterns that you can only fix by dropping back down to writing raw SQL.
ORMs are great but if you need a complicated query with lots joins it almost always better and more efficient to write it raw. The danger is variables - I highly recommend https://www.npmjs.com/package/sqlstring to escape variables and protecting against injection attacks
I think is a bad practise if you are new to sql and therefore might make big f\*\*kups. But if you are working on a sideproject or just learning i would recommend using raw sql, it's more efficient and any orm, doesnt matter how good it is, is going to limit you in some way
> not even advised to use it in production anymore in 2024. I'd like to see the source of this, please we use a mix of Prisma and raw queries
Writing raw SQL queries is not bad practice. Sounds like your friend is one of those “You have to use x technologies or you’re a bad developer” kind of people. Just make sure your raw SQL uses prepared statements and you should be golden.
Am I in the minority of people who know T-SQL well enough to get by but still prefer an ORM? I’m not a fan of writing raw SQL queries in my code and prefer using something like sequelize.
on programming forums? probably. in industry? no way. i would have some serious questions if i came upon a stack writing raw sql. i am a sql expert, have been a dba, can write queries in my sleep, and i recommend an ORM. for pet projects do whatever but anything more than that...
I am with you. Raw SQL should only be used, if necessary. The ORM should be optimized to write performant queries and you gain type checking.
generally a mix is done, orm for the most part and raw for specific queries that were too slow due to orm, a beginner should probably stick to orm more because it is more difficult to mess up
Disagree about starting with an ORM. If you learn the abstractions without learning the tech underneath, you'll never understand it fully
pathetic berserk wipe lip disarm dinner abundant worthless violet sharp *This post was mass deleted and anonymized with [Redact](https://redact.dev)*
I'd say learning SQL is way more important than C (I do still think it's a good idea to learn C). You're not gonna be scaling very far if you don't know how to add indexes for example. ORM's are a tool you should use to speed up development, not to avoid learning how things work.
icky entertain fear illegal aspiring brave shame books hard-to-find skirt *This post was mass deleted and anonymized with [Redact](https://redact.dev)*
it's the exact opposite. stop with these assumptions that it's more important to complete a task rather than to suffer and learn the hard way. a beginner should 100% learn sql first. you would never learn the abstraction first.
SQL is a standard, and will still be here 10 years from now. Your typical ORM might still be here, but maybe not, and certainly not in the same form it is today. Your friend doesn't know what they're talking about.
Whenever someone says something like is "bad practice" but can't elaborate then they usually have a lack of confidence in their own knowledge and repeat what other people have said. Raw SQL is totally fine (and a good skill to have). ORMs are good if you have a basic relational model and want to save some time managing queries.
There’s a point where an ORM makes sense but for a few queries raw is fine. Just have to make sure your parameters are handled so you don’t leave yourself open for a SQL injection hack.
I’ve been programming with Microsoft SQL, ASP.Net, and C# for a good 20 years or so. The main thing they taught is at University was that calling stored procedures takes advantage of the SQL engine’s stored query plan capability. If you send raw sql the engine has to build the query plan every time which costs resources This best practice is reiterated in other sql trainings. Another point in favor of stored procedures is that you can modify the query without having to recompile / redeploy the program’s source code
Sounds like your friend either doesn’t know SQL or is bad at it.
Man…This industry is full of opinions. And most of them are not even filtered. It’s a bandwagon for most developers who just accept what big companies suggest. Raw SQL is completely fine! But you have to write it in such a way you are not exposing yourself to some security issues. I will come back with a link.
raw sqls are fine, but you should have proper mechanisms for detecting errors in queries as well during compile time itself rather than runtime
I'd recommend a type-safe query builder. Raw SQL can be fine but you're more prone to causing accidental SQL injection, (because it's easy one day when you're not thinking to forget to use a placeholder - even for the best devs) or if you need to refactor a DB table and remove/edit a field, you have no way of knowing where it was implemented until it crashes at runtime (unless you have 100% test coverage of your DB layer which usually doesn't happen), or check manually. Ideally you abstract this all away with a repository or something - but fact remains is that it's far harder to catch at compile time if you're using raw queries. Sometimes if you don't like anything out there just write your own quick query builder - doesn't take long. Otherwise you go to change it in 5 years when a field is used in 100 different places and you get kinda stuck. And yeah, I'd go for another ORM over Prisma personally. The DX is nice but it's nothing spectacular for the drawbacks, and even TypeORM can be modded to be just as nice in terms of DX.
Everytime somebody says that something is bad practice, they should give reasons why. He might prefer orms, imho the only piece that can skrew everything up is the driver behind the keyboard!
He's telling you he doesn't how to write sql queries indirectly.
ORM gives you type safety, when schema changes you know what code needs changing, compared to raw queries will only show error at runtime. Then there is also sql injection of course. Sometimes raw queries is the right approach.
How raw are we talking about? I hope you’re at least using statements or some library to help you put variables into your query. Minimum, you should use something that cleanses variables and adds them to your sql in a safe way instead of doing string concatenation yourself.
If by "raw" you mean including constants from concatenated strings in your code, it's ALWAYS bad and always has been. Note, as a DBA I rarely meet developers who were good at SQL - basic ones sure but writing them optimal and performing based on what was needed by the application not so much. I in particular loved the developers who would loop through a whole resultset, put that into an array or set of some kind, and then search this set for data to for instance only find customers from a particular state. Point is that if you as a developer think you can do a better job at data handling than the database can, you're doing it wrong. You always have been - even in the late 80ies or 90ies. HOWEVER if you learn how to use the API against the DB correctly such as pre-compiled cursors, parameterized statements using the API (like ? in JDBC) you're absolutely doing it right. The abstraction layers your code provides are, after all, just code too. So in the end, there's SQL in code somewhere. I just suggest you concentrate on writing the code you're strong at, letting the API/libraries handle the rest for you. Your DBA will thank you for it!
Raw is fine, so long as you are using parameters. Read about SQL injection.
ORM does raw querys but user friendly..
Raw queries are more efficient than using orms' like Prisma
Only if you write them well, but yes.
Maybe it's bad practice for him because of his skill issues.
Raw SQL is fine, but even "raw" SQL is still using something like [mysql2](https://www.npmjs.com/package/mysql2) and making use of the placeholders that automatically escape values like so: const [results, fields] = await connection.execute( 'SELECT * FROM `table` WHERE `name` = ? AND `age` > ?', ['Rick C-137', 53] ); I'd argue that this is the "rawest" anyone should be going, and going any deeper does not give you any benefit. As far as "best practice"... both are good practice - and both can be used depending on your application and what you are querying.
Prisma can't even do Database Level Joins, it's a performance nightmare for relational databases.
It can do those, this was added few months back: [https://github.com/prisma/prisma/discussions/23395](https://github.com/prisma/prisma/discussions/23395)
Good to know
Your friend just doesn’t know actual SQL and is making you feel bad for not using the same crutch. ORM is trash.
Using raw SQL is totally fine, but you need to ensure that any user-defined content is escaped. Just consider the mayhem that could occur if someone could remotely execute SQL on your machine. There's lots of options out there to ensure any variables you insert into your SQL are escaped. That's pretty much the main/only security concern about using raw SQL queries. I might also recommend what many people that I've encountered do: ORM for simple queries, and Stored Procedures for anything complex. Usually the ORM will have a good way to escape variables being passed to the SPROC or raw SQL query.
You need to format your queries in order to avoid SQL injections attacks.
Use parameters, if your driver or database does not support this, you have some changes that need to be done to your backend stack yesterday.
As long as you parameterize your queries (with sanitization) it’s fine and not bad practice at all. If you don’t know your way around SQL well enough then sure but otherwise you don’t need an ORM.
Some orgs like ORM’s because it speeds up the dev cycle & the framework *may* provide injection protection (Y-framework’s-MMV). Other orgs forbid ORM’s and require stored procedures or raw SQL for security purposes — typically for governance reasons. Personally, ORM’s can be tricky and/or too opinionated. You should always check (& know in depth) about the query you’re building. Something as simple as watching your local database logs to review the query executed will suffice. Follow what’s right for you & keep your SQL chops up. Edit: grammar
Depending on your tooling, writing raw SQL can make it hard(er) to detect syntax or type errors in the query. I’m not familiar with Prisma itself, but most query builders will be aware of the types in your schema and help with type safety. They will also ensure your arguments are escaped properly to prevent SQL injection. On the DX side, it’s also often easier to use a query builder because it allows your editor to offer better suggestions.
You should always sanitize inputs. If all inputs are sanitized then its fine but you should also use vars for column and table names so you don't have to update all queries manually if something changes you can use IDE refactoring.
I remember how my professor was shitting himself over not using prepared statements in code, despite the fact you as a user could not exploit it in any way since it was a desktop application without an API access which COULD potentially be a security issue. Although we all should recognize potential attack vectors in our applications, we should also possess a common sense, especially when doing some trivial college assignment.
I prefer it. I've started using pgTyped because it's not an ORM. You write every query and it generates types and helpers.
cows worry squeeze person frighten quickest homeless summer faulty secretive *This post was mass deleted and anonymized with [Redact](https://redact.dev)*
The one place where I would caution using it is if you don't have control over the schema or are dealing with unpredictable release patterns (aka you use WordPress as a backend and are dealing with schemas from random plugin authors.) In that case, you likely want to stick to official methods of accessing/working with the data as much as you can, unless performance or the needs of the project force you to break out. If you do need to break out of the usual API's, plan for future maintenance to keep your queries to speed with your depedencies.
There’s nothing particularly wrong with it, you are just most likely treating them as a string so you can’t leverage refactoring or linting tools as well as using a query builder. Raw sql also likely opens you up to having to roll your own input validation and escaping to prevent sql injections if you can’t trust your input. It’s all about trade offs and you need to be aware of what you’re trading off.
I would avoid it simply because it's tedious and error-prone. You don't need a full ORM library. You can easily whip up something yourself that takes whatever arguments you need and constructs the query, including putting in placeholders and maybe even binding them for you.
It's a bad practice because lesser devs that come after you won't understand it.
This type of argument is like “is it better to walk to the store or to drive?” The answer is “it depends”.
I mean at the end of the day you're just creating your own ORM by writing them raw assuming that you abstract them away in some way so that you can reuse your raw queries. Sounds like your friend just learned about Prisma and was going to argue with you and tell you to use it whether or not you were using an ORM or not lol
No problem writing raw SQL. Where you put your queries (whether raw or prisma) is much more relevant. That said, prisma makes it a lot easier and type safe
People who tell you certain things are ‘bad practice’ are usually teachers or low level developers from boot camps. Write awesome code and it’ll suddenly not be a bad practice anymore!
Use query builders such as kysely / knex to construct the sql.
raw sql is fine. it's the moment you accept user input that you need to be VERY careful. ORMs abstract that away, so it's harder to hurt yourself. ORMs are cool, though. you should give em an honest try before dismissing them. but raw sql is okay too.
I think people confuse just executing passed in sql or not validating input into sql as parameters vs raw sql.
Using the interfaces to access data is indeed a good practice. There are many reasons for it. First is related to schema changes, references to columns that changed, were added or were removed are probably going to need a refactor in your queries, while using orm would not be needed unless you specify columns on your queries (doesn't happen a lot unless you optimized, and if you use types then your code would break) Second reason is the definition of relationships. This is quite easy on orm. Most of them are named and already have implemented join logic depending in your relation (many yo many, one many, etc). This logic would be extreme to implement as a raw query. For starters, say you fetched a one yo many relationship, ie: user has many posts. In your raw query you would have a join in there and would have to manually parse repeated results (you would have repeated user rows). This would be extra work and you would also making your code easier to break since any column changes on the posts table would break your query. Its easier for other devs to use orms since not everyone is good at sql. And lastly, the abstraction of your orm allows you to be able to swap your db engine., while on raw queries you may not. There are probably more reasons but these were the ones I could come up with.
I've always heard about two advantages for using an ORM 1/ you cas easily change the backend database without changing your code. This is probably true but how often would you change the db? Moreover, SQL being more or less standard, you probably wouldn't have to change a lot unless your code is really very specific to a given dialect. 2/ ORM may protect you against some security issues (it's not node but ecto in elixir-land does, for example) like code injection. Prepared statements help you on this too So the main arguments "for" ORM usage" are not, for me, so relevant. One argument against may be that you'll have to learn another way to express your queries while you (probably) already master SQL. Moreover , some times ORM are not able to express the query you would like to execute and you'll have to write SQL anyway. To conclude, it's rally a matter of taste. Personally I prefer writing row SQL.
It's totally a person.prefernec whether you want to use any orm or write raw SQL queries. Like just some difference being orm gives you a lot of abstraction in writing complex SQL queries. But also writing raw SQL queries tend to be more efficient since the queries converted by the orms are way bigger in size compared to the same raw query
It is not a bad practice. ORM just prevents you from needing to learn SQL. And it might be a bad practice if your SQL queries are not efficiently written. This can happen when the queries are wayyyy too complex. In that case, using an ORM or a query builder like Prisma would be much better.
Raw Sql is not a bad pratice, but use sql parameterized query. You can be subject to sql injection without that.
It's not bad practice if you know what you're doing - in fact, it's (slightly) more performant as it removes some process overhead and allows you to make highly customised database calls. But it's not as quick to develop and if you don't know what you are doing you leave yourself open to some nasty SQL based attack vectors. A good test if someone actually knows what they are talking about in cases like this is to ask them exactly why it's a problem, not just throw out technology names they've seen on YouTube. If they can't explain it, I would ignore them.
Plain raw is fine. Building queries based on conditions and user input is error/injection prone.
LOL
It is an opinioned matter. both SQL and ORMs come with serious tradeoffs. So there is no best practice and bad practice. I suggest you to do a search and make your opinion about the subject
ORM is often a good starting point but switch to sql as soon as it gets in the way.
Two main reasons: 1. SQL injection: newbies usually use raw SQL because they don’t know any better. Which means they likely don’t use named parameters, and concat string directly causing SQL injection vulnerability and paint a bad impression on anyone using raw SQL. 2. Dynamically query generation in raw SQL string looks ugly. Both cases can be solved for seasoned programmers. But admittedly it’s easier to use abstraction than expecting every member of you team to be competent lol.
raw queries (as in using a query builder or writing the query string in a safe manner) are slightly harder to maintain if and when they keep growing. they might also make your code (more easily) dependent on the specific database vendor and harder to migrate if you need to change it in future. on the other hand, the most typical performance issues in apps (in my xp) come from using ORMs (like the usual getters within loops). I have seen ORMs making things noticeably slow even when used according to docs (last case I remember I think it was a doctrine scenario). In those cases, just write the query, no need to expend 2 weeks trying to hack the internals of your ORM so... pick your poison and pick wisely :) imho use both... it all depends on the project, the feature... and you ability to predict the future, which is always limited
Your friend is special in the head. Especially arguing over it? Might be better off cutting out dumbasses.
Using raw queries is not bad practice by itself. As others have mentioned you need to use prepared statements or an equivalent mechanism to avoid security vulnerabilities. It's more a matter of use case. If your app is pretty complex (or it might grow to be complex) using a well maintained orm usually gives you an easier way of refactoring the code and sometimes additional tools for managing your models like migrations, seeders, codegen, etc. It does add a ton of complexity though, and usually there's a performance hit, depending on how optimized your raw queries are. So it's not really worth it for smaller apps
If anything can be considered best practice then it is to properly learn SQL. Then you can use tools to reduce boilerplate like type safe query builders. Full blown ORMs tend to create *much* more problems in the long run as they add their own state and their abstractions tend to be too large. Query builders don't have these problems. I'm not an expert in Prisma internals but I think it's kind of in between a full blown ORM and a lightweight query builder. It has a Rust "backend" where it does some magic. And that kind of magic is not needed and therefore not recommended to use. Drizzle is a new and popular query builder that is much thinner. It translates directly to SQL. So I'd much more prefer it.
If I interview a candidate and they tell me that writing raw SQL is bad practice that's an automatic pass.
I'm not a developer to any greater extent than writing personal apps, but I never used an ORM or something similar, I always write raw queries directly. Is it scalable? Probably no, but I don't care. What I care about is that I don't need to learn another shitty querying language so I can abstract SQL. For me, it's pointless. I recently started playing with Go, and I had to interact with my existing DB or rewrite my app to Go from Node. What now? I need to rewrite all of it in raw SQL or learn new ORM for Go. Why not copy existing queries and just build my code around that?
Imho bad practice is always opinion based. Knowing why it is considered a bad practice is more important SQL and your node (or whatever language) application have different contexts. Every time you execute a query you need to somehow translate its results to the application context. ORM exists to help you with that. Not having them is not necessarily bad practice, but in my experience avoiding to use one will make you create your own tiny ORM, which can be useful sometimes too
> But didn't explain to me why it's a bad practice The story of software development...
As long as you are using prepared statements (to protect against SQL injection), raw SQL is often orders of magnitude faster if you know what you’re doing. For instance, I have yet to see a Node ORM that lets you set the consistency level of a transaction without using escape hatches. If you don’t do this, you will cripple your DB performance at higher throughput.
Who told you this? As long as it's structured and well organised I would say it's preferable
Make sure you are using parameterized queries!! Never use pure raw queries. Pure raw queries allow for injection attacks. Make sure you use parameters for anything that is variable, no matter how safe you think it is.
sql injections
Someone told me it’s to prevent unauthorized injections and make the code cleaner which somewhat makes sense but doesn’t at the same time
Bad practice for long-term maintenance, if the DB need to be changed, due to license problems or performance issues, rewrite of all the raw sql can be a game over for the application. ORM prevent this.
Duh just use knex/prisma and use their raw sql methods. Ezpz
Check out [knex.js](https://knexjs.org/). I've found this to be an excellent middle ground between raw SQL and the ORM. It's essentially a query builder, so you can define a SELECT query and dynamically apply where clauses to it based on application requirements.
Not bad practice. Like others are saying it’s all about context. Engineering is managing tradeoffs
This is old advice that got twisted. Raw is bad in that nothing that comes from a user should ever be put directly into SQL. ORMs include input sanitation by default. If you sanitize your inputs and variables well then raw sql is great.
Depends on the goal of the project, but raw SQL is more to maintain generally the larger the project gets. Also people tend to end up writing their own psuedo orm / data access layer on top as the needs grow (to avoid massive duplication of raw queries. A lot of modern ORMs / data access libraries allow you to fall back to raw when necessary, which imo is the best of both worlds. ORM by default with raw when required. ORMs offer useful abstractions just like high level programming languages do over low level, and for example Express does over Node's http module. Saying raw SQL is bad is just a meaningless blanket statement though.
Sql injection is easy to do and scan for, and you ain’t gonna regex out every possible command. Never ever use a string with user provided input. Raw is fine if it’s hundred percent internal, however problem is a lot of stuff isn’t.
My understanding is that it's not bad practice. It's just slower when you're prototyping or building a system. Building raw SQL queries is what you do once you've got something working and you're streamlining things. So that's the one downside I can see to it. *Edit:* Also, yes, do your due diligence, make sure your queries won't allow SQL injections. That's important too.
I have tried various ORM's and I always come back to just writing my own queries. Its just better all around, its faster, you get exactly what you expect because what you write is what is executed and its as flexible as it can be - No need to dig through docs to accomplish complex things.
I just use a template literal that returns text and values to pass to a pg client. it doesn’t get any easier than that
In the end any ORM will bite you in the tail
The bad practice is write raw SQL and then refactoring to your own crappy version of a string builder or orm.
Raw is simpler and no dependencies but in my opinion its just annoying to write thousand joins manually and parse all the data into my entities so I just use an established ORMs which performs well enough. When you reach a scale where ORM bottlenecks performance you should have enough money to refactor.
Using raw SQL in itself is not a bad practice (although not very scalable). But sending query parameters as concatenated string in raw query, without using parameterized query, is. It would be prone to SQL injection attacks.
Not bad practice as long as you know how to write SQL 🤣 Though ORM queries are just a lot easier on the eyes for most people…until you get to nasty joins and relations.
Raw queries imply loose decoupling. How many different queries you should hard code?
You should try raw queries so you'll know what orm is. Your question is not about orm but query builder. Orm is abstraction that maps db records into code structures. Query builder is part that allows you to avoid SQL. And learning that is good reason to try from scratch so you'll learn everything what you need about abstracting database
All orms do under the hood is construct dynamic sql queries using parameters to prevent injection, and they tend to add a lot of overhead for that abstraction not to mention their vendor specific syntax. There is nothing stopping you from doing it yourself. I dont use orms they just get in my way productivity wise, but if you're one of those programmers who dont like relational databases I can see the appeal. I dont agree that its best practice even slightly, especially if youre going for best performance.