forked from DhanushNehru/ToolJet
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathpopulate-sample-db.ts
More file actions
162 lines (138 loc) 路 5.08 KB
/
Copy pathpopulate-sample-db.ts
File metadata and controls
162 lines (138 loc) 路 5.08 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
import * as fs from 'fs';
import * as path from 'path';
import { Client } from 'pg';
import { buildAndValidateDatabaseConfig } from './database-config-utils';
// PostgreSQL connection configuration
function createPGconnection(envVars): Client {
return new Client({
user: envVars.SAMPLE_PG_DB_USER,
host: envVars.SAMPLE_PG_DB_HOST,
database: envVars.SAMPLE_DB,
password: envVars.SAMPLE_PG_DB_PASS,
port: envVars.SAMPLE_PG_DB_PORT,
});
}
const folderPath = path.join(__dirname, '../src/assets/sample-data-json-files');
// Replace 'your_table_name' with the desired table name in your PostgreSQL database
// Read Excel file
async function connectToPostgreSQL(client) {
return new Promise<void>((resolve, reject) => {
client.connect((err) => {
if (err) {
console.error('Error connecting to PostgreSQL:', err);
reject(err); // Reject the promise if there's an error
} else {
console.log('Connected to PostgreSQL');
resolve(); // Resolve the promise if connected successfully
}
});
});
}
export async function populateSampleData(envVars) {
const client = createPGconnection(envVars);
try {
//Checking postgres connection
await connectToPostgreSQL(client);
// Read files from the folder asynchronously
const files = await fs.promises.readdir(folderPath);
for (const file of files) {
if (file.startsWith('.')) continue; // Skip hidden files
const filePath = path.join(folderPath, file);
// Read file contents asynchronously
const jsonString = await fs.promises.readFile(filePath, 'utf-8');
const parsedData = JSON.parse(jsonString);
const tableName = file
.replace(/\.json$/, '')
.replace(/[^\w]/g, '')
.replace(/\s+/g, '_')
.toLowerCase();
// Drop table if it exists
await dropTable(client, tableName);
// Create table query
await createTable(client, tableName, parsedData);
// Insert data into the table
await insertData(client, tableName, parsedData);
console.log(`Data populated for table: ${tableName}`);
}
} catch (error) {
console.error('Error populating sample data:', error);
} finally {
// Close the database connection
await client.end();
}
}
// Drop PostgreSQL table if it exists
async function dropTable(client: Client, tableName) {
const dropTableQuery = `DROP TABLE IF EXISTS ${tableName};`;
await client.query(dropTableQuery);
}
// Create PostgreSQL table
async function createTable(client, tableName, parsedData) {
let createTableQuery = `CREATE TABLE IF NOT EXISTS ${tableName} (`;
Object.keys(parsedData[0]).forEach((header) => {
const columnName = header;
const columnValues = parsedData.map((row) => row[header]);
let dataType;
// Analyze the data to determine the appropriate data type
if (columnValues.every((value) => typeof value === 'string' && (value === null || value.length <= 255))) {
dataType = 'VARCHAR';
} else if (columnValues.every((value) => typeof value === 'number' || value === null)) {
dataType = 'NUMERIC';
} else if (columnValues.every((value) => value instanceof Date || value === null || !isNaN(Date.parse(value)))) {
dataType = 'DATE';
} else {
dataType = 'VARCHAR(255)'; // Default to VARCHAR if data type is uncertain
}
createTableQuery += `${columnName
.trim()
.replace(/[^\w]/g, '_')
.replace(/\s+/g, '_')
.replace(/_+/g, '_')
.replace(/^_+|_+$/g, '')
.toLowerCase()} ${dataType}, `;
});
createTableQuery = createTableQuery.slice(0, -2); // Remove the last comma
createTableQuery += ' );';
console.log('creating table');
await client.query(createTableQuery);
}
export async function runPopulateScript() {
const { value: envVars, error } = buildAndValidateDatabaseConfig();
if (error) return;
populateSampleData(envVars);
}
// Insert data into PostgreSQL table
async function insertData(client: Client, tableName: string, data) {
const keys = Object.keys(data[0]);
const columns = keys
.map((key) =>
key
.trim()
.replace(/[^\w]/g, '_')
.replace(/\s+/g, '_')
.replace(/_+/g, '_')
.replace(/^_+|_+$/g, '')
.toLowerCase()
)
.join(', ');
const rows = data.map((row) => keys.map((key) => row[key]));
const insertQuery = `INSERT INTO ${tableName} (${columns}) VALUES ${rows
.map(
(row) =>
`(${row
.map((key, index) => {
if (key === null || key === undefined) {
return 'NULL';
} else if (typeof key === 'string') {
return `'${key.replace(/['",]/g, ' ')}'`; // Replace special characters only for strings
} else if (typeof key === 'number' && !Number.isInteger(key)) {
// For decimal numbers, convert them to string with proper decimal separator
return key.toString().replace(',', '.');
}
return key; // For non-string, non-decimal values, leave them unchanged
})
.join(', ')})`
)
.join(', ')}`;
await client.query(insertQuery);
}