Store and retrieve data

Each mDash account owns an isolated SQLite3 database. That database is under your full control. You can create, delete tables, insert and remove data records, etc. Also, you can download an entire database at any point.

By default, mDash creates data table, used by mDash to store events like device coming online / offline, etc. This is the default schema:

CREATE TABLE IF NOT EXISTS data (
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
    device_id TEXT NOT NULL,
    topic TEXT NOT NULL,
    message TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS data1 ON data(timestamp);
CREATE INDEX IF NOT EXISTS data2 ON data(device_id);
CREATE INDEX IF NOT EXISTS data3 ON data(device_id,timestamp);

Retrieve data

Database is accessible via the https://mdash.net/api/v2/sql/exec endpoint. Example curl command looks as follows:

curl -d "query=select * from data limit 10;" \
    https://mdash.net/api/v2/sql/exec?access_token=YOUR_API_TOKEN

You can execute any query that SQLite3 supports, see SQLite3 documentation.

mDash UI provides an easy way to the SQL REST API:

database UI

Example queries

  • select * from data order by rowid desc limit 10; - last 10 entries from data table
  • select count(*) from data; - number of records in table data
  • select * from sqlite_master; - database schema

Store data

You can store data by issuing SQL INSERT statements. REST clients can do it directly via the REST SQL API with no restrictions.

Devices, however, must be restricted, because any single leaked device password could be used to wipe out the entire database. Therefore, devices cannot access SQL API directly. They must use only allowed, safe methods to store data. Currently, the only way to store data from a device is to send a notification message to mDash, with the name DB.Save and parameters. That will save a record to the data table, with data topic. Example:

mDashNotify("DB.Save", "{%Q:%u}", "ram", mDashGetFreeRam());

Limitations

The database is limited in size. Maximum size is number_of_devices X per_device_limit. The following table summarises limits:

Account type Per device storage size limit DB size limit
Free 100K 1M
Solo 100K 50M
Business 1M 500M
Custom 10M or more unlimited