DEV Community: Jan Kleinert

Browse and query Cloud Spanner databases from Visual Studio Code

Jan Kleinert — Wed, 03 Nov 2021 16:18:21 +0000

Visual Studio Code is one of the most widely-used IDEs, due in part to the variety of extensions that are available to developers. For developers who are building applications that interact with Cloud Spanner, we're excited to announce the Google Cloud Spanner driver for the popular SQLTools extension for VS Code.

The SQLTools extension works with a variety of SQL drivers and allows developers to manage database connections, execute and generate queries, and more from within VS Code. By using the Cloud Spanner driver with SQLTools, developers can browse tables and execute queries, DDL statements, and DML statements on Cloud Spanner databases without having to leave the IDE.

In this post, we'll walk through the process of installing the extension, connecting to a Cloud Spanner database, and using SQLTools with the database.

Prerequisites

Before you get started, you'll need to have a Google Cloud Platform project with a Cloud Spanner instance and a database. This codelab will walk you through the process if you haven't used Cloud Spanner before. Alternatively, you can use the emulator. You'll also need to have VS Code installed on your computer.

Installation

To install the Cloud Spanner driver for SQLTools, click on the Extensions icon in VS Code, search for "cloud spanner driver", and install the extension called Google Cloud Spanner Driver. Alternatively, you can install the Cloud Spanner driver for SQLTools from the Visual Studio Marketplace.

Once the extension is installed, you'll see a database icon for SQLTools, as highlighted by the red rectangle in the image below, show up in VS Code. Click this database icon to access the extension.

Connecting to a Cloud Spanner database

With the extension installed, click the Add New Connection icon in SQLTools to open the Connection Assistant and choose Google Cloud Spanner Driver. You can connect either to a Spanner instance on Google Cloud or to an emulator instance.

Configuring a connection using the emulator

The Cloud SDK offers a local, in-memory emulator that you can use while developing and testing. To use the SQLTools extension with the emulator, you must first start the emulator. Then, in the Connection Settings step, enter values for Connection name, Google Cloud Project ID, Spanner Instance ID, and Spanner Database ID. Select the checkbox next to Connect to emulator. When you use this setting, the instance and database you specified will be automatically created for you in the emulator if they do not already exist.

Configuring a connection to a Cloud Spanner database on Google Cloud

If you are connecting to a Cloud Spanner database running on Google Cloud, you'll need to provide the Google Cloud Project ID, Spanner Instance ID, and Spanner Database ID. You can enter any value you like for the Connection name. You'll also need to specify your credentials in one of two ways: enter the absolute path to your credential key file in the Connection Assistant or set the GOOGLE_ACCOUNT_CREDENTIALS environment variable to the path to your credential key file. If you are using the GOOGLE_ACCOUNT_CREDENTIALS environment variable, note that if VS Code was already running before you set the environment variable, then you will need to restart VS Code. Your service account will need to be granted appropriate permissions for interacting with Cloud Spanner. For more information about credentials, see the documentation on creating service accounts and service account keys. You can find a list of Cloud Spanner roles in this table.

Testing and establishing connections

Once you've entered the connection settings information, you can click TEST CONNECTION to make sure the connection is successful, and then click SAVE CONNECTION.

On the final step of the Connection Assistant, click CONNECT NOW.

Browsing database tables

In the Connections section of SQLTools, you can view the tables in your database. In the screenshot below, you can see the columns in the comments table.

Right-clicking on a table name provides options such as showing table records or generating an insert query.

Executing queries and statements

The Cloud Spanner driver supports executing queries, DDL statements, and DML statements. If you execute multiple statements in a single script, each statement will be executed in a separate transaction. Note that the extension is intended for use during development and testing, not for administration of production database environments.

Queries use single-use read-only transactions, while DML statements use read-write transactions. Make sure that the service account you're using has the necessary permissions to execute the queries or statements. For more information on types of transactions, see the documentation.

Next steps

Interacting with your Cloud Spanner databases from within your IDE can make your development process more efficient and reduce the need to switch between multiple tools and interfaces. Ready to try it yourself? Install the Cloud Spanner driver for SQLTools and start exploring and interacting with your Cloud Spanner databases from within VS Code. If you have suggestions or issues, you can raise them in the issue tracker or for questions or comments, feel free to reach out to me on Twitter. We would love to hear your feedback.

Enable query tagging with Sqlcommenter to understand application impact on database performance

