focused_morning Posts - Answer Overflow

focused_morning

Posts Comments

DTDrizzle Team

•Created by focused_morning on 12/20/2023 in #help

Push without truncating

Is it possible to tell drizzle-kit not to truncate? Im in a situation that shouldn't truncate and it wants to.

2 replies

DTDrizzle Team

•Created by focused_morning on 11/8/2023 in #help

Why truncate??

Updating some schemas. All 48 records in the item table have cmsSlug and cmsId. All 48 records have a 36 character cmsId. So my question is, why are the only options to truncate or cancel? Output: Warning Found data-loss statements: · You're about to set not-null constraint to cmsSlug column without default, which contains 48 items · You're about to change cmsId column type from varchar(191) to varchar(36) with 48 items · You're about to set not-null constraint to cmsId column without default, which contains 48 items

1 replies

DTDrizzle Team

•Created by focused_morning on 10/31/2023 in #help

How do I write a trigger??

Not sure how this might happen, but If I needed to write a trigger how would I do this in Drizzle?

4 replies

DTDrizzle Team

•Created by focused_morning on 10/31/2023 in #help

How can I accomplish this in a schema?

I'm trying to make it so only one record with this itemId can have the status "ACTIVE" at a time. Something like this: ALTER TABLE ItemMintPermission ADD COLUMN ActiveItemID AS (CASE WHEN status='ACTIVE' THEN itemId ELSE NULL END) VIRTUAL; ALTER TABLE ItemMintPermission ADD UNIQUE INDEX idx_unique_active_item(ActiveItemID);

10 replies

DTDrizzle Team

•Created by focused_morning on 9/19/2023 in #help

How can I use a schema to generate something like this:

We have a 1 to many relationship between items and tokens, but an item can only have one minted token, example below: CREATE TABLE Token ( id INT PRIMARY KEY AUTO_INCREMENT, itemId INT, status ENUM('Minted', 'Redeemed'), uniqueMinted AS (CASE WHEN status = 'Minted' THEN itemId END), UNIQUE(itemId, uniqueMinted) ); Im not sure how to handle the uniqueMinted column

1 replies

DTDrizzle Team

•Created by focused_morning on 8/29/2023 in #help

Help, issue that will drop our user table

We generated and pushed our schemas to a dev branch in planetscale, and then deployed that branch no issue. One of the schemas was the User schema. We added two schemas to our db and pushed to dev again, no changes were made to the User schema, yet pushing to the dev db wants to truncate our user table because of the createdAt and updatedAt columns. What is happening and what should we do? Schema

export const user = mysqlTable(
    'User',
    {
        id: varchar('id', { length: 191 }).primaryKey().notNull(),
        username: varchar('username', { length: 191 }),
        profileImageUrl: varchar('profileImageUrl', { length: 191 }),
        email: varchar('email', { length: 191 }),
        bio: text('bio'),
        headline: text('headline'),
        website: varchar('website', { length: 191 }),
        twitter: varchar('twitter', { length: 191 }),
        instagram: varchar('instagram', { length: 191 }),
        opensea: varchar('opensea', { length: 191 }),
        membershipTier: mysqlEnum('membershipTier', [
            'COMMON',
            'RARE',
            'EPIC',
            'LEGENDARY',
            'PN',
        ]),
        role: mysqlEnum('role', ['ADMIN', 'CREATOR', 'USER'])
            .default('USER')
            .notNull(),
        createdAt: datetime('createdAt', { mode: 'string', fsp: 3 })
            .default(sql`(CURRENT_TIMESTAMP(3))`)
            .notNull(),
        updatedAt: datetime('updatedAt', { mode: 'string', fsp: 3 })
            .default(sql`(CURRENT_TIMESTAMP(3))`)
            .notNull(),
    },
    (table) => {
        return {
            usernameIdx: index('User_username_idx').on(table.username),
            usernameKey: uniqueIndex('User_username_key').on(table.username),
        }
    },
)

