Connection

Understanding Connections in RestBI

A Connection is the bridge between your application and the database where your data resides. It encapsulates all the necessary details to connect to your database, such as the host, port, user credentials, and the type of database you're working with. A well-defined connection ensures that RestBI can seamlessly interact with your data sources, enabling you to execute queries and retrieve data efficiently.

Connection Object Structure

The Connection object in RestBI defines all the necessary details to connect to a database. Below is an example connection object for a PostgreSQL database:

import { Connection, DatabaseType } from "./types";

// Define the PostgreSQL connection
const PostgresConnection: Connection = {
    id: '1',
    name: 'Postgres',
    host: 'localhost',
    port: 5432,
    user: 'postgres',
    password: 'test',
    database: 'AdventureWorks',
    type: DatabaseType.POSTGRES,
};

Connection Reference

Property	Description
id	A unique identifier for the connection.
name	The name of the connection, used for reference within the model.
host	The hostname or IP address of the database server.
port	The port number on which the database server is listening.
user	The username used to authenticate with the database.
password	The password used to authenticate with the database.
database	The name of the specific database to connect to.
type	The type of database (e.g., POSTGRES, MYSQL, SQL_SERVER, etc.), represented by the DatabaseType enum.

Where Can Connection Details Be Stored?

To ensure security and flexibility, connection details should be stored as code in configuration files, preferably outside your main application logic. This approach allows you to:

• Easily switch connections: For example, from a test database to a production database.

• Version control: Track changes to connection details using version control systems.

• Environment-specific configurations: Store different connections for different environments, ensuring your application always connects to the correct database.

Switching Connections

One of the key benefits of using Connection objects in RestBI is the ability to switch between connections easily. For instance, you might have a development database and a production database. By simply swapping the connection object, you can run your queries against different data sources without changing your application code.

Here’s how you can swap connections:

import { Model } from "./types";
import { PostgresConnection, MySQLConnection } from "./connections";

// Example Model using PostgreSQL Connection
export const AdventureWorksModel: Model = {
    id: '1',
    name: 'AdventureWorksModel',
    displayName: 'Adventure Works Model',
    connection: PostgresConnection, // Using PostgreSQL connection here
    // ... other properties
};

// If you want to switch to MySQL:
AdventureWorksModel.connection = MySQLConnection;  // Now using MySQL connection

Security Considerations

While connections are powerful, they must be handled securely:

• Environment Variables: Store sensitive information such as passwords in environment variables rather than hardcoding them in your application.

• Secrets Management: Consider using a secrets management service to securely store and access sensitive connection details.

Further Reading

• Tables: Learn more about how to define and use tables in your models.

• Formulas: Discover how to create complex calculations using formulas.

• Filters: Explore how to filter data effectively in your queries.

• Joins: Understand how to link tables together using joins.