CLI Command opening new db connection for every route

[AjaniBilby]

Describe the bug For every route the npm task opens a new database connection instead of continuing to use the existing one from other routes.

npx remix-sitemap
🔍 Found config file: file:///srv/APP_NAME/remix-sitemap.config.js
🔍 Generating sitemap...
🔍 Found routes: root, routes/admin.course.$courseID.$unitID.$lessonID.delete, routes/admin.transaction_.$transactionID.card.delete, routes/admin.transaction_.$transactionID.card.scrub, routes/admin.course.$courseID.$unitID.$lessonID, routes/admin.account_.$accountID.transactions, routes/admin.course.$courseID.$unitID.delete, routes/dictionary_.$dictID_.edit.bulk-import, routes/course_.$courseID_.$unitID.$lessonID, routes/org.$orgID.profile_.$profileID.edit, routes/admin.transaction_.$transactionID, routes/dictionary_.$dictID_.media.$order, routes/admin.account_.$accountID.delete, routes/dictionary_.$dictID_.edit.delete, routes/list_.view.$listID_.edit_.delete, routes/org.$orgID.group_.$groupID_.edit, routes/dictionary_.$dictID_.edit.signs, routes/resource_.$resID_.edit.category, routes/resource_.$resID_.edit.language, routes/admin.analytic.sign.individual, routes/admin.course.$courseID.$unitID, routes/category_.$catID_.edit_.delete, routes/dictionary_.$dictID_.thumbnail, routes/org.$orgID.profile.bulk-delete, routes/sign.$signID_.edit_.dictionary, routes/admin.course.$courseID.delete, routes/admin.region.$regionID.delete, routes/login.profile.$organisationID, routes/admin.analytic.subscriptions, routes/admin.referral.$refID.delete, routes/org.$orgID_.invite.$inviteID, routes/resource_.$resID_.edit.signs, routes/admin.analytic.sign.weekday, routes/admin.price.$priceID.delete, routes/admin.vocab.$vocabID.delete, routes/org.$orgID.profile.qr-codes, routes/sign.$signID_.edit.category, routes/sign.$signID_.edit.language, routes/sign.$signID_.edit_.keyword, routes/admin.post_.$postID.delete, routes/course_.$courseID_.$unitID, routes/dictionary_.$dictID._index, routes/org.$orgID.group_.$groupID, routes/resource_.$resID.thumbnail, routes/transaction.$transactionID, routes/admin.account_.$accountID, routes/api.asp.sign.$signID.poll, routes/dictionary_.$dictID.signs, routes/dictionary_.$dictID_.edit, routes/game_.select.$mode.$catID, routes/resource_.$resID.download, routes/sign.$signID.video.$order, routes/sign.$signID_.edit.delete, routes/sign.$signID_.edit.region, routes/admin.keyword.$keywordID, routes/admin.sign.missing.$type, routes/list_.view.$listID_.edit, routes/account.change-password, routes/account_.retrieve-email, routes/list_.pdf.custom-poster, routes/sign.$signID.image-crop, routes/sign.$signID.image-full, routes/about.babysign.product, routes/about.terms-conditions, routes/admin.course.$courseID, routes/admin.organisation.new, routes/admin.region.$regionID, routes/blob.$blobID.thumbnail, routes/category_.$catID_.edit, routes/org.$orgID.permissions, routes/org.$orgID.transaction, routes/quiz_.$id_.edit.delete, routes/resource_.$resID_.edit, routes/subscribe.organisation, routes/about.babysign._index, routes/admin.referral.$refID, routes/explore_.sign.popular, routes/explore_.sign.updated, routes/about.babysign.signs, routes/about.babysign.songs, routes/account.update-email, routes/admin.keyword._index, routes/admin.price.$priceID, routes/admin.vocab.$vocabID, routes/explore_.sign.random, routes/list_.pdf.dictionary, routes/list_.pdf.flash-card, routes/login.profile._index, routes/sign.$signID.clipart, routes/account.transaction, routes/admin.analytic.list, routes/admin.analytic.sign, routes/admin.blob.priority, routes/admin.post_.$postID, routes/dictionary_.$dictID, routes/list_.pdf.circle-it, routes/list_.share.$listID, routes/sign.$signID_.embed, routes/about.dictionaries, routes/admin.organisation, routes/article.$articleID, routes/game_.match.$catID, routes/list.custom-poster, routes/list_.pdf.matching, routes/list_.pdf.spell-it, routes/list_.view.$listID, routes/org.$orgID.profile, routes/sign.$signID.photo, routes/sign.$signID_.edit, routes/subscribe.personal, routes/account_.retrieve, routes/admin.blob.upload, routes/admin.sign_.media, routes/admin.transaction, routes/api.random.phrase, routes/course_.$courseID, routes/explore_.sign.new, routes/list_.pdf.dominos, routes/org.$orgID._index, routes/admin.dictionary, routes/blob.$blobID.raw, routes/org.$orgID.admin, routes/org.$orgID.group, routes/org.$orgID.seats, routes/resource_.$resID, routes/subscribe._index, routes/admin.mail-test, routes/admin.normalise, routes/category.$catID, routes/category._index, routes/choose-language, routes/list.dictionary, routes/list.flash-card, routes/quiz_.$id_.edit, routes/referral.$refID, routes/about.verified, routes/admin.category, routes/admin.referral, routes/admin.sign-old, routes/list.circle-it, routes/login.personal, routes/about.keysign, routes/admin.account, routes/admin.keyword, routes/explore_.sign, routes/list.matching, routes/list.spell-it, routes/login.profile, routes/news_.$newsID, routes/about._index, routes/about.auslan, routes/account.edit, routes/admin._index, routes/admin.course, routes/admin.demask, routes/admin.region, routes/api.category, routes/game_.number, routes/list.dominos, routes/login._index, routes/sign.$signID, routes/admin.price, routes/admin.stats, routes/admin.vocab, routes/api.keyword, routes/healthcheck, routes/list._index, routes/about.font, routes/admin.blob, routes/admin.list, routes/admin.mail, routes/admin.post, routes/admin.quiz, routes/admin.sign, routes/dictionary, routes/game_.math, routes/game_.time, routes/org.$orgID, routes/org._index, routes/util_.beta, routes/quiz_.$id, routes/subscribe, routes/browse.$, routes/category, routes/resource, routes/tutorial, routes/account, routes/contact, routes/desktop, routes/profile, routes/_index, routes/course, routes/logout, routes/search, routes/about, routes/admin, routes/cdn.$, routes/login, routes/eula, routes/game, routes/join, routes/list, routes/news, routes/api, routes/org, routes/pwa
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
🔌 setting up prisma client to redacted.postgres.database.com:25060
/srv/APP_NAME/node_modules/esbuild/lib/main.js:1073
        return callback(new Error(error), null);
                        ^

