Connect your app

Easily integrate your app with self-hosted TimescaleDB. Use your favorite programming language to connect to your database, create and manage hypertables, then ingest and query data.

Ruby

Prerequisites

To follow the steps on this page:

• Create a target self-hosted TimescaleDB instance.

  You need your connection details.

• Install Rails.

Connect a Rails app to TimescaleDB

TimescaleDB extends PostgreSQL with time-series superpowers. You connect to your TimescaleDB database from a standard Rails app configured for PostgreSQL.

1. Create a new Rails app configured for PostgreSQL

   Rails creates and bundles your app, then installs the standard PostgreSQL Gems.

   rails new my_app -d=postgresql
   cd my_app

2. Install the TimescaleDB gem

   1. Open Gemfile, add the following line, then save your changes:

      gem 'timescaledb'

   2. In the terminal, run the following command:

      bundle install

3. Connect your app to TimescaleDB

   1. In <my_app_home>/config/database.yml update the configuration to securely connect to your TimescaleDB database by adding url: <%= ENV['DATABASE_URL'] %> to the default configuration:

      default: &default
        adapter: postgresql
        encoding: unicode
        pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
        url: <%= ENV['DATABASE_URL'] %>

   2. Set the environment variable for DATABASE_URL to your connection string:

      export DATABASE_URL="postgres://username:password@host:port/dbname"

   3. Create the database for the project:

      rails db:create

   4. Run migrations:

      rails db:migrate

   5. Verify the connection from your app to your TimescaleDB database:

      echo "\dx" | rails dbconsole

      The result shows the list of extensions in your database

   Name	Version	Schema	Description
   pg_buffercache	1.5	public	examine the shared buffer cache
   pg_stat_statements	1.11	public	track planning and execution statistics of all SQL statements executed
   plpgsql	1.0	pg_catalog	PL/pgSQL procedural language
   postgres_fdw	1.1	public	foreign-data wrapper for remote PostgreSQL servers
   timescaledb	2.24.0	public	Enables scalable inserts and complex queries for time-series data (Community Edition)
   timescaledb_toolkit	1.22.0	public	Library of analytical hyperfunctions, time-series pipelining, and other SQL utilities

Optimize time-series data in hypertables

Hypertables are PostgreSQL tables designed to simplify and accelerate data analysis. Anything you can do with regular PostgreSQL tables, you can do with hypertables - but much faster and more conveniently.

In this section, you use the helpers in the TimescaleDB gem to create and manage a hypertable.

1. Generate a migration to create the page loads table

   rails generate migration create_page_loads

   This creates the <my_app_home>/db/migrate/<migration-datetime>_create_page_loads.rb migration file.

2. Add hypertable options

   Replace the contents of <my_app_home>/db/migrate/<migration-datetime>_create_page_loads.rb with the following:

   class CreatePageLoads < ActiveRecord::Migration[8.0]
     def change
       hypertable_options = {
         time_column: 'created_at',
         chunk_time_interval: '1 day',
         compress_segmentby: 'path',
         compress_orderby: 'created_at',
         compress_after: '7 days',
         drop_after: '30 days'
       }
   
       create_table :page_loads, id: false, primary_key: [:created_at, :user_agent, :path], hypertable: hypertable_options do |t|
         t.timestamptz :created_at, null: false
         t.string :user_agent
         t.string :path
         t.float :performance
       end
     end
   end

   The id column is not included in the table. This is because TimescaleDB requires that any UNIQUE or PRIMARY KEY indexes on the table include all partitioning columns. In this case, this is the time column. A new Rails model includes a PRIMARY KEY index for id by default: either remove the column or make sure that the index includes time as part of a “composite key.”

   For more information, check the Roby docs around composite primary keys.

3. Create a PageLoad model

   Create a new file called <my_app_home>/app/models/page_load.rb and add the following code:

   class PageLoad < ApplicationRecord
     extend Timescaledb::ActsAsHypertable
     include Timescaledb::ContinuousAggregatesHelper
   
     acts_as_hypertable time_column: "created_at",
       segment_by: "path",
       value_column: "performance"
   
     # Basic scopes for filtering by browser
     scope :chrome_users, -> { where("user_agent LIKE ?", "%Chrome%") }
     scope :firefox_users, -> { where("user_agent LIKE ?", "%Firefox%") }
     scope :safari_users, -> { where("user_agent LIKE ?", "%Safari%") }
   
     # Performance analysis scopes
     scope :performance_stats, -> {
       select("stats_agg(#{value_column}) as stats_agg")
     }
   
     scope :slow_requests, -> { where("performance > ?", 1.0) }
     scope :fast_requests, -> { where("performance < ?", 0.1) }
   
     # Set up continuous aggregates for different timeframes
     continuous_aggregates scopes: [:performance_stats],
       timeframes: [:minute, :hour, :day],
       refresh_policy: {
         minute: {
           start_offset: '3 minute',
           end_offset: '1 minute',
           schedule_interval: '1 minute'
         },
         hour: {
           start_offset: '3 hours',
           end_offset: '1 hour',
           schedule_interval: '1 minute'
         },
         day: {
           start_offset: '3 day',
           end_offset: '1 day',
           schedule_interval: '1 minute'
         }
       }
   end

4. Run the migration

   rails db:migrate

Insert data into your database

The TimescaleDB gem provides efficient ways to insert data into hypertables. This section shows you how to ingest test data into your hypertable.

1. Create a controller to handle page loads

   Create a new file called <my_app_home>/app/controllers/application_controller.rb and add the following code:

   class ApplicationController < ActionController::Base
     around_action :track_page_load
   
     private
   
     def track_page_load
       start_time = Time.current
       yield
       end_time = Time.current
   
       PageLoad.create(
         path: request.path,
         user_agent: request.user_agent,
         performance: (end_time - start_time)
       )
     end
   end

2. Generate some test data

   Use bin/console to join a Rails console session and run the following code to define some random page load access data:

   def generate_sample_page_loads(total: 1000)
     time = 1.month.ago
     paths = %w[/ /about /contact /products /blog]
     browsers = [
       "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.114 Safari/537.36",
       "Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:89.0) Gecko/20100101 Firefox/89.0",
       "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/14.1.1 Safari/605.1.15"
     ]
   
     total.times.map do
       time = time + rand(60).seconds
       {
         path: paths.sample,
         user_agent: browsers.sample,
         performance: rand(0.1..2.0),
         created_at: time,
         updated_at: time
       }
     end
   end

3. Insert the generated data into your TimescaleDB database

    # Insert the data in batches
    PageLoad.insert_all(generate_sample_page_loads, returning: false)

4. Validate the test data in your TimescaleDB database

   PageLoad.count
   PageLoad.first

Reference

This section lists the most common tasks you might perform with the TimescaleDB gem.

Query scopes

The TimescaleDB gem provides several convenient scopes for querying your time-series data.

• Built-in time-based scopes:

  PageLoad.last_hour.count
  PageLoad.today.count
  PageLoad.this_week.count
  PageLoad.this_month.count

• Browser-specific scopes:

  # Count requests by browser
  PageLoad.chrome_users.last_hour.count
  PageLoad.firefox_users.last_hour.count
  PageLoad.safari_users.last_hour.count
  
  # Performance analysis
  PageLoad.slow_requests.last_hour.count
  PageLoad.fast_requests.last_hour.count

• Query continuous aggregates:

  This query fetches the average and standard deviation from the performance stats for the /products path over the last day.

  # Access aggregated performance stats through generated classes
  PageLoad::PerformanceStatsPerMinute.last_hour
  PageLoad::PerformanceStatsPerHour.last_day
  PageLoad::PerformanceStatsPerDay.last_month
  
  # Get statistics for a specific path
  stats = PageLoad::PerformanceStatsPerHour.last_day.where(path: '/products').select("average(stats_agg) as average, stddev(stats_agg) as stddev").first
  puts "Average: #{stats.average}"
  puts "Standard Deviation: #{stats.stddev}"

TimescaleDB features

The TimescaleDB gem provides utility methods to access hypertable and chunk information. Every model that uses the acts_as_hypertable method has access to these methods.

Access hypertable and chunk information

• View chunk or hypertable information:

  PageLoad.chunks.count
  PageLoad.hypertable.detailed_size

• Compress/Decompress chunks:

  PageLoad.chunks.uncompressed.first.compress!  # Compress the first uncompressed chunk
  PageLoad.chunks.compressed.first.decompress!  # Decompress the oldest chunk
  PageLoad.hypertable.compression_stats # View compression stats

