mongodbToPostgresql.js

const fs = require('fs').promises;
const mongoose = require('mongoose');


const files = {
  dump: '/Users/belchior/postgresql/data/dump-postgres/dump.sql',
  languages: '/Users/belchior/Downloads/dump-postgres/languages.sql',
  licenses: '/Users/belchior/Downloads/dump-postgres/licenses.sql',
  organizations: '/Users/belchior/Downloads/dump-postgres/organizations.sql',
  organizations_users: '/Users/belchior/Downloads/dump-postgres/organizations_users.sql',
  repositories: '/Users/belchior/Downloads/dump-postgres/repositories.sql',
  users: '/Users/belchior/Downloads/dump-postgres/users.sql',
  users_followers: '/Users/belchior/Downloads/dump-postgres/users_followers.sql',
  users_following: '/Users/belchior/Downloads/dump-postgres/users_following.sql',
  users_starred_repositories: '/Users/belchior/Downloads/dump-postgres/users_starred_repositories.sql',
};

const tables = {
  languages: `
    CREATE TABLE IF NOT EXISTS languages (
      language_color    VARCHAR(7) check(length(language_color) >= 4) NOT NULL,
      language_name     VARCHAR(120),
      PRIMARY KEY(language_name)
    );
  `,
  licenses: `
    CREATE TABLE IF NOT EXISTS licenses (
      license_name     VARCHAR(120),
      PRIMARY KEY(license_name)
    );
  `,
  organizations: `
    CREATE TABLE IF NOT EXISTS organizations (
      __typename        CHAR(12) NOT NULL DEFAULT 'Organization',
      avatar_url        VARCHAR(500) NOT NULL,
      created_at        TIMESTAMP(3) WITH TIME ZONE NOT NULL DEFAULT now(),
      description       VARCHAR(500),
      email             VARCHAR(120) check(length(email) >= 3),
      id                UUID DEFAULT uuid_generate_v4(),
      location          VARCHAR(120),
      login             VARCHAR(120) NOT NULL UNIQUE,
      name              VARCHAR(120),
      url               VARCHAR(500) check(length(url) >= 5) NOT NULL,
      website_url       VARCHAR(500) check(length(website_url) >= 5),
      PRIMARY KEY(id)
    );
  `,
  organizations_users: `
    CREATE TABLE IF NOT EXISTS organizations_users (
      created_at        TIMESTAMP(3) WITH TIME ZONE NOT NULL DEFAULT now(),
      organization_login VARCHAR(120) REFERENCES organizations(login),
      user_login         VARCHAR(120) REFERENCES users(login),
      PRIMARY KEY(organization_login, user_login)
    );
  `,
  repositories: `
    CREATE TABLE IF NOT EXISTS repositories (
      __typename        CHAR(10) NOT NULL DEFAULT 'Repository',
      created_at        TIMESTAMP(3) WITH TIME ZONE NOT NULL DEFAULT NOW(),
      description       VARCHAR(500),
      fork_count        INTEGER NOT NULL CHECK(fork_count >= 0),
      id                UUID DEFAULT uuid_generate_v4(),
      license_name      VARCHAR(120) REFERENCES licenses(license_name),
      owner_login       VARCHAR(120) NOT NULL,
      owner_ref         VARCHAR(40) NOT NULL,
      language_name     VARCHAR(120) REFERENCES languages(language_name),
      name              VARCHAR(120) NOT NULL UNIQUE,
      url               VARCHAR(500) NOT NULL CHECK(LENGTH(url) >= 5),
      PRIMARY KEY(id)
    );
  `,
  users: `
    CREATE TABLE IF NOT EXISTS users (
      __typename        CHAR(4) NOT NULL DEFAULT 'User',
      avatar_url        VARCHAR(500) NOT NULL,
      bio               VARCHAR(500),
      company           VARCHAR(120),
      created_at        TIMESTAMP(3) WITH TIME ZONE NOT NULL DEFAULT now(),
      email             VARCHAR(120) check(length(email) >= 3) NOT NULL,
      id                UUID DEFAULT uuid_generate_v4(),
      location          VARCHAR(120),
      login             VARCHAR(120) NOT NULL UNIQUE,
      name              VARCHAR(120),
      url               VARCHAR(500) check(length(url) >= 5) NOT NULL,
      website_url       VARCHAR(500) check(length(website_url) >= 5),
      PRIMARY KEY(id)
    );
  `,
  users_followers: `
    CREATE TABLE IF NOT EXISTS users_followers (
      created_at        TIMESTAMP(3) WITH TIME ZONE NOT NULL DEFAULT now(),
      follower_login    VARCHAR(120) REFERENCES users(login),
      user_login        VARCHAR(120) REFERENCES users(login),
      PRIMARY KEY(user_login, follower_login)
    );
  `,
  users_following: `
    CREATE TABLE IF NOT EXISTS users_following (
      created_at        TIMESTAMP(3) WITH TIME ZONE NOT NULL DEFAULT now(),
      following_login   VARCHAR(120) REFERENCES users(login),
      user_login        VARCHAR(120) REFERENCES users(login),
      PRIMARY KEY(user_login, following_login)
    );
  `,
  users_starred_repositories: `
    CREATE TABLE IF NOT EXISTS users_starred_repositories (
      created_at        TIMESTAMP(3) WITH TIME ZONE NOT NULL DEFAULT now(),
      repository_name   VARCHAR(120) REFERENCES repositories(name),
      user_login        VARCHAR(120) REFERENCES users(login),
      PRIMARY KEY(user_login, repository_name)
    );
  `,
};


