wkd9241 Comments - Answer Overflow

wkd9241

•Created by lyron1111 on 3/26/2024 in #help

Executing raw Sql query

Hmm, I can't help you further. I've started using Drizzle a couple days ago and just happened to have made a raw sql query, so I had the answer to your question. Regarding your other issue, unfortunately I can't spot the error 🫤

5 replies

DTDrizzle Team

•Created by shikishikichangchang on 3/3/2024 in #help

how can I truncate all tables in the new drizzle studio? Previously there was a drop down button

Now add this script into your package.json file: Edit it to match the path of the file containing the prompts. You can change the script command as well.

"db:drop": "tsx drizzle/drop-prompt.ts"

"db:drop": "tsx drizzle/drop-prompt.ts"

Now you can use pnpm db:drop in your terminal. It will trigger the prompts and after 2 confirmations, it will execute the drop/truncation function.

8 replies

DTDrizzle Team

•Created by shikishikichangchang on 3/3/2024 in #help

how can I truncate all tables in the new drizzle studio? Previously there was a drop down button

Here's the file that will be executed by the exec function after the 2 confirmations (don't forget to edit the command in the function to match your file path). In my implementation, I totally drop the tables. If you just want to truncate them (deleting all of the rows but keeping the table), change DROP TABLE to TRUNCATE TABLE

// drizzle/drop-db.ts

import {drizzle} from 'drizzle-orm/node-postgres'
import * as schema from './schema'
import {sql} from 'drizzle-orm'
import {Client} from 'pg'
import 'dotenv/config'

const dropDatabase = async () => {
  const client = new Client({connectionString: process.env.SUPABASE_URL})
  await client.connect()
  const db = drizzle(client, {schema})

  const tablesSchema = db._.schema
  if (!tablesSchema) throw new Error('Schema not loaded')

  const tables = Object.values(tablesSchema).map(table => table.dbName)
  for (const table of tables)
    await db.execute(sql.raw(`DROP TABLE ${table} CASCADE;`))

  await client.end()
}

dropDatabase()

// drizzle/drop-db.ts

import {drizzle} from 'drizzle-orm/node-postgres'
import * as schema from './schema'
import {sql} from 'drizzle-orm'
import {Client} from 'pg'
import 'dotenv/config'

const dropDatabase = async () => {
  const client = new Client({connectionString: process.env.SUPABASE_URL})
  await client.connect()
  const db = drizzle(client, {schema})

  const tablesSchema = db._.schema
  if (!tablesSchema) throw new Error('Schema not loaded')

  const tables = Object.values(tablesSchema).map(table => table.dbName)
  for (const table of tables)
    await db.execute(sql.raw(`DROP TABLE ${table} CASCADE;`))

  await client.end()
}

dropDatabase()

8 replies

DTDrizzle Team

•Created by shikishikichangchang on 3/3/2024 in #help

how can I truncate all tables in the new drizzle studio? Previously there was a drop down button

I have created 2 files + 1 script to do it from the terminal. Just copy and paste them and you should be fine. Edit them to suit your needs. This code will be executed using the tsx package. It asks a confirmation twice before executing. You can edit the prompts as you wish.

// drizzle/drop-prompt.ts

// these 2 are globally available, no need npm
import readline from 'readline';
import {exec} from 'child_process';

const rl = readline.createInterface({
  input: process.stdin,
  output: process.stdout,
});

function confirmDropDatabase() {
  rl.question(
    '\n🛑 YOU ARE ABOUT TO DROP THE ENTIRE DATABASE, ARE YOU SURE? (y/N) ',
    answer => {
      if (answer.toLowerCase().trim() === 'y') {
        rl.question(
          '\n🛑 ARE YOU ABSOLUTELY SURE? THIS ACTION CANNOT BE UNDONE. (y/N) ',
          answer2 => {
            if (answer2.toLowerCase().trim() === 'y') {
              // Your command
              exec('tsx drizzle/drop-db.ts', (error, stderr) => {
                if (error) {
                  console.error(`Error: ${error.message}`);
                  return;
                }
                if (stderr) {
                  console.error(`stderr: ${stderr}`);
                  return;
                }
                console.log(`\n✅ Database dropped successfully!\n`);
              });
              rl.close();
            } else if (answer2.toLowerCase().trim() === 'n') {
              console.log('\n✅ Operation cancelled.\n');
              rl.close();
            } else {
              console.log('\n⚠️  Invalid input. Please enter Y or N.\n');
              confirmDropDatabase(); // Ask again
            }
          }
        );
      } else if (answer.toLowerCase().trim() === 'n') {
        console.log('\n✅ Operation cancelled.\n');
        rl.close();
      } else {
        console.log('\n⚠️  Invalid input. Please enter Y or N.\n');
        confirmDropDatabase(); // Ask again
      }
    }
  );
}