Access hypertable stats

You collect hypertable stats using methods that provide insights into your hypertable’s structure, size, and compression status:

• Get basic hypertable information:

  hypertable = PageLoad.hypertable
  hypertable.hypertable_name  # The name of your hypertable
  hypertable.schema_name      # The schema where the hypertable is located

• Get detailed size information:

  hypertable.detailed_size # Get detailed size information for the hypertable
  hypertable.compression_stats # Get compression statistics
  hypertable.chunks_detailed_size # Get chunk information
  hypertable.approximate_row_count # Get approximate row count
  hypertable.dimensions.map(&:column_name) # Get dimension information
  hypertable.continuous_aggregates.map(&:view_name) # Get continuous aggregate view names

Continuous aggregates

The continuous_aggregates method generates a class for each continuous aggregate.

• Get all the continuous aggregate classes:

  PageLoad.descendants # Get all continuous aggregate classes

• Manually refresh a continuous aggregate:

  PageLoad.refresh_aggregates

• Create or drop a continuous aggregate:

  Create or drop all the continuous aggregates in the proper order to build them hierarchically. See more about how it works in this blog post.

  PageLoad.create_continuous_aggregates
  PageLoad.drop_continuous_aggregates

Next steps

Now that you have integrated the ruby gem into your app:

• Learn more about the TimescaleDB gem.
• Check out the official docs.
• Follow the LTTB, Open AI long-term storage, and candlesticks tutorials.

Python

Prerequisites

To follow the steps on this page:

• Create a target self-hosted TimescaleDB instance.

  You need your connection details.

• Install the psycopg2 library. For more information, see the psycopg2 documentation.
• (Optional) Create a Python virtual environment.

Connect to TimescaleDB

In this section, you create a connection to TimescaleDB using the psycopg2 library. This library is one of the most popular PostgreSQL libraries for Python. It allows you to execute raw SQL queries efficiently and safely, and prevents common attacks such as SQL injection.

1. Import the psycogpg2 library:

   import psycopg2

2. Locate your TimescaleDB credentials

   Use them to compose a connection string for psycopg2.

   You’ll need:

   • password
   • username
   • host URL
   • port
   • database name
3. Compose your connection string variable

   Use a libpq connection string format:

   CONNECTION = "postgres://username:password@host:port/dbname"

   If you require an SSL connection, use this version instead:

   CONNECTION = "postgres://username:password@host:port/dbname?sslmode=require"

   Alternatively you can specify each parameter in the connection string as follows

   CONNECTION = "dbname=tsdb user=tsdbadmin password=secret host=host.com port=5432 sslmode=require"

   Warning

   This method of composing a connection string is for test or development purposes only. For production, use environment variables for sensitive details like your password, hostname, and port number.

4. Create a database session with psycopg2

   Use the psycopg2 connect function to create a new database session and a new cursor object to interact with the database.

   In your main function, add these lines:

   CONNECTION = "postgres://username:password@host:port/dbname"
   with psycopg2.connect(CONNECTION) as conn:
       cursor = conn.cursor()
       # use the cursor to interact with your database
       # cursor.execute("SELECT * FROM table")

   Alternatively, you can create a connection object and pass the object around as needed, like opening a cursor to perform database operations:

   CONNECTION = "postgres://username:password@host:port/dbname"
   conn = psycopg2.connect(CONNECTION)
   cursor = conn.cursor()
   # use the cursor to interact with your database
   cursor.execute("SELECT 'hello world'")
   print(cursor.fetchone())

Create a relational table

In this section, you create a table called sensors which holds the ID, type, and location of your fictional sensors. Additionally, you create a hypertable called sensor_data which holds the measurements of those sensors. The measurements contain the time, sensor_id, temperature reading, and CPU percentage of the sensors.

1. Compose the SQL statement to create a relational table

   This example creates a table called sensors, with columns id, type and location:

   query_create_sensors_table = """CREATE TABLE sensors (
                                       id SERIAL PRIMARY KEY,
                                       type VARCHAR(50),
                                       location VARCHAR(50)
                                   );
                                   """

2. Execute the query and commit the changes

   Open a cursor, execute the query you created in the previous step, and commit the query to make the changes persistent. Afterward, close the cursor to clean up:

   cursor = conn.cursor()
   # see definition in Step 1
   cursor.execute(query_create_sensors_table)
   conn.commit()
   cursor.close()

Create a hypertable

When you have created the relational table, you can create a hypertable. Creating tables and indexes, altering tables, inserting data, selecting data, and most other tasks are executed on the hypertable.

1. Create the CREATE TABLE SQL statement for your hypertable

   Notice how the hypertable has the compulsory time column:

   # create sensor data hypertable
   query_create_sensordata_table = """CREATE TABLE sensor_data (
                                           time TIMESTAMPTZ NOT NULL,
                                           sensor_id INTEGER,
                                           temperature DOUBLE PRECISION,
                                           cpu DOUBLE PRECISION,
                                           FOREIGN KEY (sensor_id) REFERENCES sensors (id)
                                       );
                                       """

2. Convert the table to a hypertable

   Formulate a SELECT statement that converts the sensor_data table to a hypertable. You must specify the table name to convert to a hypertable, and the name of the time column as the two arguments. For more information, see the create_hypertable docs:

   query_create_sensordata_hypertable = "SELECT create_hypertable('sensor_data', by_range('time'));"

   Note

   The by_range dimension builder is an addition to TimescaleDB 2.13.

3. Execute the statements and commit your changes

   Open a cursor with the connection, execute the statements from the previous steps, commit your changes, and close the cursor:

   cursor = conn.cursor()
   cursor.execute(query_create_sensordata_table)
   cursor.execute(query_create_sensordata_hypertable)
   # commit changes to the database to make changes persistent
   conn.commit()
   cursor.close()

Insert rows of data

You can insert data into your hypertables in several different ways. In this section, you can use psycopg2 with prepared statements, or you can use pgcopy for a faster insert.

1. Insert relational data into the sensors table

   Open a cursor with a connection to the database, use prepared statements to formulate the INSERT SQL statement, and then execute that statement:

   sensors = [('a', 'floor'), ('a', 'ceiling'), ('b', 'floor'), ('b', 'ceiling')]
   cursor = conn.cursor()
   for sensor in sensors:
     try:
       cursor.execute("INSERT INTO sensors (type, location) VALUES (%s, %s);",
                   (sensor[0], sensor[1]))
     except (Exception, psycopg2.Error) as error:
       print(error.pgerror)
   conn.commit()

2. (Optional) Use separate SQL and data variables

   Alternatively, you can pass variables to the cursor.execute function and separate the formulation of the SQL statement, SQL, from the data being passed with it into the prepared statement, data:

   SQL = "INSERT INTO sensors (type, location) VALUES (%s, %s);"
   sensors = [('a', 'floor'), ('a', 'ceiling'), ('b', 'floor'), ('b', 'ceiling')]
   cursor = conn.cursor()
   for sensor in sensors:
     try:
       data = (sensor[0], sensor[1])
       cursor.execute(SQL, data)
     except (Exception, psycopg2.Error) as error:
       print(error.pgerror)
   conn.commit()

If you choose to use pgcopy instead, install the pgcopy package using pip, and then add this line to your list of import statements:

from pgcopy import CopyManager

1. Generate random sensor data

   Use the generate_series function provided by PostgreSQL. This example inserts a total of 480 rows of data (4 readings, every 5 minutes, for 24 hours). In your application, this would be the query that saves your time-series data into the hypertable:

   # for sensors with ids 1-4
   for id in range(1, 4, 1):
       data = (id,)
       # create random data
       simulate_query = """SELECT generate_series(now() - interval '24 hour', now(), interval '5 minute') AS time,
                               %s as sensor_id,
                               random()*100 AS temperature,
                               random() AS cpu;
                               """
       cursor.execute(simulate_query, data)
       values = cursor.fetchall()

2. Define the column names for the target table

   This example uses the sensor_data hypertable created earlier. This hypertable consists of columns named time, sensor_id, temperature and cpu. The column names are defined in a list of strings called cols:

   cols = ['time', 'sensor_id', 'temperature', 'cpu']

3. Insert data using pgcopy CopyManager

   Create an instance of the pgcopy CopyManager, mgr, and pass the connection variable, hypertable name, and list of column names. Then use the copy function to insert the data quickly.

   mgr = CopyManager(conn, 'sensor_data', cols)
   mgr.copy(values)

