Error: The jeep-sqlite element is not present in the DOM when trying to generate migration files via TypeOrm CLI

[KiddoV]

Describe the bug I am trying to create migration files with the TypeOrm CLI:

npx typeorm-ts-node-esm migration:generate src/database/migrations/PostRefactoring -d src/database/dataSource.ts

I got the error:

Error: The jeep-sqlite element is not present in the DOM! Please check the @capacitor-community/sqlite documentation for instructions regarding the  web platform.

I already initialize jeep-sqlite before the app created:

// Initialize jeep-sqlite for the ``Web``
applyPolyfills().then(() => {
    jeepSqlite(window);
});
// Wait for DOMContentLoaded event
window.addEventListener("DOMContentLoaded", async () => {
    // Initialize Capacitor SQLite
    const platform = Capacitor.getPlatform();
    const sqlite: SQLiteConnection = new SQLiteConnection(CapacitorSQLite);

    try {
        if (platform === "web") {
            // Create the 'jeep-sqlite' Stencil component
            const jeepSqliteElement = document.createElement("jeep-sqlite");
            document.body.appendChild(jeepSqliteElement);
            await customElements.whenDefined("jeep-sqlite");

            // Initialize the Web store
            await sqlite.initWebStore();
        }

        // Proceed with your app initialization (Use this without the ``export`` as normal)
        new App({
            target: document.getElementById("app"),
        });

    } catch (err) {
        console.log(`Error: ${err}`);
        throw new Error(`Error: ${err}`);
    }
});

Looks like when using CLI, of course the app haven't created yet so jeep-sqlite will not ready in the dom.

How do I generate migration files using TypeOrm CLI if that is the case then?

Expected behavior Migration files generate without error.

Desktop (please complete the following information):

• OS: Windows 10
• Browser: Edge
• Version: 120.0.2210.133 (Official build) (64-bit)

[jepiqueau]

@KiddoV Finally i will not implementing the code as it does not work for all frameworks i test it with Angular and it does not work see error with Nodejs. Anyhow in your case "typeorm:migration:generate": "npx typeorm-ts-node-esm migration:generate src/database/migrations/PostRefactoring -d src/database/database.ts" was helping to create a 1707070065955-PostRefactoring.ts file automatically but did not do anything on the database s the database is store in the DOM and the typeOrm cli works in Nodejs ouside the DOM so any other typeOrm Cli will never work. Hope this is clear enough. So basically you must create those migration's files by hand.

[KiddoV]

@jepiqueau at least can you show me how you did it so I can implement it in my app?

[jepiqueau]

@KiddoV Well , the modification i did, allows to generate the initial migration, but what happens when you want to generate a new migration by adding some columns in entities, the result, as there is no database stored after the initial migration, is that it restart from scratch so instead of altering the table it will create all the tables from scratch with the new columns. So it is not really what you are looking for. this is inherent to the typeOrm cli which must have access to the database. If i release what i did, developpers will come with a lot of questions or issues as they will forget that only valid to help for the initial migration and not for the ones later when they would like to update their databases. This is the reason why i do not want to release it. Hope you will understand. If you create a small typescript nodejs file using sql.js as driver you will get the initial migration the same way

[KiddoV]

Understood! Thanks for the explanation!

[jepiqueau]

@KiddoV i will come back on this and provide a solution for generating the migrations the initial one and the subsequent ones. Before to generate the subsequent migrations, you will have to save the database from the DOM to the local disk in a specific location,Due to the restrictions of browser-fs-access in the

enum [WellKnownDirectory]{
  ["desktop","documents","downloads","music","pictures","videos"]
}

i have selected documents so the picker form will open on documents and you must go to Documents/CapacitorSQLite/YOUR_APPLICATION_NAME folder to save your database.

i have test it on Ionic7/React Vite and it works fine. Hope you will be glad of this

[KiddoV]

Thanks for the work on this. I will be testing when u have the PR pushed.

[jepiqueau]

@KiddoV this has been published release 5.6.0 look at TypeORM-Usage-From-5.6.0.md. I stop working for to day i will publish a react app to morrow and i will look at Svelte after but there is no reason that it should not work

[KiddoV]

I am getting this error when running migration with the new version 5.6.0:

> npx typeorm-ts-node-esm migration:generate ./src/database/migrations/PostRefactoring -d=./src/database/dataSource.ts

