JT Posts - Answer Overflow

Topics

JT

Explore posts from servers

NNuxt DTDrizzle Team

•Created by JT on 4/4/2024 in #help

How to do a foreign key with cascades

Right now I'm doing something like this:

mysqlTable('users',
    {
        id: varchar('id', { length: 36 }).notNull().default('uuid-will-be-generated').primaryKey(),
        avatarId: varchar('avatarId', { length: 36 }).default(null).references(() => S3FileTable.id, { onDelete: "set null", onUpdate: "no action" })
    },
);

mysqlTable('users',
    {
        id: varchar('id', { length: 36 }).notNull().default('uuid-will-be-generated').primaryKey(),
        avatarId: varchar('avatarId', { length: 36 }).default(null).references(() => S3FileTable.id, { onDelete: "set null", onUpdate: "no action" })
    },
);

I would like to name the key that is generated here. I understand that I can use the foreignKey() to name the key. However, I don't know how to handle the cascades if I do the foreign key using that function rather than using references(). Can anybody give me a pointer?

3 replies

•Created by JT on 3/13/2024 in #help

Can I pay for a new feature to be added?

I'd really like drizzle kit to support rollback migrations, to downgrade a database. Can I speak to someone about adding that?

3 replies

•Created by JT on 2/18/2024 in #help

query to text

Is there a way to get a query output as text for debugging/logging purposes?

5 replies

•Created by JT on 2/11/2024 in #help

where has iterator gone?

Way back in 0.24.0 I sponsored an iterator for large data sets, but it is no longer in the documentation. Can someone point me to it?

5 replies

•Created by JT on 2/1/2024 in #help

How to get the name of the table

In 0.22 I used to import Name from drizzle-orm/table.js and then I could get the name of the table by calling table[Name]. I'm now upgrading to the latest, and a lot of things have changed. Obviously this is one of them, and I can no longer get the name of the table that way. What is the appropriate way to inspect the object and get the name of the table?

2 replies

•Created by JT on 4/9/2023 in #help

Iterator for result set

I've got some big ass database tables that I'd rather not read into memory. In other languages there is an iterator attached to result set, but I haven't seen that in the JS/TS world anywhere. Is that something I'm going to have to write for myself, or have I missed it? What I'm imagining is either something that uses a DB cursor, or something that basically works like a paginator, where it will select 100 rows at a time, iterate over those rows, and keep doing that each time you call .next() until it has reached the end of the result set.

10 replies

•Created by JT on 4/5/2023 in #help

cascades

I know you can add .references() to set up foreign keys in a table definition, but how do you set up cascades?

2 replies

•Created by JT on 3/18/2023 in #help

What is the type for an .orderBy() parameter

Inside of a function, i have a query more complicated than this, but something like:

db.select().from(users).where(...).orderBy(asc(users.username))

db.select().from(users).where(...).orderBy(asc(users.username))

I'd like to be able to pass in the

asc(users.username)

asc(users.username)

part of that from the outside., but to do that I need to know what type i should be putting on the function parameter.

15 replies

•Created by JT on 3/18/2023 in #help

MySqlInsertValue<> vs typeof Table type mismatch

I've got this line of code:

await db.insert(table).values(props);

await db.insert(table).values(props);

Where props is showing a TS problem:

Argument of type 'ModelMap[T]["select"]' is not assignable to parameter of type 'MySqlInsertValue<ModelMap[T]["model"]>'.
  Type '{ id: string; createdAt: Date; updatedAt: Date; username: string; email: string; realName: string; password: string; passwordType: "bcrypt"; useAsDisplayName: "username" | "email" | "realName"; admin: boolean; developer: boolean; } | { ...; }' is not assignable to type 'MySqlInsertValue<ModelMap[T]["model"]>'.
    Type '{ id: string; createdAt: Date; updatedAt: Date; username: string; email: string; realName: string; password: string; passwordType: "bcrypt"; useAsDisplayName: "username" | "email" | "realName"; admin: boolean; developer: boolean; }' is not assignable to type 'MySqlInsertValue<ModelMap[T]["model"]>'.