Jan Kleinert — Wed, 03 Nov 2021 15:47:39 +0000

Cloud SQL Insights launched earlier this year, giving developers a tool that helps detect, diagnose, and prevent query performance problems for PostgreSQL for Cloud SQL databases. Out of the box, the Query Insights dashboard provides database load graphs, detailed query performance, and built-in query plans. Users can boost the power of Insights by enabling query tagging via an open source library called Sqlcommenter. In this post, we'll share the benefits of using query tagging, what Sqlcommenter is, and two ways you can enable it: using a supported ORM or manually by following the Sqlcommenter spec.

Application-centric database monitoring

One of the most powerful features of Insights is its ability to provide application-centric database monitoring and simplify application troubleshooting. To get this added functionality, you need to enable and set up query tagging. Query tagging augments your SQL statements with information about your application; you could tag queries by business logic, microservice, route, controller, etc. For example, using payment, inventory, business analytics, or shipping tags. You can then find the queries and database load that the various services create. This may help you find unexpected events, such as spikes for a business analytics tag at certain times of day, or you might see abnormal growth for a payment service trending over the previous week. Sounds great, but how does it work?

Overview of Sqlcommenter

Insights uses an open source library called Sqlcommenter, which allows application information related to your MVC framework to be sent to the database along with queries as a SQL comment. Sqlcommenter has recently merged with OpenTelemetry to extend the vision of OpenTelemetry to databases and help grow the observability ecosystem. In addition to application information, Sqlcommenter allows OpenTelemetry trace context information to be propagated to the database, making it possible to find correlations between application traces and database query plans. Developers can view this observability information directly in database logs, the information can be integrated into other tools like Cloud SQL Insights or APM tools such as those from Datadog, Dynatrace, and Splunk.

Sqlcommenter supports several ORMs and frameworks including Django, SQLAlchemy Ruby on Rails, Knex.js, Sequelize.js, Spring, Hibernate, and more. If you are using one of these supported ORMs, the application tags are automatically created for you, with little to no change to your application code. If you are not using an ORM, you can manually add Sqlcommenter tags to your SQL queries. Let's look at how to enable Sqlcommenter for query tagging in one of the supported languages and frameworks and how to manually tag queries if you aren't using an ORM.

Using Python and SQLAlchemy with Sqlcommenter

Each supported ORM has documentation and examples to show you how to install and enable it. Here's the process you would follow to set up Sqlcommenter in a Python application that uses SQLAlchemy.

Run the following to install Sqlcommenter with the option to record OpenTelemetry trace context:

pip3 install google-cloud-sqlcommenter[opentelemetry]

Then, in your application code, attach the event listener shown below to the before_cursor_execute event of the database engine. This will ensure that all queries that are executed with that engine will have the SQL comments included. Once you've installed Sqlcommenter and added the code below, no further code changes are needed to your application.

import sqlalchemy
from google.cloud.sqlcommenter.sqlalchemy.executor import BeforeExecuteFactory

engine = sqlalchemy.create_engine(...)
listener = BeforeExecuteFactory(
    with_db_driver=True,
    with_db_framework=True,
    with_opentelemetry=True,
)
sqlalchemy.event.listen(engine, 'before_cursor_execute', listener, retval=True)
engine.execute(...) # comment will be added before execution

The output in the database logs would look something like this:

2021-08-28 02:33:25.287 PDT [57302] LOG:  statement: SELECT candidate, time_cast FROM
Votes ORDER BY time_cast DESC /*db_driver='pg8000',framework='sqlalchemy%3A1.4.22',controller='index',route='/',
traceparent='00-5bd66ef5095369c7b0d1f8f4bd33716a-c532cb4098ac3dd2-01',
tracestate='congo%%3Dt61rcWkgMzE%%2Crojo%%3D00f067aa0ba902b7'*/

For other ORMs and frameworks, you can find links to the instructions for setting up Sqlcommenter here.

Manually tagging queries using the Sqlcommenter spec

If you're not using one of the supported ORMs, you can add comments to your SQL statements manually by following the Sqlcommenter specification. The Sqlcommenter algorithm adds a comment containing serialized key value pairs to a SQL statement. An overview of the algorithm is below; to understand the different pieces of the algorithm in more depth, see the algorithm documentation.

