Drizzle + Turso Migration Problem

Npm Scripts:

"db:push": "drizzle-kit push:sqlite",
"db:generate": "drizzle-kit generate:sqlite",

"db:push": "drizzle-kit push:sqlite",
"db:generate": "drizzle-kit generate:sqlite",

Issue: When I run db:push, it tries to create the entire db instead of looking at the migration file Drizzle Config

import { type Config } from "drizzle-kit";

export default {
  schema: "./src/server/db/schema.ts",
  driver: "turso",
  dbCredentials: {
    url: process.env.DATABASE_URL!,
    authToken: process.env.DATABASE_AUTH_TOKEN!,
  },
  out: "./drizzle",
  tablesFilter: ["learningcambridgeforall_*"],
  verbose: true,
  strict: true,
} satisfies Config;

import { type Config } from "drizzle-kit";

export default {
  schema: "./src/server/db/schema.ts",
  driver: "turso",
  dbCredentials: {
    url: process.env.DATABASE_URL!,
    authToken: process.env.DATABASE_AUTH_TOKEN!,
  },
  out: "./drizzle",
  tablesFilter: ["learningcambridgeforall_*"],
  verbose: true,
  strict: true,
} satisfies Config;

Database Index ("/src/server/db/index.ts")

import { drizzle } from "drizzle-orm/libsql";
import { createClient } from "@libsql/client";

import { subjects } from "./schema";

const client = createClient({
  url: process.env.DATABASE_URL!,
  authToken: process.env.DATABASE_AUTH_TOKEN!,
});

export const db = drizzle(client, { schema: { ...subjects } });

import { drizzle } from "drizzle-orm/libsql";
import { createClient } from "@libsql/client";

import { subjects } from "./schema";

const client = createClient({
  url: process.env.DATABASE_URL!,
  authToken: process.env.DATABASE_AUTH_TOKEN!,
});

export const db = drizzle(client, { schema: { ...subjects } });

Database Schema ("src/server/db/schema.ts")

import { sql } from "drizzle-orm";
import { text, integer, sqliteTable, index } from "drizzle-orm/sqlite-core";

export const subjects = sqliteTable(
  "subjects",
  {
    id: integer("id").notNull().primaryKey(),
    title: text("title").notNull(),
    overview: text("overview").notNull(),
    visibility: text("visibility", {
      enum: ["Pending", "Public", "Private"],
    })
      .notNull()
      .default("Pending"),
    educationLevel: text("education_level", {
      enum: ["a_level", "o_level"],
    }).notNull(),
    createdAt: text("created_at")
      .default(sql`CURRENT_TIMESTAMP`)
      .notNull(),
  },
  (table) => {
    return {
      titleIdx: index("title_idx").on(table.title),
    };
  },
);

import { sql } from "drizzle-orm";
import { text, integer, sqliteTable, index } from "drizzle-orm/sqlite-core";

export const subjects = sqliteTable(
  "subjects",
  {
    id: integer("id").notNull().primaryKey(),
    title: text("title").notNull(),
    overview: text("overview").notNull(),
    visibility: text("visibility", {
      enum: ["Pending", "Public", "Private"],
    })
      .notNull()
      .default("Pending"),
    educationLevel: text("education_level", {
      enum: ["a_level", "o_level"],
    }).notNull(),
    createdAt: text("created_at")
      .default(sql`CURRENT_TIMESTAMP`)
      .notNull(),
  },
  (table) => {
    return {
      titleIdx: index("title_idx").on(table.title),
    };
  },
);

1 Reply

kaleembhatti•4mo ago

Database Migrate ("src/server/db/migrate.ts")

import { drizzle } from "drizzle-orm/better-sqlite3";
import { migrate } from "drizzle-orm/better-sqlite3/migrator";
import Database from "better-sqlite3";

const betterSqlite = new Database(":memory:");
const db = drizzle(betterSqlite);
migrate(db, { migrationsFolder: "drizzle" });
betterSqlite.close();

import { drizzle } from "drizzle-orm/better-sqlite3";
import { migrate } from "drizzle-orm/better-sqlite3/migrator";
import Database from "better-sqlite3";

const betterSqlite = new Database(":memory:");
const db = drizzle(betterSqlite);
migrate(db, { migrationsFolder: "drizzle" });
betterSqlite.close();

Latest Migration File Generated ("drizzle")

ALTER TABLE subjects ADD `visibility` text DEFAULT 'Pending' NOT NULL;--> statement-breakpoint
ALTER TABLE subjects ADD `education_level` text NOT NULL;--> statement-breakpoint
CREATE INDEX `title_idx` ON `subjects` (`title`);

ALTER TABLE subjects ADD `visibility` text DEFAULT 'Pending' NOT NULL;--> statement-breakpoint
ALTER TABLE subjects ADD `education_level` text NOT NULL;--> statement-breakpoint
CREATE INDEX `title_idx` ON `subjects` (`title`);

What I See in the console

CREATE TABLE `subjects` (
        `id` integer PRIMARY KEY NOT NULL,
        `title` text NOT NULL,
        `overview` text NOT NULL,
        `visibility` text DEFAULT 'Pending' NOT NULL,
        `education_level` text NOT NULL,
        `created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE INDEX `title_idx` ON `subjects` (`title`);

CREATE TABLE `subjects` (
        `id` integer PRIMARY KEY NOT NULL,
        `title` text NOT NULL,
        `overview` text NOT NULL,
        `visibility` text DEFAULT 'Pending' NOT NULL,
        `education_level` text NOT NULL,
        `created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE INDEX `title_idx` ON `subjects` (`title`);

Order of commands:

npm run db:generate
npm run db:push

npm run db:generate
npm run db:push

I have no idea why its creating the table again, all the fields and schema is up to date but it just tries to create a new table which can only happen if the table is dropped. Can anyone tell me what i am doing wrong as this will remove all of the data previously stored. I read the docs multiple times and most of the code here is straight taken from the docs. I am using the t3 stack (Nextjs App Router, Trpc, Drizzle, Turso, Etc) Thanks 🙂 Ps: I am new to Turso

For some reason everything is working now
Super strange

For some reason everything is working now
Super strange

Gaming

Programming

Drizzle + Turso Migration Problem