Argument of type 'ModelMap[T]["select"]' is not assignable to parameter of type 'MySqlInsertValue<ModelMap[T]["model"]>'.
  Type '{ id: string; createdAt: Date; updatedAt: Date; username: string; email: string; realName: string; password: string; passwordType: "bcrypt"; useAsDisplayName: "username" | "email" | "realName"; admin: boolean; developer: boolean; } | { ...; }' is not assignable to type 'MySqlInsertValue<ModelMap[T]["model"]>'.
    Type '{ id: string; createdAt: Date; updatedAt: Date; username: string; email: string; realName: string; password: string; passwordType: "bcrypt"; useAsDisplayName: "username" | "email" | "realName"; admin: boolean; developer: boolean; }' is not assignable to type 'MySqlInsertValue<ModelMap[T]["model"]>'.

The variables in there are typed like:

db: MySql2Database
table: ModelMap[T]['model'] 
props: ModelMap[T]['select']

db: MySql2Database
table: ModelMap[T]['model'] 
props: ModelMap[T]['select']

The ModelMap is an index of all the different types generated by various drizzle schemas I have, so that they are available for quick reference by just passing T. ModelMap[T]['model'] is equivalent to typeof SomeTable and ModelMap[T]['select'] is equivalent to InferModel<typeof SomeTable> What might be causing this problem?

25 replies

•Created by JT on 3/16/2023 in #help

documented types for the return of `drizzle()` and the return of `mysqlTable()`

my most pressing need is documented types: https://github.com/drizzle-team/drizzle-orm/issues/273 Specifically the 2 that are holding me up right now are how to pass a the mysql2 version of drizzle() (aka a db reference) and a model reference (the result of mysqlTable()) around through my app

41 replies

•Created by JT on 3/16/2023 in #help

.andWhere()

I know that .andWhere() isn't a function, but I'm wondering if there's a way to start a query in one function, and return it. And then have the receiver be able to continue to add to the where clause? Something like:

function startIt() {
   return db.select().from(users).where(eq(users.developer, true));
}

const query = startIt().andWhere(eq(users.application, 'approved'));

function startIt() {
   return db.select().from(users).where(eq(users.developer, true));
}

const query = startIt().andWhere(eq(users.application, 'approved'));

28 replies

•Created by JT on 3/16/2023 in #help

how do you pass a query in a typesafe way?

Let's say i want to paginate a query, so I'm going to pass a query such as this to a pagination function

db.select().from(users).where(like(users.email, '%@gmail.com'))

db.select().from(users).where(like(users.email, '%@gmail.com'))

And then that pagination function will add .limit() and .offset() to the query. What type would I put on the function argument?

11 replies

•Created by JT on 3/15/2023 in #help

help with column builder types

As I play around with learning drizzle, I wondered if I could generate a drizzle schema from a master schema. I'm very close to being able to do that. As you can see in this repo I created: https://github.com/rizen/drizzle-icing/blob/main/user.ts The code works perfectly, but problem is that when I try to use InferModel to get the column types from my schema, I'm getting

type User {
   [x:string]: unknown;
}

type User {
   [x:string]: unknown;
}

Where the I would get the following on a hand-built table:

type User = {
    id: string;
    createdAt: Date;
    username: string;
    password: string;
    useAsDisplayName: "username" | "email" | "realName";
    admin: boolean;
}

type User = {
    id: string;
    createdAt: Date;
    username: string;
    password: string;
    useAsDisplayName: "username" | "email" | "realName";
    admin: boolean;
}

My guess is that the problem lies in the use of the AnyMySqlColumnBuilder type in this function:

export const makeTable = (schema: icingSchema) => {
    const columns: Record<string, AnyMySqlColumnBuilder> = {};
    for (const prop of schema.props) {
        columns[prop.name] = prop.db(prop);
    }
    return mysqlTable(schema.tableName, columns) 
}