sql_commenter(sql, attributes):
    if contains_sql_comment(sql):
        return sql # DO NOT mutate a statement with an already present comment.

    serialized_key_value_pairs := []

    for each attribute in attributes:
        serialized := serialize_key_value_pair(attribute)
        if serialized:
            serialized_key_value_pairs.append(serialized)

    sorted := sort(serialized_key_value_pairs)
    concatenated := concatenate(sorted)
    final := affix_comment(sql, concatenated)

    return final

The following keys are supported for use in Cloud SQL Insights: action, controller, framework, route, application, db_driver, traceparent, and tracestate. However, if you're not using Cloud SQL Insights, you can include any other custom keys as well.

The following information passed to the Sqlcommenter algorithm:

sql_commenter('SELECT * FROM POSTS, [
traceparent='00-ff19308b1f17fedc5864e929bed1f44e-6ddace73a9debf63-01',
     route='posts',
     action='index',
     controller='posts',
    application='SqlcommenterBlogDemo'
])

Results in this output:

SELECT * FROM POSTS/*action='index',application='SqlcommenterBlogDemo',
controller='posts',route='/posts',traceparent='00-ff19308b1f17fedc5864e929bed1f44e-6ddace73a9debf63-01'*/

Next steps

If you're running PostgreSQL for Cloud SQL databases, and you haven't checked out Insights yet, now is the time! If you're using one of the ORMs or frameworks that Sqlcommenter supports, you can find instructions in the documentation for how to enable it for each language and framework, along with some sample applications in the GitHub repo.

google / sqlcommenter

sqlcommenter

**sqlcommenter is donated to OpenTelemetry. We will continue to maintain this repository until opentelemetry SQLCommenter feature stabilizes. Further updates will be added here in due time. See the migration plans in this comment. **

Documentation

Contains all the various sqlcommenter-* implementations.

View on GitHub

If you have any feedback or questions, you can raise them in the issue tracker or reach out on Twitter.

Learning to Read Music with the Web MIDI API

Jan Kleinert — Thu, 23 Jan 2020 01:38:31 +0000

Learning to Read Music with the Web MIDI API

My two daughters both take piano lessons. Their music school has some nice online learning web apps and tools that help them with music theory and sight reading. There's one web app that they use (pictured below) that displays a note on the staff, and below the staff there is a picture of a keyboard. They have to click the key on the keyboard that corresponds with the note on the staff. It's been a good tool for them to practice recognizing notes quickly.

I've noticed that my youngest daughter will sometimes get up from the computer, walk over to our digital piano, play the note there, and then come back and click the key on the screen. That got me thinking - maybe these skills would transfer better if she could play the note directly on our digital piano to match what she sees on the screen. So I set out to build a simple web app that would do just that - display a note on the staff, and while connected to a digital piano or keyboard, use the Web MIDI API to let her know if she played the right note that corresponds with what's on the staff. To build this web app, I had to learn about MIDI and the Web MIDI API. In this article, I'll share what I learned and how the web app was created.

What is MIDI?

Before I can talk about the Web MIDI API, it's important to have a basic understanding of what MIDI is. If you were on the internet in the mid 1990s, then the word MIDI may be closely associated in your mind with the sounds emanating on auto-play from Geocities websites. Something like this perhaps. MIDI isn't sound or audio though.

MIDI stands for Musical Instrument Digital Interface, and it's a technical standard that has been around since the 1980s. It's used for communication between digital musical instruments, computers, audio devices, etc. For the purposes of this article and the demo app, the most important aspects of MIDI you need to understand are MIDI messages.

MIDI Messages

There are a few types of MIDI messages, but I only ended up using Channel Voice Messages. There are different events -- or pieces of information -- that can be represented by channel voice messages, such as Note On, Note Off, and Polyphonic Key Pressure. When MIDI messages are sent, they're transmitted on MIDI channels. Up to 16 channels are supported. For the purposes of this demo app, we are only using channel 1. A Note On message gets sent when a key is pressed on the digital piano. This Note On message consists of three pieces of numeric information: the type of event (144 represents a Note On event on channel 1) the note number (from 0 - 127, where middle C is 60), and the velocity (how hard the key was pressed).

Note On Message Example

Event Type	Note Number	Velocity (how hard the key was pressed)
144 (Note On)	0-127	1-127

With that understanding of what information is sent when a key is pressed and what it means, the next step is to learn about the Web MIDI API, so that we can make sense of that information in the browser.

What's the Web MIDI API?

The Web MIDI API allows us to interact with MIDI-enabled devices via the browser. This could mean using a MIDI-enabled device as an input to a web application, or it could mean sending MIDI messages from a web application to a MIDI-enabled device. The API is pretty straightforward and easy to get started with.

