SQL Learnings from Class 35

Your ORM is lying to you about performance. Every abstraction layer adds cost. Sequelize and TypeORM generate queries you never wrote and often never inspected. When your API slows down, the ORM is usually the first suspect - but most developers never look past it. Switch to pg and run raw SQL. Then use EXPLAIN ANALYZE directly from Node.js to see exactly what Postgres is doing. Here is a quick example: const { rows } = await pool.query(` EXPLAIN ANALYZE SELECT u.id, u.name, COUNT(o.id) AS order_count FROM users u JOIN orders o ON o.user_id = u.id GROUP BY u.id `); rows.forEach(row => console.log(row['QUERY PLAN'])); This gives you real execution time, seq scans, index hits - everything your ORM hides from you. Practical takeaway - run EXPLAIN ANALYZE on your five most-called endpoints this week. You will likely find at least one full table scan that a single index can eliminate. Have you ever caught a serious performance issue that your ORM was silently causing? #NodeJS #PostgreSQL #WebDevelopment #BackendEngineering #DatabasePerformance #SQLOptimization

Our API was slow, so we almost scaled everything. We had a simple GET API in a Spring Boot service handling just ~50 requests per second: Fetch orders for a certain user. Order → name OrderDetails → price, offers, discount Only ~100K records in Postgres DB and still the API took seconds. Grafana showed high latency. We were scratching our heads 😕 Everything seemed right: One-to-many relationships Clean design Code review approved Production: nice try 😏 So we did what engineers do: Thought about scaling Considered adding cache Increased DB connections and prayed 😌 But then we decided to dig deeper. And we found out where the problem really lied: 👉 N+1 queries 1 query → fetch orders N queries → fetch order details Our API kept querying the database like there was some kind of vendetta against it. And here’s how we solved it: 👉 @EntityGraph We told JPA upfront: 👉 Fetch both Order and OrderDetails in one join query The results: ❌ Hundreds of queries ✅ 1 clean JOIN query Now our API works fast. Grafana was not mad anymore. Database stopped hating us. Lesson: We didn't have a scaling problem. We had a query problem. 🔑 Takeaways: 👉 Use EntityGraph / NamedEntityGraph 👉 Use JOIN FETCH when needed 👉 Don’t trust default ORM behavior 💬  Do you have stories when you've chased the scaling issue, only to find out that it was a query problem instead? #java #springboot #hibernate #backendengineering #systemdesign #performanceoptimization #database #devlife #techhumor #engineeringhumor

How to Delete and Update Millions of Rows in EF Core Without Loading a Single Entity | by Chris Woodruff https://lnkd.in/eJq2Rg9m #dotnet #entityframework #efcore #csharp #data #database #orm

Your ORM is lying to you about performance. Every time Sequelize or TypeORM "helps" you query a database, it's generating bloated SQL, running unnecessary JOINs, and adding overhead you never asked for. Handwriting SQL in Node.js isn't nostalgia - it's a measurable latency win. Here's a real example. Instead of this: const users = await User.findAll({ where: { active: true }, include: [Profile] }); Write this: const { rows } = await pool.query('SELECT u.id, u.name, p.bio FROM users u JOIN profiles p ON p.user_id = u.id WHERE u.active = true'); The second version runs one predictable query. No magic. No hidden SELECT N+1 traps. No model hydration cost. At scale, these differences compound. Teams switching from ORM-heavy codebases to raw pg or better-sqlite3 queries regularly report 20-40% reductions in average query response time. Practical takeaway - profile your slowest endpoints first, extract the ORM query, rewrite it in raw SQL, and benchmark before and after. The data will make the decision for you. Have you benchmarked ORM-generated queries against handwritten SQL in your current project? #Nodejs #WebDevelopment #BackendDevelopment #SQL #PerformanceOptimization #SoftwareEngineering