export const makeTable = (schema: icingSchema) => {
    const columns: Record<string, AnyMySqlColumnBuilder> = {};
    for (const prop of schema.props) {
        columns[prop.name] = prop.db(prop);
    }
    return mysqlTable(schema.tableName, columns) 
}

Any help on what I should do to get the appropriate types out for InferModel?

76 replies

•Created by JT on 3/14/2023 in #help

migration on mysql errors

I've written this migrator:

import { migrate } from 'drizzle-orm/mysql2/migrator';
import { drizzle } from 'drizzle-orm/mysql2';
import mysql from 'mysql2/promise';
import * as dotenv from 'dotenv';
dotenv.config();
const dbConfig = new URL(process.env.DRIZZLE_DATABASE || '');

async function main() {
    const con = await mysql.createConnection({
        host: dbConfig.hostname,
        port: parseInt(dbConfig.port),
        user: dbConfig.username,
        password: dbConfig.password,
        database: dbConfig.pathname.slice(1),
    });

    const db = drizzle(con);
    await migrate(db, { migrationsFolder: 'drizzle/migrations' });
    await con.end();
}
main();

import { migrate } from 'drizzle-orm/mysql2/migrator';
import { drizzle } from 'drizzle-orm/mysql2';
import mysql from 'mysql2/promise';
import * as dotenv from 'dotenv';
dotenv.config();
const dbConfig = new URL(process.env.DRIZZLE_DATABASE || '');

async function main() {
    const con = await mysql.createConnection({
        host: dbConfig.hostname,
        port: parseInt(dbConfig.port),
        user: dbConfig.username,
        password: dbConfig.password,
        database: dbConfig.pathname.slice(1),
    });

    const db = drizzle(con);
    await migrate(db, { migrationsFolder: 'drizzle/migrations' });
    await con.end();
}
main();

and tried to run this migration, which was generated by drizzle-kit:

CREATE TABLE `users` (
    `id` serial AUTO_INCREMENT PRIMARY KEY NOT NULL,
    `createdAt` timestamp NOT NULL DEFAULT (now()),
    `updatedAt` timestamp NOT NULL DEFAULT (now()),
    `username` varchar(60) NOT NULL,
    `email` varchar(255) NOT NULL,
    `realName` varchar(60) NOT NULL,
    `password` varchar(255) NOT NULL DEFAULT 'no-password-specified',
    `passwordType` enum('bcrypt') NOT NULL DEFAULT 'bcrypt',
    `useAsDisplayName` enum('username','email','realName') NOT NULL DEFAULT 'username',
    `admin` boolean NOT NULL DEFAULT false
);

CREATE UNIQUE INDEX usernameIndex ON users (`username`);
CREATE UNIQUE INDEX emailIndex ON users (`email`);

CREATE TABLE `users` (
    `id` serial AUTO_INCREMENT PRIMARY KEY NOT NULL,
    `createdAt` timestamp NOT NULL DEFAULT (now()),
    `updatedAt` timestamp NOT NULL DEFAULT (now()),
    `username` varchar(60) NOT NULL,
    `email` varchar(255) NOT NULL,
    `realName` varchar(60) NOT NULL,
    `password` varchar(255) NOT NULL DEFAULT 'no-password-specified',
    `passwordType` enum('bcrypt') NOT NULL DEFAULT 'bcrypt',
    `useAsDisplayName` enum('username','email','realName') NOT NULL DEFAULT 'username',
    `admin` boolean NOT NULL DEFAULT false
);

CREATE UNIQUE INDEX usernameIndex ON users (`username`);
CREATE UNIQUE INDEX emailIndex ON users (`email`);

