DT
Drizzle Teamcvr

How to set a default timestamp_ms for sqlite

How do I make a default timestamp_ms? 
createdAt: integer("createdAt", {
      mode: "timestamp_ms",
    })
      .notNull().default( // what goes in here )
createdAt: integer("createdAt", {
      mode: "timestamp_ms",
    })
      .notNull().default( // what goes in here )
D
dandadan•305d ago
not sure which driver/dialect you are using but for postgres it would be something like this 
createdAt: timestamp("created_at", {
  withTimezone: true,
}).defaultNow(),
createdAt: timestamp("created_at", {
  withTimezone: true,
}).defaultNow(),
for updatedAt you can do this, but theres nothing implemented to automatically update the timestamp whenever changes to the row are made 
updatedAt: timestamp("updated_at", {
  withTimezone: true,
}), // null at first, must be updated manually sadly
updatedAt: timestamp("updated_at", {
  withTimezone: true,
}), // null at first, must be updated manually sadly
these get compiled to the following btw 
"created_at" timestamp with time zone DEFAULT now(),
"updated_at" timestamp with time zone,
"created_at" timestamp with time zone DEFAULT now(),
"updated_at" timestamp with time zone,
C
cvr•305d ago
yeah unfortunately this is with sqlite 😦
D
dandadan•305d ago
https://orm.drizzle.team/docs/column-types/sqlite 
import { sql } from "drizzle-orm";
import { text, sqliteTable } from "drizzle-orm/sqlite-core";
 
const table = sqliteTable("table", {
  time: text("time").default(sql`CURRENT_TIME`),
  date: text("date").default(sql`CURRENT_DATE`),
  timestamp: text("timestamp").default(sql`CURRENT_TIMESTAMP`),
});
import { sql } from "drizzle-orm";
import { text, sqliteTable } from "drizzle-orm/sqlite-core";
 
const table = sqliteTable("table", {
  time: text("time").default(sql`CURRENT_TIME`),
  date: text("date").default(sql`CURRENT_DATE`),
  timestamp: text("timestamp").default(sql`CURRENT_TIMESTAMP`),
});
CREATE TABLE `table` (
    `time` text DEFAULT CURRENT_TIME
    `date` text DEFAULT CURRENT_DATE
    `timestamp` text DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE `table` (
    `time` text DEFAULT CURRENT_TIME
    `date` text DEFAULT CURRENT_DATE
    `timestamp` text DEFAULT CURRENT_TIMESTAMP
);
Sqlite – DrizzleORM
Drizzle ORM | %s
D
dandadan•305d ago
i havent been able to find timestamp_ms on google so not sure what that is
C
cvr•305d ago
ah okay, so it has to be a string basically right? was hoping to get the automatic conversion to Date drizzle provides drizzle basically provides an automatic conversion to a Date object when using 
integer("createdAt", {
      mode: "timestamp_ms" | "timestamp",
    })
integer("createdAt", {
      mode: "timestamp_ms" | "timestamp",
    })
D
dandadan•305d ago
sadly i am unable to test due to an issue but 
integer("createdAt", {
  mode: "timestamp_ms" | "timestamp",
}).default(sql`(STRFTIME('%s', 'now') * 1000)`);
integer("createdAt", {
  mode: "timestamp_ms" | "timestamp",
}).default(sql`(STRFTIME('%s', 'now') * 1000)`);
maybe something like this
C
cvr•305d ago
thank you! i wonder, does that return an int or a string? does SQL automatically cast strings when doing multiplications?
D
dandadan•305d ago
according to chatgpt 
In the above SQL command, the createdAt column is initialized with the Unix timestamp (seconds since 1970-01-01 00:00:00 UTC) multiplied by 1000 to mimic milliseconds. But again, this doesn't give you millisecond precision, rather it gives you the current Unix timestamp in a "milliseconds" format.

If millisecond precision is critical for your use case, you may need to consider a different database system that supports this feature, or handle the timestamp generation in your application code before inserting data into the database.
In the above SQL command, the createdAt column is initialized with the Unix timestamp (seconds since 1970-01-01 00:00:00 UTC) multiplied by 1000 to mimic milliseconds. But again, this doesn't give you millisecond precision, rather it gives you the current Unix timestamp in a "milliseconds" format.

If millisecond precision is critical for your use case, you may need to consider a different database system that supports this feature, or handle the timestamp generation in your application code before inserting data into the database.
to my knowledge, as long as the generated sql command is valid, then sqlite should take care of the rest the column in the database should be an integer, so drizzle should also return an integer but i would really test it out since sqlite is not really made for things like these so you have to do some manual shenanigans to get the desired behavior
C
cvr•304d ago
thanks for this!
I
IP•123d ago
was just having the same issue, trying to insert a millesec-timestamp into integer("createdAt", {mode: "timestamp_ms" }) field. Had to use the createdAt: integer('created_at', { mode: 'timestamp_ms' }).$default(() => new Date()), which has a $default function (instead of the native sql function). It did work: 
  {
    createdAt: 2024-01-02T16:52:53.621Z,
  }
  {
    createdAt: 2024-01-02T16:52:53.621Z,
  }
... but would have been nice to have a native solution. I did a lot of trial and error trying to get the unixepoch('subsec') to work, but it would just end up as a null at the end. Maybe because it was a float, and drizzle was expecting an int. This is the expression i used for the reference. 
createdAt: integer('created_at', { mode: 'timestamp_ms' }).default(sql`(unixepoch('subsecond')*1000)`)
createdAt: integer('created_at', { mode: 'timestamp_ms' }).default(sql`(unixepoch('subsecond')*1000)`)
M
m&tra•85d ago
@IP @cvr @dandadan I was using IP's solution with a $default arrow function in my schema.ts returning new Date(), but the generated migration files didn't have SQL-defined DEFAULTs. I found a solution that sets the SQL default and works with SQLite, returning the same timestamp format as new Date(): 
created_at: text('created_at').default(sql`(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))`).notNull()
created_at: text('created_at').default(sql`(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))`).notNull()
Want results from more Discord servers?
Add your server
More Posts
Migrations not being applied (drizzle-orm/node-postgres/migrator)im kind of struggling with migrations... i have the following migration index.ts which i run wheneveDelete limitHi, how do I delete just 1 row from a table? ```ts // Example: db.delete(dogs).where(eq(dogs.breed, Many to many - PlanetscaleAnyone here has an example of declaring many to many relationship when using Planetscale? https://orInserting with RelationHi, What is the way of inserting a new record with a relationship in Drizzle. I understand that Prisql escaping on whereTrying to do the following: ``` where: sql`unaccent(${users.fullName}) ILIKE unaccent('%${input.queBroken typescript in selectWhatever I put inside the select function. it will generate this typescript error: I am using the laWhy is introspecting creating tables that didn't previously existMore context here: https://github.com/drizzle-team/drizzle-orm/issues/847Passing SQL functions to insertIs there any way to pass a SQL function like `gen_random_uuid()` to the `insert` function, for a speDrizzle-kit database push connection errorWhen trying to run `drizzle-kit push:mysql` to push my schema to Planetscale, I get the following erTypescript error when importing the planetscale-drizzle db instance.my db is hosted on planetscale and im trying to do the setup as explained in the drizzle docs but whMapped column name and relation with the original nameHi, I am taking over an old project and have found an issue, I'm not sure if I should file it as a bHow to properly do foreign keys in schema with PlanetScale?I'm aware that foreign key *constrains* aren't supported, but foreign keys themselves are. push: ``Typescript path alias not working properlyI'm new to Drizzle, but I'm not able to use the path alias on schema files. Using absolute paths onExpand query from ID using .select()Hi! I am trying to get the first name of a comment author, but only store the ID so the user can chaPGEnum -> Typescript EnumHey there. Is there any convenient way people have found to conveniently turn a pgEnum into a typescIs Drizzle Kit/Drizzle Studio possible with RDS Data API?I'm able to create the RDSDataClient for querying my DB using Drizzle ORM, but I don't see a way to How to apply migrations on postgres in a serverless enviroment?Hello, I want to apply postgres migrations to my vercel DB. I see that the push command does not woError when trying to generate a migration schemaanyone any ideas to what the problem is? It was working fine yesterday and now it throws an error whModelling self relationsI have a table `categories` with a parent fields: ``` { id: text("id").notNull().primaryKey(), .Issue running migrations to DBI am having an issue running migrations to Neon. I checked that everything is exporting const variab