Using an Orm or Plain Sql

Using an ORM or plain SQL?

ORMs have some nice features. They can handle much of the dog-work of copying database columns to object fields. They usually handle converting the language's date and time types to the appropriate database type. They generally handle one-to-many relationships pretty elegantly as well by instantiating nested objects. I've found if you design your database with the strengths and weaknesses of the ORM in mind, it saves a lot of work in getting data in and out of the database. (You'll want to know how it handles polymorphism and many-to-many relationships if you need to map those. It's these two domains that provide most of the 'impedance mismatch' that makes some call ORM the 'vietnam of computer science'.)

For applications that are transactional, i.e. you make a request, get some objects, traverse them to get some data and render it on a Web page, the performance tax is small, and in many cases ORM can be faster because it will cache objects it's seen before, that otherwise would have queried the database multiple times.

For applications that are reporting-heavy, or deal with a large number of database rows per request, the ORM tax is much heavier, and the caching that they do turns into a big, useless memory-hogging burden. In that case, simple SQL mapping (LinQ or iBatis) or hand-coded SQL queries in a thin DAL is the way to go.

I've found for any large-scale application you'll find yourself using both approaches. (ORM for straightforward CRUD and SQL/thin DAL for reporting).

Orm or RAW sql which one is better?

ORM and Raw SQL both have their own pros and cons.
Deciding which to use would depend on your app requirements.

When using Prisma, you could use its capabilities but also have an option to resort to Raw Queries if needed by using queryRaw or executeRaw methods.

Prisma is a new kind of ORM that fundamentally differs from traditional ORMs and doesn't suffer from many of the problems commonly associated with these.

In Prisma, you define your models in the declarative Prisma schema which serves as the single source of truth for your database schema and the models in your programming language which could be a huge benefit!

Here are the cases in which prisma would be a good fit: Comparison

raw SQL vs ORM If I already know SQL

I faced something similar recently and will share my experience with you.

I have a mostly DB background (SQL / stored procedures), with a little UI experience. I wrote my first web-app from scratch last year using Postgres and Django and jumped into writing the whole DB interface myself, because that's all I knew. I had never used a built-in ORM before and didn't even know what it was or its benefits.

It was exciting and fun at first, since you have control over all the DB operations and also get a better understanding of how all the code works. After a while, though, I realized some of the cons:

most of the Django / Python modules are designed to use Django's ORM. I had a hard time getting a lot of them to work with my custom interface. One example is they expected to have an "id" column as the PK for all related tables. Another issue was no support for multi-column PKs.
if you create a new object, you need to create multiple sets of stored procedures / functions to manage them; it got tedious after a while
you'll have to create your own upgrade scripts if your schema changes and you want to upgrade an installation that already has data

One of the main advantages of building your own interface is that you have complete control over the physical database model (i.e. you can pick the indexes / keys, custom naming convention, etc).

If you hadn't mentioned the concern about performance, then I would have suggested to just use the provided ORM since it will integrate more easily with other modules and is easier to handle support for new objects.

It really depends on how important these factors are to you:

development time
maintenance
performance
customizability (i.e. custom SQL)
ability to interface with other modules

If performance and customizability are a big concern and you don't plan to incorporate 3rd party modules, then I'd look at writing the interface yourself. Otherwise, I'd say it's easier to just use the ORM.

ORM vs traditional database query, which are their fields?

I can't agree to the common complain about ORMs that they perform bad. I've seen many plain-SQL applications until now. While it is theoretically possible to write optimized SQL, in reality, they ruin all the performance gain by writing not optimized business logic.

When using plain SQL, the business logic gets highly coupled to the db model and database operations and optimizations are up to the business logic. Because there is no oo model, you can't pass around whole object structures. I've seen many applications which pass around primary keys and retrieve the data from the database on each layer again and again. I've seen applications which access the database in loops. And so on. The problem is: because the business logic is already hardly maintainable, there is no space for any more optimizations. Often when you try to reuse at least some of your code, you accept that it is not optimized for each case. The performance gets bad by design.

An ORM usually doesn't require the business logic to care too much about data access. Some optimizations are implemented in the ORM. There are caches and the ability for batches. This automatic (and runtime-dynamic) optimizations are not perfect, but they decouple the business logic from it. For instance, if a piece of data is conditionally used, it loads it using lazy loading on request (exactly once). You don't need anything to do to make this happen.

On the other hand, ORM's have a steep learning curve. I wouldn't use an ORM for trivial applications, unless the ORM is already in use by the same team.

