Thimo_o Comments - Answer Overflow

Thimo_o

Explore posts from servers

NNuxt TtRPC TTCTheo's Typesafe Cult DTDrizzle Team

•Created by Louistiti on 7/18/2023 in #help

Clear the whole database?

This is what made the planetscale version work for me, I had to add the schema in my drizzle function and use truncate table

// db.ts
import * as schema from "./schema"
export const db = drizzle(connection, { schema })


//reset.ts
async function reset() {
  const tableSchema = db._.schema
  if (!tableSchema) {
    throw new Error("No table schema found")
  }

  console.log("🗑️  Emptying the entire database")
  const queries = Object.values(tableSchema).map((table) => {
    console.log(`🧨 Preparing delete query for table: ${table.dbName}`)
    return sql.raw(`TRUNCATE TABLE ${table.dbName};`)
  })

  console.log("📨 Sending delete queries...")

  await db.transaction(async (tx) => {
    await Promise.all(
      queries.map(async (query) => {
        if (query) await tx.execute(query)
      })
    )
  })

  console.log("✅ Database emptied")
}

reset().catch((e) => {

  console.error(e)
})

// db.ts
import * as schema from "./schema"
export const db = drizzle(connection, { schema })


//reset.ts
async function reset() {
  const tableSchema = db._.schema
  if (!tableSchema) {
    throw new Error("No table schema found")
  }

  console.log("🗑️  Emptying the entire database")
  const queries = Object.values(tableSchema).map((table) => {
    console.log(`🧨 Preparing delete query for table: ${table.dbName}`)
    return sql.raw(`TRUNCATE TABLE ${table.dbName};`)
  })

  console.log("📨 Sending delete queries...")

  await db.transaction(async (tx) => {
    await Promise.all(
      queries.map(async (query) => {
        if (query) await tx.execute(query)
      })
    )
  })

  console.log("✅ Database emptied")
}

reset().catch((e) => {

  console.error(e)
})

59 replies

DTDrizzle Team

•Created by Louistiti on 7/18/2023 in #help

Clear the whole database?

@louistiti_ As far as I know I do the same

My reset db file: 

const clearDb = async (): Promise<void> => {
  const query = sql<string>`SELECT table_name
      FROM information_schema.tables
      WHERE table_schema = 'public'
        AND table_type = 'BASE TABLE';
    `

  const tables = await db.execute(query)

  tables.forEach(async (table) => {
    const query = sql.raw(`TRUNCATE TABLE ${table.table_name} CASCADE;`)
    await db.execute(query)
  })
}

clearDb().catch((e) => {
  console.error(e)
})

my db.ts

import { connect } from "@planetscale/database"
import { env } from "~/env.mjs"
import { drizzle } from "drizzle-orm/planetscale-serverless"

// create the connection
export const connection = connect({
  url: env.DATABASE_URL,
})

export const db = drizzle(connection)

My schema


export const user = mysqlTable("auth_user", {
  id: varchar("id", {
    length: 15, // change this when using custom user ids
  }).primaryKey(),
  username: varchar("username", {
    length: 255,
  }),
  githubUsername: varchar("github_username", {
    length: 255,
  }),
  // other user attributes
})

export const key = mysqlTable("user_key", {
  id: varchar("id", {
    length: 255,
  }).primaryKey(),
  userId: varchar("user_id", {
    length: 15,
  }).notNull(),
  hashedPassword: varchar("hashed_password", {
    length: 255,
  }),
})

export const session = mysqlTable("user_session", {
  id: varchar("id", {
    length: 128,
  }).primaryKey(),
  userId: varchar("user_id", {
    length: 15,
  }).notNull(),
  activeExpires: bigint("active_expires", {
    mode: "number",
  }).notNull(),
  idleExpires: bigint("idle_expires", {
    mode: "number",
  }).notNull(),
})

export const seedTable = mysqlTable("seed", {
  id: varchar("id", {
    length: 255,
  }).primaryKey(),
  value: varchar("value", {
    length: 255,
  })
})

My reset db file: 

const clearDb = async (): Promise<void> => {
  const query = sql<string>`SELECT table_name
      FROM information_schema.tables
      WHERE table_schema = 'public'
        AND table_type = 'BASE TABLE';
    `

  const tables = await db.execute(query)

  tables.forEach(async (table) => {
    const query = sql.raw(`TRUNCATE TABLE ${table.table_name} CASCADE;`)
    await db.execute(query)
  })
}

clearDb().catch((e) => {
  console.error(e)
})

my db.ts

import { connect } from "@planetscale/database"
import { env } from "~/env.mjs"
import { drizzle } from "drizzle-orm/planetscale-serverless"

// create the connection
export const connection = connect({
  url: env.DATABASE_URL,
})

export const db = drizzle(connection)

My schema


export const user = mysqlTable("auth_user", {
  id: varchar("id", {
    length: 15, // change this when using custom user ids
  }).primaryKey(),
  username: varchar("username", {
    length: 255,
  }),
  githubUsername: varchar("github_username", {
    length: 255,
  }),
  // other user attributes
})

export const key = mysqlTable("user_key", {
  id: varchar("id", {
    length: 255,
  }).primaryKey(),
  userId: varchar("user_id", {
    length: 15,
  }).notNull(),
  hashedPassword: varchar("hashed_password", {
    length: 255,
  }),
})

