image-search-api / scripts /migrate_to_postgres.py
mbishu's picture
Add PostgreSQL/pgvector support with auto-migration and Neon integration
d34ac77
Raw History Blame Contribute Delete
9.69 kB
"""
Migration script to migrate data from SQLite to PostgreSQL.
This script:
1. Reads all products from SQLite database
2. Creates PostgreSQL tables with pgvector extension
3. Migrates all product data to PostgreSQL
4. Optionally migrates vector embeddings to pgvector
Usage:
# Using command line argument:
python scripts/migrate_to_postgres.py \
--sqlite-path app/data/products.db \
--postgres-url postgresql://user:password@host:port/database
# Using environment variable (recommended for Neon):
export DATABASE_CONNECTION_STRING="postgresql://user:password@host:port/database"
python scripts/migrate_to_postgres.py --sqlite-path app/data/products.db
For Neon PostgreSQL:
1. Create a project at https://neon.tech
2. Copy the connection string from the dashboard
3. Use it as --postgres-url or set DATABASE_CONNECTION_STRING env var
4. Neon automatically includes pgvector extension
"""
import argparse
import sqlite3
import json
import sys
import os
from pathlib import Path
from typing import List, Dict, Any, Optional
from datetime import datetime
try:
import psycopg2
from psycopg2.extras import execute_values
from psycopg2 import sql
except ImportError:
print("ERROR: psycopg2 is required. Install it with: pip install psycopg2")
print("Note: You may also need: apt-get install libpq-dev python3-dev (Linux) or brew install postgresql (macOS)")
sys.exit(1)
def create_postgres_tables(conn, vector_dimension: int = 768) -> None:
"""Create PostgreSQL tables with pgvector extension."""
with conn.cursor() as cur:
# Enable pgvector extension
cur.execute("CREATE EXTENSION IF NOT EXISTS vector;")
# Create products table
cur.execute("""
CREATE TABLE IF NOT EXISTS products (
id VARCHAR(255) PRIMARY KEY,
title TEXT NOT NULL,
description TEXT,
price REAL NOT NULL,
currency VARCHAR(10) DEFAULT 'XAF',
category TEXT NOT NULL,
brand TEXT,
image_url TEXT NOT NULL,
availability VARCHAR(50) DEFAULT 'in_stock',
rating REAL,
review_count INTEGER,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
metadata JSONB
)
""")
# Create indexes
cur.execute("CREATE INDEX IF NOT EXISTS idx_products_category ON products(category)")
cur.execute("CREATE INDEX IF NOT EXISTS idx_products_brand ON products(brand)")
cur.execute("CREATE INDEX IF NOT EXISTS idx_products_price ON products(price)")
cur.execute("CREATE INDEX IF NOT EXISTS idx_products_rating ON products(rating)")
cur.execute("CREATE INDEX IF NOT EXISTS idx_products_availability ON products(availability)")
# Create vector embeddings table with pgvector
cur.execute(f"""
CREATE TABLE IF NOT EXISTS product_embeddings (
product_id VARCHAR(255) PRIMARY KEY,
vector vector({vector_dimension}),
metadata JSONB,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
)
""")
# Create vector index for similarity search
cur.execute("""
CREATE INDEX IF NOT EXISTS product_embeddings_vector_idx
ON product_embeddings
USING ivfflat (vector vector_cosine_ops)
WITH (lists = 100)
""")
conn.commit()
print("✓ PostgreSQL tables created successfully")
def migrate_products(sqlite_path: str, postgres_url: str) -> int:
"""Migrate products from SQLite to PostgreSQL."""
# Connect to SQLite
sqlite_conn = sqlite3.connect(sqlite_path)
sqlite_conn.row_factory = sqlite3.Row
# Connect to PostgreSQL
pg_conn = psycopg2.connect(postgres_url)
try:
# Create tables
create_postgres_tables(pg_conn)
# Read all products from SQLite
cursor = sqlite_conn.execute("SELECT * FROM products")
products = cursor.fetchall()
if not products:
print("No products found in SQLite database")
return 0
print(f"Found {len(products)} products to migrate")
# Prepare data for bulk insert
products_data = []
for row in products:
metadata_json = None
if row['metadata']:
try:
metadata_json = json.loads(row['metadata'])
except:
metadata_json = {}
products_data.append((
row['id'],
row['title'],
row['description'],
row['price'],
row.get('currency', 'XAF'),
row['category'],
row.get('brand'),
row['image_url'],
row.get('availability', 'in_stock'),
row.get('rating'),
row.get('review_count'),
row.get('created_at'),
row.get('updated_at'),
json.dumps(metadata_json) if metadata_json else None
))
# Bulk insert into PostgreSQL
with pg_conn.cursor() as cur:
insert_query = """
INSERT INTO products
(id, title, description, price, currency, category, brand, image_url,
availability, rating, review_count, created_at, updated_at, metadata)
VALUES %s
ON CONFLICT (id) DO UPDATE SET
title = EXCLUDED.title,
description = EXCLUDED.description,
price = EXCLUDED.price,
currency = EXCLUDED.currency,
category = EXCLUDED.category,
brand = EXCLUDED.brand,
image_url = EXCLUDED.image_url,
availability = EXCLUDED.availability,
rating = EXCLUDED.rating,
review_count = EXCLUDED.review_count,
updated_at = EXCLUDED.updated_at,
metadata = EXCLUDED.metadata
"""
execute_values(
cur,
insert_query,
products_data,
template=None,
page_size=100
)
pg_conn.commit()
print(f"✓ Migrated {len(products)} products to PostgreSQL")
return len(products)
finally:
sqlite_conn.close()
pg_conn.close()
def main():
parser = argparse.ArgumentParser(description="Migrate SQLite database to PostgreSQL")
parser.add_argument(
"--sqlite-path",
type=str,
default="app/data/products.db",
help="Path to SQLite database file"
)
parser.add_argument(
"--postgres-url",
type=str,
default=None,
help="PostgreSQL connection URL (postgresql://user:password@host:port/database). "
"If not provided, will use DATABASE_CONNECTION_STRING environment variable."
)
parser.add_argument(
"--vector-dimension",
type=int,
default=768,
help="Vector dimension for pgvector (default: 768 for clip-ViT-L-14)"
)
args = parser.parse_args()
# Get PostgreSQL URL from argument or environment variable
postgres_url = args.postgres_url or os.getenv("DATABASE_CONNECTION_STRING")
if not postgres_url:
print("ERROR: PostgreSQL connection URL is required!")
print("Either provide --postgres-url argument or set DATABASE_CONNECTION_STRING environment variable")
print("\nExample:")
print(" export DATABASE_CONNECTION_STRING='postgresql://user:password@host:port/database'")
print(" python scripts/migrate_to_postgres.py")
sys.exit(1)
# Ensure it's a PostgreSQL URL
if not postgres_url.lower().startswith("postgresql://") and not postgres_url.lower().startswith("postgres://"):
print("ERROR: Connection string must start with 'postgresql://' or 'postgres://'")
print(f"Got: {postgres_url[:20]}...")
sys.exit(1)
# Check if SQLite file exists
if not Path(args.sqlite_path).exists():
print(f"ERROR: SQLite database not found at {args.sqlite_path}")
sys.exit(1)
print(f"Starting migration from {args.sqlite_path} to PostgreSQL...")
# Hide credentials in output - show only host and database
if "@" in postgres_url:
url_parts = postgres_url.split("@")
if len(url_parts) > 1:
print(f"PostgreSQL: {url_parts[-1]}")
else:
print("PostgreSQL: [connection string provided]")
try:
count = migrate_products(args.sqlite_path, postgres_url)
print(f"\n✓ Migration completed successfully! Migrated {count} products.")
print("\nNext steps:")
print("1. Update DATABASE_CONNECTION_STRING in your environment variables")
print("2. Set VECTOR_STORE_TYPE=pgvector")
print("3. Set PGVECTOR_CONNECTION_STRING to the same PostgreSQL URL")
print("4. Restart your application to use PostgreSQL")
except Exception as e:
print(f"ERROR: Migration failed: {e}")
import traceback
traceback.print_exc()
sys.exit(1)
if __name__ == "__main__":
main()