Is D1 scalable for large applications ? how does it compare to postgres

$ bun db:migrate
$ drizzle-kit migrate
No config path provided, using default 'drizzle.config.ts'
Reading config file 'G:\GitHub\BuzzTrip\apps\web\drizzle.config.ts'
Using G:\GitHub\BuzzTrip\apps\web\.wrangler\state\v3\d1\miniflare-D1DatabaseObject\097dd93c38860e13c35b3676a4f13f4950f08ed9c239ec99db5716131b0e2718.sqlite
[⣷] applying migrations...Error: Can't find meta/_journal.json file
    at readMigrationFiles (G:\GitHub\BuzzTrip\node_modules\src\migrator.ts:41:9)
    at migrate (G:\GitHub\BuzzTrip\node_modules\src\libsql\migrator.ts:10:21)
    at migrateFn (G:\GitHub\BuzzTrip\node_modules\drizzle-kit\bin.cjs:72211:18)
    at Object.handler (G:\GitHub\BuzzTrip\node_modules\drizzle-kit\bin.cjs:83593:11)
    at async run (G:\GitHub\BuzzTrip\node_modules\drizzle-kit\bin.cjs:82064:7)
error: script "db:migrate" exited with code 1

$ bun db:migrate
$ drizzle-kit migrate
No config path provided, using default 'drizzle.config.ts'
Reading config file 'G:\GitHub\BuzzTrip\apps\web\drizzle.config.ts'
Using G:\GitHub\BuzzTrip\apps\web\.wrangler\state\v3\d1\miniflare-D1DatabaseObject\097dd93c38860e13c35b3676a4f13f4950f08ed9c239ec99db5716131b0e2718.sqlite
[⣷] applying migrations...Error: Can't find meta/_journal.json file
    at readMigrationFiles (G:\GitHub\BuzzTrip\node_modules\src\migrator.ts:41:9)
    at migrate (G:\GitHub\BuzzTrip\node_modules\src\libsql\migrator.ts:10:21)
    at migrateFn (G:\GitHub\BuzzTrip\node_modules\drizzle-kit\bin.cjs:72211:18)
    at Object.handler (G:\GitHub\BuzzTrip\node_modules\drizzle-kit\bin.cjs:83593:11)
    at async run (G:\GitHub\BuzzTrip\node_modules\drizzle-kit\bin.cjs:82064:7)
error: script "db:migrate" exited with code 1

import { defineConfig } from "drizzle-kit";
import fs from "node:fs";
import path from "node:path";

function getLocalD1DB() {
  try {
    const basePath = path.resolve(".wrangler");
    const dbFile = fs
      .readdirSync(basePath, { encoding: "utf-8", recursive: true })
      .find((f) => f.endsWith(".sqlite"));

    if (!dbFile) {
      throw new Error(`.sqlite file not found in ${basePath}`);
    }

    const url = path.resolve(basePath, dbFile);
    console.log(`Using ${url}`);
    return `file:${url}`;
  } catch (err) {
    console.log(`Error  ${err}`);
  }
}

export default defineConfig({
  dialect: "sqlite",
  schema: "./app/server/db/schema.ts",
  out: "./drizzle",
  ...(process.env.NODE_ENV === "production"
    ? {
        driver: "d1-http",
        dbCredentials: {
          accountId: process.env.CLOUDFLARE_D1_ACCOUNT_ID,
          databaseId: process.env.DATABASE,
          token: process.env.CLOUDFLARE_D1_API_TOKEN,
        },
      }
    : {
        dbCredentials: {
          url: getLocalD1DB(),
        },
      }),
});

import { defineConfig } from "drizzle-kit";
import fs from "node:fs";
import path from "node:path";

function getLocalD1DB() {
  try {
    const basePath = path.resolve(".wrangler");
    const dbFile = fs
      .readdirSync(basePath, { encoding: "utf-8", recursive: true })
      .find((f) => f.endsWith(".sqlite"));

    if (!dbFile) {
      throw new Error(`.sqlite file not found in ${basePath}`);
    }

    const url = path.resolve(basePath, dbFile);
    console.log(`Using ${url}`);
    return `file:${url}`;
  } catch (err) {
    console.log(`Error  ${err}`);
  }
}