confirmDropDatabase();

// drizzle/drop-prompt.ts

// these 2 are globally available, no need npm
import readline from 'readline';
import {exec} from 'child_process';

const rl = readline.createInterface({
  input: process.stdin,
  output: process.stdout,
});

function confirmDropDatabase() {
  rl.question(
    '\n🛑 YOU ARE ABOUT TO DROP THE ENTIRE DATABASE, ARE YOU SURE? (y/N) ',
    answer => {
      if (answer.toLowerCase().trim() === 'y') {
        rl.question(
          '\n🛑 ARE YOU ABSOLUTELY SURE? THIS ACTION CANNOT BE UNDONE. (y/N) ',
          answer2 => {
            if (answer2.toLowerCase().trim() === 'y') {
              // Your command
              exec('tsx drizzle/drop-db.ts', (error, stderr) => {
                if (error) {
                  console.error(`Error: ${error.message}`);
                  return;
                }
                if (stderr) {
                  console.error(`stderr: ${stderr}`);
                  return;
                }
                console.log(`\n✅ Database dropped successfully!\n`);
              });
              rl.close();
            } else if (answer2.toLowerCase().trim() === 'n') {
              console.log('\n✅ Operation cancelled.\n');
              rl.close();
            } else {
              console.log('\n⚠️  Invalid input. Please enter Y or N.\n');
              confirmDropDatabase(); // Ask again
            }
          }
        );
      } else if (answer.toLowerCase().trim() === 'n') {
        console.log('\n✅ Operation cancelled.\n');
        rl.close();
      } else {
        console.log('\n⚠️  Invalid input. Please enter Y or N.\n');
        confirmDropDatabase(); // Ask again
      }
    }
  );
}

confirmDropDatabase();

8 replies

DTDrizzle Team

•Created by lyron1111 on 3/26/2024 in #help

Executing raw Sql query

import {sql} from 'drizzle-orm'

// One way
const query = sql.raw('SELECT * from users')

// You can also use string interpolation
const table = "users"
const query = sql.raw(`SELECT * from ${table}`)

// Execute the query
await db.execute(query)

import {sql} from 'drizzle-orm'

// One way
const query = sql.raw('SELECT * from users')

// You can also use string interpolation
const table = "users"
const query = sql.raw(`SELECT * from ${table}`)

// Execute the query
await db.execute(query)

5 replies

DTDrizzle Team

•Created by wkd9241 on 3/25/2024 in #help

Nested update pattern

No, the thing is that the collections are a column in the space table. They aren't a table. They look like this:

type Collection = {
  name: string
  parent?: string | null
  products: string[]
}

// with this as my Space schema
const space = pgTable('spaces', {
  {...}
  collections: json('collections').$type<Collection[]>().notNull(),
})

type Collection = {
  name: string
  parent?: string | null
  products: string[]
}

// with this as my Space schema
const space = pgTable('spaces', {
  {...}
  collections: json('collections').$type<Collection[]>().notNull(),
})

As for the name and oldName logic, using trpc, I send the new data of the collection with the oldName property that is the name of the collection before the updating (it might not change). Then, I use this oldName as a pseudo-identifier to know which collection to update. Each collection has a unique name. Now that I double-check the code, I should destructure the updated collection data to extract the oldName property before assigning it as the new collection's data (during the mapping in the Drizzle transaction) With Prisma I could update the collections within a Space as shown in the screenshot. With Drizzle, I havn't found a more concise way of doing it then by what you see there (minding the correction I just mentionned).

3 replies

Gaming

Programming