4. Commit to persist changes:

   conn.commit()

5. (Optional) Full sample code for pgcopy insert

   The full sample code to insert data into TimescaleDB using pgcopy, using the example of sensor data from four sensors:

   # insert using pgcopy
   def fast_insert(conn):
       cursor = conn.cursor()
   
       # for sensors with ids 1-4
       for id in range(1, 4, 1):
           data = (id,)
           # create random data
           simulate_query = """SELECT generate_series(now() - interval '24 hour', now(), interval '5 minute') AS time,
                                   %s as sensor_id,
                                   random()*100 AS temperature,
                                   random() AS cpu;
                                   """
           cursor.execute(simulate_query, data)
           values = cursor.fetchall()
   
           # column names of the table you're inserting into
           cols = ['time', 'sensor_id', 'temperature', 'cpu']
   
           # create copy manager with the target table and insert
           mgr = CopyManager(conn, 'sensor_data', cols)
           mgr.copy(values)
   
       # commit after all sensor data is inserted
       # could also commit after each sensor insert is done
       conn.commit()

6. (Optional) You can also check if the insertion worked:

   cursor.execute("SELECT * FROM sensor_data LIMIT 5;")
   print(cursor.fetchall())

Execute a query

This section covers how to execute queries against your database.

The first procedure shows a simple SELECT * query. For more complex queries, you can use prepared statements to ensure queries are executed safely against the database.

For more information about properly using placeholders in psycopg2, see the basic module usage document. For more information about how to execute more complex queries in psycopg2, see the psycopg2 documentation.

Execute a query

1. Define the SQL query

   This example is a simple SELECT statement querying each row from the previously created sensor_data table.

   query = "SELECT * FROM sensor_data;"

2. Execute the query

   Open a cursor from the existing database connection, conn, and then execute the query you defined:

   cursor = conn.cursor()
   query = "SELECT * FROM sensor_data;"
   cursor.execute(query)

3. Access the query results

   Use one of psycopg2’s results retrieval methods, such as fetchall() or fetchmany(). This example prints the results of the query, row by row. Note that the result of fetchall() is a list of tuples, so you can handle them accordingly:

   cursor = conn.cursor()
   query = "SELECT * FROM sensor_data;"
   cursor.execute(query)
   for row in cursor.fetchall():
       print(row)
   cursor.close()

4. (Optional) Use DictCursor for dictionary results

   If you want a list of dictionaries instead, define the cursor using DictCursor:

   cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)

   Using this cursor, cursor.fetchall() returns a list of dictionary-like objects.

For more complex queries, you can use prepared statements to ensure queries are executed safely against the database.

Execute queries using prepared statements

1. Write the query using prepared statements:

   # query with placeholders
   cursor = conn.cursor()
   query = """
              SELECT time_bucket('5 minutes', time) AS five_min, avg(cpu)
              FROM sensor_data
              JOIN sensors ON sensors.id = sensor_data.sensor_id
              WHERE sensors.location = %s AND sensors.type = %s
              GROUP BY five_min
              ORDER BY five_min DESC;
              """
   location = "floor"
   sensor_type = "a"
   data = (location, sensor_type)
   cursor.execute(query, data)
   results = cursor.fetchall()

Node.js

Prerequisites

To follow the steps on this page:

• Create a target self-hosted TimescaleDB instance.

  You need your connection details.

• Install Node.js.
• Install the Node.js package manager npm.

Connect to TimescaleDB

In this section, you create a connection to TimescaleDB with a common Node.js ORM (object relational mapper) called Sequelize.

1. Initialize a new Node.js app

   At the command prompt, initialize a new Node.js app:

   npm init -y

   This creates a package.json file in your directory, which contains all of the dependencies for your project. It looks something like this:

   {
     "name": "node-sample",
     "version": "1.0.0",
     "description": "",
     "main": "index.js",
     "scripts": {
       "test": "echo \"Error: no test specified\" && exit 1"
     },
     "keywords": [],
     "author": "",
     "license": "ISC"
   }

2. Install Express.js

   Install Express.js:

   npm install express

3. Create a simple web page

   Create a simple web page to check the connection. Create a new file called index.js, with this content:

   const express = require('express')
   const app = express()
   const port = 3000;
   
   app.use(express.json());
   app.get('/', (req, res) => res.send('Hello World!'))
   app.listen(port, () => console.log(`Example app listening at http://localhost:${port}`))

4. Test your connection

   Test your connection by starting the application:

   node index.js

   In your web browser, navigate to http://localhost:3000. If the connection is successful, it shows “Hello World!”

5. Add Sequelize to your project

   Add Sequelize to your project:

   npm install sequelize sequelize-cli pg pg-hstore

6. Locate your credentials

   Locate your TimescaleDB credentials and use them to compose a connection string for Sequelize.

   You’ll need:

   • password
   • username
   • host URL
   • port
   • database name
7. Compose the connection string

   Compose your connection string variable, using this format:

   'postgres://<user>:<password>@<host>:<port>/<dbname>'

8. Configure Sequelize in index.js

   Open the index.js file you created. Require Sequelize in the application, and declare the connection string:

   const Sequelize = require('sequelize')
   const sequelize = new Sequelize('postgres://<user>:<password>@<host>:<port>/<dbname>',
       {
           dialect: 'postgres',
           protocol: 'postgres',
           dialectOptions: {
               ssl: {
                   require: true,
                   rejectUnauthorized: false
               }
           }
       })

   Make sure you add the SSL settings in the dialectOptions section if your TimescaleDB instance requires SSL connections.

9. Test the database connection

   You can test the connection by adding these lines to index.js after the app.get statement:

   sequelize.authenticate().then(() => {
       console.log('Connection has been established successfully.');
   }).catch(err => {
       console.error('Unable to connect to the database:', err);
   });

   Start the application on the command line:

   node index.js

   If the connection is successful, you’ll get output like this:

   Example app listening at http://localhost:3000
   Executing (default): SELECT 1+1 AS result
   Connection has been established successfully.

Create a relational table

In this section, you create a relational table called page_loads.

1. Generate the model and migration

   Use the Sequelize command line tool to create a table and model called page_loads:

   npx sequelize model:generate --name page_loads \
   --attributes userAgent:string,time:date

   The output looks similar to this:

   Sequelize CLI [Node: 12.16.2, CLI: 5.5.1, ORM: 5.21.11]
   
   New model was created at <PATH>.
   New migration was created at <PATH>.

2. Edit the migration file

   Edit the migration file so that it sets up a migration key:

   'use strict';
   module.exports = {
     up: async (queryInterface, Sequelize) => {
       await queryInterface.createTable('page_loads', {
         userAgent: {
           primaryKey: true,
           type: Sequelize.STRING
         },
         time: {
           primaryKey: true,
           type: Sequelize.DATE
         }
       });
     },
     down: async (queryInterface, Sequelize) => {
       await queryInterface.dropTable('page_loads');
     }
   };

3. Run the migration

   Migrate the change and make sure that it is reflected in the database:

   npx sequelize db:migrate

   The output looks similar to this:

   Sequelize CLI [Node: 12.16.2, CLI: 5.5.1, ORM: 5.21.11]
   
   Loaded configuration file "config/config.json".
   Using environment "development".
   == 20200528195725-create-page-loads: migrating =======
   == 20200528195725-create-page-loads: migrated (0.443s)

4. Create the PageLoads model

   Create the PageLoads model in your code. In the index.js file, above the app.use statement, add these lines:

   let PageLoads = sequelize.define('page_loads', {
       userAgent: {type: Sequelize.STRING, primaryKey: true },
       time: {type: Sequelize.DATE, primaryKey: true }
   }, { timestamps: false });

5. Save data to the database

   Instantiate a PageLoads object and save it to the database.

Create a hypertable

When you have created the relational table, you can create a hypertable. Creating tables and indexes, altering tables, inserting data, selecting data, and most other tasks are executed on the hypertable.

1. Generate the hypertable migration

   Create a migration to modify the page_loads relational table, and change it to a hypertable by first running the following command:

   npx sequelize migration:generate --name add_hypertable

   The output looks similar to this:

   Sequelize CLI [Node: 12.16.2, CLI: 5.5.1, ORM: 5.21.11]
   
   migrations folder at <PATH> already exists.
   New migration was created at <PATH>/20200601202912-add_hypertable.js .

