Are there any good ways or cautions for migrating existing Oracle and MSSQL databases to OutSystems, and any reference sites?
Hi everyone,
We are planning to develop a new application in OutSystems, but our existing systems rely on both Oracle and MSSQL databases. We expect that part of the project will involve migrating or integrating with these existing databases.
Before we proceed, we would appreciate any advice on the following:
Is it recommended to migrate the data into OutSystems-managed entities, or should we integrate directly with the external Oracle/MSSQL databases?
Are there best practices or tools available in OutSystems to support the migration process from Oracle/MSSQL?
Any performance or compatibility concerns when connecting to both Oracle and MSSQL from an OutSystems application?
Is there any OutSystems documentation or community experience that you would recommend for hybrid DB environments?
Any insights, experiences, or warnings from others who have dealt with similar scenarios would be really helpful.
Thank you in advance!
Yes, I have done multiple migration of data to the Outsystems app, And what i found the best way to do that is
Best Practices for Data Migration to OutSystems
1. Connect to External Databases
Use Integration Studio to link with Oracle/MSSQL.
Import only necessary tables as database extensions.
Keep extension modules clean and focused (one per DB ideally).
Also do the mapping fist if want to move specific data only instead whole data.
2. Design OutSystems Entities Smartly
Create OutSystems entities based on business needs, not just copying the existing DB structure.
Normalize or simplify where appropriate using OutSystems data modeling.
Use Static Entities instead of regular tables for:
Fixed-type data like statuses, categories, types, roles, etc.
This enables enum-style usage, with display names and indexes.
Easier to manage via Service Studio and ensures performance & clarity.
3. Use Timers for Asynchronous Data Migration
Create server-side timers for large/batch migrations.
Prevent UI timeouts and allow background processing.
Add batch size, delay, and retry logic for better control.
4. Transform and Map Data Carefully
Handle foreign key relationships, enum conversions, and null/defaults.
Write transformation logic in reusable server actions for maintainability.
Consider using lookup mapping tables for complex ID translations.
5. Migrate in Phases or Deltas
Use flags like IsMigrated, LastModifiedDate, or audit timestamps.
Enable delta or staged migration to allow progressive rollout.
6. Replicate RDBMS Logic Thoughtfully
Move essential logic from stored procedures or views into OutSystems server actions.
7. Secure and Environment-Proof Integrations
Store all DB credentials in OutSystems environment configs in service center
Perform the migration first on non-prod env and validate and then move to prod migration.Another tool is Integration builder also you can use instead of integration studio to create the extension. that will work also in the same manner.
sample : customers, booking
Using Legacy Natural Keys (e.g., EMP_ID) Instead of OutSystems Default ID: Is It a Good Idea?
I’m currently working on a data migration project from Oracle to OutSystems.
for example, The legacy database uses natural composite keys like EMP_ID, DEPT_ID, etc., as primary or foreign keys in several tables (e.g., CUSTOMER.EMP_ID, BOOKING.EMP_ID).
After importing the data into OutSystems entities, the platform generates default Id attributes as primary keys, which is great for platform consistency. However, I was wondering:
❓ Is it okay to ignore the auto-generated Id and instead use the legacy EMP_ID fields to perform joins and relationships within Aggregates or SQL queries?
I think it's possible, but what problems might arise?