And I get this error:

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE UNIQUE INDEX usernameIndex ON users (`username`);
CREATE UNIQUE INDEX ema' at line 14
    at PromiseConnection.query (/Users/jtsmith/ving/.output/drizzle/migrate.js:18762:26)
    at MySql2PreparedQuery.<anonymous> (/Users/jtsmith/ving/.output/drizzle/migrate.js:1263:32)
    at Generator.next (<anonymous>)
    at /Users/jtsmith/ving/.output/drizzle/migrate.js:1220:71
    at new Promise (<anonymous>)
    at __awaiter (/Users/jtsmith/ving/.output/drizzle/migrate.js:1202:14)
    at MySql2PreparedQuery.execute (/Users/jtsmith/ving/.output/drizzle/migrate.js:1258:16)
    at MySql2Session.execute (/Users/jtsmith/ving/.output/drizzle/migrate.js:1094:82)
    at MySqlDialect.<anonymous> (/Users/jtsmith/ving/.output/drizzle/migrate.js:871:33)
    at Generator.next (<anonymous>) {
  code: 'ER_PARSE_ERROR',
  errno: 1064,
  sql: 'CREATE TABLE `users` (\n' +
    '\t`id` serial AUTO_INCREMENT PRIMARY KEY NOT NULL,\n' +
    '\t`createdAt` timestamp NOT NULL DEFAULT (now()),\n' +
    '\t`updatedAt` timestamp NOT NULL DEFAULT (now()),\n' +
    '\t`username` varchar(60) NOT NULL,\n' +
    '\t`email` varchar(255) NOT NULL,\n' +
    '\t`realName` varchar(60) NOT NULL,\n' +
    "\t`password` varchar(255) NOT NULL DEFAULT 'no-password-specified',\n" +
    "\t`passwordType` enum('bcrypt') NOT NULL DEFAULT 'bcrypt',\n" +
    "\t`useAsDisplayName` enum('username','email','realName') NOT NULL DEFAULT 'username',\n" +
    '\t`admin` boolean NOT NULL DEFAULT false\n' +
    ');\n' +
    '\n' +
    'CREATE UNIQUE INDEX usernameIndex ON users (`username`);\n' +
    'CREATE UNIQUE INDEX emailIndex ON users (`email`);',
  sqlState: '42000',
  sqlMessage: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE UNIQUE INDEX usernameIndex ON users (`username`);\n" +
    "CREATE UNIQUE INDEX ema' at line 14"
}

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE UNIQUE INDEX usernameIndex ON users (`username`);
CREATE UNIQUE INDEX ema' at line 14
    at PromiseConnection.query (/Users/jtsmith/ving/.output/drizzle/migrate.js:18762:26)
    at MySql2PreparedQuery.<anonymous> (/Users/jtsmith/ving/.output/drizzle/migrate.js:1263:32)
    at Generator.next (<anonymous>)
    at /Users/jtsmith/ving/.output/drizzle/migrate.js:1220:71
    at new Promise (<anonymous>)
    at __awaiter (/Users/jtsmith/ving/.output/drizzle/migrate.js:1202:14)
    at MySql2PreparedQuery.execute (/Users/jtsmith/ving/.output/drizzle/migrate.js:1258:16)
    at MySql2Session.execute (/Users/jtsmith/ving/.output/drizzle/migrate.js:1094:82)
    at MySqlDialect.<anonymous> (/Users/jtsmith/ving/.output/drizzle/migrate.js:871:33)
    at Generator.next (<anonymous>) {
  code: 'ER_PARSE_ERROR',
  errno: 1064,
  sql: 'CREATE TABLE `users` (\n' +
    '\t`id` serial AUTO_INCREMENT PRIMARY KEY NOT NULL,\n' +
    '\t`createdAt` timestamp NOT NULL DEFAULT (now()),\n' +
    '\t`updatedAt` timestamp NOT NULL DEFAULT (now()),\n' +
    '\t`username` varchar(60) NOT NULL,\n' +
    '\t`email` varchar(255) NOT NULL,\n' +
    '\t`realName` varchar(60) NOT NULL,\n' +
    "\t`password` varchar(255) NOT NULL DEFAULT 'no-password-specified',\n" +
    "\t`passwordType` enum('bcrypt') NOT NULL DEFAULT 'bcrypt',\n" +
    "\t`useAsDisplayName` enum('username','email','realName') NOT NULL DEFAULT 'username',\n" +
    '\t`admin` boolean NOT NULL DEFAULT false\n' +
    ');\n' +
    '\n' +
    'CREATE UNIQUE INDEX usernameIndex ON users (`username`);\n' +
    'CREATE UNIQUE INDEX emailIndex ON users (`email`);',
  sqlState: '42000',
  sqlMessage: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE UNIQUE INDEX usernameIndex ON users (`username`);\n" +
    "CREATE UNIQUE INDEX ema' at line 14"
}

