I recently migrated a sqlite database created with prisma to postgres and ran into an issue that I ended up resolving, and want to leave a comment on this for anyone else who may end up running into the same issue I did.
In my prisma schema I had some DateTime values defined like createdAt DateTime @default(now()). This creates a sqlite column of sqlite type "DateTime" with the default CURRENT_TIMESTAMP. When running pgloader, the default casting types for sqlite don't handle this appropriately. It turns out this value was being saved as a unix time value with millisecond precision, so it needed a custom cast to succeed. My solution does not preserve the millisecond precision, but this may be good enough for others who don't need that level of accuracy for their use case.
Drop the "with quote identifiers" if you do not want to preserve casing. For that option to work properly, the fix in this PR was also required.
load database
from sqlite:///<filepath>
into pgsql://<connectionstring>
with quote identifiers
cast type datetime to timestamptz using (lambda (x) (unix-timestamp-to-timestamptz (if x (floor x 1000))))
excluding table names like '_prisma_migrations';
I recently migrated a sqlite database created with prisma to postgres and ran into an issue that I ended up resolving, and want to leave a comment on this for anyone else who may end up running into the same issue I did.
In my prisma schema I had some DateTime values defined like
createdAt DateTime @default(now()). This creates a sqlite column of sqlite type "DateTime" with the default CURRENT_TIMESTAMP. When running pgloader, the default casting types for sqlite don't handle this appropriately. It turns out this value was being saved as a unix time value with millisecond precision, so it needed a custom cast to succeed. My solution does not preserve the millisecond precision, but this may be good enough for others who don't need that level of accuracy for their use case.Drop the "with quote identifiers" if you do not want to preserve casing. For that option to work properly, the fix in this PR was also required.