---
title: "SQL Migration Tips"
---

# SQL Migration Tips


---

This guide provides tips and best practices for migrating SQL workloads from other database systems to HeliosDB.

## Table of Contents

1. [General Migration Strategy](#general-migration-strategy)
2. [Migration from PostgreSQL](#migration-from-postgresql)
3. [Migration from MySQL](#migration-from-mysql)
4. [Migration from Oracle](#migration-from-oracle)
5. [Migration from SQL Server](#migration-from-sql-server)
6. [Migration from MongoDB](#migration-from-mongodb)
7. [Schema Migration](#schema-migration)
8. [Data Migration](#data-migration)
9. [Query Migration](#query-migration)
10. [Testing and Validation](#testing-and-validation)

---

## General Migration Strategy

### Phase 1: Assessment

1. **Inventory existing schema**
   - List all tables, views, indexes
   - Document data types and constraints
   - Identify stored procedures and functions

2. **Analyze query patterns**
   - Extract frequent queries
   - Identify performance-critical queries
   - Document transaction patterns

3. **Estimate data volume**
   - Current data size
   - Growth rate
   - Retention requirements

### Phase 2: Schema Conversion

1. **Map data types** (see source-specific sections)
2. **Convert constraints and indexes**
3. **Handle source-specific features**

### Phase 3: Data Migration

1. **Initial bulk load**
2. **Verify data integrity**

### Phase 4: Application Migration

1. **Update connection strings**
2. **Convert incompatible queries**
3. **Test and validate**

---

## Migration from PostgreSQL

### Compatibility

HeliosDB uses the PostgreSQL wire protocol, making migration straightforward.

**High Compatibility:**
- Data types
- SQL syntax
- Client libraries
- Connection strings

### Data Type Mapping

| PostgreSQL | HeliosDB | Notes |
|------------|----------|-------|
| `serial` | `BIGINT` + sequence | Use identity columns |
| `bigserial` | `BIGINT` + sequence | Use identity columns |
| `money` | `NUMERIC` | Convert to numeric |
| `cidr/inet` | `VARCHAR` | Store as text |
| `macaddr` | `VARCHAR` | Store as text |
| `point/line/box` | `JSONB` | Store as JSON |
| All others | Same | Direct compatibility |

### Connection String

```
# PostgreSQL
postgresql://user:pass@host:5432/dbname

# HeliosDB (same format)
postgresql://user:pass@host:5432/dbname
```

### Known Differences

**1. Extensions**: Some PostgreSQL extensions may not be available

```sql
-- PostgreSQL (may not work)
CREATE EXTENSION postgis;

-- HeliosDB: Use built-in geospatial types
CREATE TABLE locations (
    id BIGINT PRIMARY KEY,
    point GEOSPATIAL
);
```

**2. System catalogs**: Some pg_catalog views differ

```sql
-- May need adjustment for introspection queries
SELECT * FROM pg_stat_activity;  -- Limited support
```

---

## Migration from MySQL

### Data Type Mapping

| MySQL | HeliosDB | Notes |
|-------|----------|-------|
| `TINYINT` | `SMALLINT` | Wider range |
| `INT UNSIGNED` | `BIGINT` | Use signed |
| `FLOAT` | `REAL` | IEEE 754 |
| `DOUBLE` | `DOUBLE PRECISION` | Standard name |
| `DATETIME` | `TIMESTAMP` | With timezone |
| `ENUM` | `VARCHAR` + CHECK | Use constraint |
| `SET` | `TEXT[]` | Array type |
| `BLOB` | `BYTEA` | Binary data |
| `TEXT` | `TEXT` | Same |
| `JSON` | `JSONB` | Binary JSON |

### SQL Syntax Differences

**1. String concatenation**

```sql
-- MySQL
SELECT CONCAT(first_name, ' ', last_name) FROM users;

-- HeliosDB (same, or use ||)
SELECT first_name || ' ' || last_name FROM users;
```

**2. LIMIT with OFFSET**

```sql
-- MySQL
SELECT * FROM users LIMIT 10, 20;  -- offset 10, limit 20

-- HeliosDB
SELECT * FROM users LIMIT 20 OFFSET 10;
```

**3. Date functions**

```sql
-- MySQL
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);

-- HeliosDB
SELECT NOW() + INTERVAL '1 day';
```

**4. AUTO_INCREMENT**

```sql
-- MySQL
CREATE TABLE t (id INT AUTO_INCREMENT PRIMARY KEY);

-- HeliosDB
CREATE TABLE t (id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY);
```

**5. Backticks vs double quotes**

```sql
-- MySQL
SELECT `column` FROM `table`;

-- HeliosDB
SELECT "column" FROM "table";
```

### Connection String

```python
# MySQL
mysql://user:pass@host:3306/dbname

# HeliosDB (use PostgreSQL format)
postgresql://user:pass@host:5432/dbname
```

---

## Migration from Oracle

### Data Type Mapping

| Oracle | HeliosDB | Notes |
|--------|----------|-------|
| `NUMBER(p)` | `NUMERIC(p)` | Exact |
| `NUMBER` | `DOUBLE PRECISION` | Float |
| `VARCHAR2(n)` | `VARCHAR(n)` | Same |
| `CLOB` | `TEXT` | Unlimited |
| `BLOB` | `BYTEA` | Binary |
| `DATE` | `TIMESTAMP` | Includes time |
| `TIMESTAMP` | `TIMESTAMP` | Same |
| `RAW` | `BYTEA` | Binary |
| `ROWID` | N/A | Not supported |

### SQL Syntax Differences

**1. Dual table**

```sql
-- Oracle
SELECT SYSDATE FROM DUAL;

-- HeliosDB
SELECT NOW();  -- No FROM clause needed
```

**2. Outer join syntax**

```sql
-- Oracle (old syntax)
SELECT * FROM a, b WHERE a.id = b.id(+);

-- HeliosDB
SELECT * FROM a LEFT JOIN b ON a.id = b.id;
```

**3. NVL vs COALESCE**

```sql
-- Oracle
SELECT NVL(column, 'default') FROM table;

-- HeliosDB
SELECT COALESCE(column, 'default') FROM table;
```

**4. DECODE vs CASE**

```sql
-- Oracle
SELECT DECODE(status, 1, 'Active', 2, 'Inactive', 'Unknown') FROM t;

-- HeliosDB
SELECT CASE status
    WHEN 1 THEN 'Active'
    WHEN 2 THEN 'Inactive'
    ELSE 'Unknown'
END FROM t;
```

**5. Sequences**

```sql
-- Oracle
CREATE SEQUENCE seq START WITH 1;
SELECT seq.NEXTVAL FROM DUAL;

-- HeliosDB
CREATE SEQUENCE seq START WITH 1;
SELECT nextval('seq');
```

**6. Hierarchical queries**

```sql
-- Oracle
SELECT * FROM emp START WITH mgr IS NULL CONNECT BY PRIOR emp_id = mgr;

-- HeliosDB (recursive CTE)
WITH RECURSIVE emp_tree AS (
    SELECT * FROM emp WHERE mgr IS NULL
    UNION ALL
    SELECT e.* FROM emp e JOIN emp_tree t ON e.mgr = t.emp_id
)
SELECT * FROM emp_tree;
```

---

## Migration from SQL Server

### Data Type Mapping

| SQL Server | HeliosDB | Notes |
|------------|----------|-------|
| `BIT` | `BOOLEAN` | TRUE/FALSE |
| `TINYINT` | `SMALLINT` | Wider |
| `MONEY` | `NUMERIC(19,4)` | Exact |
| `DATETIME` | `TIMESTAMP` | Standard |
| `DATETIME2` | `TIMESTAMP` | Standard |
| `NVARCHAR` | `VARCHAR` | UTF-8 |
| `NTEXT` | `TEXT` | UTF-8 |
| `VARBINARY` | `BYTEA` | Binary |
| `UNIQUEIDENTIFIER` | `UUID` | UUID type |
| `XML` | `TEXT` or `JSONB` | Store as text |

### SQL Syntax Differences

**1. TOP vs LIMIT**

```sql
-- SQL Server
SELECT TOP 10 * FROM users;

-- HeliosDB
SELECT * FROM users LIMIT 10;
```

**2. String functions**

```sql
-- SQL Server
SELECT LEN(column), CHARINDEX('a', column) FROM t;

-- HeliosDB
SELECT LENGTH(column), POSITION('a' IN column) FROM t;
```

**3. Date functions**

```sql
-- SQL Server
SELECT GETDATE(), DATEADD(day, 1, GETDATE());

-- HeliosDB
SELECT NOW(), NOW() + INTERVAL '1 day';
```

**4. Identity columns**

```sql
-- SQL Server
CREATE TABLE t (id INT IDENTITY(1,1) PRIMARY KEY);

-- HeliosDB
CREATE TABLE t (id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY);
```

**5. Square brackets**

```sql
-- SQL Server
SELECT [column] FROM [table];

-- HeliosDB
SELECT "column" FROM "table";
```

---

## Migration from MongoDB

### Document to Relational Mapping

**1. Embedded documents -> Separate tables**

```javascript
// MongoDB
{
    _id: ObjectId("..."),
    name: "John",
    address: {
        street: "123 Main St",
        city: "Boston"
    }
}
```

```sql
-- HeliosDB Option 1: JSONB column
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255),
    address JSONB
);

-- HeliosDB Option 2: Normalized
CREATE TABLE users (
    id UUID PRIMARY KEY,
    name VARCHAR(255)
);
CREATE TABLE addresses (
    id UUID PRIMARY KEY,
    user_id UUID REFERENCES users(id),
    street VARCHAR(255),
    city VARCHAR(255)
);
```

**2. Arrays -> Array types or junction tables**

```javascript
// MongoDB
{ _id: ..., tags: ["tech", "news"] }
```

```sql
-- HeliosDB Option 1: Array type
CREATE TABLE articles (
    id UUID PRIMARY KEY,
    tags TEXT[]
);

-- Query
SELECT * FROM articles WHERE 'tech' = ANY(tags);
```

**3. ObjectId -> UUID**

```sql
-- Use native UUID type
CREATE TABLE documents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid()
);
```

### Query Translation

| MongoDB | HeliosDB |
|---------|----------|
| `db.c.find({})` | `SELECT * FROM c` |
| `db.c.find({a: 1})` | `SELECT * FROM c WHERE a = 1` |
| `db.c.find({a: {$gt: 1}})` | `SELECT * FROM c WHERE a > 1` |
| `db.c.find({a: {$in: [1,2]}})` | `SELECT * FROM c WHERE a IN (1, 2)` |
| `db.c.find({a: {$regex: 'pat'}})` | `SELECT * FROM c WHERE a ~ 'pat'` |
| `db.c.aggregate([{$group:...}])` | `SELECT... GROUP BY...` |

---

## Schema Migration

### Manual Schema Conversion

```sql
-- Example: Convert MySQL schema to HeliosDB

-- Original (MySQL)
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    metadata JSON
);

-- Converted (HeliosDB)
CREATE TABLE users (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    metadata JSONB
);
```

---

## Data Migration

### Bulk Load with COPY

```sql
-- Export from source (PostgreSQL example)
COPY users TO '/tmp/users.csv' WITH CSV HEADER;

-- Import to HeliosDB
COPY users FROM '/tmp/users.csv' WITH CSV HEADER;
```

---

## Query Migration

### Query Validation Tool

```python
# Compare query results between source and target

import psycopg2

def validate_query(source_conn, target_conn, query):
    source_cursor = source_conn.cursor()
    target_cursor = target_conn.cursor()

    source_cursor.execute(query)
    target_cursor.execute(query)

    source_results = source_cursor.fetchall()
    target_results = target_cursor.fetchall()

    assert source_results == target_results, "Results differ!"
    print(f"Query validated: {len(source_results)} rows match")
```

---

## Testing and Validation

### Data Validation Checklist

- [ ] Row counts match between source and target
- [ ] Sample data spot checks pass
- [ ] Foreign key relationships preserved
- [ ] Null handling correct
- [ ] Date/time conversions accurate
- [ ] Unicode data preserved
- [ ] Numeric precision maintained

### Performance Validation

```sql
-- Run benchmark queries
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

-- Compare with source database performance
```

### Application Testing

1. **Unit tests**: Run existing test suite against HeliosDB
2. **Integration tests**: Test all application flows
3. **Load tests**: Verify performance under load
4. **Failover tests**: Test high availability

---

## Related Documentation

- [README.md](https://www.heliosdb.com/docs/full/features/sql-interface/readme/) - SQL interface overview
- [COMPATIBILITY_MATRIX.md](https://www.heliosdb.com/docs/full/features/sql-interface/compatibility_matrix/) - SQL compliance
- [SQL_COMMAND_EXTENSIONS.md](https://www.heliosdb.com/docs/full/features/sql-interface/sql_command_extensions/) - HeliosDB extensions
- [Automated ETL](https://www.heliosdb.com/docs/full/features/automated-etl/readme/) - ETL pipeline for migration