SQLite support

[Maxime-J]

SQLite support, with appropriate date/time handling, Docker config left to be done. I don't encounter any problems but it needs thorough testing.

Configuration in .env DATABASE_URL=file:/absolute/path/to/database.db DATABASE_URL=file:./database.db with a relative path, relative to prisma folder in the app/umami path.

Few informations about it:

• SQLite doesn't have an appropriate format for time/date, so all timestamps are stored in db as integer, in ms. And that's why query params are changed.
• Like in MySQL, ? placeholder is used.
• The specific concat function used for events isn't supported by SQLite, so it's replaced before query execution. (I tried to avoid the replacement by not using concat regardless of db, but had problems with MySQL)
• https://www.sqlite.org/lang_datefunc.html for SQLite date/time functions, unixepoch function not being supported in prisma (prisma#12831)

[vercel[bot]]

@Maxime-J is attempting to deploy a commit to the umami-software Team on Vercel.

A member of the Team first needs to authorize it.

[mikecao]

We have a new query structure now. Instead of everything lib/queries there is now a queries folder with all the individual queries. Sorry you'll have to update your PR a bit.

[Maxime-J]

Rebased to current structure, with these changes:

• Updates on PostgreSQL prisma schema reflected on the SQLite one.
• New version of Prisma fires an imo incorrect error with SQLite INTEGER and dates => now using DATETIME, which is treated by SQLite as NUMERIC
• More sustainable method to handle SQLite specificities.

UTM feature not treated, as it has to be reworked.

[mikecao]

@Maxime-J Is this ready to be merged as it is? It's okay if there is some work left as long as it doesn't break anything.

[timonson]

This is awesome @Maxime-J , I can't wait to try it out! Would you mind adding a couple of lines of documentation to the README as well? Thanks!

[Maxime-J]

@mikecao Ready to be merged as it is, tested with a dataset of 2.5k views, same working behaviour for other DBs.

The only thing it could break is the ongoing ClickHouse implementation: => the concat query was "hardcoded" in pages/api/website/[id]/metrics.js I replaced it with that function in lib/db.js

export function getConcatQuery(args) {
  const db = getDatabase();
  if(db === SQLITE) {
    return args.join('||');
  }
  return `concat(${args.join(',')})`;
}

Edit: not anymore in >= 1.37.0

And I see there is some work left to line up with the dev branch (event_data migration)

Thanks @timonson! I guess README and other docs will be updated if SQLite support is officially adopted by Umami.

[Maxime-J]

And I see there is some work left to line up with the dev branch (event_data migration)

There's a blocker for SQLite support starting with umami 1.37.0, prisma doesn't support (yet) the JSON type of event_data.

Discussion on it seems to indicate it could change relatively soon https://github.com/prisma/prisma/issues/3786

PR updated to the latest version though.

[eikaramba]

WOW, this is incredible. i always wished for a simple sqlite backed umami version. @Maxime-J btw did you saw someone did a port in the past as well? I guess yours is now better and more fresh, but maybe it can suit as an inspiration? https://github.com/philippdormann/umami-sqlite

update: ok i just saw it was also referenced here

[Maxime-J]

Thanks @eikaramba ! I also think SQLite would be great to have. And it worked well with Umami < 1.37.0

Added features in Umami causes an incompatibility which can't be handled easily at first glance (see previous message).

Concerning the port you linked, it doesn't contain some SQLite specific functions, I don't see how it could have completely worked, hence why I opened this.

[Maxime-J]

Quick update : Prisma obviously won't support the needed feature soon (https://github.com/prisma/prisma/issues/3786#issuecomment-1444715044), so after a deeper look at the event logic, I've got a fully functional Umami 1.40 with SQLite 🎉

PR will be updated in few days.

[eikaramba]

@mikecao any chance this will be merged? i think it is a huge win for umami. will make self hosting it so much easier.

@Maxime-J hopefully i don't annoy you, but can you update the PR? i would LOVE to try it out :)

[Maxime-J]

I will close that PR in favour of https://github.com/Maxime-J/umami-sqlite

@eikaramba Here it is :) unless a last minute unseen mistake, it should be fully working.