Create a local copy of the Evidence WASM DuckDB database

createLocalDuckDB.js

/** 
 * Reads the manifest file and creates a schema and table for each parquet file in the manifest.
 * It will create a DB called `local.duckdb` in the root of the project.
 *  
 * Usage:
 * 1. Copy this file to the root of your project Evidence project.
 * 2. `npm run sources`
 * 3. `node createLocalDuckDB.js`
 */

import path from 'path';
import { Database } from "duckdb-async";
import { rm } from 'fs';
import fs from 'fs';

// Path to your manifest file
const filePath = '.evidence/template/static/data/manifest.json';


async function getSourcesFromManifest() {
	return new Promise((resolve, reject) => {
		fs.readFile(filePath, 'utf8', (err, data) => {
			if (err) {
				console.error('Error reading file:', err);
				reject(err);
				return;
			}

			// Parse Manifest file
			try {
				const jsonObject = JSON.parse(data);
				const sources = jsonObject.renderedFiles;
				resolve(sources);
			} catch (err) {
				console.error('Error parsing Manifest:', err);
				reject(err);
			}
		});
	});
}

export async function createTablesFromParquet(sources) {

	for (const source in sources) {
		// console.log(`Creating schema ${source}`);
		connection.all(`CREATE SCHEMA IF NOT EXISTS "${source}";`);
		for (const url of sources[source]) {
			// console.log(`Using url ${url}`);
			const table = url.split(path.sep).at(-1).slice(0, -'.parquet'.length);
			// console.log(`Creating table ${table}`);
			const file_name = `${source}_${table}.parquet`;
			// console.log(`Using file ${file_name}`);
			connection.all(
				`CREATE OR REPLACE TABLE "${source}"."${table}" AS (SELECT * FROM read_parquet('.evidence/template/${url}'));`
			);
		}
	}
}

const sources = await getSourcesFromManifest();


// Delete any existing database if it exists
fs.access("local.duckdb", fs.F_OK, (err) => {
	if (err) {
		return;
	}
	rm("local.duckdb", (err) => {
		if (err) {
			console.error(`Error deleting database: ${err}`);
			return;
		}
	});
});

// Create a connection to a local database at the root of the project
const connection = await Database.create("local.duckdb");

// Create a schema and table for each parquet file in the manifest
createTablesFromParquet(sources);