Another disadvantage of the ORM is (actually not of the ORM itself but of the fact that you'll work with a relational database an and object model), that the team needs to be strong in both worlds, the relational as well as the oo.

Conclusion:

ORMs are powerful for business-logic centric applications with data structures that are complex enough that having an OO model will advantageous.
ORMs have usually a (somehow) steep learning curve. For small applications, it could get too expensive.
Applications based on simple data structures, having not much logic to manage it, are most probably easier and straight forward to be written in plain sql.
Teams with a high level of database knowledge and not much experience in oo technologies will most probably be more efficient by using plain sql. (Of course, depending on the applications they write it could be recommendable for the team to switch the focus)
Teams with a high level of oo knowledge and only basic database experience are most probably more efficient by using an ORM. (same here, depending on the applications they write it could be recommendable for the team to switch the focus)

Raw SQL vs OOP based queries (ORM)?

I'd say it's better to try to achieve the objective in the most simple way possible.
If using an ORM has no real added advantage, and the application is fairly simple, I would not use an ORM.
If the application is really about processing large sets of data, and there is no business logic, I would not use an ORM.

That doesn't mean that you shouldn't design your application property though, but again: if using an ORM doesn't give you any benefit, then why should you use it ?

Does ORM for social networking sites makes any sense?

The value of using an ORM is to help speed up development, by automating the tedious work of assigning query results to object fields, and tracking changes to object fields so you can save them to the database. Hence the term Object-Relational Mapping.

An ORM has little value for you regarding database portability, since you only use the one database you deploy on.

The runtime performance aspect of an ORM is no better than, and typically much worse than writing plain SQL yourself. The generic methods of query generation often make naive mistakes and result in redundant queries, as you have mentioned. Again, the benefit is in development time, not runtime efficiency.

Using an ORM versus not using an ORM doesn't seem to make a huge difference for scalability. Other techniques with more bang-for-the-buck for scalability include:

Managing indexes in the RDBMS. Improve as many algorithms as possible from O(n) to O(log₂n).
Intelligent caching architecture.
Horizontal scaling by database partitioning/sharding.
Database load-balancing and replication. Read from slave databases where possible, and write to a single master database. Index slaves and masters differently.
Supplement the RDBMS with complementary technology, such as Sphinx Search.
Vertical scaling by throwing hardware at the problem. Jeff Atwood has commented about this on the StackOverflow podcast.

Some people advocate moving your data management to a distributed architecture using cloud computing or distributed non-relational databases. This is probably not necessary until you get a very large number of users. Once you grow to a certain level of magnitude, all the rules change and you probably can't use an RDBMS anyway. But unless you are the data architect at Yahoo or Facebook or LinkedIn, don't worry about it -- cloud computing is over-hyped.

There's a common wisdom that the database is always the bottleneck in web apps, but there's also a case that improving efficiency on the front-end is at least as important. Cf. books by Steve Souders.

Julia Lerman in Programming Entity Framework (2009), p.503 shows that there's a 220% increase in query execution cost between using a DataReader directly and using Microsoft’s LINQ to Entities.

Also see Jeff Atwood's post on All Abstractions are Failed Abstractions, where he shows that using LINQ is at least double the cost of using plain SQL even in a naive way.

Is it worth using ORM

You should try both using ORM and working without ORM with plain SQL. You will see that 99% [1] of time you are better off with ORM. There are very few projects which are so simple that using ORM is not beneficial.

People you are hearing are wrong or they are using bad ORMs or they are failing to use their choice of ORM properly. Good ORM has well tuned implementations for its data method using, graph algorithms to figure out optimal number of queries for fetching requested data with their relations with as few queries as possible. It wont block event loop unless you have done some slow blocking code in query lifecycle hooks by yourself.

I’ve encountered many times this misconception of ORMs that they will limit your power of doing efficient queries, but it is not true for all ORMs.

Node.js has few light weight ORMs which allows to do common ORMy tasks very easy, but they don’t force you to abstract SQL away. You will still have full power of SQL and even raw queries when ever you need in your hands all the time, but you’ll get to play with relations a lot easier than with plain SQL.

For example you can read / write nested JSON structures efficiently and populate multiple tables easily with single line and to read nested data from database, without having to figure out graph algorithm that optimize number of required queries to fetch the related data and to construct nested data from flat query results.

Finally if you are not using any ORM and set of custom helpers it will harder for other people to get into that code base since it will be full of custom plain SQL + variety helpers.

Disclaimer: I am biased (and know what I’m talking about) since I’ve been maintaining knex query builder for last few years years and I’m involved in development of the only lightweight Node.js ORM which I can recommend: objection.js

[1] Stetson-Harrison 1987

how to implement raw sql query in DRF with paramters

You can use the raw() function to add raw SQL queries.

Person.objects.raw('SELECT id, first_name, last_name, birth_date FROM myapp_person')

Code from documentation

I had a specific issue in regards of this in which I could not use Django's ORM depending on the query complexity. The problem is not due to Django ORM itself, rather the lack of deep knowlege in regards of translating a complex raw SQL query into Django ORM. In a hurry this is the best and quickest way to do.

Using an Orm or Plain Sql