Code Snippets

Here you will find a collection of code snippets that I have found useful in my projects.

CSS for rendering markdown

This snippet contains CSS for rendering markdown

css markdown-css.css

              /* General Styles */
    .markdown-body {
      font-family: "Sofia Sans", sans-serif;
      line-height: 1.6;
      color: #333;
      text-align: justify;
      padding: 1rem;
      font-optical-sizing: auto;
      font-weight: 500;
      font-style: normal;
    }

    /* Headings */
    h1 {
      font-size: 2.5rem;
      font-family: "Sofia Sans", sans-serif;
      margin-bottom: 1rem;
    }

    .markdown-body h2 {
      font-size: 2rem;
      font-weight: bolder;
      margin-bottom: 1rem;
      border-bottom: 1px solid #eee;
      padding-bottom: 0.5rem;
    }

    .markdown-body h3 {
      font-size: 1.75rem;
      font-weight: bolder;
      margin-bottom: 0.75rem;
    }

    .markdown-body h4 {
      font-size: 1.5rem;
      font-weight: bolder;
      margin-bottom: 0.5rem;
    }

    .markdown-body h5 {
      font-size: 1.25rem;
      font-weight: bolder;
      margin-bottom: 0.5rem;
    }

    .markdown-body h6 {
      font-size: 1rem;
      font-weight: bolder;
      margin-bottom: 0.5rem;
    }

    /* Paragraphs */
    .markdown-body p {
      font-size: 1.1rem;
      margin-bottom: 1rem;
    }

    /* Lists */
    .markdown-body ul {
      margin-bottom: 1rem;
    }

    .markdown-body ul li {
      margin-bottom: 0.5rem;
      padding-left: 1rem;
    }

    .markdown-body ul {
      list-style-type: "\235f";
    }

    /* Links */
    .markdown-body a {
      color: #0366d6;
      text-decoration: none;
    }

    .markdown-body a:hover {
      text-decoration: underline;
    }

    /* Blockquotes */
    .markdown-body blockquote {
      margin: 1rem 0;
      padding: 0.5rem 1rem;
      background-color: #f9f9f9;
      border-left: 4px solid #ccc;
      font-family: "Lucida Sans", "Lucida Sans Regular", "Lucida Grande",
        "Lucida Sans Unicode", Geneva, Verdana, sans-serif;
    }

    /* Code Blocks */
    .markdown-body pre {
      padding: 1rem;
      border-radius: 5px;
      overflow: auto;
      margin-bottom: 1rem;
    }

    .markdown-body code {
      padding: 0.2rem 0.4rem;
      border-radius: 3px;
    }

    /* Tables */
    .markdown-body table {
      width: 100%;
      border-collapse: collapse;
      margin-bottom: 1rem;
    }

    .markdown-body th,
    .markdown-body td {
      padding: 0.5rem;
      border: 1px solid #ddd;
      text-align: left;
    }

    .markdown-body th {
      background-color: #f3f3f3;
    }

    /* Horizontal Rules */
    .markdown-body hr {
      border: 0;
      height: 1px;
      background: #eee;
      margin: 2rem 0;
    }

What is N+1 query problem

this code snippet expands on what is N+1 query problem - and why does it happen

markdown markitdown.md

              The **N+1 query problem** is a performance issue that occurs when an application executes a separate database query for each record in a result set, leading to inefficient database access patterns.

### **How It Happens**
The problem arises in scenarios where:
1. A query retrieves a list of parent entities.
2. For each parent entity, a separate query is executed to fetch related child entities.

This results in **N+1 queries**, where:
- **1 query** is executed to fetch the list of parent entities.
- **N queries** are executed, one for each parent, to fetch its related child entities.

---

### **Example**

#### **Scenario: Blog Posts and Comments**

Imagine you want to display a list of blog posts along with their comments:

1. **Query to fetch posts:**
   ```sql
   SELECT id, title FROM posts;
   ```

   Suppose this query returns 10 posts.

2. **Query to fetch comments for each post:**
   For each post, you execute:
   ```sql
   SELECT * FROM comments WHERE post_id = <post_id>;
   ```

   If there are 10 posts, this results in 10 additional queries, one for each post.

