Insert millions of new rows to the database

[micgerber]

I have to parse a file and insert each line into the database (Oracle XE).
I have created the following Model :

import { DateTime } from 'luxon'
import { BaseModel, column } from '@ioc:Adonis/Lucid/Orm'
export default class Import extends BaseModel {
  public static table = 'import'

  @colum({ isPrimary: true })
  public id: number

  @column()
  public dst_id: number

  @column()
  public status: string
  ...

  public static async createFromJSON(json, userId: number) {
    await Import.create({
      dst_id: userId,
      status: json.status,
      ...
    })
  }
}

I use Import.createFromJSON() in this class :

import Import from "App/Models/Import";

export class CSV {
    public static parse(filePath: string, userId: number) {
        let lineNumber = 0
        const start = new Date().getTime();
        const csv = require('csv');
        const fs = require('fs');

        return new Promise(function(resolve, reject){
            fs.createReadStream(filePath)
                .pipe(csv.parse({ columns: true, relax_column_count: true, skipEmptyLines: true, relax_quotes: true, delimiter: '\t' })
                .on('data', (data) => {
                    Import.createFromJSON(data, userId)
                    lineNumber++
                })
                .on('end', async () => {
                    console.log('2. lineNumber : ' + lineNumber)
                    let elapsed = new Date().getTime() - start;
                    console.log('3. elapsed : ' + elapsed/1000 + 's')
                    resolve('end')
                })
                .on('error', reject))
        })
    }
}

I have different files and with a 9000 line file everything goes smoothly.
But in the worse case scenario, I have a 8 millions lines file...

It ends after about 5000 lines with this error:

<--- Last few GCs --->

[6524:0000022B8A42C660]    70236 ms: Scavenge 4067.4 (4140.5) -> 4066.5 (4140.5) MB, 6.3 / 0.0 ms  (average mu = 0.257, current mu = 0.003) allocation failure
[6524:0000022B8A42C660]    70243 ms: Scavenge 4067.4 (4140.5) -> 4066.7 (4142.0) MB, 5.5 / 0.0 ms  (average mu = 0.257, current mu = 0.003) allocation failure
[6524:0000022B8A42C660]    72627 ms: Mark-sweep 4068.3 (4142.0) -> 4066.6 (4146.0) MB, 2382.0 / 0.0 ms  (average mu = 0.141, current mu = 0.011) allocation failure scavenge might not succeed


<--- JS stacktrace --->

FATAL ERROR: Reached heap limit Allocation failed - JavaScript heap out of memory
 1: 00007FF6C7D47B7F v8::internal::CodeObjectRegistry::~CodeObjectRegistry+114079
 2: 00007FF6C7CD4546 DSA_meth_get_flags+65542
 3: 00007FF6C7CD53FD node::OnFatalError+301
 4: 00007FF6C860B29E v8::Isolate::ReportExternalAllocationLimitReached+94
 5: 00007FF6C85F587D v8::SharedArrayBuffer::Externalize+781
 6: 00007FF6C8498C4C v8::internal::Heap::EphemeronKeyWriteBarrierFromCode+1468
 7: 00007FF6C8495D64 v8::internal::Heap::CollectGarbage+4244
 8: 00007FF6C84936E0 v8::internal::Heap::AllocateExternalBackingStore+2000
 9: 00007FF6C84B13F0 v8::internal::FreeListManyCached::Reset+1408
10: 00007FF6C84B1AA5 v8::internal::Factory::AllocateRaw+37
11: 00007FF6C84C3A4E v8::internal::FactoryBase<v8::internal::Factory>::AllocateRawArray+46
12: 00007FF6C84C668A v8::internal::FactoryBase<v8::internal::Factory>::NewFixedArrayWithFiller+74
13: 00007FF6C84C68E3 v8::internal::FactoryBase<v8::internal::Factory>::NewFixedArrayWithMap+35
14: 00007FF6C82CCF56 v8::internal::HashTable<v8::internal::NameDictionary,v8::internal::NameDictionaryShape>::EnsureCapacity<v8::internal::Isolate>+246
15: 00007FF6C82CACFA v8::internal::Dictionary<v8::internal::NameDictionary,v8::internal::NameDictionaryShape>::Add<v8::internal::Isolate>+58
16: 00007FF6C82D2F26 v8::internal::BaseNameDictionary<v8::internal::NameDictionary,v8::internal::NameDictionaryShape>::Add+118
17: 00007FF6C81DF948 v8::internal::Runtime::GetObjectProperty+1720
18: 00007FF6C8698F91 v8::internal::SetupIsolateDelegate::SetupHeap+494417
19: 0000022B8C4DE214
[ warn ]  Underlying HTTP server died with "134 code"

In Import.createFromJSON() if I replace :

await Import.create({

with (without await at the beginning of the line) :

Database.insertQuery().table(Import.table).insert({

it doesn't crash but there is no rows in the database.

I have tried many possibilities :

• await Import create
• Import create
• Database.insertQuery().table(Import.table).insert
• Database.table(Import.table).insert

without any good conclusion.

By the way I tried to change --max-old-space-size in package.json :

"dev": "node --max-old-space-size=8192 ace serve --watch"

and it ends with the same error.

Thank you in advance for your help.