Error during migration generation:
Error: ### open Open: OpenOrCreateDatabase open database failed
    at CapacitorSQLiteWeb.open (...\node_modules\@capacitor-community\sqlite\dist\plugin.cjs.js:1396:23)
    at async SQLiteDBConnection.open (...\node_modules\@capacitor-community\sqlite\dist\plugin.cjs.js:469:13)
    at async CapacitorDriver.createDatabaseConnection (...\node_modules\typeorm\driver\capacitor\CapacitorDriver.js:59:9)  
    at async CapacitorDriver.connect (...\node_modules\typeorm\driver\capacitor\CapacitorDriver.js:26:9)
    at async DataSource.initialize (...\node_modules\typeorm\data-source\DataSource.js:136:9)
    at async Object.handler (...\node_modules\typeorm\commands\MigrationGenerateCommand.js:80:13)

[jepiqueau]

@KiddoV i test on what you send me and it works take care of the script addes to fix the bug in the typeorm driver. I have published a tutorial for typeorm react yesterday on my site. All the typeorm databases folder can be the same for any framework

[KiddoV]

https://stackblitz.com/edit/vitejs-vite-r9rfug?file=package.json

@jepiqueau I got the problem on my test Tackbliz, can you check again to see if I miss anything?

[jepiqueau]

@KiddoV If you look at the documentation TypeORM-Usage-From-5.6.0.md like at mentioned already to you you will see that

• in the datasource the migrationRun must be set to false
• the migration must be run after you initialize the datasource
• you must add a scripts folder in your app and create the script to fix a Bug in the TypeOrm Capacitor Driver then add a postinstall script in the package.json and run it and then it should work

[KiddoV]

Thanks. I will try that.

One question though, for the script to fix the bug in TypeORM Cap Driver, is that script just a temporary thing, will the driver get fix by any chance?

[jepiqueau]

@KiddoV i will certaily step back as developers cannot build anymore apps with Angular framework.

[jepiqueau]

@KiddoV i step back in v5.6.1-1. i may come back on this with a separate package. sorry for this. Hope you will understand

[KiddoV]

All good! I hope it will be easier to implement in the next couple of versions when you got more time to work on this.

In the meantime, you can check this out to see I still got the error after adding those suggestion: https://stackblitz.com/edit/vitejs-vite-r9rfug?file=package.json,src%2Fdatabase%2Fdatabase.ts I ran:

> npm run postinstall
-> got successful msg
> npm run typeorm:migration:generate
-> Still got the Error: ### open Open: OpenOrCreateDatabase open database failed

You mentioned earlier that you can make my example run without problem, so... Can you please modify my Stackblitz example to at least get it running? I would be appreciate it!

Thank you!

[jepiqueau]

@KiddoV i never work with Stackblitz, How do you run npm run typeorm:migration:generate in Stackblitz and how do you run the app after having generate the migration

[KiddoV]

@jepiqueau It very easy and straight forward. The app is automatically run when you open the Stackblitz link I sent. To run command line in terminal you need to add another terminal by clicking the plus icon:

Then you can use terminal as normal:

Or you can just actually do Ctrl + C to stop the main terminal from running the app and type all of necessary commands first before npm run dev to run the app.

[jepiqueau]

@KiddoV do i have to define myself as an editor

[KiddoV]

@jepiqueau sorry, I don't understand what do u mean by that?

[jepiqueau]

@KiddoV i made some modifications

npm i -D copyfiles

modify the scripts in package.json

        "dev": "npm run copy:sql:wasm && vite",
        "copy:sql:wasm": "copyfiles -u 3 node_modules/sql.js/dist/sql-wasm.wasm public/assets",

• delete the migration file under the migrations folder and create a file index.ts containing export {};`` -open the database.ts file and addimport * as migrations from './migrations';and then modifymigrations: migrations,`in the DataSourceOptions.

Now i got

 npm i -D copyfiles

added 11 packages in 5s

65 packages are looking for funding
  run `npm fund` for details

~/projects/vitejs-vite-r9rfug 5s
❯ npm run copy:sql:wasm

> test-app@0.0.0 copy:sql:wasm
> copyfiles -u 3 node_modules/sql.js/dist/sql-wasm.wasm public/assets

~/projects/vitejs-vite-r9rfug
❯ npm run typeorm:migration:generate

> test-app@0.0.0 typeorm:migration:generate
> npx typeorm-ts-node-esm migration:generate src/database/migrations/PostRefactoring -d src/database/database.ts

query: SELECT * FROM "sqlite_master" WHERE "type" = 'table' AND "name" IN ('person','person_group','debt','person_group_people_person')
query: SELECT * FROM "sqlite_master" WHERE "type" = 'index' AND "tbl_name" IN ('person','person_group','debt','person_group_people_person')
query: SELECT * FROM "sqlite_master" WHERE "type" = 'table' AND "name" = 'typeorm_metadata'
creating a new table: person
creating a new table: person_group
creating a new table: debt
creating a new table: person_group_people_person
creating a foreign keys: FK_5ea1d47fdfc1abc79ebf3cfa954, FK_344e6bce05f88795da48637bed8 on table "debt"
creating a foreign keys: FK_49ecd25e65d150a5afe0bfdea69, FK_654192f0e76221e38866ded7a0e on table "person_group_people_person"
Migration /home/projects/vitejs-vite-r9rfug/src/database/migrations/1708619031433-PostRefactoring.ts has been generated successfully.

~/projects/vitejs-vite-r9rfug 6s
❯ ```