Error: The service was stopped: write EPIPE
    at /srv/APP_NAME/node_modules/esbuild/lib/main.js:1073:25
    at responseCallbacks.<computed> (/srv/APP_NAME/node_modules/esbuild/lib/main.js:697:9)
    at afterClose (/srv/APP_NAME/node_modules/esbuild/lib/main.js:687:28)
    at /srv/APP_NAME/node_modules/esbuild/lib/main.js:2129:11
    at onwriteError (node:internal/streams/writable:425:3)
    at process.processTicksAndRejections (node:internal/process/task_queues:84:21)

Node.js v20.7.0

Almost every single route imports ~/db.server.ts which is where the database connection is initalized

import { PrismaClient } from "@prisma/client";
import invariant from "tiny-invariant";

let prisma: PrismaClient;

declare global {
    var __db__: PrismaClient;
}

// this is needed because in development we don't want to restart
// the server with every change, but we want to make sure we don't
// create a new connection to the DB with every change either.
// in production we'll have a single connection to the DB.
if (process.env.NODE_ENV === "production") {
    prisma = getClient();
} else {
    if (!global.__db__) {
        global.__db__ = getClient();
    }
    prisma = global.__db__;
}

function getClient() {
    const { DATABASE_URL } = process.env;
    invariant(typeof DATABASE_URL === "string", "DATABASE_URL env var not set");

    const databaseUrl = new URL(DATABASE_URL);
    console.log(`🔌 setting up prisma client to ${databaseUrl.host}`);
    // NOTE: during development if you change anything in this function, remember
    // that this only runs once per server restart and won't automatically be
    // re-run per request like everything else is. So if you need to change
    // something in this file, you'll need to manually restart the server.
    const client = new PrismaClient({
        datasources: {
            db: {
                url: databaseUrl.toString(),
            },
        },
    });
    // connect eagerly
    // client.$connect(); disabled because it always causes sitemap generation to crash
    //   but with it disabled, it only crashes most, but not all of the time...

    return client;
}

export { prisma };

Interestingly this only occurs when running on the production server, but not in my local environment which is extra odd.

[AjaniBilby]

When reading the sitemap functions in each route, are they done in parrallel or in squence? Promise.all() Because if they are bulk dispatched all at once, this would create a connection overflow in Prisma

[AjaniBilby]

These two lines sitemaps.ts:150, sitemaps.ts:167, while they are not the direct cause, they will exassurbate the issue, because it means it will attempt to run the sitemap function for every single route at once. When you attempt to run multiple queries at once in prisma it will attempt to streamline as many as possible. So if I have a connection limit of 10, it will open 10 connections.

Then since I have 207 different routes, it will open only 10 connections and as each query completes it will then schedule another awaiting one on the new open available connection, but the time it gets near to the end it starts timing out because the queries aren't able to be executed on a db connection.

I'll make a PR implementing a rate limiter which reads from a config to limit the promise all execution based on a config varaible if present

[AjaniBilby]

With further testing this issue does occur when using

prisma = getClient();

Instead of

if (!global.__db__) {
    global.__db__ = getClient();
}
prisma = global.__db__;

Which implies that global variables shared between different files importing it are not actually being shared, and multiple unique instances are being loaded.

The update in the PR does mitigate the issue a bit