export const user = mysqlTable(
    'User',
    {
        id: varchar('id', { length: 191 }).primaryKey().notNull(),
        username: varchar('username', { length: 191 }),
        profileImageUrl: varchar('profileImageUrl', { length: 191 }),
        email: varchar('email', { length: 191 }),
        bio: text('bio'),
        headline: text('headline'),
        website: varchar('website', { length: 191 }),
        twitter: varchar('twitter', { length: 191 }),
        instagram: varchar('instagram', { length: 191 }),
        opensea: varchar('opensea', { length: 191 }),
        membershipTier: mysqlEnum('membershipTier', [
            'COMMON',
            'RARE',
            'EPIC',
            'LEGENDARY',
            'PN',
        ]),
        role: mysqlEnum('role', ['ADMIN', 'CREATOR', 'USER'])
            .default('USER')
            .notNull(),
        createdAt: datetime('createdAt', { mode: 'string', fsp: 3 })
            .default(sql`(CURRENT_TIMESTAMP(3))`)
            .notNull(),
        updatedAt: datetime('updatedAt', { mode: 'string', fsp: 3 })
            .default(sql`(CURRENT_TIMESTAMP(3))`)
            .notNull(),
    },
    (table) => {
        return {
            usernameIdx: index('User_username_idx').on(table.username),
            usernameKey: uniqueIndex('User_username_key').on(table.username),
        }
    },
)

2 replies

DTDrizzle Team

•Created by focused_morning on 8/23/2023 in #help

Invalid default value for 'updatedAt'

Running into this issue, oddly enough this once worked and now breaks.

Error: target: americana-db.-.primary: vttablet: rpc error: code = InvalidArgument desc = Invalid default value for 'updatedAt' (errno 1067) (sqlstate 42000) (CallerID: planetscale-admin): Sql: "alter table InventoryItem modify column createdAt datetime(3) not null default (current_timestamp(3))", BindVars: {REDACTED}
    at PromiseConnection.query (/Users/robmartin/Projects/americana-neue/node_modules/.pnpm/drizzle-kit@0.19.2/node_modules/drizzle-kit/index.cjs:34122:26)
    at Command.<anonymous> (/Users/robmartin/Projects/americana-neue/node_modules/.pnpm/drizzle-kit@0.19.2/node_modules/drizzle-kit/index.cjs:51859:33)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
  code: 'ER_INVALID_DEFAULT',
  errno: 1067,
  sql: 'ALTER TABLE `InventoryItem` MODIFY COLUMN `createdAt` datetime(3) NOT NULL DEFAULT (CURRENT_TIMESTAMP(3));',
  sqlState: '42000',
  sqlMessage: `target: americana-db.-.primary: vttablet: rpc error: code = InvalidArgument desc = Invalid default value for 'updatedAt' (errno 1067) (sqlstate 42000) (CallerID: planetscale-admin): Sql: "alter table InventoryItem modify column createdAt datetime(3) not null default (current_timestamp(3))", BindVars: {REDACTED}`

Error: target: americana-db.-.primary: vttablet: rpc error: code = InvalidArgument desc = Invalid default value for 'updatedAt' (errno 1067) (sqlstate 42000) (CallerID: planetscale-admin): Sql: "alter table InventoryItem modify column createdAt datetime(3) not null default (current_timestamp(3))", BindVars: {REDACTED}
    at PromiseConnection.query (/Users/robmartin/Projects/americana-neue/node_modules/.pnpm/drizzle-kit@0.19.2/node_modules/drizzle-kit/index.cjs:34122:26)
    at Command.<anonymous> (/Users/robmartin/Projects/americana-neue/node_modules/.pnpm/drizzle-kit@0.19.2/node_modules/drizzle-kit/index.cjs:51859:33)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
  code: 'ER_INVALID_DEFAULT',
  errno: 1067,
  sql: 'ALTER TABLE `InventoryItem` MODIFY COLUMN `createdAt` datetime(3) NOT NULL DEFAULT (CURRENT_TIMESTAMP(3));',
  sqlState: '42000',
  sqlMessage: `target: americana-db.-.primary: vttablet: rpc error: code = InvalidArgument desc = Invalid default value for 'updatedAt' (errno 1067) (sqlstate 42000) (CallerID: planetscale-admin): Sql: "alter table InventoryItem modify column createdAt datetime(3) not null default (current_timestamp(3))", BindVars: {REDACTED}`

