Display MariaDB data on flex-table-card

Configuration Frontend
1

Update:
All issues are resolved. SQL works fine, sensors work fine, flex-table-card works fine. Database naming was the main issue, singular vs plural with an ‘s’. Getting SQL just right was another issue that got resolved by real examples presented here. The flex-table-card documentation needed a bit more real examples. Thank you, forum!

Original post:
I have looked over the forum and just don’t see it. I’m hoping for a nudge from the group.

I am trying to display on a flex-table-card some data that is stored in a small MariaDB.

ChatGPT gave me this test software, but no data displays, just labels. The goal here is to display a minimal bit of information from the database.

ChatGPT and I have gone through several iterations that don’t work and I’m about to give up. These almost looks like random code.

Here’s the code:

Dashboard

views:
  - title: Home
    sections:
      - type: grid
        cards:
          - type: custom:flex-table-card
            title: My SQL Data
            entities:
              include: sensor.maria_*
            columns:
              - name: Label
                attr: name
              - name: Value
                attr: value

Configuration:

sensor:
  sql:
    - name: Sample Sensor from        MariaDB
      query: "SELECT value DESC LIMIT 1;"
      column: "value"
      unit_of_measurement: "units"

Note: I don’t use MariaDB for HA; my HA still uses SQLlite. When I search on ‘MariaDB’, it’s mostly about HA using MariaDB in lieu of SQLlite.

2

By no mean I’m database expert, but at first glance SQL sensor you configured uses default HA database, which is SQLite, in your case. Perhaps first you need to look at how to configure this sensor to reach the right DB?
Secondly, as I might be wron with statement above, what is the output of your sample sensor? Before you put its data into the flex table, would be good to know how this data looks like.

1 Like
3

There are three things going on here, each of which is a tad challenging, particularly when you go ‘off piste’ to use something non-standard to HA.

  1. Connection to a separate MariaDB
  2. Extraction of data to HA SQL sensor
  3. Display in flex-table-card

Taking each one step at a time is probably the only way to get this working.

Having a separate additional MariaDB alongside the usual HA SQLite is fine, but it helps to have phpMyAdmin installed (HA addon) to test SQL queries first. This addon is specifically designed to work with the HA MariaDB addon. Since MariaDB runs a server, you need to tell phpMyAdmin the DB URL etc to connect, especially if your DB is not the HA addon, or is on another computer on the network. You will probably need the IP address, the port, the user/password, and the database name.

Once connected you will be able to view your DB, tables, and test your SQL queries.

I am no expert in SQL, but your query is probably missing a FROM ‘table name’ at the very least.

SELECT ‘x’ FROM ‘y’ WHERE ‘z’ …?

Once you have a working SQL query that returns a result table, then you can add an HA SQL sensor. Out of the box, the SQL sensor connects to the HA recorder tables in the standard HA (SQLite) DB, so you will again have to point the sensor to the right URL for the separate MariaDB. See the documentation for this. https://www.home-assistant.io/integrations/sql/. I know ‘documentation’ is boring, but it does have the information you need.

If your SQL is returning a table, with columns ‘foo’, ‘bar’, and ‘baz’, then the column parameter selects the one column for the sensor state value. All other columns are placed into attributes of the entity. However, the SQL sensor will only take the top row of the table, regardless of how many rows are returned by the SQL query.

At this point you should have a new sensor in HA that is updated every 30 seconds by executing a valid SQL query on your separate MariaDB.

The flex-table-card is powerful but again needs a bit of care to get it to work as required.

If you are feeding the card with several sensors, then each row is a different sensor. Each column in the table is defined as a given name and value, taken from either the sensor state or a specified attribute.

type: custom:flex-table-card
title: Input Number Entities
entities:
  include:
    - input_number.*
columns:
  - data: state
    name: State
  - data: name
    name: Name
  - data: step
    name: Step

This is a working flex-table-card config. The - input_number.* pulls in all entities of that name (ie all input_number entities on my system). There are three columns in the table, the state value (of the entity) called ‘State’, the name of the entity called ‘Name’, and the step value of the input number. Data state is always going to be the state value, the rest look for an attribute of that name and use that.

If you want several rows in the flex-table-card, then you need to start with several sensors. Using the wild card means naming the SQL sensors in the same fashion as in sensor.maria_

For myself, I have found it most useful to start with the end (flex-) table I want. A sketch is fine, so I can work backwards to get to the data I need. Also, I use Node-RED for this work. Node-RED can connect to my separate MariaDB, and unlike HA can pull in an entire table from an SQL query, not just the first row. This table I can reformat, and send to HA as an array of objects in a Node-RED generated sensor attribute. The flex-table-card has a second mode where, given one entity (sensor) you can display a table of results as items from an array of objects. Much easier.

