Drizzle Team•2mo ago

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" }),
});

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))
);

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!

1 Reply

loliklr:)OP•2mo ago

Bump:))

Gaming

Programming

Using date object for sqlite in INSERT INTO SELECT

Did you find this page helpful?