External Database Access (ORM & Direct PostgreSQL Connections)

Boltic allows you to securely connect to your Boltic-managed PostgreSQL databases using a standard PostgreSQL connection string just like you would with any ORM, psql, or database client.

This enables seamless integration with:

• Prisma
• Sequelize
• TypeORM
• Django ORM
• SQLAlchemy
• BI tools
• Custom backend services
• Direct psql access

All while your database remains fully governed, permission-controlled, and monitored by the Boltic platform.

---

Overview

• Each Boltic account can create multiple PostgreSQL databases
• Every database supports secure external access via role-based PostgreSQL connection strings
• Connection strings are automatically generated based on the user's permission level

This ensures:

• 🔒 Secure external integrations
• 👥 Role-based access control
• 🛡️ Platform-level governance
• 🔐 Controlled encryption standards
• ✅ Safe UI compatibility

---

🔌 Connecting to Your Database

From the Boltic Console:

1. Navigate to Boltic Database
2. Select your database
3. Click View Connection String
4. Copy the generated PostgreSQL URI
5. Use it in your ORM, backend service, or SQL client

Example connection string format:

postgresql://username:password@host:port/database_name

---

🔐 Role-Based Connection Strings

Boltic enforces access control even when connecting externally.

The connection string you see depends on your permission level for that database.

Permission Mapping

User Access Level	Connection Role	Capabilities
Full database access (create/update/delete/view tables & records)	db_admin	Full database privileges
Full table access (create/update/delete/view records)	db_executive	Read/write access to tables
Read-only table access	db_viewer	Read-only access

This ensures external database access strictly follows Boltic's internal RBAC (Role-Based Access Control) model.

🛠 Using with ORMs

You can use the provided connection string directly in any PostgreSQL-compatible ORM.

---

Prisma

schema.prisma:

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

.env file:

DATABASE_URL="postgresql://username:password@host:port/database_name"

---

Sequelize

const { Sequelize } = require("sequelize");

const sequelize = new Sequelize("postgresql://username:password@host:port/database_name");

// Test the connection
sequelize
    .authenticate()
    .then(() => {
        console.log("Connection established successfully.");
    })
    .catch((err) => {
        console.error("Unable to connect to the database:", err);
    });

---

TypeORM

import { DataSource } from "typeorm";

const AppDataSource = new DataSource({
    type: "postgres",
    url: "postgresql://username:password@host:port/database_name",
    synchronize: false,
    logging: true,
    entities: ["src/entity/**/*.ts"],
    migrations: ["src/migration/**/*.ts"],
});

---

Django ORM

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'database_name',
        'USER': 'username',
        'PASSWORD': 'password',
        'HOST': 'host',
        'PORT': 'port',
    }
}

---

SQLAlchemy

from sqlalchemy import create_engine

engine = create_engine('postgresql://username:password@host:port/database_name')

# Test connection
with engine.connect() as connection:
    result = connection.execute("SELECT version()")
    print(result.fetchone())

---

psql (PostgreSQL CLI)

psql postgresql://username:password@host:port/database_name

Or with separate parameters:

psql -h host -p port -U username -d database_name

---

🏷️ External Tables & UI Behavior

Boltic continuously validates schema integrity to ensure platform features work correctly.

When is a table marked as External?

A table will automatically be marked as External if:

1. It is created outside Boltic via direct SQL connection
2. System-managed columns are deleted or modified:
   • id
   • created_at
   • updated_at

What happens when a table is marked External?

When a table is marked External, certain UI-based features are restricted to prevent inconsistencies.

Restricted features may include:

• ❌ Record deletion from UI
• ❌ Certain schema edits
• ❌ Platform-managed automations
• ❌ Some data mutation operations

The table remains fully functional via:

• ✅ Direct SQL
• ✅ ORM queries
• ✅ External integrations

info

This protects data integrity between platform-managed and externally-managed schemas.

System Columns Requirement

Boltic-managed tables automatically include:

• id - Primary key
• created_at - Record creation timestamp
• updated_at - Record modification timestamp

Important

Removing or modifying these columns via external access will cause the table to be flagged as External and may limit UI operations.

We strongly recommend keeping these columns intact to retain full platform capabilities.

---

🔐 Working with Encrypted Columns

When inserting or querying encrypted columns using an external connection, you must use Boltic's encryption functions.

Important

Encryption type is defined at the column level inside Boltic. Always use the correct encryption function based on the column's configuration.

Encryption Types

Boltic supports two types of encryption:

1️⃣ Deterministic (Searchable) Encryption

Function: boltic_encrypt_searchable()

Deterministic encryption produces the same encrypted output for the same input value.

Best For:

• Exact match queries
• Unique constraints
• Indexed encrypted fields
• Joins on encrypted columns

Insert Example:

INSERT INTO users (email)
VALUES (boltic_encrypt_searchable('[email protected]'));

Query Example:

SELECT *
FROM users
WHERE email = boltic_encrypt_searchable('[email protected]');

2️⃣ Non-Deterministic Encryption

Function: boltic_encrypt()

Non-deterministic encryption produces different encrypted outputs for the same input value each time. This provides stronger protection against pattern analysis.

Best For:

• Highly sensitive data
• Fields not requiring deterministic equality matching
• Enhanced confidentiality

Insert Example:

INSERT INTO users (notes)
VALUES (boltic_encrypt('Sensitive information'));

Decrypting Values

Function: boltic_decrypt()

To read encrypted values directly using SQL:

SELECT boltic_decrypt(email)
FROM users;

Example with multiple columns:

SELECT
  id,
  name,
  boltic_decrypt(email) as email,
  boltic_decrypt(notes) as notes
FROM users;

Important Notes

Key Points

• Always use the correct encryption function based on the column's configuration
• Inserting plain text into encrypted columns will result in no data insert

---

🎯 Why Use External Access?

Use Case	Benefit
ORM Integration	Use your preferred development tools and workflows (Prisma, Sequelize, TypeORM, Django, SQLAlchemy)
Advanced Operations	Execute complex SQL operations, migrations, and batch operations directly
BI & Analytics	Connect tools like PowerBI or custom analytics pipelines
Backend Services	Integrate directly with your application's backend without API overhead
Security & Governance	Maintain role-based security — fully governed by Boltic

---

✅ Best Practices

Practice	Description
✔️ Use role-appropriate connection strings	Connect with the correct role based on your needs (db_admin, db_executive, db_viewer)
✔️ Avoid modifying system columns	Keep id, created_at, updated_at intact to maintain full platform capabilities
✔️ Use Boltic encryption functions	Always use boltic_encrypt()/ boltic_encrypt_searchable() / boltic_decrypt() for encrypted fields
✔️ Test schema migrations	Test in non-production environments before applying to production databases
✔️ Maintain ORM alignment	Keep ORM migrations aligned with Boltic-managed schema for consistency

---

🔧 Troubleshooting

Table Marked as External

If a table is unexpectedly marked as external:

1. Verify that id, created_at, and updated_at columns exist
2. Check if the table was created outside the Boltic UI
3. Contact your database administrator if you need to restore full UI functionality

Encryption Function Not Found

If you receive errors about boltic_encrypt() functions not being available:

1. Verify you're connected to a Boltic-managed database
2. Check that your connection string is correct

---

For more information on database management, see Database Management.