Compatibility

Before moving on, it's important to note that the Web MIDI API currently doesn't have wide browser support. It's currently only supported in Chrome, Opera, and the Android Browser.

If you want to use the features of the Web MIDI API for more than just experimentation, and you need broad browser support, check out JZZ, a MIDI library for Node.js and web browsers. I haven't used it myself, but it was often referenced as a good alternative when I was reading about the Web MIDI API.

Building the Demo App

I'll walk you through some of the key parts of the code, but you can find the full source code for the demo app here: https://github.com/jankleinert/get-your-notes-on. A live version of the app is hosted here - if you've got a MIDI device, hook it up to your computer and try it out! Here's a screen capture of the web app in action:

Checking for Browser Support of the Web MIDI API

The first step is to check if the browser supports the Web MIDI API. As shown in the code snippet below, we'll check navigator.requestMIDIAccess, and if that is true, then we'll call navigator.requestMIDIAccess().

if (navigator.requestMIDIAccess) {
  console.log('WebMIDI is supported in this browser.');
  navigator.requestMIDIAccess().then(onMIDISuccess, onMIDIFailure);
} else {
  console.log('WebMIDI is not supported in this browser.');
}

onMIDISuccess Callback

onMIDISuccess() is the function that is called if we successfully are able to get MIDI access. In that function, we do a few things:

Shuffle the array of notes (level1Notes), so that the user doesn't see the notes in the same order each time they use the app.
Draw the first note in the array on the staff.
Get any available MIDI inputs (there will typically only be one). When an onmidimessage event fires, we'll call getMIDIMessage.

function onMIDISuccess(midiAccess) {
  shuffleArray(level1Notes);
  drawNote(level1Notes[noteIndex]);

  var inputs = midiAccess.inputs;
  var outputs = midiAccess.outputs;

  for (var input of midiAccess.inputs.values()) {
    input.onmidimessage = getMIDIMessage;
  }
}

// 60 represents middle C                           
var level1Notes = [60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72];

Listen for noteOn

In the getMIDIMessage() function, we look at the three pieces of information that are passed as part of the channel voice message. As we learned earlier, a noteOn message on MIDI channel 1 has a value of 144. So that is what we're looking for in the switch statement. The other two pieces of information represent the note number and velocity. We update some text on the web app to show these three pieces of information, and then we call noteOnListener and pass in the note number.

function getMIDIMessage(message) {
  var command = message.data[0];
  var note = message.data[1];
  var velocity = message.data[2];

  switch (command) {
    case 144: // noteOn
      document.querySelector('.note-info').textContent = 'Command: ' + command +
        ' , Note: ' + note + ' , Velocity: ' + velocity;
      noteOnListener(note);
      break;
    }
}

In a more complex application, you could do a lot more in this function. You could listen for noteOff messages as well, for example.

noteOnListener

In the noteOnListener() function, we do the following:

Check if the note that was played was the correct note. Set the color of the note to green or red depending on the outcome.
After 1.5 seconds, reset the staff and display the next note in the array.
Display the score once all the notes have been played.

That's pretty much all there is to it! If you want to look at the code, you'll find it here:

jankleinert / get-your-notes-on

Simple Web MIDI API demo. Connect a digital keyboard and play the notes on the screen

PRs welcome, especially if you'd like to add support for bass clef notes! :)

Want to Learn More?

Here's a list of resources and links where you can learn more about the Web MIDI API.

Slides from my talk on this topic at Node+JS Interactive 2019
Web MIDI API info from midi.org
An excellent article on Getting Started with the Web MIDI API. I learned a lot from this one!

Node.js Session Management Using Express Sessions, Redis, and Passport - Part 2

Jan Kleinert — Fri, 19 Jul 2019 15:37:14 +0000

In Part 1 of this tutorial, we went step-by-step through the process of building a web app with Node.js and Express that uses express-session and connect-redis as a way of helping users understand how session management works.

In this second part, we will expand on the previous app by implementing authentication using Passport and exploring how authentication and sessions work together.

Pre-requisites

If you followed the steps in Part 1, then you can move on to the next section. If not, here's what you need to do.

Clone this GitHub repo that has the code for the demo app. The master branch contains the code as it is at the end of Part 1. You'll also need to install Redis and start the Redis server if you don't already have it installed. If you need to install Redis, you can take a look at this documentation.