const entries = data => {
  const keys = Object.keys(data).join(', ');
  const values = JSON.stringify(
    Object.values(data),
    (key, value) => (typeof value === 'string' ? `%${value}%` : value)
  )
    .replace(/^\[/, '')
    .replace(/\]$/, '')
    .replace(/[']/g, "''") // eslint-disable-line
    .replace(/"%|%"/g, "'"); // eslint-disable-line
  return [keys, values];
};

const writeUsers = async db => {
  const stages = [
    { $project: {
      _id: 0,
      __typename: 1,
      avatar_url: '$avatarUrl',
      bio: 1,
      company: 1,
      email: 1,
      location: 1,
      login: 1,
      name: 1,
      url: 1,
      website_url: '$websiteUrl',
    } },
  ];
  try {
    const result = await db.collection('users').aggregate(stages).toArray();
    const insertList = result.map((user) => {
      const [keys, values] = entries(user);
      return `INSERT INTO users (${keys}) VALUES (${values});`;
    });
    const data = insertList.join('\n');

    await fs.writeFile(files.users, tables.users);
    await fs.appendFile(files.users, data);
    console.log(`- users wrote at: ${files.users}`);
  } catch (error) {
    console.error(error);
  }
};

const writeUserFollowers = async db => {
  const stages = [
    { $match:{ followers: { $ne: [] } } },
    { $project:{ _id: 0, login: 1, followers: 1, } },
    { $unwind: '$followers' },
    { $lookup: { from: 'users', localField: 'followers._id', foreignField: '_id', as: 'user' } },
    { $project: {
      user_login: '$login',
      follower_login: { $reduce: { input: '$user', initialValue: '', in: '$$this.login' } }
    } },
    { $match:{ follower_login: { $ne: '' } } },
  ];
  try {
    const result = await db.collection('users').aggregate(stages).toArray();
    const insertList = result.map((user) => {
      const [keys, values] = entries(user);
      return `INSERT INTO users_followers (${keys}) VALUES (${values});`;
    });
    const data = insertList.join('\n');

    await fs.writeFile(files.users_followers, tables.users_followers);
    await fs.appendFile(files.users_followers, data);
    console.log(`- users_followers wrote at: ${files.users_followers}`);
  } catch (error) {
    console.error(error);
  }
};

const writeUserFollowing = async db => {
  const stages = [
    { $match:{ following: { $ne: [] } } },
    { $project:{ _id: 0, login: 1, following: 1, } },
    { $unwind: '$following' },
    { $lookup: { from: 'users', localField: 'following._id', foreignField: '_id', as: 'user' } },
    { $project: {
      user_login: '$login',
      following_login: { $reduce: { input: '$user', initialValue: '', in: '$$this.login' } }
    } },
    { $match:{ following_login: { $ne: '' } } },
  ];
  try {
    const result = await db.collection('users').aggregate(stages).toArray();
    const insertList = result.map((user) => {
      const [keys, values] = entries(user);
      return `INSERT INTO users_following (${keys}) VALUES (${values});`;
    });
    const data = insertList.join('\n');

    await fs.writeFile(files.users_following, tables.users_following);
    await fs.appendFile(files.users_following, data);
    console.log(`- users_following wrote at: ${files.users_following}`);
  } catch (error) {
    console.error(error);
  }
};

const writeOrganizations = async db => {
  const stages = [
    { $project: {
      _id: 0,
      __typename: 1,
      avatar_url: '$avatarUrl',
      description: 1,
      email: 1,
      location: 1,
      login: 1,
      name: 1,
      url: 1,
      website_url: '$websiteUrl',
    } },
  ];
  try {
    const result = await db.collection('organizations').aggregate(stages).toArray();
    const insertList = result.map(org => {
      const [keys, values] = entries(org);
      return `INSERT INTO organizations (${keys}) VALUES (${values});`;
    });
    const data = insertList.join('\n');

    await fs.writeFile(files.organizations, tables.organizations);
    await fs.appendFile(files.organizations, data);
    console.log(`- organizations wrote at: ${files.organizations}`);
  } catch (error) {
    console.error(error);
  }
};

const writeOrganizationsUsers = async db => {
  const stages = [
    { $unwind: '$people' },
    { $lookup: { from: 'users', localField: 'people._id', foreignField: '_id', as: 'user' } },
    { $project: {
      _id: 0,
      organization_login: '$login',
      user_login: { $reduce: { input: '$user', initialValue: '', in: '$$this.login' } },
    } },
    { $sort: { organization_login: 1 } },
  ];
  try {
    const result = await db.collection('organizations').aggregate(stages).toArray();
    const insertList = result.map(org => {
      const [keys, values] = entries(org);
      return `INSERT INTO organizations_users (${keys}) VALUES (${values});`;
    });
    const data = insertList.join('\n');

    await fs.writeFile(files.organizations_users, tables.organizations_users);
    await fs.appendFile(files.organizations_users, data);
    console.log(`- organizations_users wrote at: ${files.organizations}`);
  } catch (error) {
    console.error(error);
  }
};

const writeLicenses = async db => {
  const stages = [
    { $match: { licenseInfo: { $ne: null } } },
    { $replaceRoot: { newRoot: '$licenseInfo' } },
    { $group: { _id: '$name' } },
    { $project: { _id: 0, license_name: '$_id' } },
  ];
  try {
    const result = await db.collection('repositories').aggregate(stages).toArray();
    const insertList = result.map(repo => {
      const [keys, values] = entries(repo);
      return `INSERT INTO licenses (${keys}) VALUES (${values});`;
    });
    const data = insertList.join('\n');

    await fs.writeFile(files.licenses, tables.licenses);
    await fs.appendFile(files.licenses, data);
    console.log(`- licenses wrote at: ${files.licenses}`);
  } catch (error) {
    console.error(error);
  }
};

const writeLanguages = async db => {
  const stages = [
    { $match: { primaryLanguage: { $ne: null } } },
    { $replaceRoot: { newRoot: '$primaryLanguage' } },
    { $group: { _id: { language_name: '$name', language_color: '$color' } } },
    { $replaceRoot: { newRoot: '$_id' } },
  ];
  try {
    const result = await db.collection('repositories').aggregate(stages).toArray();
    const insertList = result.map(repo => {
      const [keys, values] = entries(repo);
      return `INSERT INTO languages (${keys}) VALUES (${values});`;
    });
    const data = insertList.join('\n');

    await fs.writeFile(files.languages, tables.languages);
    await fs.appendFile(files.languages, data);
    console.log(`- languages wrote at: ${files.languages}`);
  } catch (error) {
    console.error(error);
  }
};

const writeRepositories = async db => {
  const stages = [
    { $facet: {
      users: [
        { $match: { 'owner.ref': 'users' } },
        { $lookup: { from: 'users', localField: 'owner._id', foreignField: '_id', as: 'item', } },
        { $project: {
          _id: 0,
          __typename: 1,
          description: 1,
          fork_count: '$forkCount',
          license_name: '$licenseInfo.name',
          language_name: '$primaryLanguage.name',
          owner_login: { $reduce: { input: '$item', initialValue: '', in: '$$this.login' } },
          owner_ref: '$owner.ref',
          name: 1,
          url: 1,
        } },
      ],
      organizations: [
        { $match: { 'owner.ref': 'organizations' } },
        { $lookup: { from: 'organizations', localField: 'owner._id', foreignField: '_id', as: 'item', } },
        { $project: {
          _id: 0,
          __typename: 1,
          description: 1,
          fork_count: '$forkCount',
          license_name: '$licenseInfo.name',
          language_name: '$primaryLanguage.name',
          owner_login: { $reduce: { input: '$item', initialValue: '', in: '$$this.login' } },
          owner_ref: '$owner.ref',
          name: 1,
          url: 1,
        } },
      ]
    } },
    { $project: { items: { $concatArrays: [ '$users', '$organizations' ] } } },
    { $unwind: '$items' },
    { $replaceWith: '$items' },
  ];
  try {
    const result = await db.collection('repositories').aggregate(stages).toArray();
    const insertList = result.map(repo => {
      const [keys, values] = entries(repo);
      return `INSERT INTO repositories (${keys}) VALUES (${values});`;
    });
    const data = insertList.join('\n');

    await fs.writeFile(files.repositories, tables.repositories);
    await fs.appendFile(files.repositories, data);
    console.log(`- repositories wrote at: ${files.repositories}`);
  } catch (error) {
    console.error(error);
  }
};

const writeUsersStarredRepositories = async db => {
  const stages = [
    { $match: { starredRepositories: { $exists: 1 } } },
    { $unwind: '$starredRepositories' },
    { $lookup: { from: 'repositories', localField: 'starredRepositories._id', foreignField: '_id', as: 'repo', } },
    { $project: {
      _id: 0,
      user_login: '$login',
      repository_name: { $reduce: { input: '$repo', initialValue: '', in: '$$this.name' } },
    } },
  ];
  try {
    const result = await db.collection('users').aggregate(stages).toArray();
    const insertList = result.map(repo => {
      const [keys, values] = entries(repo);
      return `INSERT INTO users_starred_repositories (${keys}) VALUES (${values});`;
    });
    const data = insertList.join('\n');

    await fs.writeFile(files.users_starred_repositories, tables.users_starred_repositories);
    await fs.appendFile(files.users_starred_repositories, data);
    console.log(`- users_starred_repositories wrote at: ${files.users_starred_repositories}`);
  } catch (error) {
    console.error(error);
  }
};

const writeDump = async () => {
  try {
    await fs.writeFile(files.dump, 'CREATE EXTENSION IF NOT EXISTS "uuid-ossp";');
    await fs.appendFile(files.dump, await fs.readFile(files.users, { encoding: 'utf8' }));
    await fs.appendFile(files.dump, await fs.readFile(files.users_followers, { encoding: 'utf8' }));
    await fs.appendFile(files.dump, await fs.readFile(files.users_following, { encoding: 'utf8' }));
    await fs.appendFile(files.dump, await fs.readFile(files.organizations, { encoding: 'utf8' }));
    await fs.appendFile(files.dump, await fs.readFile(files.organizations_users, { encoding: 'utf8' }));
    await fs.appendFile(files.dump, await fs.readFile(files.licenses, { encoding: 'utf8' }));
    await fs.appendFile(files.dump, await fs.readFile(files.languages, { encoding: 'utf8' }));
    await fs.appendFile(files.dump, await fs.readFile(files.repositories, { encoding: 'utf8' }));
    await fs.appendFile(files.dump, await fs.readFile(files.users_starred_repositories, { encoding: 'utf8' }));

    console.log(`- dump wrote at: ${files.dump}`);
  } catch (error) {
    console.error(error);
  }
};

const startMigration = async () => {
  const db = mongoose.connection.db;
  console.time();
  await Promise.allSettled([
    writeLanguages(db),
    writeLicenses(db),
    writeOrganizations(db),
    writeOrganizationsUsers(db),
    writeRepositories(db),
    writeUserFollowers(db),
    writeUserFollowing(db),
    writeUsers(db),
    writeUsersStarredRepositories(db),
  ]);
  await writeDump();
  console.log('finished');
  console.timeEnd();
  process.exit(0);
};

const DATABASE_URL = 'mongodb://localhost:27017/learning-graphql';
const connectionOptions = {
  useNewUrlParser: true,
  useFindAndModify: false,
  useCreateIndex: true,
  useUnifiedTopology: true,
};
mongoose.connect(DATABASE_URL, connectionOptions);
mongoose.connection.once('open', startMigration);
mongoose.connection.on('error', console.error.bind(console, 'connection error:'));