2. Add the hypertable creation query

   In the migrations folder, there is now a new file. Open the file, and add this content:

   'use strict';
   
   module.exports = {
     up: (queryInterface, Sequelize) => {
       return queryInterface.sequelize.query("SELECT create_hypertable('page_loads', by_range('time'));");
     },
   
     down: (queryInterface, Sequelize) => {
     }
   };

   Note

   The by_range dimension builder is an addition to TimescaleDB 2.13.

3. Run the hypertable migration

   At the command prompt, run the migration command:

   npx sequelize db:migrate

   The output looks similar to this:

   Sequelize CLI [Node: 12.16.2, CLI: 5.5.1, ORM: 5.21.11]
   
   Loaded configuration file "config/config.json".
   Using environment "development".
   == 20200601202912-add_hypertable: migrating =======
   == 20200601202912-add_hypertable: migrated (0.426s)

Insert rows of data

This section covers how to insert data into your hypertables.

1. Modify the route to insert data

   In the index.js file, modify the / route to get the user-agent from the request object (req) and the current timestamp. Then, call the create method on PageLoads model, supplying the user agent and timestamp parameters. The create call executes an INSERT on the database:

   app.get('/', async (req, res) => {
       // get the user agent and current time
       const userAgent = req.get('user-agent');
       const time = new Date().getTime();
   
       try {
           // insert the record
           await PageLoads.create({
               userAgent, time
           });
   
           // send response
           res.send('Inserted!');
       } catch (e) {
           console.log('Error inserting data', e)
       }
   })

Execute a query

This section covers how to execute queries against your database. In this example, every time the page is reloaded, all information currently in the table is displayed.

1. Retrieve and display all data

   Modify the / route in the index.js file to call the Sequelize findAll function and retrieve all data from the page_loads table using the PageLoads model:

   app.get('/', async (req, res) => {
       // get the user agent and current time
       const userAgent = req.get('user-agent');
       const time = new Date().getTime();
   
       try {
           // insert the record
           await PageLoads.create({
               userAgent, time
           });
   
           // now display everything in the table
           const messages = await PageLoads.findAll();
           res.send(messages);
       } catch (e) {
           console.log('Error inserting data', e)
       }
   })

   Now, when you reload the page, you should see all of the rows currently in the page_loads table.

Go

Prerequisites

To follow the steps on this page:

• Create a target self-hosted TimescaleDB instance.

  You need your connection details.

• Install Go.
• Install the PGX driver for Go.

Connect to TimescaleDB

In this section, you create a connection to TimescaleDB using the PGX driver. PGX is a toolkit designed to help Go developers work directly with PostgreSQL. You can use it to help your Go application interact directly with TimescaleDB.

1. Locate your TimescaleDB credentials

   Use them to compose a connection string for PGX.

   You’ll need:

   • password
   • username
   • host URL
   • port number
   • database name
2. Compose your connection string variable

   Use a libpq connection string format:

   connStr := "postgres://username:password@host:port/dbname"

   If you require an SSL connection, use this format instead:

   connStr := "postgres://username:password@host:port/dbname?sslmode=require"

3. (Optional) Verify your database connection

   You can check that you’re connected to your database with this hello world program:

   package main
   
   import (
       "context"
       "fmt"
       "os"
   
       "github.com/jackc/pgx/v5"
   )
   
   //connect to database using a single connection
   func main() {
       /***********************************************/
       /* Single Connection to TimescaleDB/ PostgreSQL */
       /***********************************************/
       ctx := context.Background()
       connStr := "yourConnectionStringHere"
       conn, err := pgx.Connect(ctx, connStr)
       if err != nil {
           fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
           os.Exit(1)
       }
       defer conn.Close(ctx)
   
       //run a simple query to check our connection
       var greeting string
       err = conn.QueryRow(ctx, "select 'Hello, Timescale!'").Scan(&greeting)
       if err != nil {
           fmt.Fprintf(os.Stderr, "QueryRow failed: %v\n", err)
           os.Exit(1)
       }
       fmt.Println(greeting)
   }

   If you’d like to specify your connection string as an environment variable, you can use this syntax to access it in place of the connStr variable:

   os.Getenv("DATABASE_CONNECTION_STRING")

Alternatively, you can connect to TimescaleDB using a connection pool. Connection pooling is useful to conserve computing resources, and can also result in faster database queries:

1. Create a connection pool

   To create a connection pool for concurrent connections to your database, use the pgxpool.New() function instead of pgx.Connect(). Also note that this script imports github.com/jackc/pgx/v5/pgxpool, instead of pgx/v5 which was used to create a single connection:

   package main
   
   import (
       "context"
       "fmt"
       "os"
   
       "github.com/jackc/pgx/v5/pgxpool"
   )
   
   func main() {
   
       ctx := context.Background()
       connStr := "yourConnectionStringHere"
       dbpool, err := pgxpool.New(ctx, connStr)
       if err != nil {
           fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
           os.Exit(1)
       }
       defer dbpool.Close()
   
       //run a simple query to check our connection
       var greeting string
       err = dbpool.QueryRow(ctx, "select 'Hello, Tiger Data (but concurrently)'").Scan(&greeting)
       if err != nil {
           fmt.Fprintf(os.Stderr, "QueryRow failed: %v\n", err)
           os.Exit(1)
       }
       fmt.Println(greeting)
   }

Create a relational table

In this section, you create a table called sensors which holds the ID, type, and location of your fictional sensors. Additionally, you create a hypertable called sensor_data which holds the measurements of those sensors. The measurements contain the time, sensor_id, temperature reading, and CPU percentage of the sensors.

1. Compose the SQL statement to create a relational table

   This example creates a table called sensors, with columns for ID, type, and location:

   queryCreateTable := `CREATE TABLE sensors (id SERIAL PRIMARY KEY, type VARCHAR(50), location VARCHAR(50));`

2. Execute the CREATE TABLE statement

   Use the Exec() function on the dbpool object, using the arguments of the current context and the statement string you created:

   package main
   
   import (
       "context"
       "fmt"
       "os"
   
       "github.com/jackc/pgx/v5/pgxpool"
   )
   
   func main() {
       ctx := context.Background()
       connStr := "yourConnectionStringHere"
       dbpool, err := pgxpool.New(ctx, connStr)
       if err != nil {
           fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
           os.Exit(1)
       }
       defer dbpool.Close()
   
       /********************************************/
       /* Create relational table                      */
       /********************************************/
   
       //Create relational table called sensors
       queryCreateTable := `CREATE TABLE sensors (id SERIAL PRIMARY KEY, type VARCHAR(50), location VARCHAR(50));`
       _, err = dbpool.Exec(ctx, queryCreateTable)
       if err != nil {
           fmt.Fprintf(os.Stderr, "Unable to create SENSORS table: %v\n", err)
           os.Exit(1)
       }
       fmt.Println("Successfully created relational table SENSORS")
   }

Generate a hypertable

When you have created the relational table, you can create a hypertable. Creating tables and indexes, altering tables, inserting data, selecting data, and most other tasks are executed on the hypertable.

1. Create a variable for the CREATE TABLE SQL statement for your hypertable.

   Notice how the hypertable has the compulsory time column:

   queryCreateTable := `CREATE TABLE sensor_data (
           time TIMESTAMPTZ NOT NULL,
           sensor_id INTEGER,
           temperature DOUBLE PRECISION,
           cpu DOUBLE PRECISION,
           FOREIGN KEY (sensor_id) REFERENCES sensors (id));
           `

2. Formulate the SELECT statement to convert the table into a hypertable

   You must specify the table name to convert to a hypertable, and its time column name as the second argument. For more information, see the create_hypertable docs:

   queryCreateHypertable := `SELECT create_hypertable('sensor_data', by_range('time'));`

   Note

   The by_range dimension builder is an addition to TimescaleDB 2.13.

