--- title: Start coding with Tiger Data | Tiger Data Docs description: Integrate your app with Tiger Cloud using your preferred programming language --- Easily integrate your app with Tiger Cloud or self-hosted TimescaleDB. Use your favorite programming language to connect to your Tiger Cloud service, create and manage hypertables, then ingest and query data. - [Ruby](#tab-panel-607) - [Python](#tab-panel-608) - [Node.js](#tab-panel-609) - [Go](#tab-panel-610) - [Java](#tab-panel-611) ## Prerequisites To follow the steps on this page: - Create a target [self-hosted TimescaleDB](/docs/get-started/choose-your-path/install-timescaledb/index.md) instance. You need your [connection details](/docs/integrate/find-connection-details/index.md). * Install [Rails](https://guides.rubyonrails.org/install_ruby_on_rails.html#installing-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. Terminal window ``` 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: Terminal window ``` bundle install ``` 3. **Connect your app to TimescaleDB** 1. In `/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: Terminal window ``` export DATABASE_URL="postgres://username:password@host:port/dbname" ``` 3. Create the database for the project: Terminal window ``` rails db:create ``` 4. Run migrations: Terminal window ``` rails db:migrate ``` 5. Verify the connection from your app to your TimescaleDB database: Terminal window ``` 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](/docs/build/how-to/your-first-hypertable/index.md). 1. **Generate a migration to create the page loads table** Terminal window ``` rails generate migration create_page_loads ``` This creates the `/db/migrate/_create_page_loads.rb` migration file. 2. **Add hypertable options** Replace the contents of `/db/migrate/_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](https://guides.rubyonrails.org/active_record_composite_primary_keys.html). 3. **Create a `PageLoad` model** Create a new file called `/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** Terminal window ``` 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 `/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** Terminal window ``` # Insert the data in batches PageLoad.insert_all(generate_sample_page_loads, returning: false) ``` 4. **Validate the test data in your TimescaleDB database** Terminal window ``` 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](https://www.tigerdata.com/blog/building-a-better-ruby-orm-for-time-series-and-analytics). ``` 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](https://github.com/timescale/timescaledb-ruby). - Check out the [official docs](https://timescale.github.io/timescaledb-ruby/). - Follow the [LTTB](https://timescale.github.io/timescaledb-ruby/toolkit_lttb_tutorial/), [Open AI long-term storage](https://timescale.github.io/timescaledb-ruby/chat_gpt_tutorial/), and [candlesticks](https://timescale.github.io/timescaledb-ruby/toolkit_candlestick/) tutorials. ## Prerequisites To follow the steps on this page: - Create a target [self-hosted TimescaleDB](/docs/get-started/choose-your-path/install-timescaledb/index.md) instance. You need your [connection details](/docs/integrate/find-connection-details/index.md). * Install the `psycopg2` library. For more information, see the [psycopg2 documentation](https://pypi.org/project/psycopg2/). * (Optional) Create a [Python virtual environment](https://docs.python.org/3/library/venv.html). ## 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](https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING) 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](https://www.psycopg.org/docs/connection.html?highlight=cursor#connection.cursor) 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](/docs/reference/timescaledb/hypertables/create_hypertable/index.md): ``` 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](https://pypi.org/project/pgcopy/), 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](https://www.psycopg.org/docs/usage.html). For more information about how to execute more complex queries in `psycopg2`, see the [psycopg2 documentation](https://www.psycopg.org/docs/usage.html). ### 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](https://www.psycopg.org/docs/cursor.html), 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`](https://www.psycopg.org/docs/extras.html#dictionary-like-cursor): ``` 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() ``` ## Prerequisites To follow the steps on this page: - Create a target [self-hosted TimescaleDB](/docs/get-started/choose-your-path/install-timescaledb/index.md) instance. You need your [connection details](/docs/integrate/find-connection-details/index.md). * Install [Node.js](https://nodejs.org). * Install the Node.js package manager [npm](https://docs.npmjs.com/getting-started). ## Connect to TimescaleDB In this section, you create a connection to TimescaleDB with a common Node.js ORM (object relational mapper) called [Sequelize](https://sequelize.org). 1. **Initialize a new Node.js app** At the command prompt, initialize a new Node.js app: Terminal window ``` 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: Terminal window ``` 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: Terminal window ``` 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: Terminal window ``` 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://:@:/' ``` 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://:@:/', { 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: Terminal window ``` node index.js ``` If the connection is successful, you’ll get output like this: Terminal window ``` 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`: Terminal window ``` npx sequelize model:generate --name page_loads \ --attributes userAgent:string,time:date ``` The output looks similar to this: Terminal window ``` Sequelize CLI [Node: 12.16.2, CLI: 5.5.1, ORM: 5.21.11] New model was created at . New migration was created at . ``` 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: Terminal window ``` npx sequelize db:migrate ``` The output looks similar to this: Terminal window ``` 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: Terminal window ``` npx sequelize migration:generate --name add_hypertable ``` The output looks similar to this: Terminal window ``` Sequelize CLI [Node: 12.16.2, CLI: 5.5.1, ORM: 5.21.11] migrations folder at already exists. New migration was created at /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: Terminal window ``` npx sequelize db:migrate ``` The output looks similar to this: Terminal window ``` 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. ## Prerequisites To follow the steps on this page: - Create a target [self-hosted TimescaleDB](/docs/get-started/choose-your-path/install-timescaledb/index.md) instance. You need your [connection details](/docs/integrate/find-connection-details/index.md). * Install [Go](https://golang.org/doc/install). * Install the [PGX driver for Go](https://github.com/jackc/pgx). ## 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](https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING) 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](/docs/reference/timescaledb/hypertables/create_hypertable/index.md): ``` 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](https://pkg.go.dev/github.com/jackc/pgx) for more information about pgx. - Want fast inserts on CSV data? Check out [TimescaleDB parallel copy](https://github.com/timescale/timescaledb-parallel-copy), a tool for fast inserts, written in Go. ## Prerequisites To follow the steps on this page: - Create a target [self-hosted TimescaleDB](/docs/get-started/choose-your-path/install-timescaledb/index.md) instance. You need your [connection details](/docs/integrate/find-connection-details/index.md). * Install the [Java Development Kit (JDK)](https://openjdk.java.net). * Install the [PostgreSQL JDBC driver](https://jdbc.postgresql.org). 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: Terminal window ``` 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](https://mvnrepository.com/artifact/org.postgresql/postgresql) as a dependency. 4. **Download the JDBC Driver JAR** Download the [JAR artifact of the JDBC Driver](https://jdbc.postgresql.org/download/) 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: Terminal window ``` 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://:/?user=&password="; ``` For more information about creating connection strings, see the [JDBC documentation](https://jdbc.postgresql.org/documentation/datasource/). 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://:/?user=&password="; var conn = DriverManager.getConnection(connUrl); System.out.println(conn.getClientInfo()); } } ``` 9. **Run the code** Run the code: Terminal window ``` 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://:/?user=&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://:/?user=&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 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: Terminal window ``` 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://:/?user=&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 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://:/?user=&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 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](/docs/build/continuous-aggregates/create-a-continuous-aggregate/index.md) - [Migrate your data](/docs/migrate/index.md) You are not limited to these languages. Tiger Cloud is based on PostgreSQL, you can interface with TimescaleDB and Tiger Cloud using any [PostgreSQL client driver](https://wiki.postgresql.org/wiki/List_of_drivers).