export default defineConfig({
  dialect: "sqlite",
  schema: "./app/server/db/schema.ts",
  out: "./drizzle",
  ...(process.env.NODE_ENV === "production"
    ? {
        driver: "d1-http",
        dbCredentials: {
          accountId: process.env.CLOUDFLARE_D1_ACCOUNT_ID,
          databaseId: process.env.DATABASE,
          token: process.env.CLOUDFLARE_D1_API_TOKEN,
        },
      }
    : {
        dbCredentials: {
          url: getLocalD1DB(),
        },
      }),
});

#:schema node_modules/wrangler/config-schema.json
name = "buzztrip"
compatibility_date = "2024-08-21"
compatibility_flags = ["nodejs_compat"]
pages_build_output_dir = ".vercel/output/static"

[[d1_databases]]
binding = "BUZZTRIP_DATABASE"
database_name = "BuzzTrip-db"
database_id = "<database-id>"
migrations_dir = "drizzle/migrations"

#:schema node_modules/wrangler/config-schema.json
name = "buzztrip"
compatibility_date = "2024-08-21"
compatibility_flags = ["nodejs_compat"]
pages_build_output_dir = ".vercel/output/static"

[[d1_databases]]
binding = "BUZZTRIP_DATABASE"
database_name = "BuzzTrip-db"
database_id = "<database-id>"
migrations_dir = "drizzle/migrations"

[[env.dev.d1_databases]]
binding = "DB"
database_name = "d-dev"
database_id = "67d41931-140c-418b-xxxx-xxxxx"

[[env.dev.d1_databases]]
binding = "DB"
database_name = "d-dev"
database_id = "67d41931-140c-418b-xxxx-xxxxx"

/**
 * Update milestone by milestoneId
 * @param request
 * @param env
 * @returns
 */
const updateMilestone = async (request: Request, env: any) => {
    try {
        const params = new URL(request.url).searchParams
        const milestoneId = parseInt(params.get('milestoneId') || '')
        const milestoneData: any = await request.json()
        const fields: string[] = []
        const values: any[] = []
        let index = 1
        for (const [key, value] of Object.entries(milestoneData)) {
            if (value !== undefined) {
                fields.push(`${key} = ?${index}`)
                values.push(value)
                index++
            }
        }
        if (fields.length === 0) {
            return new Response(JSON.stringify({ error: 'No fields to update' }), { status: 400 })
        }
        values.push(milestoneId)
        const query = `UPDATE milestone SET ${fields.join(', ')}, updatedAt = CURRENT_TIMESTAMP WHERE _id = ?${index}`
        const { success } = await env.DB.prepare(query)
            .bind(...values)
            .run()
        return new Response(JSON.stringify({ success }), { status: 200 })
    } catch (err: any) {
        return new Response(JSON.stringify(err), { status: 500 })
    }
}

/**
 * Update milestone by milestoneId
 * @param request
 * @param env
 * @returns
 */
const updateMilestone = async (request: Request, env: any) => {
    try {
        const params = new URL(request.url).searchParams
        const milestoneId = parseInt(params.get('milestoneId') || '')
        const milestoneData: any = await request.json()
        const fields: string[] = []
        const values: any[] = []
        let index = 1
        for (const [key, value] of Object.entries(milestoneData)) {
            if (value !== undefined) {
                fields.push(`${key} = ?${index}`)
                values.push(value)
                index++
            }
        }
        if (fields.length === 0) {
            return new Response(JSON.stringify({ error: 'No fields to update' }), { status: 400 })
        }
        values.push(milestoneId)
        const query = `UPDATE milestone SET ${fields.join(', ')}, updatedAt = CURRENT_TIMESTAMP WHERE _id = ?${index}`
        const { success } = await env.DB.prepare(query)
            .bind(...values)
            .run()
        return new Response(JSON.stringify({ success }), { status: 200 })
    } catch (err: any) {
        return new Response(JSON.stringify(err), { status: 500 })
    }
}