---
title: "SQLite to HeliosDB Migration Guide"
---

# SQLite to HeliosDB Migration Guide

**Version:** 1.0

---

## Overview

Migrating from SQLite embedded databases to HeliosDB multi-user server provides:
- Multi-user concurrent access
- Advanced replication
- Enterprise security
- Distributed queries

---

## Pre-Migration Checklist

- [ ] Backup SQLite database file (.db)
- [ ] Assess database size
- [ ] Review schema for SQLite-specific syntax
- [ ] Plan concurrent user load
- [ ] Set up HeliosDB instance

---

## Step 1: Export SQLite Schema

```bash
# Extract schema from SQLite
sqlite3 mydb.db ".schema" > schema.sql

# Or using sqlite3 CLI
sqlite3 mydb.db ".dump" > full_dump.sql
```

---

## Step 2: Convert SQLite to PostgreSQL

```sql
-- Common SQLite to HeliosDB conversions:

-- SQLite: AUTOINCREMENT → PostgreSQL: SERIAL
-- SQLite: INTEGER PRIMARY KEY → PostgreSQL: SERIAL PRIMARY KEY
-- SQLite: REAL → PostgreSQL: DOUBLE PRECISION
-- SQLite: BLOB → PostgreSQL: BYTEA
-- SQLite: Text affinity → PostgreSQL: TEXT
-- SQLite: Flexible typing → PostgreSQL: explicit typing

-- Example:
-- SQLite:
CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  age INTEGER
);

-- PostgreSQL/HeliosDB:
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  age INTEGER
);
```

---

## Step 3: Export Data from SQLite

```bash
# Export each table as CSV
sqlite3 mydb.db ".mode csv" ".output users.csv" "SELECT * FROM users;"

# Or export all as SQL dump
sqlite3 mydb.db ".dump" > data_dump.sql
```

---

## Step 4: Create Database in HeliosDB

```bash
# Create database
createdb mydb_helios

# Create schema
psql -h heliosdb.host -U user -d mydb_helios -f schema.sql
```

---

## Step 5: Import Data

```bash
# Import CSV data
psql -h heliosdb.host -U user -d mydb_helios \
  -c "COPY users(id, name, age) FROM STDIN CSV" \
  < users.csv

# Or import from SQL dump
psql -h heliosdb.host -U user -d mydb_helios < data_dump.sql
```

---

## Step 6: Verify Migration

```sql
-- Check table structure
\d users

-- Count rows
SELECT COUNT(*) FROM users;

-- Sample data
SELECT * FROM users LIMIT 10;

-- Check constraints
SELECT constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name = 'users';
```

---

## Step 7: Update Application

```javascript
// SQLite (file-based)
const db = new Database(':memory:');

// HeliosDB (server-based)
const Pool = require('pg').Pool;
const pool = new Pool({
  host: 'heliosdb.host',
  port: 5432,
  database: 'mydb_helios',
  user: 'username',
  password: 'password'
});

// Query (similar API)
pool.query('SELECT * FROM users WHERE id = $1', [1]);
```

---

## Step 8: Testing

```sql
-- Verify data integrity
SELECT COUNT(*) as total FROM users;

-- Check indexes
SELECT indexname FROM pg_indexes
WHERE tablename = 'users';

-- Test queries
EXPLAIN ANALYZE SELECT * FROM users WHERE name LIKE '%john%';
```

---

## Migration Patterns

### Pattern 1: Local to Cloud

```
SQLite (local) → HeliosDB Cloud
- Backup SQLite
- Export data
- Import to cloud
- Update connection string
```

### Pattern 2: Embedded to Distributed

```
SQLite (single file) → HeliosDB (multi-node cluster)
- Export data
- Create replicated database
- Enable replication
- Deploy application
```

---

## Performance Improvements

```sql
-- Create indexes for faster queries
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_date ON orders(order_date);

-- Enable query cache
ALTER TABLE frequently_accessed ENABLE INTELLIGENT_CACHING;

-- Monitor performance
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com';
```

---

## SQL Syntax Differences

| SQLite | HeliosDB |
|--------|----------|
| `CAST(x AS INTEGER)` | `CAST(x AS INTEGER)` ✓ |
| `DATETIME('now')` | `CURRENT_TIMESTAMP` |
| `SUBSTR(str, 1, 10)` | `SUBSTRING(str, 1, 10)` |
| `LENGTH(str)` | `LENGTH(str)` ✓ |
| `GLOB pattern` | `~ pattern` or `LIKE` |
| `REPLACE INTO` | `INSERT... ON CONFLICT DO UPDATE` |
| `VACUUM` | `VACUUM` ✓ |
| `PRAGMA` | PostgreSQL system functions |

---

## Scaling Considerations

```sql
-- SQLite is single-threaded
-- HeliosDB supports concurrent users

-- Configure for multiple concurrent connections
ALTER SYSTEM SET max_connections = 100;

-- Use connection pooling
ALTER DATABASE mydb_helios SET pool_size = 20;
```

---

## Post-Migration

- [ ] All data imported successfully
- [ ] Row counts match
- [ ] Indexes created
- [ ] Queries optimized
- [ ] Application tested
- [ ] Performance validated
- [ ] Backups configured
- [ ] Monitoring enabled

---

## Rollback Plan

```bash
# Keep SQLite file as backup
cp mydb.db mydb.db.backup

# If rollback needed, point application back to SQLite
# Update connection string in application
```

---

## Best Practices

1. Test on development environment first
2. Backup both databases during migration
3. Use constraint validation
4. Monitor performance
5. Use connection pooling for scalability
6. Enable replication for HA
7. Set up automated backups

---

**Related Documentation:**