TimescaleDB and Django

Moritz Ulmer
Oct 2, 2020 · 2 min read

Time series data is at the heart of IoT projects and hydroponic systems are no different. In order to efficiently handle these data streams, a specialized database is needed, in our case TimescaleDB. Since it is integrated into PostgreSQL, it is possible to integrate it with applications that already support PostgreSQL. The steps to integrate it are described below.

Combining Django and TimescaleDB

The first step is to create a database schema. As we are working with Django, this is done in the models.py file of our app. An important thing to note is that the primary key has to be set on the time column in order for TimescaleDB to function properly. Omitting a primary key field will cause Django to create one implicitly.

The next step is to create a migration file with the ./manage.py createmigrations. Create another file in the migrations folder to convert your table into a TimescaleDB hypertable. Add the code below to the file. The Django app is called farms, the timestamp field is called time and the field by which to group the readings by is called sensor_id to link it to the Sensor model.

Run the migration with ./manage.py migrate.

The proper method to define the primary key would be to create a composite primary key with the time and sensor_id field. However, as Django does not support composite keys, only using time is a valid alternative. In most use cases the sensor readings will be spares enough to not collide frequently. However, in the case of a collision, the violation can be resolved by smearing the timestamp a bit. The code below catches this error and retries saving the sensor reading by incrementing the timestamp by a microsecond.

A repository with the code and instructions to start TimescaleDB can be found in the https://github.com/protohaus/django-timescale repository.

Smart Digital Garden

A hydroponic and urban farming project by Protohaus

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store