Bun script to migrate SQLite data into Pocketbase via Batch API

migrate.ts

  import { Database } from 'bun:sqlite';
  import PocketBase from 'pocketbase';
  
  const BATCH_SIZE = 50;
  const SLEEP_TIME = 1000;
  const pb = new PocketBase('https://tx-addresses.pockethost.io');
  
  const sleep = (ms: number) => new Promise(resolve => setTimeout(resolve, ms));
  
  async function migrateData() {
      const db = new Database('./texas_addresses.sqlite');
      pb.autoCancellation(false);
  
      try {
          // First authenticate as admin
          await pb.admins.authWithPassword(
              process.env.PB_ADMIN_EMAIL!,
              process.env.PB_ADMIN_PASSWORD!
          );
  
          // Turn off autoCancellation
          pb.autoCancellation(false);

          const totalCount = db.query('SELECT COUNT(*) as count FROM addresses').get() as { count: number };
          console.log(`Total records to migrate: ${totalCount.count}`);
  
          let offset = 0;
          let processedRecords = 0;
          
          while (processedRecords < totalCount.count) {
              const rows = db.query(
                  `SELECT * FROM addresses LIMIT ${BATCH_SIZE} OFFSET ${offset}`
              ).all() as Record<string, any>[];
  
              if (rows.length === 0) break;
  
              try {
                  const batchData = rows.map(row => ({
                      number: String(row.number),
                      street: String(row.street),
                      unit: row.unit ? String(row.unit) : null,
                      city: row.city ? String(row.city) : null,
                      district: row.district ? String(row.district) : null,
                      region: String(row.region),
                      postcode: row.postcode ? String(row.postcode) : null,
                      longitude: Number(row.longitude),
                      latitude: Number(row.latitude),
                      county: String(row.county)
                  }));
  
                  // Use collection API with array of records
                  await pb.collection('addresses').create(batchData);
  
                  processedRecords += rows.length;
                  console.log(`Migrated records ${offset + 1} to ${offset + rows.length}`);
                  
              } catch (error) {
                  console.error('Batch error:', error);
                  if (error.response) {
                      console.error('Error details:', JSON.stringify(error.response, null, 2));
                  }
                  throw error;
              }
  
              await sleep(SLEEP_TIME);
              offset += BATCH_SIZE;
          }
  
          console.log('\nMigration completed successfully');
          console.log(`Total records processed: ${processedRecords}`);
  
      } catch (error) {
          console.error('Error during migration:', error);
          throw error;
      } finally {
          db.close();
      }
  }
  
  async function main() {
      try {
          await migrateData();
      } catch (error) {
          console.error('Migration failed:', error);
          process.exit(1);
      }
  }
  
  main();