This project provides a Python script to test connection and perform simple vector operations with a PostgreSQL database deployed on Railway using the pgvector extension.

1. Install dependencies:

   pip install -r requirements.txt

2. Configure your Railway database credentials:

   Copy .env.example to .env and fill in your Railway database credentials:

   cp .env.example .env

   Edit .env with your actual Railway database details:

   DB_HOST=your-railway-host.railway.app
   DB_PORT=5432
   DB_NAME=railway
   DB_USER=postgres
   DB_PASSWORD=your-password
   

1. Go to your Railway project dashboard
2. Navigate to your PostgreSQL service
3. Go to the "Variables" tab
4. Copy the connection details or use the provided DATABASE_URL

Run the test script:

python connect-pgvecto.py

1. Connection Test: Establishes connection to your Railway PostgreSQL database
2. Extension Setup: Enables the pgvector extension (if available)
3. Table Creation: Creates a test table with a vector column (384 dimensions)
4. Vector Insertion: Inserts sample vectors into the database
5. Query Test: Retrieves and displays stored vectors
6. Similarity Search: Performs a cosine similarity search on the vectors

• ✅ Database connection testing
• ✅ pgvector extension setup
• ✅ Vector table creation
• ✅ Vector insertion with random data
• ✅ Basic vector querying
• ✅ Similarity search using cosine distance
• ✅ Error handling and cleanup

🚀 Starting PostgreSQL Vector Database Test
==================================================
Connecting to database at your-host.railway.app:5432/railway
✅ Successfully connected to PostgreSQL database!
✅ pgvector extension enabled!
✅ Test table 'test_vectors' created successfully!

📝 Inserting test vectors...
✅ Successfully inserted vector 'sample_vector_1' with ID: 1
✅ Successfully inserted vector 'sample_vector_2' with ID: 2
✅ Successfully inserted vector 'sample_vector_3' with ID: 3

📊 Querying all vectors...
📊 Retrieved 3 vectors from database:
  ID: 3, Name: sample_vector_3, Vector: [0.1, 0.2, 0.3, ...]
  ID: 2, Name: sample_vector_2, Vector: [0.4, 0.5, 0.6, ...]
  ID: 1, Name: sample_vector_1, Vector: [0.7, 0.8, 0.9, ...]

🔍 Testing similarity search...
🔍 Similarity search results (top 5):
  ID: 2, Name: sample_vector_2, Distance: 0.1234
  ID: 1, Name: sample_vector_1, Distance: 0.2345
  ID: 3, Name: sample_vector_3, Distance: 0.3456

✅ All tests completed successfully!
🔐 Database connection closed

• The script generates random normalized vectors for testing
• Vector dimensions are set to 384 (common for sentence embeddings)
• The script uses cosine distance for similarity search (<=> operator)
• Make sure your Railway PostgreSQL service has the pgvector extension enabled

1. Connection Error: Double-check your Railway database credentials
2. pgvector Extension: If not available, contact Railway support or use a different deployment method
3. Permission Errors: Ensure your database user has sufficient privileges to create tables and extensions