What is Umami? (And Why I’m Writing This)
Umami is an open-source, self-hosted web analytics platform. It tracks page views, referrers, device types, and other basics, without cookies or third-party scripts. It’s a privacy-first alternative to Google Analytics that’s easy to run and doesn’t need a lot of infrastructure. If you’re looking for a broader critique of Google’s tracking ecosystem and the case for switching to tools like this, I wrote about that here.
This post is a technical follow-up focused specifically on running Umami with MariaDB in a local development setup.
Why MariaDB?
I run MariaDB in development and production, so it made sense to use it for Umami as well. It’s lightweight, fully open source, and a solid fit for small deployments, especially when you already have it up and running.
According to Umami’s GitHub page, MariaDB is supported. I can vouch for that, as can many others, but there are some caveats to keep in mind:
- There are installation and upgrade issues.
- Future MariaDB support is uncertain.
- The developers now recommend using PostgreSQL (more on that later).
Still, if you’re running MariaDB already (or just prefer it) you can absolutely get Umami working. Let’s walk through the setup together.
What We’ll Need Before Continuing
Before we get into installing Umami itself, here’s what we’ll need to have set up and ready to go.
Node.js
I used v22.12.0 with NVM. Version 22 is the Active LTS release as of writing. You can check out other versions here. To install with NVM:
nvm install 22
nvm use 22
Note: for all multi-line code blocks in this post, run each command line by line, pressing Enter after each one.
pnpm
Umami’s docs use Yarn, but I prefer pnpm. It works especially well for monorepos like this one. If it’s not already installed, add it globally to your system:
npm install -g pnpm
MariaDB
I used Homebrew to install MariaDB on macOS, but any install method should work as long as it gives us a working local instance.
brew install mariadb
brew services start mariadb
Create the Database and User
With MariaDB installed and running, we’ll need a database for Umami to connect to. First, we log in to the MariaDB CLI as the root user:
mariadb -u root
Once we’re in the MariaDB CLI prompt, we enter the following commands one at a time:
CREATE DATABASE umami;
CREATE USER 'umami_user'@'localhost' IDENTIFIED BY 'your_strong_password';
GRANT ALL PRIVILEGES ON umami.* TO 'umami_user'@'localhost';
FLUSH PRIVILEGES;
EXIT;
You can name the database whatever you like. Also make sure you replace unami_user and your_strong_password with your preferred values.
Install Umami
The Umami installation docs are straightforward, though in this case we’ve swapped out Yarn for pnpm.
We start by cloning Umami to wherever we want it on our local system (I’m using ~/projects/umami):
git clone https://github.com/umami-software/umami.git ~/projects/umami
Next, we’ll configure Umami by setting the DATABASE_URL environment variable. We can also define other optional variables, such as APP_SECRET, which isn’t required by the installer but is worth setting. (See the full list of environment variables here.)
To do this, we create a .env file in ~/projects/umami (or wherever the Umami installation is) and add the following to it:
DATABASE_URL="mysql://umami_user:your_strong_password@localhost:3306/umami"
APP_SECRET="a_secure_random_string"
You can generate a secure random string for APP_SECRET using a command like this:
openssl rand -base64 32
This will output a 32-character random string, which you can paste into the .env file in place of “a_secure_random_string”.
Now we’re ready to install Umami’s dependencies and build the project. From the Umami project root (i.e. ~/projects/umami), run these commands:
pnpm install
pnpm build
And here’s where things go wrong. Why have you forsaken us, pnpm build? 😭
Where Things Break with MariaDB
So, what’s going on? With our .env file set and our database in place, running pnpm build should kick off the standard process to prepare Umami for use. Instead, we hit this:
Database error: Cannot load from mysql.proc. The table is probably corrupted
This message is misleading. The real issue isn’t a corrupted system table, it’s that one of the included migration files uses a MySQL-only function that MariaDB doesn’t support:
BIN_TO_UUID(session_id)
What Umami is actually trying to do here is initialize its database schema using a package called Prisma. That involves running a sequence of migration files (inside the prisma/migrations folder of our Umami installation) to bring the schema to its expected state. These files are applied automatically during the build process by a script called check-db.js, even on fresh installs.
The problem is that the 05_add_visit_id migration contains the BIN_TO_UUID function, and Prisma doesn’t check whether that function exists in the database engine. It just tries to run the SQL, and when that fails, we get this vague and unhelpful mysql.proc error.
So how do we fix this?
Two Solutions, Depending on Your Setup
The best fix depends on whether you’re installing Umami for the first time, like we’re doing in this guide, or upgrading an existing instance.
Option 1: For Existing Databases
If you already have data in your Umami instance and want to skip the failing migration, you can patch the schema manually. This approach preserves your data and completes what the broken 05_add_visit_id migration was supposed to do.
Step 1: Mark the migration as applied
In your terminal, navigate to the root of your Umami project, then run:
npx prisma migrate resolve --applied "05_add_visit_id"
We use npx here because Prisma is already included as a dependency in the Umami project, so there’s no need to install it globally.
Step 2: Open the MariaDB CLI
Connect as a user with access to your Umami database (we used umami_user and a database named umami earlier in this guide, but your existing setup may use different names):
# Using the umami_user (already selects the database)
mariadb -u umami_user -p umami
# Or using root (you'll need to select the database manually)
mariadb -u root
If you’re using root, run this first to select the database:
USE umami;
Step 3: Apply the patch manually
Once you’re in the prompt, paste the entire SQL block below. The CLI accepts multi-statement input, so you can enter it all at once:
-- AlterTable
ALTER TABLE website_event ADD COLUMN visit_id VARCHAR(36) NULL;
-- Update with UUIDv4
UPDATE website_event we
JOIN (
SELECT DISTINCT
s.session_id,
s.visit_time,
LOWER(CONCAT(
HEX(SUBSTR(MD5(RAND()), 1, 4)), '-',
HEX(SUBSTR(MD5(RAND()), 1, 2)), '-4',
SUBSTR(HEX(SUBSTR(MD5(RAND()), 1, 2)), 2, 3), '-',
CONCAT(HEX(FLOOR(ASCII(SUBSTR(MD5(RAND()), 1, 1)) / 64)+8), SUBSTR(HEX(SUBSTR(MD5(RAND()), 1, 2)), 2, 3)), '-',
HEX(SUBSTR(MD5(RAND()), 1, 6))
)) AS uuid
FROM (
SELECT DISTINCT session_id,
DATE_FORMAT(created_at, '%Y-%m-%d %H:00:00') visit_time
FROM website_event
) s
) a ON we.session_id = a.session_id AND DATE_FORMAT(we.created_at, '%Y-%m-%d %H:00:00') = a.visit_time
SET we.visit_id = a.uuid
WHERE we.visit_id IS NULL;
-- ModifyColumn
ALTER TABLE website_event MODIFY visit_id VARCHAR(36) NOT NULL;
-- CreateIndex
CREATE INDEX website_event_visit_id_idx ON website_event(visit_id);
CREATE INDEX website_event_website_id_visit_id_created_at_idx ON website_event(website_id, visit_id, created_at);
This script adds the visit_id column, assigns values to existing records, and creates the necessary indexes. (Source: GitHub Issue #2645).
Your database should now be in a valid state without needing to downgrade or wipe anything.
Option 2: For Fresh Installs
For our fresh installation, a better approach is to bypass Prisma’s migration system altogether and apply the full schema ourselves. This avoids the broken migration and gives us a clean, working schema based on the current state of the project.
All of the following steps should be run from the Umami project root (e.g., ~/projects/umami).
Step 1: Remove bundled Prisma migrations
We don’t need Umami’s migration history, so we’ll remove them to ensure Prisma won’t try to apply them later:
rm -rf prisma/migrations
Step 2: Generate the full schema as SQL
Now we’ll use prisma migrate diff to create a raw SQL file based on the schema.prisma file in the prisma folder. We’ll save the generated schema to a new sql folder to keep things organized. Run this to create it:
mkdir sql
Then:
DATABASE_URL="mysql://umami_user:your_strong_password@localhost:3306/umami" \
npx prisma migrate diff --from-empty --to-schema-datamodel prisma/schema.prisma --script > sql/umami_schema.sql
Note: This is a single command split across two lines using \ for readability. You can paste it as-is or write it all on one line.
This command starts by setting the DATABASE_URL inline to tell Prisma which database to connect to. It then compares an empty database (--from-empty) with the current schema defined in schema.prisma (--to-schema-datamodel). Instead of applying changes directly, it generates the SQL needed to bring a blank database up to date with that schema. The --script flag tells Prisma to output raw SQL instead of running a migration, and the result is redirected to sql/umami_schema.sql for manual review.
Since Prisma is already included as a dependency in the Umami project, we use npx to run it without needing a global install.
Step 3: Apply it manually
Now we apply the generated SQL to the database manually. This sets up all the tables and relationships Umami needs to run:
mariadb -u umami_user -p umami < sql/umami_schema.sql
Step 4: Patch the build script
As mentioned earlier, the Umami build process runs a script called check-db.js, which attempts to apply migrations using prisma migrate deploy. Since we’ve already applied the schema manually, we want to prevent this step from running.
One option is to manually edit package.json and remove check-db from the build script:
- "build": "npm-run-all check-env build-db check-db build-tracker build-geo build-app",
+ "build": "npm-run-all check-env build-db build-tracker build-geo build-app",
Alternatively, you can keep check-db in place and use the SKIP_DB_MIGRATION environment variable to tell it not to apply migrations. This variable was added in Umami 2.17 and should be set inline when running the build command:
SKIP_DB_MIGRATION=true pnpm build
Either approach will prevent Prisma from trying to reapply migrations we’ve already handled manually.
Step 5: Build and run
Once the schema is in place and the build script is patched, we can build and start Umami normally:
pnpm build
pnpm start
The install should create the following default login:
Username: admin
Password: umami
Where MariaDB Support Might Be Headed
Although MariaDB is still listed as supported, it’s unclear how much longer that will be the case. The issues we covered here are early signs that the project is shifting away from maintaining compatibility with MySQL-based databases.
In fact, one of the Umami maintainers recently commented on this in a GitHub discussion:
We may be moving away from MySQL entirely. It’s a lot of work supporting multiple databases. Have you run Postgres before?
If you’re running Umami with MariaDB today, it can still work well. But future updates may require more hands-on fixes or might not work at all without switching to PostgreSQL.