3. Execute the statements to create the hypertable

   Execute the CREATE TABLE statement and SELECT statement which converts the table into a hypertable. You can do this by calling the Exec() function on the dbpool object, using the arguments of the current context, and the queryCreateTable and queryCreateHypertable statement strings:

   package main
   
   import (
       "context"
       "fmt"
       "os"
   
       "github.com/jackc/pgx/v5/pgxpool"
   )
   
   func main() {
       ctx := context.Background()
       connStr := "yourConnectionStringHere"
       dbpool, err := pgxpool.New(ctx, connStr)
       if err != nil {
           fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
           os.Exit(1)
       }
       defer dbpool.Close()
   
       /********************************************/
       /* Create Hypertable                        */
       /********************************************/
       // Create hypertable of time-series data called sensor_data
       queryCreateTable := `CREATE TABLE sensor_data (
           time TIMESTAMPTZ NOT NULL,
           sensor_id INTEGER,
           temperature DOUBLE PRECISION,
           cpu DOUBLE PRECISION,
           FOREIGN KEY (sensor_id) REFERENCES sensors (id));
           `
   
       queryCreateHypertable := `SELECT create_hypertable('sensor_data', by_range('time'));`
   
       //execute statement
       _, err = dbpool.Exec(ctx, queryCreateTable+queryCreateHypertable)
       if err != nil {
           fmt.Fprintf(os.Stderr, "Unable to create the `sensor_data` hypertable: %v\n", err)
           os.Exit(1)
       }
       fmt.Println("Successfully created hypertable `sensor_data`")
   }

Insert rows of data

You can insert rows into your database in a couple of different ways. Each of these example inserts the data from the two arrays, sensorTypes and sensorLocations, into the relational table named sensors.

The first example inserts a single row of data at a time. The second example inserts multiple rows of data. The third example uses batch inserts to speed up the process.

1. Insert data using prepared statements

   Open a connection pool to the database, then use prepared statements to formulate an INSERT SQL statement, and execute it:

   package main
   
   import (
       "context"
       "fmt"
       "os"
   
       "github.com/jackc/pgx/v5/pgxpool"
   )
   
   func main() {
       ctx := context.Background()
       connStr := "yourConnectionStringHere"
       dbpool, err := pgxpool.New(ctx, connStr)
       if err != nil {
           fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
           os.Exit(1)
       }
       defer dbpool.Close()
   
       /********************************************/
       /* INSERT into  relational table            */
       /********************************************/
       //Insert data into relational table
   
       // Slices of sample data to insert
       // observation i has type sensorTypes[i] and location sensorLocations[i]
       sensorTypes := []string{"a", "a", "b", "b"}
       sensorLocations := []string{"floor", "ceiling", "floor", "ceiling"}
   
       for i := range sensorTypes {
           //INSERT statement in SQL
           queryInsertMetadata := `INSERT INTO sensors (type, location) VALUES ($1, $2);`
   
           //Execute INSERT command
           _, err := dbpool.Exec(ctx, queryInsertMetadata, sensorTypes[i], sensorLocations[i])
           if err != nil {
               fmt.Fprintf(os.Stderr, "Unable to insert data into database: %v\n", err)
               os.Exit(1)
           }
           fmt.Printf("Inserted sensor (%s, %s) into database \n", sensorTypes[i], sensorLocations[i])
       }
       fmt.Println("Successfully inserted all sensors into database")
   }

Instead of inserting a single row of data at a time, you can use this procedure to insert multiple rows of data, instead:

1. Generate sample time-series data

   This example uses PostgreSQL to generate some sample time-series data to insert into the sensor_data hypertable. Define the SQL statement to generate the data, called queryDataGeneration. Then use the .Query() function to execute the statement and return the sample data. The data returned by the query is stored in results, a slice of structs, which is then used as a source to insert data into the hypertable:

   package main
   
   import (
       "context"
       "fmt"
       "os"
       "time"
   
       "github.com/jackc/pgx/v5/pgxpool"
   )
   
   func main() {
       ctx := context.Background()
       connStr := "yourConnectionStringHere"
       dbpool, err := pgxpool.New(ctx, connStr)
       if err != nil {
           fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
           os.Exit(1)
       }
       defer dbpool.Close()
   
       // Generate data to insert
   
       //SQL query to generate sample data
       queryDataGeneration := `
           SELECT generate_series(now() - interval '24 hour', now(), interval '5 minute') AS time,
           floor(random() * (3) + 1)::int as sensor_id,
           random()*100 AS temperature,
           random() AS cpu
           `
       //Execute query to generate samples for sensor_data hypertable
       rows, err := dbpool.Query(ctx, queryDataGeneration)
       if err != nil {
           fmt.Fprintf(os.Stderr, "Unable to generate sensor data: %v\n", err)
           os.Exit(1)
       }
       defer rows.Close()
   
       fmt.Println("Successfully generated sensor data")
   
       //Store data generated in slice results
       type result struct {
           Time        time.Time
           SensorId    int
           Temperature float64
           CPU         float64
       }
   
       var results []result
       for rows.Next() {
           var r result
           err = rows.Scan(&r.Time, &r.SensorId, &r.Temperature, &r.CPU)
           if err != nil {
               fmt.Fprintf(os.Stderr, "Unable to scan %v\n", err)
               os.Exit(1)
           }
           results = append(results, r)
       }
   
       // Any errors encountered by rows.Next or rows.Scan are returned here
       if rows.Err() != nil {
           fmt.Fprintf(os.Stderr, "rows Error: %v\n", rows.Err())
           os.Exit(1)
       }
   
       // Check contents of results slice
       fmt.Println("Contents of RESULTS slice")
       for i := range results {
           var r result
           r = results[i]
           fmt.Printf("Time: %s | ID: %d | Temperature: %f | CPU: %f |\n", &r.Time, r.SensorId, r.Temperature, r.CPU)
       }
   }

2. Formulate an SQL insert statement for the sensor_data hypertable:

   //SQL query to generate sample data
   queryInsertTimeseriesData := `
       INSERT INTO sensor_data (time, sensor_id, temperature, cpu) VALUES ($1, $2, $3, $4);
       `

3. Execute the SQL statement for each sample in the results slice:

   //Insert contents of results slice into TimescaleDB
   for i := range results {
       var r result
       r = results[i]
       _, err := dbpool.Exec(ctx, queryInsertTimeseriesData, r.Time, r.SensorId, r.Temperature, r.CPU)
       if err != nil {
           fmt.Fprintf(os.Stderr, "Unable to insert sample into TimescaleDB %v\n", err)
           os.Exit(1)
       }
       defer rows.Close()
   }
   fmt.Println("Successfully inserted samples into sensor_data hypertable")

4. (Optional) Full sample code for data generation and insert

   This example main.go generates sample data and inserts it into the sensor_data hypertable:

   package main
   
   import (
       "context"
       "fmt"
       "os"
       "time"
   
       "github.com/jackc/pgx/v5/pgxpool"
   )
   
   func main() {
       /********************************************/
       /* Connect using Connection Pool            */
       /********************************************/
       ctx := context.Background()
       connStr := "yourConnectionStringHere"
       dbpool, err := pgxpool.New(ctx, connStr)
       if err != nil {
           fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
           os.Exit(1)
       }
       defer dbpool.Close()
   
       /********************************************/
       /* Insert data into hypertable              */
       /********************************************/
       // Generate data to insert
   
       //SQL query to generate sample data
       queryDataGeneration := `
           SELECT generate_series(now() - interval '24 hour', now(), interval '5 minute') AS time,
           floor(random() * (3) + 1)::int as sensor_id,
           random()*100 AS temperature,
           random() AS cpu
           `
       //Execute query to generate samples for sensor_data hypertable
       rows, err := dbpool.Query(ctx, queryDataGeneration)
       if err != nil {
           fmt.Fprintf(os.Stderr, "Unable to generate sensor data: %v\n", err)
           os.Exit(1)
       }
       defer rows.Close()
   
       fmt.Println("Successfully generated sensor data")
   
       //Store data generated in slice results
       type result struct {
           Time        time.Time
           SensorId    int
           Temperature float64
           CPU         float64
       }
       var results []result
       for rows.Next() {
           var r result
           err = rows.Scan(&r.Time, &r.SensorId, &r.Temperature, &r.CPU)
           if err != nil {
               fmt.Fprintf(os.Stderr, "Unable to scan %v\n", err)
               os.Exit(1)
           }
           results = append(results, r)
       }
       // Any errors encountered by rows.Next or rows.Scan are returned here
       if rows.Err() != nil {
           fmt.Fprintf(os.Stderr, "rows Error: %v\n", rows.Err())
           os.Exit(1)
       }
   
       // Check contents of results slice
       fmt.Println("Contents of RESULTS slice")
       for i := range results {
           var r result
           r = results[i]
           fmt.Printf("Time: %s | ID: %d | Temperature: %f | CPU: %f |\n", &r.Time, r.SensorId, r.Temperature, r.CPU)
       }
   
       //Insert contents of results slice into TimescaleDB
       //SQL query to generate sample data
       queryInsertTimeseriesData := `
           INSERT INTO sensor_data (time, sensor_id, temperature, cpu) VALUES ($1, $2, $3, $4);
           `
   
       //Insert contents of results slice into TimescaleDB
       for i := range results {
           var r result
           r = results[i]
           _, err := dbpool.Exec(ctx, queryInsertTimeseriesData, r.Time, r.SensorId, r.Temperature, r.CPU)
           if err != nil {
               fmt.Fprintf(os.Stderr, "Unable to insert sample into TimescaleDB %v\n", err)
               os.Exit(1)
           }
           defer rows.Close()
       }
       fmt.Println("Successfully inserted samples into sensor_data hypertable")
   }

