17 posts tagged with "Node.js"

Encountered this issue while building an e-commerce analytics platform for a client. Here's the root cause and solution.

TL;DR​

In Drizzle ORM's sql template tag, sql.join(values.map(v => sql(v))) parameterizes all values. If the values array contains SQL expressions (like date_trunc('week', '2026-05-17'::date)::date), PostgreSQL treats them as plain strings and throws invalid input syntax for type date. SQL expressions must use sql.raw() or be written separately in the template.

The Problem​

Data collection pipeline: Chrome extension → CCLHub server → Analytics API → PostgreSQL. Symptoms:

1. CCLHub logs show correct collected data (uv: 403, payAmt: 19478.47)
2. Analytics API returns 200 success
3. But database query shows all zeros: uv: 0, pay_amt: 0.00

-- Actual database data
 report_date | uv  | pay_amt  | reveal_cnt
-------------+-----+----------+------------
 2026-05-12  | 392 |  7333.67 |      11879  -- old data fine
 2026-05-13  |   0 |     0.00 |          0  -- new data all zeros!

Analytics error log reveals:

PostgresError: invalid input syntax for type date:
  "date_trunc('week', '2026-05-17'::date)::date"

Root Cause​

The original code mixed parameterized values with SQL expressions:

// ❌ Problem code
const insertVals: (string | number | null)[] = [
  String(shop_id),
  String(platform_id),
  reportDate,
  tenant_id,
  `date_trunc('week', '${reportDate}'::date)::date`, // ← SQL expression
];

// sql.join parameterizes ALL values, including the date_trunc expression
await db.execute(sql`
  INSERT INTO table (..., week_start_date)
  VALUES (${sql.join(insertVals.map(v => sql`${v}`), sql`,`)})
  ...
`);

Generated SQL:

-- PostgreSQL receives $5 as a literal string value
INSERT INTO table (..., week_start_date)
VALUES ($1, $2, $3, $4, $5, ...)
-- $5 = "date_trunc('week', '2026-05-17'::date)::date"  ← treated as string!

PostgreSQL tries to parse "date_trunc('week', '2026-05-17'::date)::date" as a date type → error.

Why zeros instead of an error? Because the same table has a separate inquiry INSERT (PARTIAL UPSERT) that succeeded, creating rows with dashboard columns defaulting to 0. The daily report UPSERT failed but didn't roll back the existing rows.

Solution​

Separate SQL expressions from parameterized values using sql.raw() or direct template embedding:

// ✅ Fix: separate parameterized values from SQL expressions
const insertCols = ['shop_id', 'platform_id', 'report_date', 'tenant_id'];
const insertVals: (string | number | null)[] = [
  String(shop_id), String(platform_id), reportDate, tenant_id,
];

// 19 data columns parameterized normally
for (const [apiKey, dbCol] of Object.entries(DAILY_COLUMNS)) {
  insertCols.push(dbCol);
  insertVals.push(row[apiKey] != null ? String(row[apiKey]) : '0');
}

// week_start_date uses SQL expression, NOT in parameterized array
await db.execute(sql`
  INSERT INTO table (${sql.raw(insertCols.join(', '))}, week_start_date)
  VALUES (
    ${sql.join(insertVals.map(v => sql`${v}`), sql`,`)},
    date_trunc('week', ${reportDate}::date)::date  -- ← directly in template
  )
  ...
`);

Key distinction:

Caveats​

Encountered this issue while building a SaaS authentication system for a client. Here's the root cause and solution.

TL;DR​

In Node.js ES Modules, import statements execute before dotenv.config(). If module-level code reads process.env.JWT_SECRET, it gets undefined, causing JWT signing to use the string "undefined" as the secret — no errors thrown, but all token verification fails. The fix: lazy initialization.

The Problem​

JWT login returns 200, but all subsequent requests return 401. Investigation reveals:

1. Tokens generated at login can't be verified by jwtVerify()
2. Every server restart invalidates all previously issued tokens
3. console.log(process.env.JWT_SECRET) outputs undefined

// jwt.ts — module-level code
import crypto from 'crypto';

// ❌ This line executes BEFORE dotenv.config(), JWT_SECRET is undefined
const SECRET = crypto.createSecretKey(
  new TextEncoder().encode(process.env.JWT_SECRET)
);

The worst part: no error is thrown. new TextEncoder().encode(undefined) encodes the string "undefined" into bytes, producing a valid but wrong secret key.

Root Cause​

ES Module import statements are statically hoisted:

// server.ts (entry file)
import { router } from './routes/auth';      // ← runs first
import { authenticateToken } from './middleware/auth'; // ← runs first

dotenv.config(); // ← runs AFTER all imported modules execute

Execution order:

1. Node.js scans all import statements and builds the dependency graph
2. Executes all imported modules' top-level code depth-first (jwt.ts's const SECRET = ... runs here)
3. Returns to server.ts, runs dotenv.config()
4. Now .env is loaded into process.env

So jwt.ts module-level code reads process.env.JWT_SECRET as undefined.

Solution​

Option 1: Lazy Initialization (Recommended)​

Move secret initialization into a function — env var is read on first call, not at import time:

import crypto from 'crypto';

let _secret: crypto.KeyObject | null = null;