Now each of the commands individually inside that migration work. So I think it's croaking on the fact that all 3 lines are being sent at once. Any thoughts on how to get around this?

8 replies

•Created by JT on 3/14/2023 in #help

drizzle-kit generates mysql 8 syntax, but AWS Aurora uses 5.7 syntax, any way to switch?

I think the title says it all

2 replies

•Created by JT on 3/14/2023 in #help

error: there is no transaction in progress

I created a drizzle table:

export const passwordTypeEnum = pgEnum('passwordType', ['bcrypt']);
export const useAsDisplayNameEnum = pgEnum('useAsDisplayName', ['username', 'email', 'realName']);
export const users = pgTable('users',
    {
        id: serial('id').primaryKey(),
        createdAt: timestamp('createdAt').defaultNow().notNull(),
        updatedAt: timestamp('updatedAt').defaultNow().notNull(),
        username: varchar('username', { length: 60 }).notNull(),
        email: varchar('email', { length: 255 }).notNull(),
        realName: varchar('realName', { length: 60 }).notNull(),
        password: varchar('password', { length: 255 }).notNull().default('no-password-specified'),
        passwordType: passwordTypeEnum('passwordType').default('bcrypt').notNull(),
        useAsDisplayName: useAsDisplayNameEnum('useAsDisplayName').default('username').notNull(),
        admin: boolean('admin').notNull().default(false),
        developer: boolean('admin').notNull().default(false),
    },
    (users) => ({
        usernameIndex: uniqueIndex('usernameIndex').on(users.username),
        emailIndex: uniqueIndex('emailIndex').on(users.email),
    })
);

export const passwordTypeEnum = pgEnum('passwordType', ['bcrypt']);
export const useAsDisplayNameEnum = pgEnum('useAsDisplayName', ['username', 'email', 'realName']);
export const users = pgTable('users',
    {
        id: serial('id').primaryKey(),
        createdAt: timestamp('createdAt').defaultNow().notNull(),
        updatedAt: timestamp('updatedAt').defaultNow().notNull(),
        username: varchar('username', { length: 60 }).notNull(),
        email: varchar('email', { length: 255 }).notNull(),
        realName: varchar('realName', { length: 60 }).notNull(),
        password: varchar('password', { length: 255 }).notNull().default('no-password-specified'),
        passwordType: passwordTypeEnum('passwordType').default('bcrypt').notNull(),
        useAsDisplayName: useAsDisplayNameEnum('useAsDisplayName').default('username').notNull(),
        admin: boolean('admin').notNull().default(false),
        developer: boolean('admin').notNull().default(false),
    },
    (users) => ({
        usernameIndex: uniqueIndex('usernameIndex').on(users.username),
        emailIndex: uniqueIndex('emailIndex').on(users.email),
    })
);

Which then generated this migration:

DO $$ BEGIN
 CREATE TYPE "passwordType" AS ENUM('bcrypt');
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
 CREATE TYPE "useAsDisplayName" AS ENUM('username', 'email', 'realName');
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

CREATE TABLE IF NOT EXISTS "users" (
    "id" serial PRIMARY KEY NOT NULL,
    "createdAt" timestamp DEFAULT now() NOT NULL,
    "updatedAt" timestamp DEFAULT now() NOT NULL,
    "username" varchar(60) NOT NULL,
    "email" varchar(255) NOT NULL,
    "realName" varchar(60) NOT NULL,
    "password" varchar(255) DEFAULT 'no-password-specified' NOT NULL,
    "passwordType" passwordType DEFAULT 'bcrypt' NOT NULL,
    "useAsDisplayName" useAsDisplayName DEFAULT 'username' NOT NULL,
    "admin" boolean DEFAULT false NOT NULL
);

