Migrate database from SQLITE to MariaDB (With full history)
DO IT ON YOUR OWN RISK!
You have been warned
This is step by step guide, how to migrate Home Assistant default SQLITE database to MariaDB.
I have tried also sqlite3-to-mysql mentioned here:
but in my case it didn't work (history was corrupted)
I did it on the latest version of HA 2025.9.1
0. required
DBeaver community 👉download
database knowledges :) I won't write exact SQL scripts here for empty DB table and so.
1. BACKUP!!!
2. stop HA core
From home assistant console (not from web, but directly on PC/virtual machine) run
'core stop'
3. backup home-assistant_v2.db
from directory config backup home-assistant_v2.db database and copy it somewhere to windows machine, where it will be accessible to installed DBeaver
This snapshot will be restored to MariaDB
4.Install MariaDB
Install MariaDB somewhere base on your requirements. and make connection to DBeaver.
(I recommend to have PC with DBeaver on one LAN with MariaDB :)
5. Configure HA to MariaDB
add/modify this lines in config/configuration.yaml:
recorder:
db_url: mysql://user:password@serverIP:port/databaseName?charset=utf8mb4
purge_keep_days: 31
(important is db_url .... purge_... you can use as you wish)
6. Make database structure
from console (as in point1)
1. start HA Core ( "core start")
2. wait, until HA fully starts - it create database tables and starts to inserting data.
3. stop HA Core ("core stop")
7. Remove indexes from 'States' Table
For cleaning the tables and further migration is necessary to delete three foreign keys on 'State' table
Script:
ALTER TABLE `states` DROP FOREIGN KEY `states_ibfk_1`;
ALTER TABLE `states` DROP FOREIGN KEY `states_ibfk_2`;
ALTER TABLE `states` DROP FOREIGN KEY `states_ibfk_3`;
You can create script for delete and further adding those indexes by asking AI chatbot base on DDL of 'state' table.
8. Empty all tables
clear all data in ALL tables with 'delete from' sql command
Due to foreign keys between tables you should use some order. just play until all tables are empty.
9. Migration with DBeaver
make connection to SQLITE "home-assistant_v2.db" database.
1. connect to home-assistant_v2.db
2. select all tables
3. right click > "Export data"
4. choose Database (default)
5. choose database, where you want to migrate to:
5a. next you see, that all tables exists, so data will be moved:
6. next - I used default
7. so the next screen
9. make coffee ☕
10. Add indexes
We recreate indexes we have deleted:
ALTER TABLE `states`
ADD CONSTRAINT `states_ibfk_1` FOREIGN KEY (`old_state_id`) REFERENCES `states` (`state_id`),
ADD CONSTRAINT `states_ibfk_2` FOREIGN KEY (`attributes_id`) REFERENCES `state_attributes` (`attributes_id`),
ADD CONSTRAINT `states_ibfk_3` FOREIGN KEY (`metadata_id`) REFERENCES `states_meta` (`metadata_id`);
it takes a few minutes
11. Start HA Core
From home assistant console (not from web, but directly on PC/virtual machine) run
'core start'
12. Check Home Assistant
that's all. now home assistant should be working and you will be able to see history