33 replies

DTDrizzle Team

•Created by focused_morning on 8/15/2023 in #help

whats the difference.

Whats the difference of using references like this:

export const profileInfo = pgTable('profile_info', {
    id: serial('id').primaryKey(),
    userId: integer("user_id").references(() => users.id),
    metadata: jsonb("metadata"),
});

export const profileInfo = pgTable('profile_info', {
    id: serial('id').primaryKey(),
    userId: integer("user_id").references(() => users.id),
    metadata: jsonb("metadata"),
});

and like this:

export const usersRelations = relations(users, ({ one }) => ({
    profileInfo: one(profileInfo, {
        fields: [users.id],
        references: [profileInfo.userId],
    }),
}));

export const usersRelations = relations(users, ({ one }) => ({
    profileInfo: one(profileInfo, {
        fields: [users.id],
        references: [profileInfo.userId],
    }),
}));

7 replies

DTDrizzle Team

•Created by focused_morning on 8/15/2023 in #help

onDelete cascade, Error: VT10001: foreign key constraints are not allowed

Trying to add onDelete cascade and db push produces this error: foreign key constraints are not allowed

import { relations } from "drizzle-orm";
import { mysqlTable, index, varchar, double } from "drizzle-orm/mysql-core";
import { request } from "./request";

export const requestItem = mysqlTable(
  "RequestItem",
  {
    id: varchar("id", { length: 191 }).primaryKey().notNull(),
    requestId: varchar("requestId", { length: 191 })
      .notNull()
      .references(() => request.id, { onDelete: "cascade" }),
    title: varchar("title", { length: 191 }).notNull(),
    description: varchar("description", { length: 191 }).notNull(),
    estimatedValue: double("estimatedValue", {
      precision: 10,
      scale: 2,
    }),
  },
  (table) => {
    return {
      requestIdIdx: index("RequestItem_requestId_idx").on(table.requestId),
    };
  }
);

export const requestItemRelations = relations(requestItem, ({ one }) => ({
  request: one(request, {
    fields: [requestItem.requestId],
    references: [request.id],
  }),
}));

import { relations } from "drizzle-orm";
import { mysqlTable, index, varchar, double } from "drizzle-orm/mysql-core";
import { request } from "./request";

export const requestItem = mysqlTable(
  "RequestItem",
  {
    id: varchar("id", { length: 191 }).primaryKey().notNull(),
    requestId: varchar("requestId", { length: 191 })
      .notNull()
      .references(() => request.id, { onDelete: "cascade" }),
    title: varchar("title", { length: 191 }).notNull(),
    description: varchar("description", { length: 191 }).notNull(),
    estimatedValue: double("estimatedValue", {
      precision: 10,
      scale: 2,
    }),
  },
  (table) => {
    return {
      requestIdIdx: index("RequestItem_requestId_idx").on(table.requestId),
    };
  }
);

export const requestItemRelations = relations(requestItem, ({ one }) => ({
  request: one(request, {
    fields: [requestItem.requestId],
    references: [request.id],
  }),
}));

10 replies

DTDrizzle Team

•Created by focused_morning on 8/15/2023 in #help

migration/meta/0000_snapshot.json data is malformed

Running generate I see this in the console.

9 replies

DTDrizzle Team

•Created by focused_morning on 8/9/2023 in #help

Top Level await issue with latest Drizzle@0.28.1, mysql2, planetscale

