如何将MySQL数据库迁移至Cloud Firestore？求最优方案及实施步骤

Great question! Migrating from MySQL to Cloud Firestore requires shifting from a relational mindset to a document-oriented one, but it’s totally manageable. Let me walk you through the key steps, address your REST API concerns, and share practical tips from hands-on experience.

First, you’ll need to translate your MySQL tables and relationships into Firestore’s document/collection structure—this is the most critical part, since Firestore doesn’t do joins like MySQL. Here’s how to handle common relationships:

• One-to-One: Nest related data directly inside the parent document. For example, if you have a users table and a user_profiles table, you can merge profile fields into the user document instead of keeping them separate.
• One-to-Many: Choose between two approaches based on your query needs:
  • Nest child data as an array (good for small, rarely updated lists like a user’s recent orders).
  • Use a subcollection (better for large datasets or when you need to query child items independently, like a users/{userId}/orders subcollection).
• Many-to-Many: Use an array of reference IDs in each document (e.g., a products document has a userIds array of users who purchased it) or create a junction collection (like user_product_links with documents containing both userId and productId).

Pro tip: Design your structure around how you’ll query data later, not just mirroring MySQL. Firestore is query-driven—start by listing your most common queries, then build the schema to support those efficiently.

Next, pull your data out of MySQL. You have two main options:

1. Use mysqldump to export data as CSV or JSON files, then read those files in your Node.js script.
2. Connect directly to MySQL from Node.js using a library like mysql2 to fetch data on the fly. Here’s a quick example:

const mysql = require('mysql2/promise');

async function fetchMySQLData() {
  const connection = await mysql.createConnection({
    host: 'your-mysql-host',
    user: 'your-db-user',
    password: 'your-db-password',
    database: 'your-db-name'
  });

  // Example: Fetch users with their associated orders
  const [usersWithOrders] = await connection.execute(`
    SELECT u.id as userId, u.name, u.email, o.id as orderId, o.total, o.order_date
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
  `);
  
  await connection.end();
  return usersWithOrders;
}

Once you have your MySQL data, you’ll need to reshape it to fit Firestore’s document structure. For example, if you fetched flattened user-order data, you can group orders under their respective users:

function transformToFirestoreFormat(rawData) {
  const userMap = new Map();

  rawData.forEach(row => {
    if (!userMap.has(row.userId)) {
      userMap.set(row.userId, {
        name: row.name,
        email: row.email,
        orders: []
      });
    }

    if (row.orderId) {
      userMap.get(row.userId).orders.push({
        orderId: row.orderId,
        total: row.total,
        orderDate: row.order_date.toISOString()
      });
    }
  });

  // Convert map to array of { id, data } objects for easy import
  return Array.from(userMap.entries()).map(([userId, userData]) => ({
    id: userId.toString(),
    data: userData
  }));
}

To answer your question directly: Yes, writing a Node.js script using the Firestore REST API is totally feasible, even if you prefer REST over sockets. Here’s how to implement it:

First, you’ll need authentication—use a Firebase service account key (downloadable from your Firebase Console) to generate an access token for the REST API. Install required dependencies first:

npm install axios googleapis

Then, here’s a complete script snippet to import data:

const axios = require('axios');
const { google } = require('googleapis');
const serviceAccount = require('./path-to-your-service-account-key.json');
const PROJECT_ID = 'your-firebase-project-id';

async function getFirestoreAuthToken() {
  const jwtClient = new google.auth.JWT(
    serviceAccount.client_email,
    null,
    serviceAccount.private_key,
    ['https://www.googleapis.com/auth/datastore']
  );
  const authResult = await jwtClient.authorize();
  return authResult.access_token;
}

async function importDocument(collection, docId, data, authToken) {
  const url = `https://firestore.googleapis.com/v1/projects/${PROJECT_ID}/databases/(default)/documents/${collection}/${docId}`;
  
  // Convert JS data to Firestore's REST API field format
  const firestoreFields = convertToFirestoreFields(data);

  try {
    await axios.put(url, { fields: firestoreFields }, {
      headers: {
        'Authorization': `Bearer ${authToken}`,
        'Content-Type': 'application/json'
      }
    });
    console.log(`Successfully imported ${collection}/${docId}`);
  } catch (err) {
    console.error(`Failed to import ${collection}/${docId}:`, err.response.data);
  }
}

// Helper to convert JS types to Firestore REST format
function convertToFirestoreFields(data) {
  const fields = {};
  for (const [key, value] of Object.entries(data)) {
    if (typeof value === 'string') {
      fields[key] = { stringValue: value };
    } else if (typeof value === 'number') {
      fields[key] = value % 1 === 0 ? { integerValue: value.toString() } : { doubleValue: value };
    } else if (Array.isArray(value)) {
      fields[key] = {
        arrayValue: {
          values: value.map(item => ({ mapValue: { fields: convertToFirestoreFields(item) } }))
        }
      };
    }
  }
  return fields;
}

// Main migration flow
async function runMigration() {
  const rawMySQLData = await fetchMySQLData();
  const firestoreReadyData = transformToFirestoreFormat(rawMySQLData);
  const authToken = await getFirestoreAuthToken();

  // Add a small delay between requests to avoid hitting Firestore rate limits
  for (const { id, data } of firestoreReadyData) {
    await importDocument('users', id, data, authToken);
    await new Promise(resolve => setTimeout(resolve, 100));
  }

  console.log('Migration complete!');
}

runMigration().catch(console.error);

Practical Notes for REST API Migration #

• Rate Limits: Firestore enforces write rate limits (1 write per second per document, 500 writes per second per collection). Add delays or use batch writes (via the REST API's batchWrite endpoint) to avoid throttling.
• Admin SDK Alternative: While the REST API works, the Firebase Admin SDK for Node.js is more concise and handles data type conversion automatically. If you’re open to it, here’s a quick example of the import step with the SDK:

  const admin = require('firebase-admin');
  admin.initializeApp({ credential: admin.credential.cert(serviceAccount) });
  const db = admin.firestore();
  
  async function importWithAdminSDK(collection, docId, data) {
    await db.collection(collection).doc(docId).set(data);
  }
  

  But if REST is more comfortable for you, stick with that—there’s no wrong choice here.

Don’t skip this part! After migration:

• Validate Data Integrity: Randomly check documents to ensure all fields and relationships are correctly migrated.
• Test Queries: Run your most common queries (e.g., "get all orders for a user" or "find orders over $100") to confirm they work as expected in Firestore.
• Create Indexes: Firestore requires composite indexes for queries that filter/sort on multiple fields. You’ll get error messages with links to create missing indexes directly from the Firebase Console.

内容的提问来源于stack exchange，提问作者michoprogrammer