Disclaimer.
None of this was written with the use of ChatGPT in any shape or form and was entirely generate by a real human of limited but not artificial intelligence.

4

The sensor doesn’t show in Developer Tools > States

A python script populates the values in the database with no issues. I confirmed the database content is as designed, some text and some values in nine rows

5

phpMyAdmin SQL returns database values in demand.

I narrowed down the issue to sensors. I tried several iterations of SQL sensors, and the entities don’t show in Developer Tools> States. There are no errors in the logs. Even a simple sensor like this doesn’t show (password obscured):

  - platform: sql
    name: "SQL 42 Test"
    query: "SELECT 42 AS value"
    column: "value"
    database_url: "mysql://homeassistant:xxx@core-mariadb/mama_bear?charset=utf8mb4"
    unique_id: sql_test_42
6

What has “platform” got to do with this? Platforms are for trains and political parties.

OK so I used the UI for the SQL integration - much easier than messing with YAML.

OK so I have a separate machine with my separate Maria DB on it.

Took about 5 minutes to get working tops.

SQL sensor using UI
SQL sensor using UI1025×931 47.7 KB

Database_url: mysql://user:password@192.168.0.191:3306/octopus?charset=utf8mb4
Query: SELECT * FROM readings ORDER BY uid DESC
Column: UID

Where -

  • user is the username of the Maria DB install
  • and the password
  • 192.168.0.191 is the ip address of the machine used
  • 3306 is the port on which Maria is exposed
  • octopus is the name of the database I am using in Maria
  • ‘readings’ is the name of the table in the database
  • ‘UID’ is one of the columns in the table

The simple query returns ALL records (sorted in descending order) and the SQL picks just the first one, with the named column value in entity state, and the rest of the columns in attributes.

HA sensor based on an SQL query to a disparate MariaDB database
HA sensor based on an SQL query to a disparate MariaDB database737×943 68 KB

Again, I point you towards the relevant documentation for the SQL integration.

The SQL is an integration in its own right, and it generates a sensor. Of course, if you add the SQL YAML under sensors and try and use SQL as a sub platform it probably wont work at all.

Edit.
Cross reference back to a similar post from last year (for anyone who might read this later…):

If you are going to use YAML, then it helps to use the correct construct as given in the relevant documentation.

2 Likes
7

ChatGPT suggested using platform: SQL. What does it know, anyway?

I always start with documentation, and refer to documentation until success. That is good advice.

The SQL integration is rather poor at logging errors.

I enter the data exactly as described in the documentation and your great example. On ‘Submit’ I get ‘Query invalid.’ I’ve tried a variety of queries, your example, phpMyAdmin queries that work elsewhere but not. SQL integration.

I verified the database, table and column names many times. My Python script uses the same database once a day.

It appears that there is a permissions issue. Neither SQL nor MariaDB documentation is helpful in resolving this. Maybe I need to set up a SQL integration user.

When MariaDB is used in lieu of SQLlite for HA, permissions become an issue. My Python script has no issue with permissions, but HA sensor apparently do.

The advice I’m getting is to use MariaDB for HA to resolve permissions issues. I’m trying to avoid that as MariaDB for HA is not recommended and has issues.

I love your clear and succinct advice. I just can’t seem to get it to work.

8

Now I’m being told this:
“The SQL integration must be configured via YAML , not via the UI (as of 2025.7).”

Is this correct?

9

Here, for the third and final time, is a link to the SQL integration documentation.

You will find, in the documentation, just down from the first paragraph

This integration can be configured using both config flow and by YAML.

So - big choice:-
Ask the hallucinating AI or read the documentation.

Difficult one this.

Good luck with your project. Ever better luck with trying to use ChatGPT.

10

I checked all changes for 2024.7 and found nothing about changes to SQL integration. I see a change to sqlalchemy for 2025.7, but I don’t see what the change is. According to the documentation, SQL uses sqlalchemy.

So, you’re right.

Try as I might, carefully following your advice and documentation, I can’t get SQL UI to work. I’ve checked for hidden chars, you name it. Well, maybe not. You may see the issue in an instant.

MariaDB log:
[2025-07-27 11:37:49 ](calendar:T7:2025-07-27 11:37:49 )51 [Warning] Access denied for user ‘mp’@‘172.30.32.1’ (using password: YES)

The image has my password obscured.

1000014376
1000014376762×1280 90.7 KB

