Why Querying the Database Directly is the Best Way to Do Data Engineering on On-Prem LabVantage LIMS

Pipelines, client transfers, and operational reports on an on-premises LabVantage LIMS run into the same fork. You can write Java extensions and native reports inside the application, or you can query the database with a custom read-only Python wrapper.

LabVantage has APIs and configuration tools. Building external transfers inside the Java application is still the wrong place for that work. It ties the pipeline to the vendor, it needs a specialized LIMS consultant, and it hides the data model behind the application.

A read-only SQL connection over ODBC or JDBC, wrapped in a small Python module, avoids that. The same module works against Oracle or Microsoft SQL Server. The people who already write SQL and Python can run it.

Application-layer exports are the bottleneck

Three problems show up as soon as exports live inside LabVantage.

LabVantage is Java, and every laboratory configures it differently. A custom data transfer written as a Java routine needs someone who knows that configuration. A data engineer who knows SQL and Python does not.

Schemas diverge for the same reason. One instance sits on SQL Server. Another sits on Oracle, with a different set of custom tables. You have to read the real schema either way. SQL is the faster way to do that inspection.

Calling the Java API from Python or R, only so the application can run a query you already know, adds latency and another failure point. A database driver is the shorter path.

Provision the account before the pipeline

Direct access has to stay read-only.

• Create a SQL service account with SELECT on the LabVantage tables or views the pipeline needs, and nothing else.
• Limit that account to the ETL hosts, through the firewall or a VPN.
• Keep host, port, database name, and credentials in a local .env file, and keep that file out of git.

One module for every script

Connection setup should not be copied into each report. A helper module picks the environment, picks Oracle or SQL Server, and returns a DataFrame.

# db_helpers.py
import os
import pandas as pd
from sqlalchemy import create_engine
from dotenv import load_dotenv

load_dotenv()

def get_connection_string(system: str = "labvantage", env: str = "prod") -> str:
    """Build a SQLAlchemy URL for Oracle or SQL Server in the requested environment."""
    env_suffix = f"_{env.upper()}"
    prefix = system.upper()
    db_type = os.getenv(f"{prefix}_DB_TYPE{env_suffix}", "oracle")
    user = os.getenv(f"{prefix}_DB_USER{env_suffix}")
    password = os.getenv(f"{prefix}_DB_PASS{env_suffix}")
    host = os.getenv(f"{prefix}_DB_HOST{env_suffix}")
    port = os.getenv(f"{prefix}_DB_PORT{env_suffix}")
    service_name = os.getenv(f"{prefix}_DB_NAME{env_suffix}")

    if db_type == "oracle":
        return f"oracle+oracledb://{user}:{password}@{host}:{port}/?service_name={service_name}"
    if db_type == "mssql":
        return (
            f"mssql+pyodbc://{user}:{password}@{host}:{port}/{service_name}"
            "?driver=ODBC+Driver+17+for+SQL+Server"
        )
    raise ValueError(f"Unsupported DB type: {db_type}")

def query_labvantage(sql_query: str, params: dict | None = None, env: str = "prod") -> pd.DataFrame:
    """Run a query against LabVantage and return a DataFrame."""
    engine = create_engine(get_connection_string(system="labvantage", env=env))
    with engine.connect() as connection:
        return pd.read_sql_query(sql_query, connection, params=params)

A script imports query_labvantage, passes the environment, and gets a frame. Dev, staging, and production are the same function with a different env.

Convert timestamps in the pipeline

LabVantage stores timestamps in UTC, or in the database server’s clock. The interface shows those times in the technician’s local zone, from that user’s profile. A transfer or a dashboard built from the raw column will not match the screen until you convert it.

# pipeline_export.py
import pandas as pd
from db_helpers import query_labvantage

sql_query = """
    SELECT
        s.sampleid,
        s.samplename,
        s.status,
        s.sdate AS accessioned_utc
    FROM
        s_sample s
    WHERE
        s.sdate >= :start_date
"""

raw_df = query_labvantage(sql_query, params={"start_date": "2026-01-01"}, env="prod")

raw_df["accessioned_utc"] = pd.to_datetime(raw_df["accessioned_utc"], utc=True)
raw_df["accessioned_local"] = raw_df["accessioned_utc"].dt.tz_convert("America/New_York")

print(raw_df[["sampleid", "samplename", "accessioned_local"]])

Do that conversion in the transformation step, aimed at the reporting zone or the client’s specification. Do not assume the column already matches the UI.

What the wrapper unlocks

Once the helper exists, the downstream jobs look the same. A scheduled script reads completed results through the read-only account, then branches.

• Client transfers. Format the frame to the client’s specification and send it by SFTP or API.
• A shared store. Land LIMS rows next to ERP, CRM, or billing extracts, without a custom report inside the LIMS.
• Operations. Load throughput, turnaround time, and bottlenecks into Power BI, Tableau, Snowflake, or BigQuery.

Checklist

1. Ask for a dedicated SQL user with SELECT only.
2. Put connection settings in .env, and confirm .gitignore excludes it.
3. Keep Oracle versus SQL Server, and dev versus production, inside one helper module.
4. Convert UTC timestamps to the zone the report or the client expects.
5. Leave the Java application alone. The pipeline is SQL and Python.

Shannon Alliance builds these read-only wrappers and the transfers that sit on them for clinical and research laboratories. If an on-prem LabVantage instance is still the system of record, book a consultation.