MySQL / MariaDB edition

The MySQL edition of SnailyCADv4 stores everything in MySQL 8.0+ or MariaDB 10.6+ instead of PostgreSQL. The CAD itself works the same: only the database, a few .env values and the update process differ.

This edition

These docs are written for this edition, from github.com/EWANZO101/snailycad-mysql. The official SnailyCAD releases (SnailyCAD/snaily-cadv4) still use PostgreSQL; their guides are under Manual install (PostgreSQL, upstream).

One-line install

On an Ubuntu server you can skip the steps below: one command starts a setup wizard in your browser that installs everything with buttons.

Docker

curl -fsSL https://raw.githubusercontent.com/EWANZO101/snailycad-mysql/main/scripts/installer/install-docker.sh | sudo bash

Ubuntu (standalone)

curl -fsSL https://raw.githubusercontent.com/EWANZO101/snailycad-mysql/main/scripts/installer/install-ubuntu.sh | sudo bash

See One-line install.

Requirements

• Git (Linux guide)
• Node.js 22.18.x and pnpm (Linux guide)
• MySQL 8.0+ or MariaDB 10.6+ (install guide)
• At least 2 vCPUs and 2 GB of RAM (building the client needs the memory)

Tested with MySQL 8.0.46 and MariaDB 11.4.12.

Standalone installation

1. Create the database and user

Open a MySQL/MariaDB shell as an admin (sudo mysql or sudo mariadb on Linux) and run:

CREATE DATABASE `snaily-cad-v4` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'snailycad'@'localhost' IDENTIFIED BY 'a-strong-password';
GRANT ALL PRIVILEGES ON `snaily-cad-v4`.* TO 'snailycad'@'localhost';
FLUSH PRIVILEGES;

tip

The password ends up inside a URL (DATABASE_URL). Use letters and numbers only, or URL-encode any special characters.

2. Get the code

git clone https://github.com/EWANZO101/snailycad-mysql.git snaily-cadv4
cd snaily-cadv4
pnpm install

3. Configure .env

cp .env.example .env

Fill in the database values. Everything else is the same as the standard .env reference.

.env

DB_USER="snailycad"
DB_PASSWORD="a-strong-password"
DB_HOST="localhost"
DB_PORT="3306"
DB_NAME="snaily-cad-v4"

# Do not change this, unless you know what you're doing!
DATABASE_URL=mysql://${DB_USER}:${DB_PASSWORD}@${DB_HOST}:${DB_PORT}/${DB_NAME}

Then copy it to the apps:

node scripts/copy-env.mjs --client --api

4. Build and start

The recommended way is start.sh. It only installs and builds when something changed, and while the CAD builds or restarts it shows a progress page with a live log on your client port instead of an error.

chmod +x start.sh
./start.sh

Useful flags: --force-build, --force-install, --no-build, --help.

Or build and start by hand:

pnpm run build
pnpm run start

The first start creates all tables. Open the client URL and register the first account: it becomes the owner of the CAD.

5. Add departments, statuses and other values

A new CAD has no values yet. Add them under Admin → Values, import the templates, or copy them from another CAD (see only the values).

Docker installation

The Docker Compose files in the MySQL edition use MariaDB 11.4.

git clone https://github.com/EWANZO101/snailycad-mysql.git snaily-cadv4
cd snaily-cadv4
cp .env.example .env

In .env, set DB_HOST="mariadb" and DB_PORT="3306", choose a DB_USER, DB_PASSWORD and DB_NAME, then start everything:

docker network create cad_web
docker compose -f production.docker-compose.yml up -d

The database container creates the database and user from your .env on first start, and the API waits until it is healthy before it starts. Data is kept in ./.data.

Converting a PostgreSQL CAD

You can move a running CAD, with all its users, citizens, records and settings, from PostgreSQL to MySQL/MariaDB. PostgreSQL is only read, so your old CAD keeps working until you switch over.

The quick way

Install the MySQL edition (steps 1 to 3 above) with an empty database, but don't start it yet. Then run one command from its folder, pointing at the old CAD's .env:

pnpm convert:postgres --from-env /path/to/old/snaily-cadv4/.env

It walks through seven steps and stops with a clear message if anything is wrong:

1. Reads the old CAD's database settings from its .env
2. Checks it can reach both databases, and that the MySQL/MariaDB database is empty
3. Checks the old data for things MySQL can't store, such as usernames that only differ in case
4. Creates the tables
5. Shows how many rows will be copied and asks you to confirm
6. Copies everything in one transaction (all or nothing) and checks every table's row count
7. Offers to copy the old ENCRYPTION_TOKEN into the new .env, so encrypted data can be read

