Connecting Databases

VizBoard allows you to connect PostgreSQL databases to your projects for data visualization and analysis. Database credentials are encrypted at rest for security.

Overview

Each VizBoard project can have multiple database connections. When you add a connection:

Credentials are encrypted using AES-256 encryption
Connection validity is automatically tested
Database schema is introspected and cached
You can create widgets using tables from connected databases

Currently, VizBoard supports PostgreSQL databases only. Support for additional databases may be added in future releases.

Adding a Database Connection

You can add database connections when creating a new project or editing an existing one.

Navigate to Project Form

For New Projects:

Go to /projects and click “New Project”
Or click the “Create Project” button in the navigation

For Existing Projects:

Navigate to your project
Click “Edit Project” in the project settings

Add Database Connection

In the project form, locate the “Database Connections” section and click “Add Connection”.Fill in the connection details:

title

string

required

A friendly name for this connection (e.g., “Production DB”, “Analytics”)

host

string

required

The database server hostname or IP address (e.g., localhost, db.example.com)

port

number

required

PostgreSQL port number (default: 5432)

database

string

required

The database name to connect to

user

string

required

Database username with read permissions

password

string

required

Database password (encrypted before storage)

Save and Validate

When you save the project, VizBoard will:

Encrypt credentials using AES-256 (src/app/actions/project/crud.ts:57-64)
Validate connection by attempting to connect to the database
Introspect schema to discover tables and columns
Store schema in the database for quick access

If the connection is invalid, you’ll see a badge indicating the issue.

Connection Ready

Once validated, the connection is ready to use in widgets. A green badge will indicate the connection is valid.

Connection Form Example

Here’s an example of a valid PostgreSQL connection:

{
  "title": "Analytics Database",
  "host": "analytics.example.com",
  "port": 5432,
  "database": "analytics_prod",
  "user": "readonly_user",
  "password": "your-secure-password"
}

Use a read-only database user whenever possible. VizBoard only needs SELECT permissions to visualize data.

How Connections Are Stored

Database connections are stored securely in the dbconnections table.

Database Schema

model DbConnection {
  id                  String    @id @default(uuid())
  projectId           String
  title               String
  dbAccess            Json      // Encrypted credentials
  dbSchema            Json?     // Cached schema
  lastIntrospectionAt DateTime?
  createdAt           DateTime  @default(now())
  updatedAt           DateTime  @updatedAt
  isValid             Boolean?  // Connection validation status
  validationError     String?   // Error message if invalid
  project             Project   @relation(...)
}

Credential Encryption

Credentials are encrypted before storage (src/app/actions/project/crud.ts:57-65):

const encryptedAccess = encrypt(
  JSON.stringify({
    host: conn.host,
    port: conn.port,
    database: conn.database,
    user: conn.user,
    password: conn.password,
  })
)

await tx.dbConnection.create({
  data: {
    projectId: createdProject.id,
    title: conn.title,
    dbAccess: encryptedAccess,
  },
})

The encrypt() function uses AES-256 encryption with a secret key from environment variables.

Connection Validation

After creating or updating connections, VizBoard automatically validates them.

Validation Process

Connection Test

VizBoard attempts to establish a connection to the database using the provided credentials.

Update Status

The isValid field is updated:

true - Connection successful
false - Connection failed
null - Not yet validated

Store Error (if any)

If validation fails, the error message is stored in validationError for debugging.

Validation Code Reference

Connections are validated silently after creation (src/app/actions/project/crud.ts:80-89):

const validationResult = await validateConnectionsQuietly(
  project.id,
  input.userId
)

if (validationResult?.success) {
  const validConnections = validationResult.connectionStatuses.filter(
    (conn) => conn.isValid
  )
  // Proceed with schema introspection
}

Schema Introspection

For valid connections, VizBoard automatically introspects the database schema.

What is Introspected

Tables: All tables in the database
Columns: Column names, data types, and constraints
Primary Keys: Identified for each table
Foreign Keys: Relationships between tables

Introspection Flow

Trigger Introspection

After successful validation, VizBoard calls CreateDbSchema(connectionId) for each valid connection.

Fetch Schema

The function makes a request to /api/createschema which:

Connects to the database
Queries information_schema tables
Builds a complete schema representation

Store Schema

The schema is stored as JSON in the dbSchema field with a timestamp in lastIntrospectionAt.

Cache for Widgets

Widgets use this cached schema to display available tables and columns without querying the database.

Schema Introspection Code

From src/app/actions/project/database.ts:3-42:

export default async function CreateDbSchema(connectionId: string) {
  const response = await fetch(
    `${process.env.NEXTAUTH_URL}/api/createschema`,
    {
      method: "POST",
      headers: { "Content-Type": "application/json" },
      body: JSON.stringify({ connectionId }),
    }
  )
  
  if (!response.ok) {
    throw new Error(`API call failed with status ${response.status}`)
  }
  
  const result = await response.json()
  return result.tables
}

Auto-Introspection

Schema introspection happens automatically in these scenarios:

When creating a project with new connections
When updating a project and adding/modifying connections
When manually regenerating schemas via the UI

Introspection runs in parallel for multiple connections to improve performance (src/app/actions/project/crud.ts:102-134).

Managing Connections

Updating a Connection

Edit Project

Navigate to your project and click “Edit Project”.

Modify Connection

Update any connection fields as needed. For security, the password field is always blank in the edit form.To keep the existing password, leave the password field as KEEP_CURRENT_PASSWORD.

Save Changes

Click “Update Project”. VizBoard will:

Re-encrypt updated credentials
Re-validate the connection
Re-introspect the schema if valid

Password Management

When editing connections, passwords are handled specially (src/app/actions/project/crud.ts:258-281):

