Raw queries when needed in Express - Deep Dive

Overview - Raw queries when needed

What is it?

Raw queries in Express are direct database commands written as plain text strings. Instead of using helpers or tools that build queries for you, raw queries let you write exactly what the database understands. This gives you full control over what data you ask for or change. It is useful when you need something special that helpers can't do easily.

Why it matters

Raw queries exist because sometimes the usual tools or helpers can't express complex or unique database requests. Without raw queries, developers might be stuck or forced to write inefficient code. Using raw queries lets you solve tricky problems, optimize performance, or use special database features. Without them, apps might be slower or less flexible.

Where it fits

Before learning raw queries, you should understand basic Express routing and how to use database helpers or ORMs (tools that simplify database work). After mastering raw queries, you can explore advanced database optimization, security practices like preventing injection attacks, and complex data handling.

Mental Model

Core Idea

Raw queries are like speaking directly to the database in its own language, bypassing any middleman tools.

Think of it like...

Imagine ordering food at a restaurant: using helpers is like choosing from a menu, while raw queries are like telling the chef exactly how to prepare your dish step-by-step.

┌───────────────┐
│ Express App   │
├───────────────┤
│ Helper/ORM    │
│ (build query) │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Raw Query     │
│ (direct SQL)  │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Database      │
│ (executes SQL)│
└───────────────┘

Build-Up - 6 Steps

1

Foundation

What are raw queries in Express

Concept: Raw queries are plain text commands sent directly to the database from Express.

In Express, when you connect to a database, you usually use helpers or ORMs that build queries for you. Raw queries skip these helpers and send SQL commands as strings. For example, you write 'SELECT * FROM users;' yourself and send it to the database.

Result

You get full control over the exact database command sent.

Understanding raw queries helps you see how database communication works at the simplest level.

2

Foundation

How to run raw queries in Express

Concept: Express uses database libraries that let you send raw SQL strings directly.

Using a database library like 'pg' for PostgreSQL or 'mysql2' for MySQL, you can call a method like client.query('YOUR SQL HERE'). This sends your raw SQL string to the database and returns the result.

Result

Your SQL runs exactly as you wrote it, and you get back the data or confirmation.

Knowing how to send raw queries is the first step to using them effectively.

3

Intermediate

When to choose raw queries over helpers

🤔Before reading on: Do you think raw queries are only for complex queries or also for simple ones? Commit to your answer.

Concept: Raw queries are best when helpers can't express what you need or when you want maximum control or performance.

Helpers are great for common tasks but can limit you. For example, if you need a special SQL feature or a very optimized query, raw queries let you write exactly what you want. They also help when helpers don't support certain database commands.

Result

You can solve problems helpers can't and optimize your app.

Knowing when to use raw queries prevents frustration and unlocks advanced database power.

4

Intermediate

Risks of raw queries and how to avoid them

🤔Before reading on: Do you think raw queries are always safe if you write them yourself? Commit to your answer.

Concept: Raw queries can cause security risks like SQL injection if not handled carefully.

If you insert user input directly into raw queries, attackers can trick your database. To avoid this, always use parameterized queries or escaping methods provided by your database library. Never build SQL strings by just joining user data.

Result

Your app stays safe from common database attacks.

Understanding security risks is crucial to using raw queries responsibly.

5

Advanced

Combining raw queries with helpers in Express

🤔Before reading on: Do you think raw queries replace helpers completely or can they work together? Commit to your answer.

Concept: You can mix raw queries and helpers to get the best of both worlds.

Sometimes you use helpers for simple tasks and raw queries for special cases. Many ORMs let you run raw queries inside their system, so you keep benefits like connection management while writing custom SQL when needed.

Result

Your code stays clean and flexible, using raw queries only when necessary.

Knowing how to combine approaches helps maintain code quality and power.

6

Expert

Performance tuning with raw queries in production

🤔Before reading on: Do you think raw queries always improve performance or can they sometimes hurt it? Commit to your answer.

Concept: Raw queries let you write optimized SQL, but careless use can cause problems.

In production, raw queries can speed up your app by using database-specific features or optimized commands. However, poorly written raw queries can cause slowdowns or errors. Profiling and testing queries is essential. Also, caching results or using prepared statements improves performance and safety.

Result

Your app runs faster and more reliably when raw queries are used wisely.

Understanding performance trade-offs helps you use raw queries as a powerful tool, not a risk.

Under the Hood

When you send a raw query string from Express, the database driver sends it over the network to the database server. The database parses the SQL text, plans how to execute it, runs the commands, and sends back results. This bypasses any abstraction layers that helpers or ORMs provide, so you control the exact SQL sent and received.