Inserting multiple rows of data using this method executes as many insert statements as there are samples to be inserted. This can make ingestion of data slow. To speed up ingestion, you can batch insert data instead.

Here’s a sample pattern for how to do so, using the sample data you generated in the previous procedure. It uses the pgx Batch object:

1. This example batch inserts data into the database:

   package main
   
   import (
       "context"
       "fmt"
       "os"
       "time"
   
       "github.com/jackc/pgx/v5"
       "github.com/jackc/pgx/v5/pgxpool"
   )
   
   func main() {
       /********************************************/
       /* Connect using Connection Pool            */
       /********************************************/
       ctx := context.Background()
       connStr := "yourConnectionStringHere"
       dbpool, err := pgxpool.New(ctx, connStr)
       if err != nil {
           fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
           os.Exit(1)
       }
       defer dbpool.Close()
   
       // Generate data to insert
   
       //SQL query to generate sample data
       queryDataGeneration := `
           SELECT generate_series(now() - interval '24 hour', now(), interval '5 minute') AS time,
           floor(random() * (3) + 1)::int as sensor_id,
           random()*100 AS temperature,
           random() AS cpu
           `
   
       //Execute query to generate samples for sensor_data hypertable
       rows, err := dbpool.Query(ctx, queryDataGeneration)
       if err != nil {
           fmt.Fprintf(os.Stderr, "Unable to generate sensor data: %v\n", err)
           os.Exit(1)
       }
       defer rows.Close()
   
       fmt.Println("Successfully generated sensor data")
   
       //Store data generated in slice results
       type result struct {
           Time        time.Time
           SensorId    int
           Temperature float64
           CPU         float64
       }
       var results []result
       for rows.Next() {
           var r result
           err = rows.Scan(&r.Time, &r.SensorId, &r.Temperature, &r.CPU)
           if err != nil {
               fmt.Fprintf(os.Stderr, "Unable to scan %v\n", err)
               os.Exit(1)
           }
           results = append(results, r)
       }
       // Any errors encountered by rows.Next or rows.Scan are returned here
       if rows.Err() != nil {
           fmt.Fprintf(os.Stderr, "rows Error: %v\n", rows.Err())
           os.Exit(1)
       }
   
       // Check contents of results slice
       /*fmt.Println("Contents of RESULTS slice")
       for i := range results {
           var r result
           r = results[i]
           fmt.Printf("Time: %s | ID: %d | Temperature: %f | CPU: %f |\n", &r.Time, r.SensorId, r.Temperature, r.CPU)
       }*/
   
       //Insert contents of results slice into TimescaleDB
       //SQL query to generate sample data
       queryInsertTimeseriesData := `
           INSERT INTO sensor_data (time, sensor_id, temperature, cpu) VALUES ($1, $2, $3, $4);
           `
   
       /********************************************/
       /* Batch Insert into TimescaleDB            */
       /********************************************/
       //create batch
       batch := &pgx.Batch{}
       //load insert statements into batch queue
       for i := range results {
           var r result
           r = results[i]
           batch.Queue(queryInsertTimeseriesData, r.Time, r.SensorId, r.Temperature, r.CPU)
       }
       batch.Queue("select count(*) from sensor_data")
   
       //send batch to connection pool
       br := dbpool.SendBatch(ctx, batch)
       //execute statements in batch queue
       _, err = br.Exec()
       if err != nil {
           fmt.Fprintf(os.Stderr, "Unable to execute statement in batch queue %v\n", err)
           os.Exit(1)
       }
       fmt.Println("Successfully batch inserted data")
   
       //Compare length of results slice to size of table
       fmt.Printf("size of results: %d\n", len(results))
       //check size of table for number of rows inserted
       // result of last SELECT statement
       var rowsInserted int
       err = br.QueryRow().Scan(&rowsInserted)
       fmt.Printf("size of table: %d\n", rowsInserted)
   
       err = br.Close()
       if err != nil {
           fmt.Fprintf(os.Stderr, "Unable to closer batch %v\n", err)
           os.Exit(1)
       }
   }

Execute a query

This section covers how to execute queries against your database.

1. Define the SQL query

   This example uses a SQL query that combines time-series and relational data. It returns the average CPU values for every 5 minute interval, for sensors located on location ceiling and of type a:

   // Formulate query in SQL
   // Note the use of prepared statement placeholders $1 and $2
   queryTimebucketFiveMin := `
       SELECT time_bucket('5 minutes', time) AS five_min, avg(cpu)
       FROM sensor_data
       JOIN sensors ON sensors.id = sensor_data.sensor_id
       WHERE sensors.location = $1 AND sensors.type = $2
       GROUP BY five_min
       ORDER BY five_min DESC;
       `

2. Execute the query

   Use the .Query() function to execute the query string. Make sure you specify the relevant placeholders:

   //Execute query on TimescaleDB
   rows, err := dbpool.Query(ctx, queryTimebucketFiveMin, "ceiling", "a")
   if err != nil {
       fmt.Fprintf(os.Stderr, "Unable to execute query %v\n", err)
       os.Exit(1)
   }
   defer rows.Close()
   
   fmt.Println("Successfully executed query")

3. Access the returned rows

   Create a struct with fields representing the columns that you expect to be returned, then use the rows.Next() function to iterate through the rows returned and fill results with the array of structs. This uses the rows.Scan() function, passing in pointers to the fields that you want to scan for results.

   This example prints out the results returned from the query, but you might want to use those results for some other purpose. Once you’ve scanned through all the rows returned you can then use the results array however you like.

   //Do something with the results of query
   // Struct for results
   type result2 struct {
       Bucket time.Time
       Avg    float64
   }
   
   // Print rows returned and fill up results slice for later use
   var results []result2
   for rows.Next() {
       var r result2
       err = rows.Scan(&r.Bucket, &r.Avg)
       if err != nil {
           fmt.Fprintf(os.Stderr, "Unable to scan %v\n", err)
           os.Exit(1)
       }
       results = append(results, r)
       fmt.Printf("Time bucket: %s | Avg: %f\n", &r.Bucket, r.Avg)
   }
   
   // Any errors encountered by rows.Next or rows.Scan are returned here
   if rows.Err() != nil {
       fmt.Fprintf(os.Stderr, "rows Error: %v\n", rows.Err())
       os.Exit(1)
   }
   
   // use results here…

4. (Optional) Full sample code for querying

   This example program runs a query, and accesses the results of that query:

   package main
   
   import (
       "context"
       "fmt"
       "os"
       "time"
   
       "github.com/jackc/pgx/v5/pgxpool"
   )
   
   func main() {
       ctx := context.Background()
       connStr := "yourConnectionStringHere"
       dbpool, err := pgxpool.New(ctx, connStr)
       if err != nil {
           fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
           os.Exit(1)
       }
       defer dbpool.Close()
   
       /********************************************/
       /* Execute a query                          */
       /********************************************/
   
       // Formulate query in SQL
       // Note the use of prepared statement placeholders $1 and $2
       queryTimebucketFiveMin := `
           SELECT time_bucket('5 minutes', time) AS five_min, avg(cpu)
           FROM sensor_data
           JOIN sensors ON sensors.id = sensor_data.sensor_id
           WHERE sensors.location = $1 AND sensors.type = $2
           GROUP BY five_min
           ORDER BY five_min DESC;
           `
   
       //Execute query on TimescaleDB
       rows, err := dbpool.Query(ctx, queryTimebucketFiveMin, "ceiling", "a")
       if err != nil {
           fmt.Fprintf(os.Stderr, "Unable to execute query %v\n", err)
           os.Exit(1)
       }
       defer rows.Close()
   
       fmt.Println("Successfully executed query")
   
       //Do something with the results of query
       // Struct for results
       type result2 struct {
           Bucket time.Time
           Avg    float64
       }
   
       // Print rows returned and fill up results slice for later use
       var results []result2
       for rows.Next() {
           var r result2
           err = rows.Scan(&r.Bucket, &r.Avg)
           if err != nil {
               fmt.Fprintf(os.Stderr, "Unable to scan %v\n", err)
               os.Exit(1)
           }
           results = append(results, r)
           fmt.Printf("Time bucket: %s | Avg: %f\n", &r.Bucket, r.Avg)
       }
       // Any errors encountered by rows.Next or rows.Scan are returned here
       if rows.Err() != nil {
           fmt.Fprintf(os.Stderr, "rows Error: %v\n", rows.Err())
           os.Exit(1)
       }
   }

