sqlite database

[bm55]

Allow sqlite database configuration so there is an option to keep the database on local storage and store clips in different storage like network share. Or allow using existing database service like mySql?

[mattheys]

How does the sqlite database help? Currently my /config folder is mapped to one location with my config file and my /clips mapped somewhere else. I can easily use docker to map my /clips to a network share if I needed too. Wouldn't having your config in a sqlite format make it more complicated to update and maintain than plain text yml file? I could understand using sql if you wanted to do a mass camera update to enable or disable a feature but can't see much use from your suggested scenario.

[bm55]

the database location wasn't configurable and placing it in network share was causing issue (database locked) but now with beta3 there is an option to configure the database location, addition to the clip location.

[bm55]

https://github.com/blakeblackshear/frigate/tree/release-0.8.0

Database
Event and clip information is managed in a sqlite database at /media/frigate/clips/frigate.db. If that database is deleted, clips will be orphaned and will need to be cleaned up manually. They also won't show up in the Media Browser within HomeAssistant.

If you are storing your clips on a network share (SMB, NFS, etc), you may get a database is locked error message on startup. You can customize the location of the database in the config if necessary.

[mattheys]

Ok, new feature I hadn't seen. Looking at beta4 it appears that this is already in the config file

frigate/README.md

Lines 194 to 198 in 444fa45

	# Optional: database configuration
	database:
	# Optional: database path
	# This may need to be in a custom location if network storage is used for clips
	path: /media/frigate/clips/frigate.db

is that what you mean?
Just seen your other comment about it being in Beta3

Also docker can map individual files in a container so you could try in compose something like

  volumes:
    - /local/frigate.db:/media/frigate/clips/frigate.db
    - /remote/frigate:/media/frigate/clips

Although I have a feeling this might be something to do with installing it as a HASS addon in which case I'm not sure what I said will help or not.

[walthowd]

Thanks -- Moved frigate.db from NFS share to NVME and this greatly helped query times. Database is 22mb, contains 630 events. Initial query time would be ~ 15 seconds which would time out in the Home Assistant media browser. Follow up queries seemed to be cached and would take ~ 1-2 seconds.

After moving the database all queries are under 200ms now.

[rickgitdone]

IMHO regarding the docker deployment ... adding a bit more context to the database mapping would greatly help many since its mapped into /media in the provided examples.. By default you map external filesystem to hold clips, event, recordings etc as the container size is locked and any restarts would blow away these files if there was space. Changing the default example to map the to a local storage resource similar to the config.yml mapping and writeable would benefit everyone going forward and prevent the "database is locked" error...

[krdosier]

External database support would be a great enhancement. I recommend adding support for using external databases like mysql and preferably clickhouse as an optional alternative to the sqlite file.

I am finding great success using clickhouse to analyze the frigate.db sqlite database for detection rates of people and cars. Using release 0.14.1, I use the clickhouse external table engine to query the frigate.db sqlite file, exposing the frigate database contents to grafana for quick visualizations of detection counts even re-rendering thumbnails is quite easy. The only headache is record volume io with the sqlite file. Direct query of clickhouse native data would greatly reduce the query latency for long time periods.

[moandcompany]

I have a project where I analyze records stored in a Frigate database, and believe use of a separate database could have a lot of interesting uses.

However, have you considered making a copy of the Frigate sqlite database file and reading from it directly using whatever analysis engine of choice? (e.g. DuckDB, Clickhouse, etc)?

The number of records in a typical Frigate instance likely does not reach sufficient volume to warrant record storage in a Columnar format, and the base case for those records is transaction retrieval where the existing format makes sense. You could re-write (i.e. replicate) records from the existing Frigate database into a Columnar format for faster analysis in Clickhouse/whatever, as well.