electric-sql / electric-sql/pglite
`useCopy` doesn't support `JSONB` or arrays
- Dominant language
- TypeScript
- Stars
- 16k
- Forks
- 442
- Avg merge
- 20h 19m
- Merged PRs (30d)
- 7
Description
I am unable to build the project after trying for a couple of hours (wasm docker won't build with the scripts, at least on my machine) and it will require a few tests, but the following does what I need it to - it allows for arrays of simple types and `JSONB`. I spent far, far too long trying to get `JSONB[]` to import but in the end abandoned it. `JSONB` now probably has all the methods you might want that previously only had array versions, so maybe left as a challenge to the reader...
I tried to create a nix flake for building the project but wasn't able to get wasm building using the scripts.
```typescript
function toCopyable(value: any, surround = '"') {
// Escape double quotes and wrap in quotes if necessary
if (typeof value === "string" && (value.includes(",") || value.includes('"') || value.includes("\n"))) {
return `${surround}${value.replace(/"/g, '""')}${surround}`;
}
return value === null ? "\\N" : value;
}
function baseValue(value: any) {
return typeof value === "object" && value !== null && !Array.isArray(value) ? JSON.stringify(value) : value;
}
async function applyMessagesToTableWithCopy({
pg,
table,
schema = "public",
messages,
mapColumns,
debug,
}: ApplyMessagesToTableWithCopyOptions) {
if (debug) console.log("applying messages with COPY");
// Map the messages to the data to be inserted
const data: Record[] = messages.map((message) =>
mapColumns ? doMapColumns(mapColumns, message) : message.value,
);
// Get column names from the first message
const columns = Object.keys(data[0]);
// Create CSV data
const csvData = data
.map((message) => {
return columns
.map((column) => {
const value = baseValue(message[column]);
if (Array.isArray(value)) {
const vals: any[] = [];
let hasObjects = false;
for (const val of value) {
vals.push(toCopyable(baseValue(val), ""));
if (typeof val === "object") {
hasObjects = true;
}
}
return `"${hasObjects ? "[" : "{"}${vals.join(",")}${hasObjects ? "]" : "}"}"`;
}
return toCopyable(value);
})
.join(",");
})
.join("\n");
const csvBlob = new Blob([csvData], { type: "text/csv" });
const sql = `
COPY "${schema}"."${table}" (${columns.map((c) => `"${c}"`).join(", ")})
FROM '/dev/blob'
WITH (FORMAT csv, NULL '\\N')
`;
// Perform COPY FROM
await pg.query(sql, [], {
blob: csvBlob,
});
if (debug) console.log(`Inserted ${messages.length} rows using COPY`);
}
```
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.