Lakebase Express

A Databricks App that guides you through migrating a transactional database to Databricks Lakebase (managed serverless Postgres), through a connector catalog. It's a migration engine, not a code generator: you review and edit each artifact, and the app applies the schema/code and streams the data into Lakebase itself, with live progress.

Enabled sources today: Azure SQL and SQL Server. More (Oracle, PostgreSQL, MySQL, …) "Coming soon".

Disclaimer. This is not an official Databricks product or service. It is an independent, community/solution accelerator provided "as is", without any warranty or support commitment, and carries no SLA. Databricks is not liable for its use. You are solely responsible for reviewing every generated artifact, validating the migration against your own requirements, testing on non-production data first, securing credentials, and verifying the results before relying on them. Migrating databases is inherently risky — always keep verified backups of your source and target. See LICENSE.md for the full terms.

Try it in dev/QA first. Point the app at a non-production source and a throwaway Lakebase target until you've seen a full cycle — assessment, plan, apply, data load, validation — succeed on your own schema. Plan items execute real DDL against the target and the data load can truncate target tables, so a first run belongs somewhere you can drop and recreate.

Prerequisites

• Databricks CLI ≥ 0.239, authenticated to your workspace (databricks auth login) — used to deploy the app.
• Node.js and npm — to build the React frontend.
• Python 3.10+ (tested on 3.13) — for local development and running tests.
• A Databricks workspace with a Lakebase database instance (the migration target).
• A source database — Azure SQL or SQL Server — reachable from the app (a firewall rule for the app's egress IP, or a private link).
• A Databricks secret scope holding the Lakebase role password under lakebase-password — the app errors on every request without it. The app runs as its own service principal, which needs an ACL on that scope; deploy.sh grants it, provided you hold MANAGE there.

The scan and assessment run without a Databricks workspace; the Foundation Model features and the Job-offload/Async data paths require one, plus grants on system.ai. The source login needs three grants to be assessable.

Quick Start

Deploy it as a Databricks App.

# 1. Authenticate the CLI (pick any profile name)
databricks auth login --host <workspace-url> --profile <your-profile>

# 2. Set your deploy target — which workspace the bundle deploys to.
#    target.yml is gitignored (per-user), so create it from the sample:
cp target.yml.sample target.yml
#    Then edit target.yml:
#      * name the target after your CLI profile (<your-profile>), so deploy.sh
#        matches them automatically;
#      * set projects_pg_host / projects_pg_user to your Lakebase instance and
#        role (required — the app won't start without them).

# 3. Create the secret scope and store the Lakebase role password in it.
#    Required with the default `postgres` project store — the app reads this key
#    the first time it touches the store, and errors on every request without it.
databricks secrets create-scope lakebase-express --profile <your-profile>
databricks secrets put-secret lakebase-express lakebase-password --profile <your-profile>

# 4. Build the SPA, deploy the bundle, grant scope access, and start the app
DATABRICKS_PROFILE=<your-profile> ./deploy.sh

deploy.sh builds frontend/dist, runs databricks bundle deploy, grants the app's service principal WRITE on the secret scope, then databricks bundle run to launch the app. When it finishes it prints the app URL — open it and:

1. New migration → pick a source connector.
2. Connections & Target — enter the source (Azure SQL / SQL Server) and the Lakebase target, and test both.
3. Work through Assessment → Schema & Code → Data Migration → Create Sync to scan, plan, and run the migration.
4. After migrating, use the Post-migration modules — Validation and Query Parity — to confirm the target matches the source.

Deployment knobs and the permissions the app's service principal needs are in Deploy as a Databricks App. To iterate on the code without redeploying, see Local development.

What it does

Work is organized into migration projects — a saved unit (source/target config, object selection, assessment, plan, run history) you create, leave, and resume. Passwords are never stored; optional workspace-bound secret scope/key references are. Projects persist to a local dir (dev) or a UC volume (LBX_PROJECTS_BACKEND=volume) or Lakebase (=postgres).

Modules are independent and always enabled — no forced sequence. Connections are configured once and reused; other modules show a soft hint if something is missing.

Module	What it does
Connection & Assessment	Deterministic scan (schema, data types, T-SQL objects) + rule-based compatibility report, augmented by an AI migration analysis (complexity, effort, deeper risks)
Sizing & Cost	Map source capacity → Lakebase CUs + cost
Schema & Code	Build an editable plan (DDL + AI-translated code) and apply it to Lakebase
Data Migration	Stream data into Lakebase (batched COPY) with live per-table progress
Create Sync	Sync now (in-app) or offload a re-runnable PySpark snapshot to a Databricks Job (run now, create unstarted, or schedule)
Validation (post-migration)	Re-scan both sides and match every object — existence, structure, column collations, exact row counts, plus constraints, indexes, and foreign keys — then remediate with an autonomous AI repair agent, one-shot AI fixes, or manual SQL
Query Parity (post-migration)	Generate N synthetic read-only queries, run each against both sides, and compare row count, result format, and performance — with a side-by-side result preview on any mismatch

Target identifiers are lower-cased by default (PostgreSQL convention); a project can instead preserve source casing (double-quoted, case-sensitive). System objects (sys, INFORMATION_SCHEMA, is_ms_shipped, …) are never migrated.

Collations

Column collations are translated, not dropped. SQL Server's usual collations are case-insensitive (SQL_Latin1_General_CP1_CI_AS, the Azure SQL default) and PostgreSQL's default is case-sensitive, so ignoring them silently changes results — 'ana' = 'ANA', sort order, GROUP BY, unique indexes — without raising an error.

Each source collation becomes an ICU collation created before the tables using it, and every character column carries a COLLATE clause. Strength maps to an ICU level (CI_AS → level2, CI_AI → level1, CS_AS → level3), the locale to the closest ICU tag, and _BIN/_BIN2 to the built-in C.

Case- or accent-insensitive collations must be nondeterministic — otherwise equality stays byte-wise and only sorting changes. The assessment flags every affected column (COLLATION_INSENSITIVE) and any CHECK or filtered index that pattern-matches one (COLLATION_PATTERN_MATCH, which will fail to apply); Validation compares each target column's collation and reports drift with a fix.

Queries that LIKE those columns must be rewritten. PostgreSQL rejects pattern matching on a nondeterministic collation, so put a deterministic collation on the operand and use ILIKE to keep the case-insensitive result:

WHERE email LIKE '%gmail%'                  -- ERROR: not supported for LIKE
WHERE email COLLATE "C" ILIKE '%gmail%'     -- same rows as the source

Two traps: plain LIKE after COLLATE "C" is case-sensitive (fewer rows than the source), and lower(email) LIKE lower(?) is rejected too — lower()'s result keeps the column's collation. For an accent-insensitive source collation (..._CI_AI), ILIKE alone still under-matches; wrap both sides in unaccent() (available on Lakebase via CREATE EXTENSION unaccent).

How it works

• Backend (backend/) is pure Python with no web framework in the migration logic, so it's reusable from notebooks and unit-testable without Databricks. The source scan uses pymssql (no Spark, no system ODBC driver) and reads only small catalog result sets.
• Schema & code — the assessment becomes editable PlanItems applied to Lakebase in dependency order, each in its own transaction so a failure is isolated and reported.
• Data — tables stream via fetchmany + bulk COPY on a background thread the UI polls; large tables can be offloaded to a Databricks Job or a PySpark snapshot (range-partitioned reads → per-partition COPY, tables loaded concurrently, idempotent re-runs).
• Collations are mirrored as ICU collations created before the tables whose columns reference them, so string comparison keeps the source's case/accent semantics (see Collations).
• Constraints, indexes, FKs, and triggers are created after the data load, so the bulk copy pays no per-row maintenance and identity sequences sync to MAX+1 once rows exist.
• Post-migration — Validation re-inventories both sides and diffs them through the naming rules; Query Parity runs generated read-only queries on both sides and compares the results. Both run on background threads with polled progress. Constraints, indexes, and foreign keys are compared as one row per table per kind ("5 of 5 present") rather than one row per object — a real database has hundreds — and an expanded row names exactly what differs.

Architecture

app.yaml       Databricks Apps runtime config
backend/       FastAPI + all migration logic (framework-free, unit-testable)
frontend/      React + Vite + TypeScript SPA (built to frontend/dist/)
tests/         Pure-Python unit tests

In production the SPA is built and served by the same FastAPI process. Database passwords are typed per session or referenced by a workspace-bound Databricks secret scope/key (including Key Vault-backed scopes on Azure).

The app is bound to exactly one Databricks workspace, not selectable in the UI: locally the CLI profile it was started with, and when deployed the workspace the App is published in. Restart with a different profile to switch.

Local development

To iterate on the code without redeploying, run the two processes locally:

# Backend (FastAPI) — from the repo root
pip install -r requirements.txt
DATABRICKS_CONFIG_PROFILE=<your-profile> uvicorn backend.main:app --reload --port 8000

# Frontend (React + Vite) — separate shell; proxies /api -> :8000
cd frontend && npm install && npm run dev

Open the printed Vite URL (default http://localhost:5173).

DATABRICKS_CONFIG_PROFILE is a profile from ~/.databrickscfg and picks the workspace for the whole session. Settings shows which one is connected.

Foundation Model access (Unity Catalog grants)

The AI features need both grants on system.ai for your user (locally) or the app's service principal (deployed):

GRANT USE SCHEMA ON SCHEMA system.ai TO `<principal>`;
GRANT EXECUTE    ON SCHEMA system.ai TO `<principal>`;

Verify:

databricks serving-endpoints query databricks-claude-opus-4-8 \
  --json '{"messages":[{"role":"user","content":"ping"}],"max_tokens":16}' \
  --profile <your-profile>

Choosing the model and the API

Pick the model per session in Settings → Foundation Model endpoint, or change the default with LBX_FM_ENDPOINT:

LBX_FM_ENDPOINT=databricks-claude-sonnet-4-5 \
DATABRICKS_CONFIG_PROFILE=<your-profile> ./run_local.sh

LBX_FM_API selects which API carries the chat calls. Both reach the same model and differ only in how it is named:

LBX_FM_API	Request	Accepted names
serving	POST /serving-endpoints/{name}/invocations	endpoint name: databricks-claude-opus-4-8
gateway (default)	POST /ai-gateway/mlflow/v1/chat/completions	endpoint name or the gateway model id: system.ai.claude-opus-4-8

gateway is the default and the route the AI Gateway console's sample request uses, so the system.ai.* ids copied from there work as-is:

LBX_FM_API=gateway LBX_FM_ENDPOINT=system.ai.claude-opus-4-8 \
DATABRICKS_CONFIG_PROFILE=<your-profile> ./run_local.sh

In gateway mode Settings lists both forms for each pay-per-token endpoint. Note the system.ai.<model> id drops the endpoint's databricks- prefix, and that it is not a Unity Catalog path — the registered model is system.ai.databricks-claude-opus-4-8. Passing a system.ai.* id in serving mode fails with ENDPOINT_NOT_FOUND, since that route takes the name from the URL path.

Deployed, set these through the bundle: fm_endpoint and fm_api in target.yml (see target.yml.sample).

Run tests

pip install pytest httpx   # httpx backs FastAPI's TestClient
pytest tests/

Deploy as a Databricks App

Deployment is an Asset Bundle (databricks.yml) orchestrated by deploy.sh (see the Quick Start for the end-to-end steps). It builds the SPA, runs databricks bundle deploy, grants the app's service principal access to the secret scope, then databricks bundle run to start the app.

Deploy targets live in target.yml, a per-user file that is gitignored so no workspace-specific config is committed. Copy target.yml.sample to target.yml and set your target name; databricks.yml pulls it in via its include: list. No workspace host is committed — auth comes from the CLI profile named after the target (deploy.sh default), an explicit DATABRICKS_PROFILE, or a workspace.host you add in target.yml.

Knobs (see the header of deploy.sh): --skip-build reuses frontend/dist; BUNDLE_TARGET=<target> picks a target from target.yml; DATABRICKS_PROFILE=<profile> pins a CLI profile; APP_NAME=<name> overrides the app name; LBX_SECRET_ACL=READ and LBX_SKIP_ACL=1 control the secret-scope grant. To deploy to another workspace, add a target to target.yml and run BUNDLE_TARGET=<target> ./deploy.sh.

Permissions. deploy.sh grants the app's service principal access to the secret scope; doing so needs MANAGE on that scope. You must still give the app network access to the source DB endpoint (firewall rule for its egress IP, or private link). Async-mode runtime scopes need scope create/write. Passwords are never stored in clear text.

Adding a source connector

1. Frontend — set enabled: true and add connection-form hints in frontend/src/connectors.ts. Optionally drop a logo at frontend/public/logos/<id>.svg.
2. Backend — register the connector's source_type in backend/connectors/factory.py. For a non-T-SQL dialect, add a connector exposing database, query(sql) -> list[dict], and test_connection(); the scanner is connector-agnostic.

Limitations

• Data-type coercion is light: bit→boolean is handled; other edge types rely on psycopg adapters and surface as a per-table error rather than dropping rows.
• Check constraints, defaults, and filtered-index predicates are translated mechanically; anything unrecognized passes through verbatim and fails visibly at apply time for review.
• Run state is in-process memory — fine for a single-user App; use a table/Redis for multi-worker deployments.
• Lakebase auth in lakebase-express is native Postgres roles only — a role name and password over the Postgres wire protocol. Databricks identity auth (OAuth/OIDC for users, service principals, or groups) is not supported yet, so target access isn't governed by workspace RBAC and doesn't inherit SSO/MFA or short-lived credentials. TLS is always required and passwords are never stored in clear text, but a long-lived role password is a shared secret you must rotate yourself: use a dedicated least-privilege role, and prefer a private network path (Private Link / private endpoints) over a public endpoint with an IP allowlist. OAuth support is on the Roadmap.
• Job-offload and Async-mode paths require a live workspace and expect the schema plan to have created the target tables. Key Vault-backed runtime scopes work only when the keys already exist (Databricks can't write through to them).
• money/smallmoney arithmetic differs between the two engines even though the type mapping itself is lossless — see money and fixed-scale arithmetic.

money and fixed-scale arithmetic

money/smallmoney map to numeric(19,4)/numeric(10,4) — lossless: same scale, full range, no stored value changes. The arithmetic differs. T-SQL AVG() over a money column returns money, truncating the division to four decimals inside the aggregate; Postgres avg(numeric) rounds at the end instead. Same data, same query, one digit apart (for example, 254759.0624 vs 254759.0625).

According to the Microsoft documentation:

You can experience rounding errors through truncation, when storing monetary values as money and smallmoney. Avoid using this data type if your money or currency values are used in calculations. Instead, use the decimal data type with at least four decimal places. — money and smallmoney (Transact-SQL)

Only division is exposed — SUM, COUNT, MIN, MAX are exact on both sides. Worth reviewing where a divided money value is written back to a column and feeds later calculations, since truncation always biases the same way and accumulates. Converting MONEY → DECIMAL(19,4) on the source before migrating avoids it entirely.

Query Parity reports these as mismatches, correctly — the two sides really do return different values. Expand the row to see the side-by-side preview: matching counts and sums with a difference confined to the last decimal point to this behaviour rather than to missing or altered data.

Troubleshooting

npm install fails with ENOTFOUND on every package

The lockfile pins a private npm mirror that your network can't reach, and its host wins over your configured registry. frontend/package-lock.json is kept on the public registry to prevent this — npm still downloads from a mirror in your ~/.npmrc if you have one.

If you commit from behind a mirror, run npm run lockfile:check in frontend/ first (lockfile:normalize fixes it).

Source database grants

The source user needs some read only grants:

GRANT CONNECT             TO [<login>];
GRANT VIEW DEFINITION     TO [<login>];  -- object definitions to translate
GRANT VIEW DATABASE STATE TO [<login>];  -- row counts
ALTER ROLE db_datareader ADD MEMBER [<login>];

Nothing ever writes to the source — no db_datawriter, db_ddladmin, or db_owner is required.

Run scripts/source_grants.sql to apply all of the above (idempotent, one run per database); it ends with a verification query. If a scan still fails or comes back short, scripts/assessment_scan_queries.sql runs the scanner's queries individually to show which one is the problem.

Testing and debugging the source connection

scripts/azure_sql_connect.py reproduces a failing Test connection from a terminal. It drives the same code path as the app (build_connector → AzureSqlConnection) outside FastAPI — same pymssql driver, login/query timeouts, and transient-error retry (serverless auto-pause resume, 40613) — then runs two cheap catalog queries, so it's safe against production.

# Coordinates come from the environment (nothing workspace-specific committed)
export LBX_SRC_HOST="<server>.database.windows.net"
export LBX_SRC_DATABASE="<db>"
export LBX_SRC_USER="<login>"
export LBX_SRC_PASSWORD="<password>"
PYTHONPATH=. python3 scripts/azure_sql_connect.py

# Or keep them in a file the probe parses itself
cp .vscode/azure_sql.env.sample .vscode/azure_sql.env   # gitignored — fill it in
PYTHONPATH=. python3 scripts/azure_sql_connect.py --env-file .vscode/azure_sql.env

To step through it in VS Code, use the Azure SQL probe (env file) launch configuration in .vscode/launch.json, which reads .vscode/azure_sql.env and pins the interpreter that has pymssql installed.

Roadmap

Not commitments — the gaps we'd close next, in rough priority order.

• Databricks identity auth for Lakebase (OAuth/OIDC). Connect as a Databricks user, service principal, or group with short-lived tokens instead of a native Postgres role password, so target access is governed by workspace identity and inherits SSO/MFA and credential rotation.
• More source connectors — Oracle, PostgreSQL, MySQL. The scanner is connector-agnostic; see Adding a source connector.
• Multi-user run state. Run state is in-process memory, so the app is single-user today; persisting it would allow concurrent users and multi-worker deployments.

How to get help

Databricks support doesn't cover this content. For questions or bugs, please open a GitHub issue and the team will help on a best effort basis. See NOTICE.md.

License

© 2026 Databricks, Inc. All rights reserved. The source in this repository is provided subject to the Databricks License https://databricks.com/db-license-source. All included or referenced third party libraries are subject to the licenses set forth below.

Provided "as is", without warranty of any kind — not an official Databricks product; see the disclaimer at the top. Full terms in LICENSE.md.

library	description	license	source