export const session = mysqlTable("user_session", {
  id: varchar("id", {
    length: 128,
  }).primaryKey(),
  userId: varchar("user_id", {
    length: 15,
  }).notNull(),
  activeExpires: bigint("active_expires", {
    mode: "number",
  }).notNull(),
  idleExpires: bigint("idle_expires", {
    mode: "number",
  }).notNull(),
})

export const seedTable = mysqlTable("seed", {
  id: varchar("id", {
    length: 255,
  }).primaryKey(),
  value: varchar("value", {
    length: 255,
  })
})

59 replies

DTDrizzle Team

•Created by Louistiti on 7/18/2023 in #help

Clear the whole database?

@louistiti_ I do get Property 'forEach' does not exist on type 'ExecutedQuery'.. Does your tables variable have another type?

59 replies

DTDrizzle Team

•Created by Louistiti on 7/18/2023 in #help

Clear the whole database?

@mendy.l @louistiti I tried the raw example, but I'm doing/forgetting something I think In the for loop of the raw example I get tables: ExecutedQuery, which I can not iterate over. Do I have to change something here so the typing is correct? Mendy, I had db..schema undefined but I still don't know why. Do you know why that might happen? For my db variable I have this: That should be correct right?

export const connection = connect({
  url: env.DATABASE_URL,
})

export const db = drizzle(connection)

export const connection = connect({
  url: env.DATABASE_URL,
})

export const db = drizzle(connection)

59 replies

DTDrizzle Team

•Created by Louistiti on 7/18/2023 in #help

Clear the whole database?

I now have a separate account just for testing, so that works quite good

59 replies

DTDrizzle Team

•Created by Louistiti on 7/18/2023 in #help

Clear the whole database?

yeah, thats also true, I hope I can get my clear script working then

59 replies

DTDrizzle Team

•Created by Louistiti on 7/18/2023 in #help

Clear the whole database?

I think I saw a tweet from drizzle before, where they said it was quite easy to use multiple providers at once, like an sqlite and a planetscale one, that might be a sweet spot. Do you know how that might be done perhaps?

59 replies

DTDrizzle Team

•Created by Louistiti on 7/18/2023 in #help

Clear the whole database?

Opening a new branch would be quite overkill I think, since I then need to also push the schema again and wait for planetscale to configure it. I wonder how Prisma does it (although this was mostly for a local sqlite thing too ) https://github.com/prisma/prisma/issues/11261

59 replies

DTDrizzle Team

•Created by Louistiti on 7/18/2023 in #help

Clear the whole database?

I would rather prefer locally too, but the way planetscale uses mysql through vitess, it is a lot easier to use a branch on there than to try and set that up locally for now. I haven't seen anyone do a complete copy of a planetscale db locally yet

59 replies

DTDrizzle Team

•Created by Louistiti on 7/18/2023 in #help

Clear the whole database?

That would achieve the same goal yes, but since I use planetscale, I can't use docker. Do you know the best way to do this for planetscale?

59 replies

DTDrizzle Team

•Created by Louistiti on 7/18/2023 in #help

Clear the whole database?

Hello Mendy, I saw your reset script and was trying to make one myself. I came up with a slight variation but I think I made a mistake somewhere and was wondering how you did it I used the db variable from where the connection is created: but I see db._.schema as undefined at my side. Is there a certain order or something I have to do to know the variable is not undefined?

export const connection = connect({
  url: env.DATABASE_URL,
})

export const db = drizzle(connection)

import { sql } from "drizzle-orm"
import { db } from "~/db/db"

async function reset() {
  console.log(db)
  const tableSchema = db._.schema
  if (!tableSchema) {
    throw new Error("No table schema found")
  }

  console.log("🗑️ Emptying the entire database")
  const queries = Object.values(tableSchema).map((table) => {
    console.log(`🧨 Preparing delete query for table: ${table.dbName}`)
    return sql.raw(`DELETE FROM ${table.dbName};`)
  })

  console.log("🛜 Sending delete queries")

  await db.transaction(async (tx) => {
    await Promise.all(
      queries.map(async (query) => {
        if (query) await tx.execute(query)
      })
    )
  })

  console.log("✅ Database emptied")
}

reset().catch((e) => {
  console.error(e)
})

export const connection = connect({
  url: env.DATABASE_URL,
})

export const db = drizzle(connection)

import { sql } from "drizzle-orm"
import { db } from "~/db/db"

async function reset() {
  console.log(db)
  const tableSchema = db._.schema
  if (!tableSchema) {
    throw new Error("No table schema found")
  }

  console.log("🗑️ Emptying the entire database")
  const queries = Object.values(tableSchema).map((table) => {
    console.log(`🧨 Preparing delete query for table: ${table.dbName}`)
    return sql.raw(`DELETE FROM ${table.dbName};`)
  })

  console.log("🛜 Sending delete queries")

  await db.transaction(async (tx) => {
    await Promise.all(
      queries.map(async (query) => {
        if (query) await tx.execute(query)
      })
    )
  })

  console.log("✅ Database emptied")
}

reset().catch((e) => {
  console.error(e)
})

59 replies

Gaming

Programming