Timbolicious Comments - Answer Overflow

Timbolicious

•Created by Hearse on 11/22/2024 in #help

Local Testing for D1?

Maybe also check this dynamic config for d1 this other thread: https://discord.com/channels/1043890932593987624/1265077849002475571 I have these scripts added to my package.json:

{
  "scripts": {
     // other scripts
    "db:local": "wrangler d1 execute remix-test --local --command='SELECT 1'",
    "db:reset-local": "rm -r .wrangler && bun db:local",
    "db:studio": "drizzle-kit studio",
    "db:push": "drizzle-kit push"
  }
}

{
  "scripts": {
     // other scripts
    "db:local": "wrangler d1 execute remix-test --local --command='SELECT 1'",
    "db:reset-local": "rm -r .wrangler && bun db:local",
    "db:studio": "drizzle-kit studio",
    "db:push": "drizzle-kit push"
  }
}

db:local is needed to create the local DB if it not yet exists in the .wrangler directory db:reset is needed to use push when the table already exists. I don't like this though. Is there another way you know about? Would be nice if push would use CREATE TABLE IF NOT EXIST to prevent such errors.

11 replies

DTDrizzle Team

•Created by Tom Sherman on 7/22/2024 in #help

D1: `drizzle-kit push` says changes applied but database unchanged

Update: I found a solution yesterday The problem was, that push used the local DB while Drizzle ORM and Studio used the remote DB. That was super confusing. I found a nice config snippet that I modified slightly:

// drizzle.config.ts
// original snippet by noelukwa: https://github.com/drizzle-team/drizzle-orm/discussions/1545#discussioncomment-11115682
import { defineConfig, type Config } from "drizzle-kit";
import fs from "fs";
import path from "path";
import env from "./env";

const getLocalD1 = () => {
  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);
    return url;
  } catch (err) {
    console.log(`Error  ${err}`);
  }
};

const isProd = () => process.env.NODE_ENV === "production";

const getCredentials = () => {
  const prod = {
    driver: "d1-http",
    dbCredentials: {
      databaseId: env.CLOUDFLARE_D1_ID,
      accountId: env.CLOUDFLARE_ACCOUNT_ID,
      token: env.CLOUDFLARE_API_TOKEN,
    },
  };

  const dev = {
    dbCredentials: {
      url: getLocalD1(),
    },
  };

  return isProd() ? prod : dev;
};

export default defineConfig({
  dialect: "sqlite",
  schema: "./db/schema/index.ts",
  out: "./db/migrations",
  tablesFilter: ["/^(?!.*_cf_KV).*$/"],
  ...getCredentials(),
}) satisfies Config;

// drizzle.config.ts
// original snippet by noelukwa: https://github.com/drizzle-team/drizzle-orm/discussions/1545#discussioncomment-11115682
import { defineConfig, type Config } from "drizzle-kit";
import fs from "fs";
import path from "path";
import env from "./env";

const getLocalD1 = () => {
  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);
    return url;
  } catch (err) {
    console.log(`Error  ${err}`);
  }
};

const isProd = () => process.env.NODE_ENV === "production";

const getCredentials = () => {
  const prod = {
    driver: "d1-http",
    dbCredentials: {
      databaseId: env.CLOUDFLARE_D1_ID,
      accountId: env.CLOUDFLARE_ACCOUNT_ID,
      token: env.CLOUDFLARE_API_TOKEN,
    },
  };

  const dev = {
    dbCredentials: {
      url: getLocalD1(),
    },
  };

  return isProd() ? prod : dev;
};

export default defineConfig({
  dialect: "sqlite",
  schema: "./db/schema/index.ts",
  out: "./db/migrations",
  tablesFilter: ["/^(?!.*_cf_KV).*$/"],
  ...getCredentials(),
}) satisfies Config;