CREATE UNIQUE INDEX IF NOT EXISTS usernameIndex ON users ("username");
CREATE UNIQUE INDEX IF NOT EXISTS emailIndex ON users ("email");

DO $$ BEGIN
 CREATE TYPE "passwordType" AS ENUM('bcrypt');
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
 CREATE TYPE "useAsDisplayName" AS ENUM('username', 'email', 'realName');
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

CREATE TABLE IF NOT EXISTS "users" (
    "id" serial PRIMARY KEY NOT NULL,
    "createdAt" timestamp DEFAULT now() NOT NULL,
    "updatedAt" timestamp DEFAULT now() NOT NULL,
    "username" varchar(60) NOT NULL,
    "email" varchar(255) NOT NULL,
    "realName" varchar(60) NOT NULL,
    "password" varchar(255) DEFAULT 'no-password-specified' NOT NULL,
    "passwordType" passwordType DEFAULT 'bcrypt' NOT NULL,
    "useAsDisplayName" useAsDisplayName DEFAULT 'username' NOT NULL,
    "admin" boolean DEFAULT false NOT NULL
);

CREATE UNIQUE INDEX IF NOT EXISTS usernameIndex ON users ("username");
CREATE UNIQUE INDEX IF NOT EXISTS emailIndex ON users ("email");

That creates this error when I try to use the migrator to run it.

error: there is no transaction in progress

error: there is no transaction in progress

It looks like transactions were generated to wrap around the enums, but not the create table or create indexes. Did I do something wrong here? Or is this error pointing to something else?

7 replies

•Created by JT on 3/13/2023 in #help

Having a problem just connecting with drizzle

Howdy, I just found out about drizzle and thought I might give it a try, but unfortunately I'm having trouble just getting the example working from the documentation. I've created this sample file:

import { drizzle } from 'drizzle-orm/mysql2';
import mysql from 'mysql2/promise';
import { mysqlTable,  serial,  text, varchar} from 'drizzle-orm/mysql-core';

const users = mysqlTable('users', {
    id: serial('id').primaryKey(),
    fullName: text('full_name'),
    phone: varchar('phone', { length: 256 }),
});

console.log('create pool');
const poolConnection = mysql.createPool({
    host: 'localhost',
    user: 'user',
    password: 'pass',
    database: 'drizzle',
});

console.log('create db');
export const db = drizzle(poolConnection);
async function main() {
    console.log('list users');
    const allUsers = await db.select().from(users);
    console.log(allUsers);
    console.log('after list users');
    return
}
console.log('main')
main();
console.log('after main')

import { drizzle } from 'drizzle-orm/mysql2';
import mysql from 'mysql2/promise';
import { mysqlTable,  serial,  text, varchar} from 'drizzle-orm/mysql-core';

const users = mysqlTable('users', {
    id: serial('id').primaryKey(),
    fullName: text('full_name'),
    phone: varchar('phone', { length: 256 }),
});

console.log('create pool');
const poolConnection = mysql.createPool({
    host: 'localhost',
    user: 'user',
    password: 'pass',
    database: 'drizzle',
});

console.log('create db');
export const db = drizzle(poolConnection);
async function main() {
    console.log('list users');
    const allUsers = await db.select().from(users);
    console.log(allUsers);
    console.log('after list users');
    return
}
console.log('main')
main();
console.log('after main')

Then compiled it into JS like this:

npx esbuild db.ts --platform=node --bundle --log-level=warning --outfile=db.js

npx esbuild db.ts --platform=node --bundle --log-level=warning --outfile=db.js

Then ran it like this:

node db.js

node db.js

The execution order is wrong, and the program just hangs at the end rather than exiting cleanly. Here's the output:

create pool
create db
main
list users
after main
[]
after list users

create pool
create db
main
list users
after main
[]
after list users

Any ideas what I'm doing wrong?

8 replies