if (conn.password === "KEEP_CURRENT_PASSWORD") {
  // Fetch existing connection and decrypt password
  const existingConnection = existingConnectionsMap.get(conn.id)
  const existingDbAccess = decrypt(existingConnection.dbAccess)
  const existingConfig = JSON.parse(existingDbAccess)
  finalPassword = existingConfig.password
}

This ensures you don’t need to re-enter passwords when updating other connection details.

Deleting a Connection

To remove a connection from a project:

Edit Project

Go to the project edit page.

Remove Connection

Click the delete/remove button next to the connection you want to remove.

Save Changes

Save the project. The connection will be permanently deleted (src/app/actions/project/crud.ts:247-254).

Deleting a connection will break any widgets that depend on it. Ensure no widgets are using the connection before removing it.

Regenerating Schemas

If your database schema changes (new tables, columns, etc.), you can regenerate the cached schema.

Manual Regeneration

Navigate to Project

Go to your project dashboard.

Open Settings

Click the project settings or options menu.

Regenerate Schemas

Click “Regenerate Schemas” or similar option.

Wait for Completion

VizBoard will re-introspect all valid connections and update the cached schemas.

Programmatic Regeneration

The regenerateProjectSchemas function handles schema regeneration (src/app/actions/project/crud.ts:685-782):

export async function regenerateProjectSchemas(
  projectId: string,
  userId: string
) {
  const project = await prisma.project.findFirst({
    where: { id: projectId, userId },
    include: {
      dbconnections: {
        where: { isValid: true },
      },
    },
  })
  
  // Regenerate schemas for all valid connections
  for (const connection of project.dbconnections) {
    await CreateDbSchema(connection.id)
  }
}

Multiple Connections Per Project

VizBoard supports multiple database connections in a single project.

Use Cases

Multi-Database Analytics

Connect to multiple databases (e.g., production, staging) and create widgets from each

Cross-Database Dashboards

Build dashboards that pull data from different sources (e.g., sales DB, inventory DB)

Read Replicas

Connect to read replicas to reduce load on primary databases

Data Warehouse + App DB

Combine data from your application database and data warehouse

Each widget specifies which connection to use via the connectionId field in its configuration.

const configs = {
  connectionId: "abc-123",  // Specific connection
  tableName: "users",
  // ... other config
}

If no connectionId is specified, widgets use the first available connection in the project.

Troubleshooting

Connection Validation Fails

Symptoms: Red badge on connection, isValid: false Solutions:

Verify hostname, port, and database name are correct
Check that the database user has SELECT permissions
Ensure the database server allows connections from VizBoard’s IP
Check firewall rules and network security groups
Verify password is correct (try connecting with psql or another client)

Schema Introspection Fails

Symptoms: Connection valid but dbSchema is null Solutions:

Check that the user has permissions to query information_schema
Verify the database contains tables
Try regenerating schemas manually
Check server logs for detailed error messages

Connection Errors in Widgets

Symptoms: Widgets show “Unable to load data” Solutions:

Ensure the connection is valid (green badge)
Verify the table name exists in the database
Check that schema introspection completed successfully
Regenerate schemas if database structure changed

”No valid connections” Error

Symptoms: Cannot make project public Context: Projects require at least one valid connection to be made public. Solutions:

Fix any invalid connections in the project
Ensure at least one connection has isValid: true
Re-validate connections by editing and saving the project

Security Best Practices

Use Read-Only Users

Create database users with SELECT-only permissions for VizBoard connections

Limit Network Access

Use IP whitelisting or VPN to restrict database access

Rotate Credentials

Periodically update database passwords and update connections

Monitor Access

Enable database audit logs to track query activity

Environment Variables

Required for database connection encryption:

# Encryption key for database credentials
ENCRYPTION_KEY=your-32-character-secret-key

# Application URL for schema introspection API
NEXTAUTH_URL=http://localhost:3000

Never commit the ENCRYPTION_KEY to version control. Use environment variable management tools like Doppler, AWS Secrets Manager, or similar.

Get Started

Core Features

Widget Types

Guides

​Overview

​Adding a Database Connection

​Connection Form Example

​How Connections Are Stored

​Database Schema

​Credential Encryption

​Connection Validation

​Validation Process

​Validation Code Reference

​Schema Introspection

​What is Introspected

​Introspection Flow

​Schema Introspection Code

​Auto-Introspection

​Managing Connections

​Updating a Connection

​Password Management

​Deleting a Connection

​Regenerating Schemas

​Manual Regeneration

​Programmatic Regeneration

​Multiple Connections Per Project

​Use Cases

Multi-Database Analytics

Cross-Database Dashboards

Read Replicas

Data Warehouse + App DB

​Widget-Connection Association

​Troubleshooting

​Connection Validation Fails

​Schema Introspection Fails

​Connection Errors in Widgets

​”No valid connections” Error

​Security Best Practices

Use Read-Only Users

Limit Network Access

Rotate Credentials

Monitor Access

​Environment Variables

Build docs developers (and LLMs) love

Overview

Adding a Database Connection

Connection Form Example

How Connections Are Stored

Database Schema

Credential Encryption

Connection Validation

Validation Process

Validation Code Reference

Schema Introspection

What is Introspected

Introspection Flow

Schema Introspection Code

Auto-Introspection

Managing Connections

Updating a Connection

Password Management

Deleting a Connection

Regenerating Schemas

Manual Regeneration

Programmatic Regeneration

Multiple Connections Per Project

Use Cases

Widget-Connection Association

Troubleshooting

Connection Validation Fails

Schema Introspection Fails

Connection Errors in Widgets

”No valid connections” Error

Security Best Practices

Environment Variables