Next steps

Now that you’re able to connect, read, and write to a TimescaleDB instance from your Go application, check out these resources:

• Refer to the pgx documentation for more information about pgx.
• Want fast inserts on CSV data? Check out TimescaleDB parallel copy, a tool for fast inserts, written in Go.

Java

Prerequisites

To follow the steps on this page:

• Create a target self-hosted TimescaleDB instance.

  You need your connection details.

• Install the Java Development Kit (JDK).
• Install the PostgreSQL JDBC driver.

All code in this quick start is for Java 16 and later. If you are working with older JDK versions, use legacy coding techniques.

Connect to TimescaleDB

In this section, you create a connection to your TimescaleDB database using an application in a single file. You can use any of your favorite build tools, including gradle or maven.

1. Create the Main.java file

   Create a directory containing a text file called Main.java, with this content:

   package com.timescale.java;
   
   public class Main {
   
       public static void main(String... args) {
           System.out.println("Hello, World!");
       }
   }

2. Run the application

   From the command line in the current directory, run the application:

   java Main.java

   If the command is successful, Hello, World! line output is printed to your console.

3. Import the PostgreSQL JDBC driver

   If you are using a dependency manager, include the PostgreSQL JDBC Driver as a dependency.

4. Download the JDBC Driver JAR

   Download the JAR artifact of the JDBC Driver and save it with the Main.java file.

5. Import the JDBC Driver into the application

   Import the JDBC Driver into the Java application and display a list of available drivers for the check:

   package com.timescale.java;
   
   import java.sql.DriverManager;
   
   public class Main {
   
       public static void main(String... args) {
           DriverManager.drivers().forEach(System.out::println);
       }
   }

6. Run the examples

   Run all the examples:

   java -cp *.jar Main.java

   If the command is successful, a string similar to org.postgresql.Driver@7f77e91b is printed to your console. This means that you are ready to connect to TimescaleDB from Java.

7. Locate your TimescaleDB credentials

   Locate your TimescaleDB credentials and use them to compose a connection string for JDBC.

   You’ll need:

   • password
   • username
   • host URL
   • port
   • database name
8. Compose the connection string

   Compose your connection string variable, using this format:

   var connUrl = "jdbc:postgresql://<HOSTNAME>:<PORT>/<DATABASE_NAME>?user=<USERNAME>&password=<PASSWORD>";

   For more information about creating connection strings, see the JDBC documentation.

   Warning

   This method of composing a connection string is for test or development purposes only. For production, use environment variables for sensitive details like your password, hostname, and port number.

   package com.timescale.java;
   
   import java.sql.DriverManager;
   import java.sql.SQLException;
   
   public class Main {
   
       public static void main(String... args) throws SQLException {
           var connUrl = "jdbc:postgresql://<HOSTNAME>:<PORT>/<DATABASE_NAME>?user=<USERNAME>&password=<PASSWORD>";
           var conn = DriverManager.getConnection(connUrl);
           System.out.println(conn.getClientInfo());
       }
   }

9. Run the code

   Run the code:

   java -cp *.jar Main.java

   If the command is successful, a string similar to {ApplicationName={C.PG} JDBC Driver} is printed to your console.

Create a relational table

In this section, you create a table called sensors which holds the ID, type, and location of your fictional sensors. Additionally, you create a hypertable called sensor_data which holds the measurements of those sensors. The measurements contain the time, sensor_id, temperature reading, and CPU percentage of the sensors.

1. Compose the CREATE TABLE statement

   Compose a string which contains the SQL statement to create a relational table. This example creates a table called sensors, with columns id, type and location:

   CREATE TABLE sensors (
       id SERIAL PRIMARY KEY,
       type TEXT NOT NULL,
       location TEXT NOT NULL
   );

2. Execute the query and verify

   Create a statement, execute the query you created in the previous step, and check that the table was created successfully:

   package com.timescale.java;
   
   import java.sql.DriverManager;
   import java.sql.SQLException;
   
   public class Main {
   
       public static void main(String... args) throws SQLException {
           var connUrl = "jdbc:postgresql://<HOSTNAME>:<PORT>/<DATABASE_NAME>?user=<USERNAME>&password=<PASSWORD>";
           var conn = DriverManager.getConnection(connUrl);
   
           var createSensorTableQuery = """
                   CREATE TABLE sensors (
                       id SERIAL PRIMARY KEY,
                       type TEXT NOT NULL,
                       location TEXT NOT NULL
                   )
                   """;
           try (var stmt = conn.createStatement()) {
               stmt.execute(createSensorTableQuery);
           }
   
           var showAllTablesQuery = "SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname = 'public'";
           try (var stmt = conn.createStatement();
                var rs = stmt.executeQuery(showAllTablesQuery)) {
               System.out.println("Tables in the current database: ");
               while (rs.next()) {
                   System.out.println(rs.getString("tablename"));
               }
           }
       }
   }

Create a hypertable

When you have created the relational table, you can create a hypertable. Creating tables and indexes, altering tables, inserting data, selecting data, and most other tasks are executed on the hypertable.

1. Write the CREATE TABLE statement for the hypertable

   Create a CREATE TABLE SQL statement for your hypertable. Notice how the hypertable has the compulsory time column:

   CREATE TABLE sensor_data (
       time TIMESTAMPTZ NOT NULL,
       sensor_id INTEGER REFERENCES sensors (id),
       value DOUBLE PRECISION
   );

2. Convert the table to a hypertable

   Create a statement, execute the query you created in the previous step:

   SELECT create_hypertable('sensor_data', by_range('time'));

   Note

   The by_range and by_hash dimension builder is an addition to TimescaleDB 2.13.

3. Execute and commit the schema changes

   Execute the two statements you created, and commit your changes to the database:

   package com.timescale.java;
   
   import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.SQLException;
   import java.util.List;
   
   public class Main {
   
       public static void main(String... args) {
           final var connUrl = "jdbc:postgresql://<HOSTNAME>:<PORT>/<DATABASE_NAME>?user=<USERNAME>&password=<PASSWORD>";
           try (var conn = DriverManager.getConnection(connUrl)) {
               createSchema(conn);
               insertData(conn);
           } catch (SQLException ex) {
               System.err.println(ex.getMessage());
           }
       }
   
       private static void createSchema(final Connection conn) throws SQLException {
           try (var stmt = conn.createStatement()) {
               stmt.execute("""
                       CREATE TABLE sensors (
                           id SERIAL PRIMARY KEY,
                           type TEXT NOT NULL,
                           location TEXT NOT NULL
                       )
                       """);
           }
   
           try (var stmt = conn.createStatement()) {
               stmt.execute("""
                       CREATE TABLE sensor_data (
                           time TIMESTAMPTZ NOT NULL,
                           sensor_id INTEGER REFERENCES sensors (id),
                           value DOUBLE PRECISION
                       )
                       """);
           }
   
           try (var stmt = conn.createStatement()) {
               stmt.execute("SELECT create_hypertable('sensor_data', by_range('time'))");
           }
       }
   }

Insert data

You can insert data into your hypertables in several different ways. In this section, you can insert single rows, or insert by batches of rows.

1. Insert rows with prepared statements

   Open a connection to the database, use prepared statements to formulate the INSERT SQL statement, then execute the statement:

   final List<Sensor> sensors = List.of(
           new Sensor("temperature", "bedroom"),
           new Sensor("temperature", "living room"),
           new Sensor("temperature", "outside"),
           new Sensor("humidity", "kitchen"),
           new Sensor("humidity", "outside"));
   for (final var sensor : sensors) {
       try (var stmt = conn.prepareStatement("INSERT INTO sensors (type, location) VALUES (?, ?)")) {
           stmt.setString(1, sensor.type());
           stmt.setString(2, sensor.location());
           stmt.executeUpdate();
       }
   }

If you want to insert a batch of rows by using a batching mechanism. In this example, you generate some sample time-series data to insert into the sensor_data hypertable:

