Node.js quick start

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

   npm init -y

   Copy

   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"
   }

   Copy

2. Install Express.js:

   npm install express

   Copy

3. 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}`))

   Copy

4. Test your connection by starting the application:

   node index.js

   Copy

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

5. Add Sequelize to your project:

   npm install sequelize sequelize-cli pg pg-hstore

   Copy

6. 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 your connection string variable, using this format:

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

   Copy

8. 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
               }
           }
       })

   Copy

   Make sure you add the SSL settings in the dialectOptions sections. You can't connect to TimescaleDB using SSL without them.

9. 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);
   });

   Copy

   Start the application on the command line:

   node index.js

   Copy

   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.

   Copy

1. 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

   Copy

   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>.

   Copy

2. 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');
     }
   };

   Copy

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

   npx sequelize db:migrate

   Copy

   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)

   Copy

4. 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 });

   Copy

5. Instantiate a PageLoads object and save it to the database.

1. 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

   Copy

   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 .

   Copy

2. 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) => {
     }
   };

   Copy
   

   Note

   The by_range dimension builder is an addition to TimescaleDB 2.13.

3. At the command prompt, run the migration command:

   npx sequelize db:migrate

   Copy

   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)

   Copy

1. 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)
       }
   })

   Copy

1. 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)
       }
   })

   Copy

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