We are getting this error when following the release notes recommended setup for pscale/mysql:

[ERROR] Top-level await is not available in the configured target environment ("node14")
remix-app:dev:
remix-app:dev:     ../../packages/db/index.ts:18:19:
remix-app:dev:       18 │ const connection = await mysql.createConnection({

[ERROR] Top-level await is not available in the configured target environment ("node14")
remix-app:dev:
remix-app:dev:     ../../packages/db/index.ts:18:19:
remix-app:dev:       18 │ const connection = await mysql.createConnection({

8 replies

DTDrizzle Team

•Created by focused_morning on 8/1/2023 in #help

Does changing the name of a relationship have any consequence aside from having to fix any queries?

We want to change the name of a relationship and I was curious if this would cause any issues? Let’s say person had a cat relationship and I wanted to name it cats, would this be okay?

4 replies

DTDrizzle Team

•Created by focused_morning on 7/25/2023 in #help

introspect failing with pscale db

Getting a strange error when running the introspect command. I'm using a connectionString from pscale. drizzle.config:

  schema: "./schema.ts",
  out: "./drizzle",
  driver: "mysql2",
  dbCredentials: {
       connectionString: ...
  }

  schema: "./schema.ts",
  out: "./drizzle",
  driver: "mysql2",
  dbCredentials: {
       connectionString: ...
  }

- I've included ?ssl={"rejectUnauthorized":true} script: "introspect": "drizzle-kit introspect:mysql", Error:

Error: rpc error: code = Unknown desc = uncaught panic: interface conversion: interface is nil, not sqlparser.Expr, vtgate: 
    at PromiseConnection.execute (/Users/robmartin/Projects/americana-neue/node_modules/.pnpm/drizzle-kit@0.19.11/node_modules/drizzle-kit/index.cjs:35440:26)
    at fromDatabase (/Users/robmartin/Projects/americana-neue/node_modules/.pnpm/drizzle-kit@0.19.11/node_modules/drizzle-kit/index.cjs:11845:33)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
  code: 'ER_UNKNOWN_ERROR',
  errno: 1105,
  sql: 'SELECT table_name, column_name\n' +
    '  FROM information_schema.table_constraints t\n' +
    '  LEFT JOIN information_schema.key_column_usage k\n' +
    '  USING(constraint_name,table_schema,table_name)\n' +
    "  WHERE t.constraint_type='PRIMARY KEY'\n" +
    "      and table_name != '__drizzle_migrations'\n" +
    '      AND t.table_schema = ?',
  sqlState: 'HY000',
  sqlMessage: 'rpc error: code = Unknown desc = uncaught panic: interface conversion: interface is nil, not sqlparser.Expr, vtgate:

Error: rpc error: code = Unknown desc = uncaught panic: interface conversion: interface is nil, not sqlparser.Expr, vtgate: 
    at PromiseConnection.execute (/Users/robmartin/Projects/americana-neue/node_modules/.pnpm/drizzle-kit@0.19.11/node_modules/drizzle-kit/index.cjs:35440:26)
    at fromDatabase (/Users/robmartin/Projects/americana-neue/node_modules/.pnpm/drizzle-kit@0.19.11/node_modules/drizzle-kit/index.cjs:11845:33)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
  code: 'ER_UNKNOWN_ERROR',
  errno: 1105,
  sql: 'SELECT table_name, column_name\n' +
    '  FROM information_schema.table_constraints t\n' +
    '  LEFT JOIN information_schema.key_column_usage k\n' +
    '  USING(constraint_name,table_schema,table_name)\n' +
    "  WHERE t.constraint_type='PRIMARY KEY'\n" +
    "      and table_name != '__drizzle_migrations'\n" +
    '      AND t.table_schema = ?',
  sqlState: 'HY000',
  sqlMessage: 'rpc error: code = Unknown desc = uncaught panic: interface conversion: interface is nil, not sqlparser.Expr, vtgate:

5 replies

Gaming

Programming