fastify-hana

SAP HANA integration plugin for Fastify

The fastify-hana plugin provides seamless integration between Fastify and SAP HANA, allowing you to easily connect and interact with your SAP HANA database within your Fastify application.

Under the hood, this plugin utilizes the official @sap/hana-client library to ensure reliable and high-performance connectivity to HANA databases. Read more about the hana client here!

Features

• Establishes a connection pool to efficiently manage database connections.
• Provides decorators to execute queries and transactions with ease.
• Supports named parameter binding for convenient and secure query execution.
• Handles connection pooling and connection lifecycle management automatically.
• Offers flexibility to directly access the underlying HANA Client API if needed.

Installation

npm i fastify-hana

Usage

Firstly, register the plugin:

import fastify from "fastify";
import hanaFastifyPlugin, { HanaOptions } from "fastify-hana";

const server = fastify();

const hanaOpts: HanaOptions = {
  host: "myHanaHost",
  port: "30015",
  user: "myUser",
  password: "myPassword",
  // Optional more info in the next section
  // poolOptions: {
  //   maxConnectedOrPooled: 0,
  //   maxPooledIdleTime: 0,
  //   pingCheck: false,
  //   poolCapacity: 0,
  // },
};

server.register(hanaFastifyPlugin, hanaOpts);

HanaOptions

When registering the plugin, you need to provide HanaOptions:

| Property | Type | Description | | ------------- | ---------------------------- | --------------------------------------------------------------------- | | host | string | The hostname or IP address of the SAP HANA database server. | | port | string | The port number used to connect to the SAP HANA database server. | | user | string | The username used for authentication to the SAP HANA database server. | | password | string | The password used for authentication to the SAP HANA database server. | | poolOptions | HanaClientExplicitPoolParams | Optional. The configuration options for the connection pool. |

HanaClientExplicitPoolParams

| Property | Type | Description | | ---------------------- | -------- | ---------------------------------------------------------------------------------------------------------------------------- | | maxConnectedOrPooled | number | Optional. The maximum number of open connections created by the pool. Default: 0 (no limit). | | maxPooledIdleTime | number | Optional. The maximum time, in seconds, that connections are allowed to remain in the pool. Default: 0 (no limit). | | pingCheck | boolean | Optional. Specifies whether or not the pooled connection should be tested for viability before being reused. Default: false. | | poolCapacity | number | Optional. The maximum number of connections allowed to be in the pool, waiting to be reused. Default: 0 (no limit). |

API

Fatify decorators

The plugin adds the following decorators to the Fastify instance:

• executeQuery
• executeInTransaction
• hanaPool
• hana

util functions

• namedParameterBindingSupport

executeQuery

Executes a query on the HANA database.

Example 1: Basic Query

server.get("/runQuery", async (request, reply) => {
  const result = await server.executeQuery("SELECT * FROM DUMMY");
  return result;
});

Example 2: Query with Index-Based Parameter Binding

server.get("/index-based-paramters-bining", async (request, reply) => {
  const result = await server.executeQuery(
    "SELECT * FROM MY_TABLE WHERE ID = ?",
    [1]
  );
  return result;
});

Example 3: Query with Named Parameters Binding

server.post("/named-parameters-binding", async (request, reply) => {
  try {
    const { id, name, age } = request.body;

    const query =
      "INSERT INTO myTable (id, name, age) VALUES (:id, :name, :age)";
    const parameters = { id, name, age };

    const result = await fastify.executeQuery(query, parameters);
    console.log(result); // Process the query result

    reply.send({ success: true, message: "Record inserted successfully" });
  } catch (error) {
    console.error(error); // Handle any errors
    reply
      .status(500)
      .send({ success: false, message: "Error inserting record" });
  }
});

executeInTransaction

Executes a set of actions in a transaction:

server.get("/runTransaction", async (request, reply) => {
  await server.executeInTransaction(async (conn) => {
    await conn.exec("INSERT INTO MY_TABLE (ID, NAME) VALUES (?, ?)", [
      1,
      "name1",
    ]);
    await conn.exec("INSERT INTO MY_TABLE (ID, NAME) VALUES (?, ?)", [
      2,
      "name2",
    ]);
  });

  return { status: "ok" };
});

NOTE: If the transaction function fail a roll back to the transaction begining will take place.

In this example, if either of the INSERT statements fails, both will be rolled back.

hanaPool

This decorator returns the pool instance, giving you full control and responsibility over the connections. Use with caution!

Using hanaPool to execute a batch operation:

server.post("/batchInsert", async (request, reply) => {
  const data = request.body; // Assume this is an array of arrays, where each sub-array contains field values
  const conn = await server.hanaPool.getConnection();
  try {
    const stmt = conn.prepare(
      "INSERT INTO MY_TABLE (FIELD1, FIELD2) VALUES(?, ?)"
    );
    for (const row of data) {
      stmt.add(row);
    }
    stmt.execBatch((err, results) => {
      if (err) throw err;
      // handle results
    });
  } finally {
    conn.disconnect();
  }
});

Using hanaPool to manage connections directly:

server.get("/directConnectionManagement", async (request, reply) => {
  const conn = await server.hanaPool.getConnection();
  try {
    await conn.setAutoCommit(false);
    await conn.exec("INSERT INTO MY_TABLE (ID, NAME) VALUES (?, ?)", [
      1,
      "name1",
    ]);
    await conn.commit();
  } catch (err) {
    await conn.rollback();
    throw err;
  } finally {
    conn.disconnect();
  }
});

hana

In some cases, you might need to establish a custom connection to the HANA database that uses different connection parameters than the default pool. For example, you might need to connect to a different database, or use different credentials. In this case, you could use hana decorator to create a new, custom connection.

import Fastify from "fastify";
import fastifyHana, { namedParameterBindingSupport } from "fastify-hana";

const fastify = Fastify();

fastify.register(fastifyHana, {
  host: "defaultHost", // Default values, these would ideally come from environment variables or config
  port: "defaultPort",
  user: "defaultUser",
  password: "defaultPassword",
});

fastify.post("/customConnection", async (request, reply) => {
  const { host, port, user, password, query, params } = request.body;

  const connectionParameters = {
    serverNode: `${host}:${port}`,
    uid: user,
    pwd: password,
  };

  const connection = fastify.hana.createConnection();

  try {
    await new Promise((resolve, reject) => {
      connection.connect(connectionParameters, (err) => {
        if (err) reject(err);
        resolve();
      });
    });

    const [formattedQuery, queryParameters] = namedParameterBindingSupport(
      query,
      params
    );

    const results = await new Promise((resolve, reject) => {
      connection.exec(formattedQuery, queryParameters, (error, results) => {
        if (error) reject(error);
        resolve(results);
      });
    });

    reply.send(results);
  } catch (error) {
    reply.code(500).send({ error: "Query execution failed", details: error });
  } finally {
    // Always disconnect after you're done.
    connection.disconnect();
  }
});

NOTE: Connections created via the hana decorator would not be managed by the plugin's connection pool. This means that connections created this way would not be reused, and the pooling mechanisms provided by the plugin would not be effective. Each connection created using the decorated hana would be separate and not part of the connection pool.

If your use case requires users to create their own connections manually and bypass the connection pooling provided by the plugin, use the hana decorator. However, it's important to carefully manage and handle the lifecycle of these connections to ensure efficient resource utilization.

namedParameterBindingSupport

Enables named parametes binding.

import fastify from "fastify";
import fastifyHana, { namedParameterBindingSupport } from "./fastify-hana"; // Replace with the path to your plugin file

// Create a Fastify server instance
const app = fastify();

// Register the HANA plugin
app.register(fastifyHana, {
  host: "your-host",
  port: "your-port",
  user: "your-username",
  password: "your-password",
  // Optional more info in the next section
  // poolOptions: {
  //   maxConnectedOrPooled: 0,
  //   maxPooledIdleTime: 0,
  //   pingCheck: false,
  //   poolCapacity: 0,
  // },
});

// Define a route that uses `executeInTransaction` and `namedParameterBindingSupport`
app.post("/transaction-route", async (request, reply) => {
  try {
    const { id, name, age } = request.body;

    const actions = async (conn) => {
      const query1 =
        "INSERT INTO myTable (id, name, age) VALUES (:id, :name, :age)";
      const parameters1 = { id, name, age };

      const [formattedQuery1, paramValues1] = namedParameterBindingSupport(
        query1,
        parameters1
      );

      await conn.exec(formattedQuery1, paramValues1);

      const query2 = "UPDATE myTable SET name = :newName WHERE id = :id";
      const parameters2 = { id, newName: "John Doe" };

      const [formattedQuery2, paramValues2] = namedParameterBindingSupport(
        query2,
        parameters2
      );

      await conn.exec(formattedQuery2, paramValues2);
    };

    await app.executeInTransaction(actions);

    reply.send({ success: true, message: "Transaction executed successfully" });
  } catch (error) {
    console.error(error); // Handle any errors
    reply
      .status(500)
      .send({ success: false, message: "Error executing transaction" });
  }
});

// Start the server
app.listen(3000, (err) => {
  if (err) {
    console.error(err);
    process.exit(1);
  }
  console.log("Server is running on port 3000");
});

Contributing

Pull requests are welcome. For major changes, please open an issue first to discuss what you would like to change.

License

MIT