function getSecret(): crypto.KeyObject {
  if (!_secret) {
    const secretValue = process.env.JWT_SECRET;
    if (!secretValue) {
      throw new Error('JWT_SECRET environment variable not set');
    }
    _secret = crypto.createSecretKey(
      new TextEncoder().encode(secretValue)
    );
  }
  return _secret;
}

// Use getSecret() everywhere the key is needed
export async function generateToken(payload: any): Promise<string> {
  return new SignJWT(payload)
    .setProtectedHeader({ alg: 'HS256' })
    .sign(getSecret()); // ← deferred until runtime
}

No dependency on entry file import order — safe regardless of when called.

Option 2: Call dotenv at the Very Top of Entry File​

// server.ts — ensure these lines come before ALL imports
import 'dotenv/config'; // or require('dotenv').config()
import express from 'express';
// ...other imports

Limitation: If another entry point (cron job, worker) forgets this line, the bug resurfaces.

Caveats​

Encountered this issue while building a SaaS analytics platform for a client. Here's the root cause and solution.

TL;DR​

In Node.js ESM mode, import('./path/to/module') doesn't auto-resolve ./path/to/module.js. If the TypeScript build output is missing .js extensions, the module throws ERR_MODULE_NOT_FOUND. When this import is inside deferred logic (timers, conditionals), the app starts fine but crashes later — PM2 shows a climbing restart count.

Fix: Ensure all ESM dynamic imports include .js extensions, and automate this in the build pipeline.

Problem​

PM2 showed the app restarting continuously:

│ name             │ ↺    │ status │ uptime │
│ analytics-api    │ 9    │ online │ 28m    │

Error log repeated every few minutes:

Error [ERR_MODULE_NOT_FOUND]: Cannot find module '/app/dist/domains/video/cleanup'
  imported from /app/dist/server.js

But the file clearly exists:

$ ls dist/domains/video/
cleanup.js  executor.js  queue.js

Root Cause​

ESM doesn't auto-resolve file extensions​

Node.js CommonJS (require()) automatically tries .js, .json, and other extensions. ESM (import) does not.

// ❌ ESM can't find the module
import('./domains/video/cleanup')
// Node.js looks for: ./domains/video/cleanup (exact path, no extension)
// Actual file:       ./domains/video/cleanup.js

// ✅ Must include .js extension
import('./domains/video/cleanup.js')

Deferred imports hide the problem​

The import was inside a deferred execution:

// server.ts — doesn't execute immediately on startup
import('./domains/video/cleanup.js').then(({ startCleanupScheduler }) => {
  startCleanupScheduler(); // triggers seconds later
});

The app starts successfully (DB connection, port binding all fine). When the timer fires, the import fails → process crashes → PM2 restarts → starts fine again → timer fires again → crashes again. This creates a crash loop.

Why did it work before?​

Previous deployment used a build script that included a post-build step to fix import paths. One deployment skipped this step and deployed raw tsc output — tsc doesn't modify import paths in output files.

Solution​

1. Write .js extensions in TypeScript source​

TypeScript officially recommends writing .js extensions even in .ts files:

// ✅ Write .js even in .ts source files
import('./domains/video/cleanup.js').then(({ startCleanupScheduler }) => {
  startCleanupScheduler();
});

2. Automated post-build fix (recommended)​

Add an import-fixing script to the build pipeline:

{
  "scripts": {
    "build": "tsc && node fix-imports.js"
  }
}

Core logic of fix-imports.js:

import { readFileSync, writeFileSync, readdirSync } from 'fs';
import { join } from 'path';

function fixImports(dir) {
  for (const file of readdirSync(dir, { withFileTypes: true })) {
    const fullPath = join(dir, file.name);
    if (file.isDirectory()) {
      fixImports(fullPath);
    } else if (file.name.endsWith('.js')) {
      let content = readFileSync(fullPath, 'utf8');
      // Fix dynamic imports
      const fixed = content.replace(
        /import\(['"](\.[^'"]+)['"]\)/g,
        (match, path) => path.endsWith('.js') ? match : match.replace(path, path + '.js')
      );
      // Fix static imports
      const fixed2 = fixed.replace(
        /from\s+['"](\.[^'"]+)['"]/g,
        (match, path) => path.endsWith('.js') ? match : match.replace(path, path + '.js')
      );
      if (fixed2 !== content) {
        writeFileSync(fullPath, fixed2);
      }
    }
  }
}

The build pipeline automatically appends .js to all relative import paths, keeping TypeScript source extension-free while preventing module-not-found errors in ESM deployments.

TL;DR​

Chrome extensions submitting collected data via API write directly to the production database — even during development and testing. A 3-layer DRY-RUN switch solves this: set an env variable in .env.development → client reads it and adds an X-Dry-Run header → server intercepts the header and returns a data preview without writing. Production never sets the variable, so it's completely unaffected.

Encountered this issue while upgrading Node.js version for an enterprise SaaS system. Documenting the root cause and solution.

TL;DR​

Node.js v24 changed Web Crypto API implementation. The jose library now requires keys to be KeyObject or CryptoKey type. Wrap your key with crypto.createSecretKey() to fix.

TL;DR​

Module-level const URL = process.env.SERVICE_URL executes before dotenv loads, resulting in undefined. Use getter functions const getUrl = () => process.env.SERVICE_URL for lazy evaluation.