$ git clone https://github.com/jankleinert/redis-session-demo
$ cd redis-session-demo

Let's try running the app to make sure it works.

$ npm install
$ export SESSION_SECRET=some_secret_value_here
$ npm run dev

Open http://localhost:3000 in your browser, and you should see something like this.

Set up a MySQL user database

Regardless of whether or not you completed Part 1, you'll need to ensure you have MySQL installed. Instructions are here if you need to install and set up MySQL. Next, launch mysql and create a new database and a new table.

mysql> CREATE DATABASE redis_session_demo; 
mysql> USE redis_session_demo;
mysql> CREATE TABLE users (id varchar(20), email varchar(20), password varchar(60));

This will be our user database. To speed things up, rather than having an account creation page, we'll manually insert a test user into the database. The app will be using bcrypt to create a hash for our passwords. Our test user will have id = a1b2c3d4, email = test@example.com, and password = password. You can use this site to create a hashed password. Next, we'll insert that into our user database.

mysql> INSERT INTO users (id, email, password) VALUES ('a1b2c3e4', 'test@example.com', '$2y$12$7Mj1fG3bdlpmRcXtZpwimOI4pItCQcj5x2.ZqydPbR5wWlKGVaQVe');

Quick recap of the demo app

The demo app was built using express-generator to create the app skeleton. It's using Pug for the view engine. When you click the Pour Another button, it makes a request to an API that will return a machine-learning-generated craft beer name.

In Part 1, we added a session information panel that displays the session ID, how many more seconds are left before the session expires, and also our session data: the number of beer names that have been viewed in the current session. To implement session management, we used express-session for the session middleware and connect-redis as the session store. In the next step, we will add links to log in and log out, create a login page, and refactor the session panel that was originally included directly in /views/index.pug.

Add authentication support to the frontend

We are going to start by refactoring the session info panel. By moving it to a separate file, it will be easier to include it in multiple pages. Create a new file /views/session.pug and paste in this code. There is a section at the bottom now that displays whether or not the user is authenticated.

    .session
      p Session Info
      if sessionID
        p= 'Session ID: ' + sessionID 
      if sessionExpireTime
        p= 'Session expires in ' + Math.round(sessionExpireTime/1000) + ' seconds'
      if beersViewed
        p= 'Beers viewed in this session: ' + beersViewed
      else 
        p= 'No beers viewed yet in this session.'
      if isAuthenticated
        p= 'Logged in as: ' + email
      else  
        p= 'Not logged in'

Now, open up /views/index.pug and replace the .session section with the following line. It should be lined up in the same column as the h1.

    include session.pug

It's time to create the login page. Create /views/login.pug and paste in this code. It's a simple form with fields for email and password.

    extends layout

    block content
      h1= 'Log In'
      .lead
        form#login-form(action='/login', method='post')
          .form-group
            input(name='email', type='text', placeholder='Email', required='')
          .form-group
            input(name='password', type='password', placeholder='Password', required='')
          button.btn.btn-primary(type='submit')= 'Log In'
        if error
          p= error

      include session.pug

Now, we need to add Home, Log In, and Log Out links to the navigation. Open layout.pug and add this code directly below h3.masthead-brand Craft Beer Name Demo.

    nav.nav.nav-masthead.justify-content-center
      a.nav-link(href='/') Home
      a.nav-link(href='/login') Log In
      a.nav-link(href='/logout') Log Out

Update app.js

To support adding authentication to the app, we need to install some additional packages.

npm install --save bcryptjs mysql passport passport-local

bcryptjs is used for for hashing and checking passwords. passport is the authentication middleware we are using, and passport-local is the authentication strategy, meaning we are authenticating with a username and password.

Next, open up app.js and add the following code below the existing requires.

const loginRouter = require('./routes/login');
const logoutRouter = require('./routes/logout');
const mysql = require('mysql');
const passport = require('passport');
const LocalStrategy = require('passport-local').Strategy;
const bcrypt = require('bcryptjs');

const mysqlConnection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '', // demo purposes only
  database: 'redis_session_demo'
});

mysqlConnection.connect(function(err) {
  if (err) {
    console.log('error connecting to mysql: ' + err.stack);
    return;
  }
});

Note that we're using a MySQL database with root and '' as the login and password. This is only for demo purposes; don't do that in production! You probably also noticed loginRouter and logoutRouter reference files that don't exist. We'll create those in the next section.

Scroll down a bit until you see const redisClient = redis.createClient();. Directly after that line, add the following code.