𝗘𝗻𝘁𝗶𝘁𝘆 𝗙𝗿𝗮𝗺𝗲𝘄𝗼𝗿𝗸 𝗶𝘀 𝗻𝗼𝘁 𝘆𝗼𝘂𝗿 𝗲𝗻𝗲𝗺𝘆 𝙀𝙣𝙩𝙞𝙩𝙮 𝙁𝙧𝙖𝙢𝙚𝙬𝙤𝙧𝙠 𝙞𝙨 𝙣𝙤𝙩 𝙮𝙤𝙪𝙧 𝙚𝙣𝙚𝙢𝙮. 𝙐𝙨𝙞𝙣𝙜 𝙞𝙩 𝙬𝙧𝙤𝙣𝙜 𝙞𝙨. 𝗜'𝘃𝗲 𝘀𝗲𝗲𝗻 𝘁𝘄𝗼 𝗸𝗶𝗻𝗱𝘀 𝗼𝗳 𝗘𝗙 𝗵𝗮𝘁𝗲 𝗼𝗻𝗹𝗶𝗻𝗲: 1. 𝘋𝘦𝘷𝘦𝘭𝘰𝘱𝘦𝘳𝘴 𝘸𝘩𝘰 𝘯𝘦𝘷𝘦𝘳 𝘶𝘯𝘥𝘦𝘳𝘴𝘵𝘰𝘰𝘥 𝘚𝘘𝘓 𝘣𝘭𝘢𝘮𝘪𝘯𝘨 𝘌𝘍 𝘧𝘰𝘳 𝘴𝘭𝘰𝘸 𝘲𝘶𝘦𝘳𝘪𝘦𝘴 𝘵𝘩𝘦𝘺 𝘸𝘳𝘰𝘵𝘦 2. 𝘋𝘦𝘷𝘦𝘭𝘰𝘱𝘦𝘳𝘴 𝘸𝘩𝘰 𝘶𝘯𝘥𝘦𝘳𝘴𝘵𝘢𝘯𝘥 𝘚𝘘𝘓 𝘱𝘦𝘳𝘧𝘦𝘤𝘵𝘭𝘺 𝘢𝘯𝘥 𝘸𝘢𝘯𝘵 𝘵𝘰 𝘸𝘳𝘪𝘵𝘦 𝘪𝘵 𝘮𝘢𝘯𝘶𝘢𝘭𝘭𝘺 Both groups have a point. Both groups are missing something. 𝗪𝗵𝗲𝗿𝗲 𝗘𝗙 𝗲𝘅𝗰𝗲𝗹𝘀: → CRUD operations on well-modelled entities → Rapid development with migrations → Keeping your domain model free of SQL concerns 𝗪𝗵𝗲𝗿𝗲 𝗘𝗙 𝗳𝗮𝗶𝗹𝘀 𝘆𝗼𝘂 (𝗶𝗳 𝘆𝗼𝘂 𝗹𝗲𝘁 𝗶𝘁): → Complex reporting queries — use Dapper or raw SQL here → Bulk operations — EF.BulkExtensions exists for a reason → When you don't understand the SQL it generates 𝗧𝗵𝗲 𝗯𝗶𝗴𝗴𝗲𝘀𝘁 𝗺𝗶𝘀𝘁𝗮𝗸𝗲: Treating EF as an abstraction that removes the need to understand SQL. It doesn't. It generates SQL. If you don't understand what it generates, you'll write slow queries and not know why. 𝗨𝘀𝗲 𝗘𝗙. 𝗕𝘂𝘁 𝗹𝗲𝗮𝗿𝗻 𝗦𝗤𝗟 𝗳𝗶𝗿𝘀𝘁. 𝗧𝗵𝗲𝗻 𝘂𝘀𝗲 𝗘𝗙. What's your current ORM opinion? Strongly held views welcome. 👇 #EntityFramework #DotNet #SQLServer #ORM #CSharp #DatabasePerformance #BackendDevelopment #Dapper

☕ Week 12 -> Day 01 ☕ Just wrapped up an intense 3.5-hour live session of Web Dev Cohort and dived deep into SQL fundamentals 🚀 💠 What is Database and how data is actually stored 💠 SQL vs NoSQL understanding 💠 Data Types and Constraints (INT, CHECK, IN, BOOLEAN, NOT NULL) 💠 DQL, DDL & DML Basics 💠 PRIMARY KEY, SERIAL, VARCHAR, DATE 💠 Filtering data and Logical Operations (AND, OR) 💠 LIKE, ORDER BY, DESC 💠 DISTINCT values 💠 INSERTING and UPDATING data 💠 Data Aggregation with GROUP BY Clear concepts, real learning, and hands-on understanding 💯 Grateful to Hitesh Choudhary, Piyush Garg and amazing team at Chai Aur Code☕ #backend #sql #database #fullstack #chaicode #webdevelopment #learninginpublic

When “Correct” Queries Don’t Return Correct Results. Recently, I ran into an interesting backend issue where a jpa query was logically correct, the database data was correct, yet some expected results were missing. At first, everything looked fine: The filters were valid The relationships made sense The data existed in the database But the output didn’t match expectations. What was really happening: The issue came from how the ORM translates relationships between entities into SQL. In some cases: A simple condition on a related entity can silently change the type of SQL join being generated Instead of keeping all records and checking conditions safely, the query can become restrictive This can unintentionally exclude valid rows before filtering even happens So even though the logic was correct, the execution plan was not what I expected. The fix: The solution was to make the relationship handling explicit instead of relying on implicit behavior. By explicitly controlling how related data is joined (for example using a LEFT JOIN instead of relying on automatic joins), the query became predictable again and all valid results were correctly included. Key lesson: In ORM-based systems (like JPA/Hibernate): The way you write a condition is not always how it executes Relationships can change query behavior silently Small assumptions can lead to missing or incomplete data Takeaway: Always validate not only what your query says, but how your ORM translates it into SQL. Because sometimes, the issue is not the data or logic… but the hidden behavior behind the abstraction. #SoftwareEngineering #BackendDevelopment #ORM #Hibernate #SpringBoot #SystemDesign #Debugging #Programming #LessonsLearned

