So for a while now I have been working on a new app for my Local Game Store for our Magic: The Gathering sessions. For this app, I decided to try out SvelteKit, a newer JavaScript framework that is meant to simply apps that need both front-end and back-end services. Yes, I could use something like Laravel or Vite and a custom backend, but after getting frustrated with React I decided to try out SvelteKit.
The only reason this matters is that this means my stack is Node.js-based because of this decision. Since this app will be heavily data-driven I will need a database, but it's also very much an MVP that is changing week to week as we tweak it. I wanted a database ORM that would give me migrations to make it easier to deploy changes. This led me to Prisma, the "next-generation Node.js and TypeScript ORM."
Great! Now, since I am building this on the cheap I did not want to deal with database hosting. I wanted to deploy the application to one of my existing servers so I picked SQLite. Since I am using an ORM I could easily switch to a different relational database later on and migrate the data.
So far everything has been working great, at least it was until I needed to start building reports. After failing to use the ORM to build the joins I needed, I resorted to just writing the SQL I needed. The data is normalized in a sane way, so the calculation I needed was a simple join between three tables and a GROUP BY and SUM().
Here was where I ran into my first issue.
Prisma has you define all the schemas in a schema.prisma file using their own syntax. It is nothing complicated, but you define the tables and columns, and it uses this to write migrations as well as generate ORM objects to use in the code. For a game we have a createdAt and a submittedAt column that I have set to a Datetime type.
// schema.prisma
model Game {
id Int @id @default(autoincrement())
title String
createdAt DateTime @default(now())
submittedAt DateTime?
owner Player @relation(fields: [ownerId], references: [id])
ownerId Int
objectives GameObjective[]
players GamePlayer[]
}
The report I was writing was for a month's worth of time, so I wrote a basic SQL query to find all the games that were submitted within the month. The best part was I got no results back.
SELECT * FROM Game
WHERE submittedAt >= '2023-07-01 00:00:00' AND submittedAt < '2023-08-01 00:00:00'
No results! So I decided to look at the data as there was no reason such a simple query should fail. I knew I had data there.
This was when I learned a very important thing about SQLite - it does not have a DateTime data type. It does have date-time functions and can work with date-time values though, we just need to store string dates as TEXT and in the ISO8601 format, REAL for Julian day numbers, or an INTEGER for Unix Timestamps.
Looking at the data in the table, I noticed large numbers. Awesome, we have Unix Timestamps. This is not the best as I would have preferred strings, but it will do.
SELECT * FROM Game
WHERE submittedAt >= 1688169600 AND submittedAt < 1690848000
This still returned no results. If I took off the AND ... part I got the expected games, but I needed to restrain the games by a single month. I went down another rabbit hole trying to figure this out, because none of the date-time functions worked either. I was looking at the data and realized that these numbers were stored in a text column.
This was where I learned another thing about SQLite - SQLite uses "Type Affinity" for data.
In order to maximize compatibility between SQLite and other database engines, and so that the example above will work on SQLite as it does on other SQL database engines, SQLite supports the concept of "type affinity" on columns. The type affinity of a column is the recommended type for data stored in that column. The important idea here is that the type is recommended, not required. Any column can still store any type of data. It is just that some columns, given the choice, will prefer to use one storage class over another. The preferred storage class for a column is called its "affinity".
The type of the column is merely a suggestion, and you can store any type of data in any type of column. SQLite will just make it work.
I tried to convert the column to an integer, which was also when I learned you could not change a column's type. You have to make a temporary table with the new schema, copy the data over, delete the original table, and rename the temporary table.
This still did not fix it.
OK, fine, whatever. I swapped the table back to being a TEXT field. It was after this I noticed that I was now getting errors saying that the createdAt and submittedAt values were too large for the INTEGER datatype, and to switch to BIGINT. Why was I getting this now? Shouldn't a Unix Timestamp fit inside an integer? I should have a good fifteen years until 2038 when this will be a problem.
I then realized the problem. My data was not a proper Unix Timestamp, which is the number of seconds since the epoch (1970-01-01). My data was the number of milliseconds since the epoch, which is not a Unix Timestamp.
This is why none of the date-time functions would work for me, because the data I was passing in was not a valid Unix Timestamp. My sample queries were not working because the greater-than-or-equal value was small enough to find my games, but the less-than value I was using was too small. The comparison worked fine, I was just using proper Unix Timestamps to generate the query so my WHERE condition failed.
But wait, aren't I using an ORM that should be properly storing my data?
So I tested my sample query with milliseconds and it worked perfectly.
It turns out Prisma is storing the SQLite DateTime type as a millisecond-since-epoch value, not any of the three values that SQLite will interpret as DateTime values. Prisma just seems to be turning date strings into a new Date() object and just dumping it using Date().getTime(). It is not doing the best for compatibility (a date-time string in ISO8601 format) but doing the least amount of work to store the data. Prisma is not even using Date().toISOString() to get a date.
Two hours of my time went down the drain because SQLite allows duck typing on data, and Prisma decided to store dates in a non-standard format.
Thanks, JavaScript.