// configure passport.js to use the local strategy
passport.use(new LocalStrategy(
  { usernameField: 'email' },
  (email, password, done) => {
    mysqlConnection.query('SELECT * FROM users WHERE email = ?', [email], function (error, results, fields) {
      if (error) throw error;
      var user = results[0];
      if (!user) {
        return done(null, false, { message: 'Invalid credentials.\n' });
      }
      if (!bcrypt.compareSync(password, user.password)) {
        return done(null, false, { message: 'Invalid credentials.\n' });
      }
      return done(null, user);
    });
  }
));

passport.serializeUser((user, done) => {
  done(null, user.id);
});

passport.deserializeUser((id, done) => {
  mysqlConnection.query('SELECT * FROM users WHERE id = ?', [id], function (error, results, fields) {
    if (error) {
      done(error, false);
    }
    done(null, results[0]);  
  });
});

I found this article very helpful in understanding what happens during the authentication process. You'll notice that I modeled some parts of this code after what was done in that article.

Scroll down a bit more in the file until you find app.use('/', indexRouter);. Replace that line with the following code.

app.use(passport.initialize());
app.use(passport.session());

app.use('/', indexRouter);
app.use('/login', loginRouter);
app.use('/logout', logoutRouter);

This code is setting up our app to use passport as middleware, and then we're adding the two new routes for logging in and logging out.

Update routes

The last step we need to take is to update /routes/index.js and create two new files: /routes/login.js and /routes/logout.js. Open /routes/index.js. In each of the four res.render() calls, add this to the end of the list of properties in the locals object.

, isAuthenticated: req.isAuthenticated(), email: (req.isAuthenticated() ? req.user.email : null)

So, for example, the res.render() call in router.get() would become:

res.render('index', { sessionID: req.sessionID, sessionExpireTime: expireTime, beersViewed: req.session.views, beerName: null, beerStyle: null, error: null, isAuthenticated: req.isAuthenticated(), email: (req.isAuthenticated() ? req.user.email : null) });

Next create /routes/login.js and paste in this code.

const express = require('express');
const router = express.Router();
const passport = require('passport');

/* GET request for login page */
router.get('/', function(req, res, next) {
  var expireTime = new Date(req.session.cookie.expires) - new Date();
  res.render('login', { sessionID: req.sessionID, sessionExpireTime: expireTime, beersViewed: req.session.views, error: null, isAuthenticated: req.isAuthenticated(), email: (req.isAuthenticated() ? req.user.email : null) });
});


router.post('/', function (req, res, next) {
  var expireTime = new Date(req.session.cookie.expires) - new Date();
  passport.authenticate('local', (err, user, info) => {
    if(info) {return res.send(info.message)}
    if (err) { return next(err); }
    if (!user) { return res.redirect('/login'); }
    req.login(user, (err) => {
      if (err) { return next(err); }
      res.render('login', {sessionID: req.sessionID, sessionExpireTime: expireTime, beersViewed: req.session.views, username: req.user.id, error: null, isAuthenticated: req.isAuthenticated(), email: (req.isAuthenticated() ? req.user.email : null)});
    })
  })(req, res, next);
})  

router.get('')

module.exports = router;

router.post() is where our login form submissions are handled using passport, using the local strategy.

Finally create /routes/logout.js and paste in this code.

const express = require('express');
const router = express.Router();

/* GET request for logout page */
router.get('/', function(req, res, next) {
  var expireTime = new Date(req.session.cookie.expires) - new Date();   
  req.logout();
  req.session.destroy(function() {
    res.redirect('/');
  });
});

router.get('')

module.exports = router;

There is no page that is displayed specifically for logging out. Instead, when the GET request is made to /logout, the app will log the user out, destroy the session, and then redirect them to the home page. At this point there will not be an authenticated user, and a new session will be created.

Try it!

Let's try it out! Open http://localhost:3000 in your browser. When it first loads, you should see the info panel displays a session ID and a time until the session expires, as well as "Not logged in".

Click the "Log In" link in the header and authenticate using our test credentials: test@example.com / password. When the page reloads, you should see that you are now logged in as test@example.com

As you take other actions on the site, you'll see that you stay logged in, but if you click "Log Out" in the navigation, you will no longer be logged in and a new session will be started.

That's it! You now have a simple app that handles session management as well as authentication. Is it complete? Definitely not! There are lots of improvements and additions that could be made, including an account creation page, better error handling, etc. You can find the complete code for Part 2 on GitHub.