As I’ve mentioned before, I think it’s a permissions issue since I don’t use MariaDB as the HA recorder. MariaDB startup log shows:
INFO: Granting all privileges to mp on muscular_portfolio

The same URL works for my Python script. The table is populated and column names match. The same SQL works with phpMyAdmin.

I’ve dug deep with MariaDB and SQL documentation, followed links in those documents.

I use a plain Home Assistant Yellow, MariaSB Add-on, SQL integration all from official sources.

So, I wonder what I’m doing that is inconsistent with the documentation.

11

I have two HA machines. The second runs MariaDB as an add-on, additionally to the standard SQLite, but quite separate. I don’t use MariaDB as the HA anything.

I added the SQL integration on my first machine, and for testing your question added an entry to query the MariaDB on the second machine.

I did see a few ‘invalid query’ messages, but I persisted and eventually it all worked.

I have the same Access denied error in my MariaDB logs, but the IP address 172.30.32.1 is apparently local and used by the docker container system - so probably a start up issue or similar, nothing more.

The user (mp) and password (xxxx) have to be correct, and the IP address (core-mariadb, I would use the full IP address and the port myself to be sure).
The database (muscular_portfolio) and (as you add) user mp has to have access rights to the database.

I found, in testing this, MariaDB to be unhappy with my SQL. Things like the table name are/are not in quotes, and the column used to ORDER BY did not appear to like being in capitals and quoted. No idea, but eventually it worked.

SELECT + FROM mama_bear is not an SQL command I recognise. I always thought it was *.

I am sure there will be an SQL query expert who knows how to get it to work, particularly on MariaDB

Good luck.

Edit.
There are a few postings regarding attempting to get HA SQL integration to work (with MariaDB) and getting the SQL query invalid error.

As a generalisation, looking at a few posts, I would surmise to myself that the two issues are
1 getting the URL exactly right
2 getting the text string SQL in a form that MariaDB likes

Errors in the URL will result in this error. Use of ’ single quotes seems to be important, and in my case I tried table name / columns with and without the quotes until it worked.

1 Like
12

Wow. Thank you for doing that. I owe you a coffee.l or two.

Quotes: I tried double and not single quotes for table name, column and password. I only tried double quotes. I will try single.

IP: I tried core-mariadb,172.30.32.1:3306 with and without port. I was previously getting URL errors, but quickly fixed that. I would not expect MariaDB logs to show “Access denied for user ‘mp’@‘172.30.32.1’ (using password: YES)” when the IP address is wrong. Side note: My Python script has to add ‘&ssl_disabled=true’ to the URL ‘mysql://mp:xxx@core-mariadb/muscular_portfolio?charset=utf8mb4&ssl_disabled=true.’ SQL returns the error ‘URL invalid’ with this ssl add-on.

Query: I tried several queries, ones that MariaDB likes using phpMyAdmin. I made my query match yours for the screenshot.

Password: Absolutely has to be exactly correct. User ‘mp’ is correct, but ‘password’ must be the issue. SQL reaches MariaDB and rejects the user/password. Maybe I’ll create another user just for SQL.

I have a few more things to try.

Thanks!

13

On a local machine, the IP address is usually 127.0.0.1

I do all my MariaDB work using Node-RED. I use the node-red-node-mysql node set, and have had no issues at all in both writing to and reading from the DB.

I run queries on the same machine (both Node-RED and MariaDB are running as HA add-ons) and use 127.0.0.1 as the IP address.

Using Node-RED to write and read Maria database
Using Node-RED to write and read Maria database1661×949 113 KB

I can also run queries on a different machine, running Node-RED, and have to use the main DB machine IP address, but again I have never had any issues.

Node-RED is something else to load/learn but it does permit full use of the entire SQL table return, which is something the HA SQL integration cannot do.

14

Ive been using chat gpt for home assistant and in the beggining its been great but it quickly started to give me faulty code, try out Grok Ai, im interested in the project as well, as i want to graph data from ConEd realtime usage

15

After weeks of repeated failures with almost nothing in the logs, I turned on ‘debug’ logging. Between System > Log and MariaDB > Log, I discovered my issue was permissions. I defined the MariaDB user for a singular database name and the actual name is plural, ’s’ or no ’s’ — that made all the difference in the world. The SQL UI succeeded in creating a sensor in configuration.yaml and the query result displays on the default Overview screen.

Boy, did I go around the horn on that one. I apologize for taking you along with me.

By the way, the suggested documentation says to set the permissions. Accuracy matters!

Thanks everyone for your help. I will mark this solved when I get flex-table-card working.

Dennis