---

### **Problems with N+1 Queries**
- **Performance Impact**: Each query introduces latency and consumes resources.
- **Scalability Issues**: As the number of parent entities increases, the number of queries grows linearly, severely impacting performance on large datasets.
- **Increased Database Load**: The database handles a higher number of queries than necessary.

---

### **Solutions**

#### **1. Use Joins**
Combine the parent and child queries into a single query using a `JOIN`.

Example:
```sql
SELECT p.id AS post_id, p.title, c.id AS comment_id, c.content
FROM posts p
LEFT JOIN comments c ON p.id = c.post_id;
```

This retrieves posts and their comments in one query.

---

#### **2. Use Subqueries with `IN`**
Instead of querying for each parent, fetch all related child entities in one go using a `WHERE IN` clause.

Example:
```sql
SELECT * FROM comments WHERE post_id IN (SELECT id FROM posts);
```

---

#### **3. Use Batch Loading**
In ORMs like Hibernate, Django ORM, or Sequelize, use "eager loading" or "preloading" to load related entities in fewer queries.

- **Lazy Loading** (causes N+1): Fetches related data when accessed, leading to multiple queries.
- **Eager Loading** (fixes N+1): Fetches related data in a single query upfront.

Example (Django ORM):
```python
# Causes N+1 problem
posts = Post.objects.all()
for post in posts:
    comments = post.comments.all()

# Fix with select_related or prefetch_related
posts = Post.objects.prefetch_related('comments')
```

---

#### **4. Denormalization**
Store precomputed or denormalized data in the database to avoid frequent joins or lookups.

Example:
- Add a `comment_count` column in the `posts` table to avoid fetching all comments for counting.
- Store related data in a JSON column or materialized view.

---

### **Key Takeaway**
The N+1 query problem occurs when an application issues a query for each record in a result set. It can be mitigated using:
- Joins
- Subqueries
- ORM features like eager loading
- Denormalized or cached data

Efficient query design and ORM optimization are critical to avoid this issue.

Upsert data into tables

Below ways are the most common ways to upsert the data into tables using 1. INSERT ... ON CONFLICT DO NOTHING or DO UPDATE 2. MERGE INTO table USING table2 ON clause WHEN MATCHED THEN 3.

sql x.sql

              -- Upsert using ON CONFLICT DO UPDATE
INSERT INTO my_table (id, name, age)
VALUES (1, 'John Doe', 30)
ON CONFLICT (id) 
DO UPDATE SET name = EXCLUDED.name, age = EXCLUDED.age;

-- Merge using Merge into table query
MERGE INTO my_table AS target
USING (SELECT 1 AS id, 'John' AS name, 100 AS value) AS source
ON target.id = source.id
WHEN MATCHED THEN
  UPDATE SET name = source.name, value = source.value
WHEN NOT MATCHED THEN
  INSERT (id, name, value) VALUES (source.id, source.name, source.value);


-- Upsert using ON CONFLICT DO NOTHING
INSERT INTO my_table (id, name, age)
VALUES (1, 'John Doe', 30)
ON CONFLICT (id) 
DO NOTHING;


-- Upsert with complex logic in DO UPDATE
INSERT INTO my_table (id, name, age)
VALUES (1, 'John Doe', 30)
ON CONFLICT (id)
DO UPDATE 
SET name = EXCLUDED.name,
    age = CASE 
            WHEN EXCLUDED.age > my_table.age THEN EXCLUDED.age
            ELSE my_table.age
          END;

-- upsert using WITH clause and using only in specific conditions 
WITH upsert AS (
  UPDATE my_table
  SET name = 'John', value = 100
  WHERE id = 1
  RETURNING *
)
INSERT INTO my_table (id, name, value)
SELECT 1, 'John', 100
WHERE NOT EXISTS (SELECT * FROM upsert);

script commands to kill tasks running on port number

the code snippet kills the tasks already running on the port number. this is highly required because webstorm does not kill the terminal processes.

sh taskkill

              #Windows
netstat -ano | findstr :portNumber
taskkill /PID :processIdFromAbove /F

#Linux
sudo lsof -i :portNumber
kill :processId