Calling .ToList() feels so natural at the end of a LINQ query. But placing it in the wrong spot can silently kill your API's performance. 📉 Following up on my last post about IEnumerable vs IQueryable, the real danger often hides in exactly when the query gets executed. When chaining LINQ methods, execution order is everything: ❌ .ToList().Where(...) ✅ .Where(...).ToList() Here is why placement matters: .ToList() triggers Materialization. It tells Entity Framework: "I am done building the query. Execute it immediately and bring the data into memory." If you call it before your .Where() or .Select() clauses, EF may fetch more data than needed from the database, and the rest of your filtering happens in your server's RAM. ⚠️ If you defer .ToList() to the very end, EF can combine all your conditions into a more efficient SQL query. ⚡ Lower database load. Faster response times. 💡 Lesson learned: Defer execution as long as possible. Build the query first. Execute it last. I’m continuing my series on small .NET changes that actually impact performance. ⚡ Next: AsNoTracking() — when and why to use it 👀 What is your strategy for catching inefficient LINQ queries before they hit production? 👇 #csharp #dotnetcore #softwarearchitecture #efcore #backend

Ever tried building a “global filter API” by joining multiple datasets into a single response? Sounds simple… until it isn’t. Recently, I worked on combining data from multiple sources into one API using native SQL joins. On paper, it looked efficient — one query, one response. Reality was different. ⚠️ Challenges I faced: LEFT JOIN created duplicate and bloated rows SELECT * caused column order mismatches during DTO mapping Handling array fields from DB to Java was tricky Inconsistent data types across sources (BigDecimal vs Double, Timestamp vs LocalDateTime) Trying to map everything into a single DTO led to tight coupling The biggest pain: splitting combined query results back into meaningful structures 💡 Key learnings: Avoid SELECT * in complex joins — always map explicitly Native queries + DTO mapping = order matters more than you think One “global” response is not always a good design Sometimes, separate APIs or structured responses are cleaner and scalable Debugging mapping issues can take more time than writing the query itself In the end, what seemed like a query problem turned out to be a design problem. How do you handle multi-source joins in your APIs? 🤔 #Java #SpringBoot #BackendDevelopment #SQL #DatabaseDesign #APIDesign #Microservices #SoftwareEngineering #CodingChallenges #Developers #TechLearning #CleanCode

Good architecture pays for itself. I just finished integrating 𝗣𝗼𝘀𝘁𝗴𝗿𝗲𝗦𝗤𝗟 into my Mini Message Board project, moving away from temporary local storage to a persistent database. The most satisfying part? It took almost no time at all. Because I had strictly followed the 𝗠𝗩𝗖 (𝗠𝗼𝗱𝗲𝗹-𝗩𝗶𝗲𝘄-𝗖𝗼𝗻𝘁𝗿𝗼𝗹𝗹𝗲𝗿) pattern from the start, I didn't have to touch a single line of code in my views or my main application logic. I only had to update the controllers. 𝗪𝗵𝗮𝘁 𝗜 𝗹𝗲𝗮𝗿𝗻𝗲𝗱 𝗶𝗻 𝘁𝗵𝗶𝘀 𝘀𝗽𝗿𝗶𝗻𝘁: • 𝗗𝗮𝘁𝗮𝗯𝗮𝘀𝗲 𝗠𝗶𝗴𝗿𝗮𝘁𝗶𝗼𝗻: Created a custom script to initialize my tables and seed the data, making the deployment process repeatable. • 𝗠𝘂𝗹𝘁𝗶-𝗣𝗮𝗮𝗦 𝗗𝗲𝗽𝗹𝗼𝘆𝗺𝗲𝗻𝘁: I hosted the database on a different service than the backend. Managing those connections and environment variables was a great lesson in distributed systems. • 𝗨𝗫 𝘄𝗶𝘁𝗵 𝗘𝗝𝗦: Since I’m using server-side rendering, I used Express Validator to handle "sticky" form data. If a user makes a mistake, the form doesn't clear, it stays populated with their previous input alongside a helpful error message. It’s one thing to build an app that works. It’s another to build one that is easy to upgrade. Now that I've moved to SQL, I’m seeing exactly why relational databases are the industry standard for data integrity. #PostgreSQL #NodeJS #WebDevelopment #Database #TheOdinProject #BackendEngineer