1. Insert batches of rows

   Insert batches of rows:

   final var sensorDataCount = 100;
   final var insertBatchSize = 10;
   try (var stmt = conn.prepareStatement("""
           INSERT INTO sensor_data (time, sensor_id, value)
           VALUES (
               generate_series(now() - INTERVAL '24 hours', now(), INTERVAL '5 minutes'),
               floor(random() * 4 + 1)::INTEGER,
               random()
           )
           """)) {
       for (int i = 0; i < sensorDataCount; i++) {
           stmt.addBatch();
   
           if ((i > 0 && i % insertBatchSize == 0) || i == sensorDataCount - 1) {
               stmt.executeBatch();
           }
       }
   }

Execute a query

This section covers how to execute queries against your database.

1. Define the SQL query

   Define the SQL query you’d like to run on the database. This example combines time-series and relational data. It returns the average values for every 15 minute interval for sensors with specific type and location.

   SELECT time_bucket('15 minutes', time) AS bucket, avg(value)
   FROM sensor_data
   JOIN sensors ON sensors.id = sensor_data.sensor_id
   WHERE sensors.type = ? AND sensors.location = ?
   GROUP BY bucket
   ORDER BY bucket DESC;

2. Execute the query and read results

   Execute the query with the prepared statement and read out the result set for all a-type sensors located on the floor:

   try (var stmt = conn.prepareStatement("""
           SELECT time_bucket('15 minutes', time) AS bucket, avg(value)
           FROM sensor_data
           JOIN sensors ON sensors.id = sensor_data.sensor_id
           WHERE sensors.type = ? AND sensors.location = ?
           GROUP BY bucket
           ORDER BY bucket DESC
           """)) {
       stmt.setString(1, "temperature");
       stmt.setString(2, "living room");
   
       try (var rs = stmt.executeQuery()) {
           while (rs.next()) {
               System.out.printf("%s: %f%n", rs.getTimestamp(1), rs.getDouble(2));
           }
       }
   }

   If the command is successful, you’ll see output like this:

   2021-05-12 23:30:00.0: 0,508649
   2021-05-12 23:15:00.0: 0,477852
   2021-05-12 23:00:00.0: 0,462298
   2021-05-12 22:45:00.0: 0,457006
   2021-05-12 22:30:00.0: 0,568744
   ...

Complete code samples

This section contains complete code samples.

Complete code sample

package com.timescale.java;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;

public class Main {

    public static void main(String... args) {
        final var connUrl = "jdbc:postgresql://<HOSTNAME>:<PORT>/<DATABASE_NAME>?user=<USERNAME>&password=<PASSWORD>";
        try (var conn = DriverManager.getConnection(connUrl)) {
            createSchema(conn);
            insertData(conn);
        } catch (SQLException ex) {
            System.err.println(ex.getMessage());
        }
    }

    private static void createSchema(final Connection conn) throws SQLException {
        try (var stmt = conn.createStatement()) {
            stmt.execute("""
                    CREATE TABLE sensors (
                        id SERIAL PRIMARY KEY,
                        type TEXT NOT NULL,
                        location TEXT NOT NULL
                    )
                    """);
        }

        try (var stmt = conn.createStatement()) {
            stmt.execute("""
                    CREATE TABLE sensor_data (
                        time TIMESTAMPTZ NOT NULL,
                        sensor_id INTEGER REFERENCES sensors (id),
                        value DOUBLE PRECISION
                    )
                    """);
        }

        try (var stmt = conn.createStatement()) {
            stmt.execute("SELECT create_hypertable('sensor_data', by_range('time'))");
        }
    }

    private static void insertData(final Connection conn) throws SQLException {
        final List<Sensor> sensors = List.of(
                new Sensor("temperature", "bedroom"),
                new Sensor("temperature", "living room"),
                new Sensor("temperature", "outside"),
                new Sensor("humidity", "kitchen"),
                new Sensor("humidity", "outside"));
        for (final var sensor : sensors) {
            try (var stmt = conn.prepareStatement("INSERT INTO sensors (type, location) VALUES (?, ?)")) {
                stmt.setString(1, sensor.type());
                stmt.setString(2, sensor.location());
                stmt.executeUpdate();
            }
        }

        final var sensorDataCount = 100;
        final var insertBatchSize = 10;
        try (var stmt = conn.prepareStatement("""
                INSERT INTO sensor_data (time, sensor_id, value)
                VALUES (
                    generate_series(now() - INTERVAL '24 hours', now(), INTERVAL '5 minutes'),
                    floor(random() * 4 + 1)::INTEGER,
                    random()
                )
                """)) {
            for (int i = 0; i < sensorDataCount; i++) {
                stmt.addBatch();

                if ((i > 0 && i % insertBatchSize == 0) || i == sensorDataCount - 1) {
                    stmt.executeBatch();
                }
            }
        }
    }

    private record Sensor(String type, String location) {
    }
}

Execute more complex queries

package com.timescale.java;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;

public class Main {

    public static void main(String... args) {
        final var connUrl = "jdbc:postgresql://<HOSTNAME>:<PORT>/<DATABASE_NAME>?user=<USERNAME>&password=<PASSWORD>";
        try (var conn = DriverManager.getConnection(connUrl)) {
            createSchema(conn);
            insertData(conn);
            executeQueries(conn);
        } catch (SQLException ex) {
            System.err.println(ex.getMessage());
        }
    }

    private static void createSchema(final Connection conn) throws SQLException {
        try (var stmt = conn.createStatement()) {
            stmt.execute("""
                    CREATE TABLE sensors (
                        id SERIAL PRIMARY KEY,
                        type TEXT NOT NULL,
                        location TEXT NOT NULL
                    )
                    """);
        }

        try (var stmt = conn.createStatement()) {
            stmt.execute("""
                    CREATE TABLE sensor_data (
                        time TIMESTAMPTZ NOT NULL,
                        sensor_id INTEGER REFERENCES sensors (id),
                        value DOUBLE PRECISION
                    )
                    """);
        }

        try (var stmt = conn.createStatement()) {
            stmt.execute("SELECT create_hypertable('sensor_data', by_range('time'))");
        }
    }

    private static void insertData(final Connection conn) throws SQLException {
        final List<Sensor> sensors = List.of(
                new Sensor("temperature", "bedroom"),
                new Sensor("temperature", "living room"),
                new Sensor("temperature", "outside"),
                new Sensor("humidity", "kitchen"),
                new Sensor("humidity", "outside"));
        for (final var sensor : sensors) {
            try (var stmt = conn.prepareStatement("INSERT INTO sensors (type, location) VALUES (?, ?)")) {
                stmt.setString(1, sensor.type());
                stmt.setString(2, sensor.location());
                stmt.executeUpdate();
            }
        }

        final var sensorDataCount = 100;
        final var insertBatchSize = 10;
        try (var stmt = conn.prepareStatement("""
                INSERT INTO sensor_data (time, sensor_id, value)
                VALUES (
                    generate_series(now() - INTERVAL '24 hours', now(), INTERVAL '5 minutes'),
                    floor(random() * 4 + 1)::INTEGER,
                    random()
                )
                """)) {
            for (int i = 0; i < sensorDataCount; i++) {
                stmt.addBatch();

                if ((i > 0 && i % insertBatchSize == 0) || i == sensorDataCount - 1) {
                    stmt.executeBatch();
                }
            }
        }
    }

    private static void executeQueries(final Connection conn) throws SQLException {
        try (var stmt = conn.prepareStatement("""
                SELECT time_bucket('15 minutes', time) AS bucket, avg(value)
                FROM sensor_data
                JOIN sensors ON sensors.id = sensor_data.sensor_id
                WHERE sensors.type = ? AND sensors.location = ?
                GROUP BY bucket
                ORDER BY bucket DESC
                """)) {
            stmt.setString(1, "temperature");
            stmt.setString(2, "living room");

            try (var rs = stmt.executeQuery()) {
                while (rs.next()) {
                    System.out.printf("%s: %f%n", rs.getTimestamp(1), rs.getDouble(2));
                }
            }
        }
    }

    private record Sensor(String type, String location) {
    }
}

Next steps

Now that you’re able to connect, read, and write to a TimescaleDB instance from your Java application, and generate the scaffolding necessary to build a new application from an existing TimescaleDB instance, be sure to check out these advanced TimescaleDB tutorials:

• Continuous aggregates
• Migrate your data

You are not limited to these languages! You can interface with TimescaleDB using any PostgreSQL client driver.