Node.js Session Management Using Express Sessions, Redis, and Passport - Part 1

Jan Kleinert — Fri, 19 Jul 2019 15:22:05 +0000

Recently, I set out to create a demo application for my talk at Redis Day NYC that illustrates how session management works in a Node.js/Express web app, using Redis as the session store and then adds authentication on top of all that. Understanding the concepts and how they work together is one thing, but I hadn't actually built an app that used all these components together before.

As part of my initial research, I looked for existing tutorials or examples that did what I was trying to do. I found several good blog posts and tutorials, but none did exactly what I was looking for. Part 1 of this tutorial will take you step-by-step through the process of building a web app with Node.js and Express that uses express-session and connect-redis as a way of helping users understand how session management works. Part 2 will expand on this by implementing authentication using Passport and exploring how authentication and sessions work together.

Get the code for the craft beer name demo app

We will be starting with a simple demo app, and once we have that up and running, we'll add in session management and then authentication. Let's start by cloning the GitHub repo that has the code for the demo app and then switch to the beer-demo branch.

$ git clone https://github.com/jankleinert/redis-session-demo
$ cd redis-session-demo
$ git checkout beer-demo

Let's try running the app to make sure it works.

$ npm install
$ npm run dev

Open http://localhost:3000 in your browser, and you should see something like this.

Understanding the demo app

The three main files we'll be working with are app.js, /routes/index.js, and /views/index.pug.

Why do we care about session management anyway?

"Session" is such an overloaded term, and can mean very different things depending on context. In this tutorial, we're talking about a user’s session in a web application. You can think of it as the set of requests and responses within a web app, initiated by a single user, from the start of their interaction until they end the session or it expires.

Why do we care about or need a construct like a session? HTTP is stateless, so each request and response pair is independent of the others. By default, no state is maintained and the server doesn’t know who you are from one request to another. Session management gives us the ability to assign an identifier to a user session, and use that ID to store state or data relevant to the session. This could be something like whether or not a user is authenticated, the items in a shopping cart, and so on - whatever state needs to be kept during that session.

There are multiple ways of handling session management, but we’re going to look at one specific way, where session data is kept in a session store, and we’ll be using Redis as the session store.

On the client side, a cookie is stored with the session ID but none of the session data. In your application’s session store (Redis in this case), the session ID is also stored, along with the session data.

Add a session info panel to the app

To make it easy to visualize what's happening with a session, we will add a session info panel to the app. Open /views/index.pug and add the following code to the bottom of the file. Be careful with your indentation; .session should line up in the same column as h1.

    .session
      p Session Info
      if sessionID
        p= 'Session ID: ' + sessionID 
      if sessionExpireTime
        p= 'Session expires in ' + Math.round(sessionExpireTime) + ' seconds'
      if beersViewed
        p= 'Beers viewed in this session: ' + beersViewed

This panel will display the session ID, how many more seconds are left before the session expires, and also our session data: the number of beer names that have been viewed in this session. We'll be specifying those values in /routes/index.js in a later step.

Add express-session and connect-redis to app.js

express-session is session middleware for Express. It's pretty straightforward to set up and use. There are quite a few compatible session stores that you can use for storing session data. We will be using connect-redis. Let's start by installing the npm modules that we need.

$ npm install --save express-session uuid redis connect-redis

Next, open up app.js and add the following code below the existing requires. uuid will be used to generate a unique ID to use for our session ID.

const uuid = require('uuid/v4')
const session = require('express-session');
const redis = require('redis');
const redisStore = require('connect-redis')(session);   

const redisClient = redis.createClient();

redisClient.on('error', (err) => {
  console.log('Redis error: ', err);
});

Before we move on, make sure you have Redis installed and that the Redis server is running. If you need to install Redis, you can take a look at this documentation. Now we can set up the session middleware and tell it to use our Redis store as the session store. Add this code above the line app.use('/', indexRouter);.

app.use(session({
  genid: (req) => {
    return uuid()
  },
  store: new redisStore({ host: 'localhost', port: 6379, client: redisClient }),
  name: '_redisDemo', 
  secret: process.env.SESSION_SECRET,
  resave: false,
  cookie: { secure: false, maxAge: 60000 }, // Set to secure:false and expire in 1 minute for demo purposes
  saveUninitialized: true
}));

