SQL - Help me getting started

Development
1

Hi, I’m rather new to HA and would like to learn by doing something ‘not to hard’. But it’s not going too well…

I would like to use the SQL integration.

First a few questions. I’m almost sure the answer is yes, but just to be sure.

  • Have I understood it right, that this is “extra”, i.e. if I add the SQL integration, nothing will break. The database HA uses for its sensors etc. will not be affected?
  • I can have 0, 1, or as many SQL integrations as I want, - with a seperate sqlite or MariaDB/MySQL
  • I can have both, i.e. both sqlite and MariaDB/MySQL

If I am right about the above, let’s get started.

I would prefer to make the database myself. Which rules should I abide? I don’t quite understand the documentation here (SQL - Home Assistant) but what I believe I understand is:

  • I should have a table called states.
  • states should have a field called entity_id which I can choose myself, but whatever I choose, that would be the name of my “sensor”. And Obviously I should choose something new that isn’t already used. Can I do sensor.lytje_sql1 etc. always prefix with sensor.lytje_ and thus assume I will never break anything except perhaps my own stuff (i.e. have around zero percent chance of choosing the same name as the next gadget that I am buying next month
  • states should have a (preferably auto-increment) integer field called state_id

For the first project, let’s say I have something very simple. It just put in the work-load of my computer, let’s say once every 30 minutes. (Probably very useless, doesn’t matter, it’s just to learn!). Could my schema be just:

  • state_id INT autoincrement
  • entity_id TEXT
  • state REAL
  • last_update TEXT

Or do I need all of the fields from the home-assistant_v2.db i.e. state_id, entity_id, state, attributes, event_id, last_changed, last_updated, old_state_id, attributes_id, context_id, context_user_id, context_parent_id|origin_idx. And I suppose have the ‘surrounding’ tables (e.g. events) also?

And what if I want to do something a little more complicated, say the power-prices? I have made a script that will download the prices and put it into a database. The format is wrong, but I will adjust it to whatever is needed. But I would like to show it as a graph for today, with the price for each hour, and then this hour marked i.e. by a different color.

Finally the documentation (SQL - Home Assistant) writes a lot about database size. Why does that matter? Do I need that? And what when the database gets bigger? Do I need to keep a track of that and somehow report it to HA?