Skip to content
HeliosDB HeliosDB Full

SQLite to HeliosDB Migration Guide

SQLite to HeliosDB Migration Guide

Version: 1.0 Last Updated: 2025-11-30

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

Terminal window
# 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

-- 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

Terminal window
# 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

Terminal window
# Create database
createdb mydb_helios


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

Step 5: Import Data

Terminal window
# 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

-- 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

// 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

-- 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

-- 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

SQLiteHeliosDB
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 INTOINSERT ... ON CONFLICT DO UPDATE
VACUUMVACUUM ✓
PRAGMAPostgreSQL system functions

Scaling Considerations

-- 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

Terminal window
# 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: