Skip to main content

Database Load

Getting Started​

SQL DiagramSQL Diagram

this example will be using SQL however the DB language is irrelevant, all DB loads will follow this same pattern

For this example we will assume a Book always has one Publisher and one Author

We will be generating mock data utilizing faker-js and writing the generated data to a database

Configuring Data​

In this example we have three types Publisher, Author and Book

type Publisher = {
  id: string
  name: string
};

type Author = {
  id: string
  first_name: string
  last_name: string
};

type Book = {
  id: string
  publisher_id: string
  author_id: string
  name: string
};

Here are our data generators utilizing faker-js

/generators.js
import { faker } from "@faker-js/faker";
  
export const generateMockPublisher = () => ({
  id: faker.string.uuid(),
  name: faker.company.name()
});

export const generateMockAuthor = () => ({
  id: faker.string.uuid(),
  first_name: faker.person.firstName(),
  last_name: faker.person.lastName()
});

export const generateMockBook = () => ({
  id: faker.string.uuid(),
  publisher_id: faker.string.uuid(),
  author_id: faker.string.uuid(),
  name: faker.company.buzzPhrase()
});

[mocq] configuration

/index.js
import { mocq } from 'mocq';
import { faker } from "@faker-js/faker";
import { generateMockPublisher, generateMockAuthor, generateMockBook } from './generators';

const config = {
  publishers: {
    generator: generateMockPublisher,
    count: 3,
  },
  authors: {
    generator: generateMockAuthor,
    count: 5,
  },
  books: {
    generator: generateMockBook,
    count: 10,
    connections: {
      // setting book publisher_id to an ID from a random publisher
      publishers: (publishers)=>({ publisher_id: faker.helpers.arrayElement(publishers).id }),
      // setting book author_id to an ID from a random author
      authors: (authors)=>({ author_id: faker.helpers.arrayElement(authors).id }),
    },
  },
};

const { generate } = mocq(config);

const { data: { publishers, authors, books } } = generate();

Sample Resolved Data

publishers:

[
  {
    id: "59ce7ec3-ec1b-4096-a4f2-0c0da5725a06",
    name: "Upton - Kuhn"
  },
  // ... 2 more entries
]

authors:

[
  {
    id: "dbaa0570-536b-4254-b1d3-f975ef1f4400",
    first_name: "Russell",
    last_name: "Zboncak"
  },
  // ... 4 more entries
]

books:

[
  {
    id: "a24be51e-1a3e-4959-bb8e-9cf6ca5e3c30",
    publisher_id: "59ce7ec3-ec1b-4096-a4f2-0c0da5725a06",
    author_id: "dbaa0570-536b-4254-b1d3-f975ef1f4400",
    name: "engage value-added architectures"
  }
  // ... 9 more entries
]

Database Load Function​

When working with a database it is convention to keep the database connection open and reuse it to reduce overhead

note

In this example we will just be logging the SQL but this getDbConnection function is meant to represent your db language of choice connection or transaction function

/database.js
export const getDbConnection = () => {
  return {
    write(...message: any[]) {
      console.log('[SQL]', ...message)
    },
    close() {
      console.log('[SQL] connection closed ✅')
    },
  };
};

We'll create a Database Load Function that handles pre and post steps and wrap our [mocq] configuration in a function that accepts the database connection

/index.js
import { mocq } from 'mocq';
import { faker } from "@faker-js/faker";
import { generateMockPublisher, generateMockAuthor, generateMockBook } from './generators';
import { getDbConnection } from './database'

/*
  we wrap our mocq usage in a function in order to preserve the
  database connection reducing overhead
*/ 
const seedMockData = async (dbConnection) => {
  const config = {
    publishers: {
      generator: generateMockPublisher,
      count: 3,
      handler: (publishers) => {
        publishers.forEach(x => dbConnection.write(`INSERT INTO publisher VALUES ('${x.id}', '${x.name}');`));
      },
    },
    authors: {
      generator: generateMockAuthor,
      count: 5,
      handler: (authors) => {
        authors.forEach(x => dbConnection.write(`INSERT INTO author VALUES ('${x.id}', '${x.first_name}', '${x.last_name}');`));
      },
    },
    books: {
      generator: generateMockBook,
      count: 10,
      connections: {
        publishers: (publishers)=>({ publisher_id: faker.helpers.arrayElement(publishers).id }),
        authors: (authors)=>({ author_id: faker.helpers.arrayElement(authors).id }),
      },
      handler: (books) => {
        books.forEach(x => dbConnection.write(`INSERT INTO books VALUES ('${x.id}', '${x.name}', '${x.publisher_id}', '${x.author_id}');`));
      },
    },
  };

  const { execute } = mocq(config);
  return execute()
};

/* database load ƒ(x) */
const loadDataBaseWithPseudoRandomData = async () => {
  // pre load step
  const dbConnection = getDbConnection();
  dbConnection.write('CREATE TABLE publishers (id char, name char);');
  dbConnection.write('CREATE TABLE authors (id char, first_name char, last_name char);');
  dbConnection.write('CREATE TABLE books (id char, name char, author_id char, publisher_id char);');
  // mocq executed
  const { data: { publishers, authors, books }} = await seedMockData(dbConnection);
  // post load step
  dbConnection.close();
};

/* execute database load ƒ(x) */
await loadDataBaseWithPseudoRandomData();

Sample Output

[SQL] CREATE TABLE publishers (id char, name char);
[SQL] CREATE TABLE authors (id char, first_name char, last_name char);
[SQL] CREATE TABLE books (id char, name char, author_id char, publisher_id char);
[SQL] INSERT INTO publisher VALUES ('54b1d77c-5c7a-45b1-a59a-da56afec3ab7', 'Schultz - Cassin');
[SQL] INSERT INTO publisher VALUES ('b854fe7f-d26f-47f3-83be-ce6b03be844f', 'Carter Group');
[SQL] INSERT INTO publisher VALUES ('40bb1f15-f01e-42a9-834a-b9ae28dc655d', 'Rath and Sons');
[SQL] INSERT INTO author VALUES ('e72766ee-bb85-4f5d-8986-f0d632416468', 'Deven', 'Williamson');
[SQL] INSERT INTO author VALUES ('5db435c7-d092-4676-bc69-bc10c8618938', 'Korbin', 'Abshire');
[SQL] INSERT INTO author VALUES ('b9a4708b-e82b-467e-b1fa-245c9f9174b3', 'Stan', 'Hudson');
[SQL] INSERT INTO author VALUES ('e9d6035a-8cc6-437d-a860-51b1800855ef', 'Pete', 'Larkin');
[SQL] INSERT INTO author VALUES ('15c665ea-6ae1-4ae5-91aa-58d66aeade86', 'Damaris', 'Swift');
[SQL] INSERT INTO books VALUES ('04be8162-b5cf-41f3-baae-c2211745b974', 'maximize integrated deliverables', '40bb1f15-f01e-42a9-834a-b9ae28dc655d', 'e72766ee-bb85-4f5d-8986-f0d632416468');
[SQL] INSERT INTO books VALUES ('b8b0f4e7-3d3d-471f-9064-fb6e71f3695c', 'evolve value-added applications', '40bb1f15-f01e-42a9-834a-b9ae28dc655d', 'e9d6035a-8cc6-437d-a860-51b1800855ef');
[SQL] INSERT INTO books VALUES ('70bdd155-22fb-40b6-b6eb-2a9dd158ebf4', 'benchmark end-to-end partnerships', '40bb1f15-f01e-42a9-834a-b9ae28dc655d', 'e9d6035a-8cc6-437d-a860-51b1800855ef');
[SQL] INSERT INTO books VALUES ('d918f05f-8ce0-408e-aebc-f00caa8c2fc0', 'e-enable strategic synergies', '40bb1f15-f01e-42a9-834a-b9ae28dc655d', '15c665ea-6ae1-4ae5-91aa-58d66aeade86');
[SQL] INSERT INTO books VALUES ('bc6c70e9-97fa-4c03-906c-991524cf4ff0', 'grow front-end markets', 'b854fe7f-d26f-47f3-83be-ce6b03be844f', 'e9d6035a-8cc6-437d-a860-51b1800855ef');
[SQL] INSERT INTO books VALUES ('be64e9c5-a4c8-4644-b83c-435b7f92a2f6', 'transition leading-edge networks', 'b854fe7f-d26f-47f3-83be-ce6b03be844f', 'e9d6035a-8cc6-437d-a860-51b1800855ef');
[SQL] INSERT INTO books VALUES ('d9afdb08-77e2-44d1-bfc6-e57c94fdc5e8', 'whiteboard wireless interfaces', 'b854fe7f-d26f-47f3-83be-ce6b03be844f', '15c665ea-6ae1-4ae5-91aa-58d66aeade86');
[SQL] INSERT INTO books VALUES ('1dc3ff59-2fc7-4f92-bcd8-e674b3d3a385', 'deliver strategic e-commerce', '54b1d77c-5c7a-45b1-a59a-da56afec3ab7', 'b9a4708b-e82b-467e-b1fa-245c9f9174b3');
[SQL] INSERT INTO books VALUES ('3bd6c787-f977-415d-bbd7-a4cfd6ad5cd8', 'scale frictionless models', 'b854fe7f-d26f-47f3-83be-ce6b03be844f', '5db435c7-d092-4676-bc69-bc10c8618938');
[SQL] INSERT INTO books VALUES ('c86b83d3-2e48-40af-8bee-f8fdda9ace82', 'disintermediate ubiquitous applications', '40bb1f15-f01e-42a9-834a-b9ae28dc655d', 'e9d6035a-8cc6-437d-a860-51b1800855ef');
[SQL] connection closed ✅