Migrating from PostgreSQL full-text search to Meilisearch

mkdir pg-meilisearch-migration
cd pg-meilisearch-migration
npm init -y
touch script.js

npm install -s pg meilisearch

const { Pool } = require("pg");

const pool = new Pool({
  host: "PG_HOST",
  port: 5432,
  database: "PG_DATABASE",
  user: "PG_USER",
  password: "PG_PASSWORD",
});

const TABLE_NAME = "YOUR_TABLE_NAME";
const BATCH_SIZE = 10000;

async function fetchAllRows() {
  const records = [];
  let offset = 0;

  while (true) {
    const result = await pool.query(
      `SELECT * FROM ${TABLE_NAME} ORDER BY id LIMIT $1 OFFSET $2`,
      [BATCH_SIZE, offset]
    );

    if (result.rows.length === 0) break;

    records.push(...result.rows);
    offset += result.rows.length;
  }

  return records;
}

function prepareDocuments(rows) {
  return rows.map((row) => {
    const doc = { ...row };

    // Ensure the primary key is a string named "id"
    if (doc.id === undefined && doc.your_pk_column !== undefined) {
      doc.id = String(doc.your_pk_column);
    } else {
      doc.id = String(doc.id);
    }

    // Convert Date objects to ISO strings
    for (const [key, value] of Object.entries(doc)) {
      if (value instanceof Date) {
        doc[key] = value.toISOString();
      }
    }

    return doc;
  });
}

// When querying, extract lat/lng from PostGIS:
// SELECT *, ST_Y(location::geometry) AS lat, ST_X(location::geometry) AS lng FROM your_table

function convertGeoFields(doc) {
  if (doc.lat !== undefined && doc.lng !== undefined) {
    doc._geo = {
      lat: parseFloat(doc.lat),
      lng: parseFloat(doc.lng),
    };
    delete doc.lat;
    delete doc.lng;
  }
  // Remove the original PostGIS column if present
  delete doc.location;
  return doc;
}

const { MeiliSearch } = require("meilisearch");

const meiliClient = new MeiliSearch({
  host: "MEILI_HOST",
  apiKey: "MEILI_API_KEY",
});
const meiliIndex = meiliClient.index("MEILI_INDEX_NAME");

const UPLOAD_BATCH_SIZE = 100000;
await meiliIndex.addDocumentsInBatches(documents, UPLOAD_BATCH_SIZE);

node script.js

const { Pool } = require("pg");
const { MeiliSearch } = require("meilisearch");

const TABLE_NAME = "YOUR_TABLE_NAME";
const FETCH_BATCH_SIZE = 10000;
const UPLOAD_BATCH_SIZE = 100000;

(async () => {
  // Connect to PostgreSQL
  const pool = new Pool({
    host: "PG_HOST",
    port: 5432,
    database: "PG_DATABASE",
    user: "PG_USER",
    password: "PG_PASSWORD",
  });

  // Fetch all rows
  const records = [];
  let offset = 0;

  while (true) {
    const result = await pool.query(
      `SELECT * FROM ${TABLE_NAME} ORDER BY id LIMIT $1 OFFSET $2`,
      [FETCH_BATCH_SIZE, offset]
    );

    if (result.rows.length === 0) break;

    records.push(...result.rows);
    offset += result.rows.length;
  }

  await pool.end();

  // Prepare documents for Meilisearch
  const documents = records.map((row) => {
    const doc = { ...row };
    doc.id = String(doc.id);

    for (const [key, value] of Object.entries(doc)) {
      if (value instanceof Date) {
        doc[key] = value.toISOString();
      }
    }

    return doc;
  });

  console.log(`Fetched ${documents.length} rows from PostgreSQL`);

  // Upload to Meilisearch
  const meiliClient = new MeiliSearch({
    host: "MEILI_HOST",
    apiKey: "MEILI_API_KEY",
  });
  const meiliIndex = meiliClient.index("MEILI_INDEX_NAME");

  await meiliIndex.addDocumentsInBatches(documents, UPLOAD_BATCH_SIZE);
  console.log("Migration complete");
})();