- now you can see a 1708619031433-PostRefactoring.ts file created
- then  open the index.ts file under migrations and add 

import {PostRefactoring1708619031433} from './1708619031433-PostRefactoring' ;

and modify the export

export {PostRefactoring1708619031433}; now you will be able to run the migrations inside the

i do not know how to save the modifications as i am not an editor

[jepiqueau]

@KiddoV if you look at it Do you see my modifications?

[jepiqueau]

@KiddoV in the app.svelte you must have this

        //Initialize main app database
        initializeAppDatabase().then((dataSource) => {
            $AppDB = dataSource;
            console.log("Database has been initialized!");
            $AppDB.initialize();
            if($AppDB.isInitialized) {
                $AppDB.runMigrations();
            }
            if (Capacitor.getPlatform() === "web" ) {
                  // here we must save the database to store  
            }

            // $AppDB.synchronize(false);
            // $AppDB.runMigrations({transaction: "all", fake: false});
        }).catch((err) => {
            console.error("Failed to initialize database,", err);
        });

[jepiqueau]

and now you get in the console

Database has been initialized!
typeorm.js?v=8953e149:6409 query:  SELECT * FROM "sqlite_master" WHERE "type" = 'table' AND "name" = 'migrations'
typeorm.js?v=8953e149:6409 query:  SELECT "person"."id" AS "person_id", "person"."firstName" AS "person_firstName", "person"."lastName" AS "person_lastName", "person"."nickName" AS "person_nickName", "person"."phoneNumber" AS "person_phoneNumber", "person"."emailAddr" AS "person_emailAddr", "person"."debtBalance" AS "person_debtBalance" FROM "person" "person"
typeorm.js?v=8953e149:6412 query failed:  SELECT "person"."id" AS "person_id", "person"."firstName" AS "person_firstName", "person"."lastName" AS "person_lastName", "person"."nickName" AS "person_nickName", "person"."phoneNumber" AS "person_phoneNumber", "person"."emailAddr" AS "person_emailAddr", "person"."debtBalance" AS "person_debtBalance" FROM "person" "person"
logError @ typeorm.js?v=8953e149:6412
Show 1 more frame
Show less
typeorm.js?v=8953e149:6412 error:  Error: Query failed: SelectSQL: queryAll: no such table: person
logError @ typeorm.js?v=8953e149:6412
Show 1 more frame
Show less
typeorm.js?v=8953e149:6409 query:  CREATE TABLE "migrations" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "timestamp" bigint NOT NULL, "name" varchar NOT NULL)
typeorm.js?v=8953e149:6409 query:  SELECT * FROM "migrations" "migrations" ORDER BY "id" DESC
typeorm.js?v=8953e149:6418 0 migrations are already loaded in the database.
typeorm.js?v=8953e149:6418 1 migrations were found in the source code.
typeorm.js?v=8953e149:6418 1 migrations are new migrations must be executed.
query:  PRAGMA foreign_keys = OFF
typeorm.js?v=8953e149:6409 query:  BEGIN TRANSACTION
typeorm.js?v=8953e149:6409 query:  CREATE TABLE "person" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "firstName" varchar NOT NULL, "lastName" varchar NOT NULL, "nickName" varchar, "phoneNumber" varchar, "emailAddr" varchar, "debtBalance" decimal NOT NULL DEFAULT (0))
typeorm.js?v=8953e149:6409 query:  CREATE TABLE "person_group" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL)
typeorm.js?v=8953e149:6409 query:  CREATE TABLE "debt" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "dateCreated" datetime NOT NULL DEFAULT (datetime('now')), "title" varchar, "note" varchar, "debtorId" integer, "debtorsId" integer)
typeorm.js?v=8953e149:6409 query:  CREATE TABLE "person_group_people_person" ("personGroupId" integer NOT NULL, "personId" integer NOT NULL, PRIMARY KEY ("personGroupId", "personId"))
typeorm.js?v=8953e149:6409 query:  CREATE INDEX "IDX_49ecd25e65d150a5afe0bfdea6" ON "person_group_people_person" ("personGroupId") 
typeorm.js?v=8953e149:6409 query:  CREATE INDEX "IDX_654192f0e76221e38866ded7a0" ON "person_group_people_person" ("personId") 
typeorm.js?v=8953e149:6409 query:  CREATE TABLE "temporary_debt" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "dateCreated" datetime NOT NULL DEFAULT (datetime('now')), "title" varchar, "note" varchar, "debtorId" integer, "debtorsId" integer, CONSTRAINT "FK_5ea1d47fdfc1abc79ebf3cfa954" FOREIGN KEY ("debtorId") REFERENCES "person" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT "FK_344e6bce05f88795da48637bed8" FOREIGN KEY ("debtorsId") REFERENCES "person_group" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION)
typeorm.js?v=8953e149:6409 query:  INSERT INTO "temporary_debt"("id", "dateCreated", "title", "note", "debtorId", "debtorsId") SELECT "id", "dateCreated", "title", "note", "debtorId", "debtorsId" FROM "debt"
typeorm.js?v=8953e149:6409 query:  DROP TABLE "debt"
query:  ALTER TABLE "temporary_debt" RENAME TO "debt"
typeorm.js?v=8953e149:6409 query:  DROP INDEX "IDX_49ecd25e65d150a5afe0bfdea6"
typeorm.js?v=8953e149:6409 query:  DROP INDEX "IDX_654192f0e76221e38866ded7a0"
typeorm.js?v=8953e149:6409 query:  CREATE TABLE "temporary_person_group_people_person" ("personGroupId" integer NOT NULL, "personId" integer NOT NULL, CONSTRAINT "FK_49ecd25e65d150a5afe0bfdea69" FOREIGN KEY ("personGroupId") REFERENCES "person_group" ("id") ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT "FK_654192f0e76221e38866ded7a0e" FOREIGN KEY ("personId") REFERENCES "person" ("id") ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY ("personGroupId", "personId"))
typeorm.js?v=8953e149:6409 query:  INSERT INTO "temporary_person_group_people_person"("personGroupId", "personId") SELECT "personGroupId", "personId" FROM "person_group_people_person"
typeorm.js?v=8953e149:6409 query:  DROP TABLE "person_group_people_person"
typeorm.js?v=8953e149:6409 query:  ALTER TABLE "temporary_person_group_people_person" RENAME TO "person_group_people_person"
typeorm.js?v=8953e149:6409 query:  CREATE INDEX "IDX_49ecd25e65d150a5afe0bfdea6" ON "person_group_people_person" ("personGroupId") 
typeorm.js?v=8953e149:6409 query:  CREATE INDEX "IDX_654192f0e76221e38866ded7a0" ON "person_group_people_person" ("personId") 
typeorm.js?v=8953e149:6409 query:  INSERT INTO "migrations"("timestamp", "name") VALUES (1708619031433, ?) -- PARAMETERS: ["PostRefactoring1708619031433"]
typeorm.js?v=8953e149:6418 Migration PostRefactoring1708619031433 has been  executed successfully.
typeorm.js?v=8953e149:6409 query:  COMMIT
typeorm.js?v=8953e149:6409 query:  PRAGMA foreign_keys = ON
typeorm.js?v=8953e149:34846 Uncaught (in promise) CannotConnectAlreadyConnectedError: Cannot create a "default" connection because connection to the database already established.
    at DataSource.initialize (typeorm.js?v=8953e149:34846:13)
    at app.svelte:52:20