Why designed this way?

Raw queries exist because no helper or ORM can cover every possible database command or optimization. Databases have rich, complex languages and features that helpers simplify but sometimes hide. Raw queries give developers full power and flexibility, trading off convenience for control.

┌───────────────┐
│ Express App   │
│ (your code)   │
└──────┬────────┘
       │ sends raw SQL string
       ▼
┌───────────────┐
│ Database      │
│ Driver/Client │
└──────┬────────┘
       │ transmits SQL
       ▼
┌───────────────┐
│ DB Server     │
│ (parses, runs │
│  SQL commands)│
└──────┬────────┘
       │ returns results
       ▼
┌───────────────┐
│ Express App   │
│ (receives data)│
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Do you think raw queries automatically protect against SQL injection? Commit yes or no.

Common Belief: Raw queries are safe because you write the SQL yourself.

Reality: Raw queries are risky if you insert user data directly without parameterization or escaping.

Why it matters: Ignoring this leads to security breaches where attackers steal or damage data.

Quick: Do you think raw queries always make your app faster? Commit yes or no.

Common Belief: Using raw queries always improves performance compared to helpers.

Reality: Raw queries can be slower if poorly written or if they bypass optimizations helpers provide.

Why it matters: Blindly using raw queries can cause slowdowns and bugs.

Quick: Do you think raw queries replace the need for ORMs or helpers? Commit yes or no.

Common Belief: Raw queries make ORMs and helpers unnecessary.

Reality: Helpers simplify many tasks and improve code safety; raw queries complement but don't replace them.

Why it matters: Ignoring helpers leads to more complex, error-prone code.

Quick: Do you think raw queries are only for experts? Commit yes or no.

Common Belief: Only expert developers should use raw queries.

Reality: Beginners can use raw queries safely with guidance and simple examples.

Why it matters: Avoiding raw queries out of fear limits learning and problem-solving options.

Expert Zone

1

Raw queries can be combined with prepared statements to improve security and performance, a detail often missed by beginners.

2

Some ORMs allow embedding raw queries inside their query builders, letting you mix abstraction and control seamlessly.

3

Database-specific SQL dialects mean raw queries must be tailored per database, requiring deep knowledge for cross-database apps.

When NOT to use

Avoid raw queries when simple CRUD operations suffice; use helpers or ORMs for maintainability and safety. Also, avoid raw queries if you cannot guarantee proper input sanitization. Instead, use parameterized queries or ORM methods.

Production Patterns

In production, raw queries are used for complex reports, batch updates, or performance-critical paths. Teams often isolate raw queries in dedicated modules with thorough testing and monitoring to prevent errors and security issues.

Connections

SQL Injection

Raw queries can cause or prevent SQL injection depending on usage.

Understanding raw queries deeply helps grasp how injection attacks happen and how to defend against them.

Abstraction Layers in Software

Raw queries bypass abstraction layers like ORMs.

Knowing when to bypass abstractions teaches when control is more important than convenience.

Natural Language Communication

Raw queries are like speaking directly in the database's language.

This connection shows how mastering a system's native language unlocks full power, similar to learning a foreign language fluently.

Common Pitfalls

#1 Inserting user input directly into raw SQL strings.

Wrong approach:

const query = `SELECT * FROM users WHERE name = '${userInput}'`;
client.query(query);

Correct approach:

const query = 'SELECT * FROM users WHERE name = $1';
client.query(query, [userInput]);

Root cause:

Misunderstanding that raw strings with user data allow attackers to inject harmful SQL.

#2 Using raw queries for all database operations unnecessarily.

Wrong approach:

Writing every query as raw SQL even for simple inserts or selects.

Correct approach:

Use ORM or helpers for simple tasks and raw queries only when needed.

Root cause:

Believing raw queries are always better without considering maintainability and safety.

#3 Writing raw queries without testing or profiling.

Wrong approach:

Deploying complex raw queries without checking performance or correctness.

Correct approach:

Test and profile raw queries locally before production use.

Root cause:

Underestimating the complexity and impact of raw SQL on app performance.

Key Takeaways

Raw queries let you send exact SQL commands from Express to the database, giving full control.

They are powerful for complex or special database tasks but require careful handling to avoid security risks.

Always use parameterized queries or escaping to protect against SQL injection when using raw queries.

Combining raw queries with helpers or ORMs balances control and safety in your app.

In production, raw queries must be tested and optimized to ensure performance and reliability.