There are a couple things to note about this code. The cookie that stores the session ID will be named "_redisDemo". We are using an environment variable to set the secret. In the next step, we'll export that env variable (you can set it to whatever you like). We are setting the session expiration to 1 minute to make it easier to understand what's happening in the demo app. In a real application, you'd set the maxAge to something more reasonable for your application. In your terminal, stop nodemon and then run the following.

$ export SESSION_SECRET=some_secret_value_here && npm run dev

Add session management code to /routes/index.js

The last step will be to add logic to keep track of the number of beer names viewed per session and to pass the session-related information through to the session panel. Open /routes/index.js and replace the existing get and post with the code below.

router.get('/', function(req, res, next) {
  var expireTime = req.session.cookie.maxAge / 1000; 
  res.render('index', { sessionID: req.sessionID, sessionExpireTime: expireTime, beersViewed: req.session.views, beerName: null, beerStyle: null, error: null });
});

router.post('/', function (req, res) {
  request('https://www.craftbeernamegenerator.com/api/api.php?type=trained', function (err, response, body) {
    if (req.session.views) {
      req.session.views++
    } else {
      req.session.views = 1
    }
    var expireTime = req.session.cookie.maxAge / 1000;   

    if(err){
      res.render('index', { sessionID: req.sessionID, sessionExpireTime: expireTime, beersViewed: req.session.views, beerName: null, beerStyle: null, error: 'Error, please try again'});
    } else {
      var beerInfo = JSON.parse(body)

      if(beerInfo.status != 200){
        res.render('index', { sessionID: req.sessionID, sessionExpireTime: expireTime, beersViewed: req.session.views, beerName: null, beerStyle: null, error: 'Error, please try again'});
      } else {
        res.render('index', { sessionID: req.sessionID, sessionExpireTime: expireTime, beersViewed: req.session.views, beerName: beerInfo.data.name, beerStyle: beerInfo.data.style, error: null});
      }
    }
  });
});

What did we change? In router.get, we added expireTime so that we can calculate the amount of time until the session expires. Then in res.render, we are passing some additional values: the session ID from req.sessionID, the expire time we just calculated, and the number of beers viewed per session, which is stored as req.session.views. On the first page view of a session, there will not be a value for req.session.views, but our template knows how to handle that.

In router.post, after we make the API request for the beer name, we are either incrementing req.session.views or setting it to 1 if this is the first beer name viewed in the session. Then, similar to what we saw above, we're passing along the additional session-related information in res.render.

Session management in action!

With everything in place now, open http://localhost:3000 in your browser. When it first loads, you should see the info panel displays a session ID and a time until the session expires.

Click on the Pour Another button (within 60 seconds, so your session doesn't expire), and you should see that the session ID remains the same, and now you also see the number of beers viewed in the session set to 1. If you open dev tools in your browser and view cookies, you should see a cookie named _redisDemo, and part of its value will contain the session ID.

Finally, if you start redis-cli and then issue the following command, where YOUR_SESSION_ID is replaced with the session ID shown in your browser, you should see the session data that's being stored in Redis for that session, including the views.

$ redis-cli
$ get "sess:YOUR_SESSION_ID"

The output should look something like this:

Play around with the app some more to get a better understanding for how the sessions work. What happens if you close and then quickly re-open your browser? What happens if you wait more than 60 seconds and then refresh the page?

At this point, hopefully you have a better understanding of what session management is and how to implement it for a Node.js app using express-session and connect-redis. In Part 2, we'll build on what we've done in this tutorial by adding authentication to the app using Passport.

Just want the code from Part 1? Get it here:

jankleinert / redis-session-demo

Demo app that shows session management for a Node.js app using express-sessions and connect-redis

redis-session-demo overview

Demo app that shows session management for a Node.js app using express-sessions and connect-redis. Originally created for Redis Day NYC 2019: https://events.redislabs.com/sessions/life-user-session/

The app queries an API for ML-generated craft beer names and displays them on the page. There is a session management panel that displays session ID, time until the session expires, and the number of beer names viewed in that session.

Learn more about how it works in this tutorial:

how to run

Make sure you have have Redis server running locally:

redis-server

Then clone this repo, run npm install and then run it in dev mode:

git clone https://github.com/jankleinert/redis-session-demo
cd redis-session-demo
npm install
export SESSION_SECRET=<some value you choose>
npm run dev

Then in your browser, go to http://localhost:3000. It should look something like this:

how it works

This demo uses express-session for session management and connect-redis as the session store.

branches

The master branch…

View on GitHub