Drizzle Schema Defaults

This is one of my table schemas in Prisma

model User {
  id          Int          @id @default(autoincrement())
  email       String       @unique
  username    String       @unique
  credentials Credential[]

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

model User {
  id          Int          @id @default(autoincrement())
  email       String       @unique
  username    String       @unique
  credentials Credential[]

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

Are identifiers such as @id and default with values such as autoincrement() and now() available in Drizzle? I am using better-sqlite-3 as the driver

13 Replies

Angelelz•10mo ago

sure https://orm.drizzle.team/docs/column-types/sqlite

Drizzle ORM - next gen TypeScript ORM

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

christradingOP•10mo ago

Ah thanks. What would be the best column type to represent a Uint8Array? blob? I'm still getting 'unknown' as the implied data type in my IDE intellisense so it's failing my typescript IDE check Ah, buffer mode

publicKey: blob('public_key', { mode: 'buffer' }).unique().notNull(),

publicKey: blob('public_key', { mode: 'buffer' }).unique().notNull(),

so date: text("date").default(sqlCURRENT_DATE), will be the current date like new Date() every time the table is updated or only when table is created? If not, how do we do this everytime table is updated without having to explcility update the value? for the createdAt and updatedAt fields

Angelelz•10mo ago

SQLite doesn't have an on update like clause like MySQL. But there is an open PR in review phase implementing this in the orm

christradingOP•10mo ago

What do Drizzle relations actually do once you define them as they are application level and not database level foreign key constraints?

christradingOP•10mo ago

@Angelelz If I have I have a 'users' table and a 'credentials' table, where one user has many credentials, would I still have to do two round-trip queries to get the specific user, and then the user's credentials? Will defining a relation help that or yeah what does Drizzle relations actually do? https://orm.drizzle.team/docs/rqb#declaring-relations I found this page from search, I'm not sure what rqb is from the URL, but seeing relations is imported from drizzle-orm and not a specific driver library, I hope and presume that this page feature is supported agnositc to what driver (SQLite) we're using

Drizzle ORM - next gen TypeScript ORM

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

Angelelz•10mo ago

You would be correct assuming that

christradingOP•10mo ago

How do I query with the related table in SQLite (better-sqlite-3)? I see in a postgres example from Planetscale's docs

const user = await db.query.users.findFirst({
  where: eq(users.username, username),
  // Providing `with` tells Drizzle you want to return related data
  with: {
    blocks: true
  }
})

const user = await db.query.users.findFirst({
  where: eq(users.username, username),
  // Providing `with` tells Drizzle you want to return related data
  with: {
    blocks: true
  }
})

But I don't see a .with() command coming up with intellisense for an SQLite query

  const userCredential = db
    .select()
    .from(credential)
    .where(eq(credential.externalID, authenticationResponse.id))
    .prepare()
    .get()

  if (userCredential == null) {
    throw new Error('Unknown User Credential')
  }

  try {
    db
      .update(credential)
      .set({
        signCount: verification.authenticationInfo.newCounter,
      })
      .where(
        eq(credential.id, userCredential.id),
      )
      .prepare()
      .run()
  } catch (error) {
    console.error(error)
    throw error
  }

  const user = db
    .select()
    .from(drizzleUser)
    .where(
      eq(drizzleUser.id, userCredential.userID),
    )
    .prepare()
    .get()

  const userCredential = db
    .select()
    .from(credential)
    .where(eq(credential.externalID, authenticationResponse.id))
    .prepare()
    .get()

  if (userCredential == null) {
    throw new Error('Unknown User Credential')
  }

  try {
    db
      .update(credential)
      .set({
        signCount: verification.authenticationInfo.newCounter,
      })
      .where(
        eq(credential.id, userCredential.id),
      )
      .prepare()
      .run()
  } catch (error) {
    console.error(error)
    throw error
  }

  const user = db
    .select()
    .from(drizzleUser)
    .where(
      eq(drizzleUser.id, userCredential.userID),
    )
    .prepare()
    .get()

I'd like to just get back the user for the respective credential rather than having to do a seperate and additional round-trip query

Angelelz•10mo ago

There are several steps you have to follow to have that syntax available Declaring relations, exporting them and passing them to the drizzle function when instantiating the db object

Angelelz•10mo ago

https://orm.drizzle.team/docs/rqb

Drizzle ORM - next gen TypeScript ORM

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

christradingOP•10mo ago

Thanks, I was able to get it to work now. Are there any benchmarks available between regular queries (two queries) and the relational queries (one query)? Does it save a round-trip, it doesn't just do two round-trips under the hood, correct? So we can expect better performance refactoring all of our functions to relational queries? I see these benchmarks for regular queries vs prepared queries but not anything about relational queries: https://github.com/drizzle-team/drizzle-northwind-benchmarks/tree/master

GitHub

GitHub - drizzle-team/drizzle-northwind-benchmarks

Contribute to drizzle-team/drizzle-northwind-benchmarks development by creating an account on GitHub.

christradingOP•10mo ago

From this article: https://medium.com/drizzle-stories/best-typescript-orm-just-got-better-5a33688b8d2e

Regardless of how many nested relations you query - Drizzle will always make exactly one SQL query to the database, it makes it extremely explicit and easy to tune performance with indexes.

Are there any specific cases where I would not want to migrate over to relational queries for performance or is relational always lower latency?

Medium

Best TypeScript ORM just got better

Drizzle ORM v0.26 is out and it changes the game

christradingOP•10mo ago

And oh rqb in the URL means relational query builder doesnt it

Angelelz•10mo ago

Yes

Gaming

Programming

Drizzle Schema Defaults