Drizzle TeamDT
Drizzle Team•10mo ago
loliklr:)

Using date object for sqlite in INSERT INTO SELECT

I'm struggling with an insert into select query in SQLite where I need to provide custom timestamp values.

Here's my table definition:
export const games = sqliteTable("games", {
  id: text("id", { length: 36 })
    .primaryKey()
    .$defaultFn(() => randomUUID()),
  playerId: text("doctor_id")
    .notNull()
    .references(() => users.id),
  name: text("name").notNull(),
  createdAt: integer("created_at", { mode: "timestamp" }).notNull(),
  updatedAt: integer("updated_at", { mode: "timestamp" }).notNull(),
  deletedAt: integer("deleted_at", { mode: "timestamp" }),
});


I'm trying to insert a new game record by selecting some fields from the users table, but I want to provide custom timestamps for createdAt and updatedAt (they should be when the game started, not when the DB insert happens). Here's what I tried:

const result = await db.insert(games).select(
  db
    .select({
      playerId: users.id,
      name: users.firstName, // Need current name from users table
      createdAt: sql<Date>`${gameStartDate}`.as("created_at"),
      updatedAt: sql<Date>`${gameStartDate}`.as("updated_at"),
    })
    .from(users)
    .where(eq(users.id, ctx.user.id))
);


This throws an error because SQLite seems confused about the date format. What's the correct way to provide custom timestamp values in an insert into select query with Drizzle?

Thanks for any help!
Was this page helpful?