Example output

[5/7] What will be copied
  ✓ Read 6513 rows from 131 tables
  Copy this data into the MySQL/MariaDB database? [y/N] y

[6/7] Copying (one transaction: all or nothing)
  ✓ Done: 6513 rows copied, row counts match for all 131 tables.

Then start the CAD and sign in with your existing accounts.

Option
--from-env <path>	Read the old database settings from its .env
--pg-url <url>	Or give the PostgreSQL URL directly (postgresql://user:pass@host:5432/db)
--mysql-url <url>	Convert into another database instead of the one in this .env (this .env isn't changed)
--values	Copy only the values, into a CAD that already has its accounts
--yes	Don't ask questions (for scripts)

The machine needs psql (Ubuntu: sudo apt install postgresql-client).

From the admin area

The owner can also import from the CAD itself: Admin → CAD Settings → Import from PostgreSQL.

1. Connect to the old CAD with its .env file on the server (full path), the database details, or a postgresql:// URL.
2. Choose what to import:
   • Only the values: departments, statuses, call types, penal codes, vehicles, weapons and addresses. Your accounts and settings stay. Only works while this CAD has no values yet.
   • Everything: replaces all data in this CAD, including your account. Afterwards you sign in with the old CAD's accounts. You have to type REPLACE to confirm.
3. Press Check. It shows how many users and rows will be copied, any usernames that only differ in case, and whether the encryption key matches the old CAD.
4. Start the import and follow its progress on the page. It runs in one transaction: if anything goes wrong, nothing changes. When importing everything from a .env file, it can also switch this CAD to the old encryption key (the CAD restarts).

The server needs psql installed.

Doing it by hand

1. Install the MySQL edition (steps 1 to 3 above) but don't start it yet. Create the tables only:

   cd apps/api
   pnpm prisma migrate deploy

2. Use the same ENCRYPTION_TOKEN as your old CAD. Some stored data is encrypted with it.

3. Make sure psql is installed on the machine, then do a dry run (reads only, prints the row count):

   PG_URL="postgresql://USER:PASSWORD@HOST:5432/DB" \
   DATABASE_URL="mysql://USER:PASSWORD@HOST:3306/DB" \
     node ../../scripts/mysql/migrate-data.mjs --dry-run

4. Copy the data:

   PG_URL="..." DATABASE_URL="..." node ../../scripts/mysql/migrate-data.mjs

   The copy runs in a single transaction and checks the row count of every table afterwards. If anything fails, nothing is written. The target database must be empty.

5. Start the CAD and sign in with your existing accounts.

Copy only the values

To give a new CAD the departments, statuses, call types, penal codes, vehicles, weapons and addresses of another CAD, without its users or other data:

pnpm convert:postgres --from-env /path/to/other/.env --values

The value tables must be empty in the new CAD. Its accounts and settings are kept.

Differences from the PostgreSQL edition

	PostgreSQL edition	MySQL / MariaDB edition
Database	PostgreSQL 15+	MySQL 8.0+ / MariaDB 10.6+
.env	POSTGRES_USER, POSTGRES_PASSWORD, POSTGRES_DB	DB_USER, DB_PASSWORD, DB_NAME
Usernames	Admin and admin can be two accounts	Case-insensitive: they are the same name
Demo mode tools	pg_dump / psql	mysqldump / mysql or mariadb-dump / mariadb
Docker image	postgres	mariadb:11.4

Duplicate usernames

If your PostgreSQL CAD has usernames that differ only in upper/lower case (like Admin and admin), the data copy stops and writes nothing. Rename one of them in the old CAD first.

Updating

Pull the latest changes of the branch, then rebuild:

git pull
./start.sh

Or use Admin → Dashboard → Update CAD (Linux with systemd). It pulls main from the repository you cloned, which is where the MySQL edition lives, so nothing needs to be set.

For developers

• The Prisma schema uses the mysql provider with one baseline migration. PostgreSQL list columns (String[], enum lists) are JSON arrays.
• After creating a new migration, run node scripts/mysql/patch-json-defaults.mjs apps/api/prisma/schema.prisma apps/api/prisma/migrations/<name>/migration.sql. Prisma leaves the DEFAULT (json_array()) of JSON list columns out of the SQL it generates.
• Migrations from the official PostgreSQL branch have to be translated by hand when merging updates.