typeorm.js?v=8953e149:29413 Uncaught (in promise) QueryFailedError: Query failed: SelectSQL: queryAll: no such table: person
    at CapacitorQueryRunner.query (typeorm.js?v=8953e149:29413:13)
    at async _SelectQueryBuilder.loadRawResults (typeorm.js?v=8953e149:13571:21)
    at async _SelectQueryBuilder.executeEntitiesAndRawResults (typeorm.js?v=8953e149:13463:20)
    at async _SelectQueryBuilder.getRawAndEntities (typeorm.js?v=8953e149:12569:23)
    at async _SelectQueryBuilder.getMany (typeorm.js?v=8953e149:12626:21)

you see that the migration has been created now it is up to you to continue ....

[jepiqueau]

@KiddoV i save it modified

[KiddoV]

@jepiqueau Thank you so much for your time on this. I got it working: https://stackblitz.com/edit/vitejs-vite-sjahxd?file=package.json

One more question though. Will this be able to generate the update version of migration when I modify or add more columns? Or it just generate the initial database migration?

[jepiqueau]

@KiddoV i think you did not read the doc that i have added to the release or look at the tutorial i did for React. The answer is yes but you have to store the database in your "Documents/CapacitorSQLite/YOUR_APP_NAME. Anyhow this will not work on future releases starting 5.6.1.1

[KiddoV]

Thanks for clarifying. I actually did read the docs. It's just too many steps to process at once.

[jepiqueau]

@KiddoV 👍i think i will close the issue and may come back on this If i found a solution working for all frameworks.

[KiddoV]

@jepiqueau good to me! Again, thank you for your time on this!