Then you can specify which DB to use with the CLI by setting the NODE_ENV environment variable: Dev (local, default): bun drizzle-kit push Production (remote): NODE_ENV=production bun drizzle-kit push (you might need to use crossenv if you're on Windows)

4 replies

DTDrizzle Team

•Created by Tom Sherman on 7/22/2024 in #help

D1: `drizzle-kit push` says changes applied but database unchanged

I would love to have push working though as migrations are a bit tedious while developing

4 replies

DTDrizzle Team

•Created by Tom Sherman on 7/22/2024 in #help

D1: `drizzle-kit push` says changes applied but database unchanged

Hi Tom, did you ever find a solution to this? I'm having the same issues right now:

// schema.ts
import { createId } from "@paralleldrive/cuid2";
import { integer, sqliteTable, text } from "drizzle-orm/sqlite-core";

export const users = sqliteTable("users", {
  id: text().primaryKey().unique().$default(createId),
  username: text(),
  email: text().notNull().unique(),
  password: text().notNull(),
  forgotPasswordToken: text(),
  createdAt: integer(),
  updatedAt: integer(),
});

// schema.ts
import { createId } from "@paralleldrive/cuid2";
import { integer, sqliteTable, text } from "drizzle-orm/sqlite-core";

export const users = sqliteTable("users", {
  id: text().primaryKey().unique().$default(createId),
  username: text(),
  email: text().notNull().unique(),
  password: text().notNull(),
  forgotPasswordToken: text(),
  createdAt: integer(),
  updatedAt: integer(),
});

// drizzle.config.ts
import { defineConfig } from "drizzle-kit";

import env from "./env";

export default defineConfig({
  dialect: "sqlite",
  schema: "./db/schema/index.ts",
  out: "./db/migrations",
  driver: "d1-http",
  dbCredentials: {
    databaseId: env.CLOUDFLARE_D1_ID,
    accountId: env.CLOUDFLARE_ACCOUNT_ID,
    token: env.CLOUDFLARE_API_TOKEN,
  },
  tablesFilter: ["/^(?!.*_cf_KV).*$/"],
});

// drizzle.config.ts
import { defineConfig } from "drizzle-kit";

import env from "./env";

export default defineConfig({
  dialect: "sqlite",
  schema: "./db/schema/index.ts",
  out: "./db/migrations",
  driver: "d1-http",
  dbCredentials: {
    databaseId: env.CLOUDFLARE_D1_ID,
    accountId: env.CLOUDFLARE_ACCOUNT_ID,
    token: env.CLOUDFLARE_API_TOKEN,
  },
  tablesFilter: ["/^(?!.*_cf_KV).*$/"],
});

➜  remix-template-cf-workers git:(main) ✗ bun drizzle-kit push    
No config path provided, using default 'drizzle.config.ts'
Reading config file [redacted]
[✓] Pulling schema from database...
[✓] Changes applied

➜  remix-template-cf-workers git:(main) ✗ bun drizzle-kit push    
No config path provided, using default 'drizzle.config.ts'
Reading config file [redacted]
[✓] Pulling schema from database...
[✓] Changes applied

Result: DB is still empty. 😦 But generating a migration and migrating DOES work:

➜  remix-template-cf-workers git:(main) ✗ bun drizzle-kit generate
No config path provided, using default 'drizzle.config.ts'
Reading config file [redacted]
1 tables
users 7 columns 2 indexes 0 fks
[✓] Your SQL migration file ➜ db/migrations/0000_green_toad_men.sql 🚀
➜  remix-template-cf-workers git:(main) ✗ bun drizzle-kit migrate 
No config path provided, using default 'drizzle.config.ts'
Reading config file [redacted]
[✓] migrations applied successfully!%

➜  remix-template-cf-workers git:(main) ✗ bun drizzle-kit generate
No config path provided, using default 'drizzle.config.ts'
Reading config file [redacted]
1 tables
users 7 columns 2 indexes 0 fks
[✓] Your SQL migration file ➜ db/migrations/0000_green_toad_men.sql 🚀
➜  remix-template-cf-workers git:(main) ✗ bun drizzle-kit migrate 
No config path provided, using default 'drizzle.config.ts'
Reading config file [redacted]
[✓] migrations applied successfully!%

4 replies

Gaming

Programming