Comments (29)
jepiqueau
commented on June 12, 2024
1
@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.
from sqlite.
jepiqueau
commented on June 12, 2024
1
@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
from sqlite.
jepiqueau
commented on June 12, 2024
1
@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
from sqlite.
jepiqueau
commented on June 12, 2024
1
@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
from sqlite.
jepiqueau
commented on June 12, 2024
1
@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
from sqlite.
jepiqueau
commented on June 12, 2024
1
@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
from sqlite.
jepiqueau
commented on June 12, 2024
1
@KiddoV 👍i think i will close the issue and may come back on this If i found a solution working for all frameworks.
from sqlite.
KiddoV
commented on June 12, 2024
@jepiqueau at least can you show me how you did it so I can implement it in my app?
from sqlite.
KiddoV
commented on June 12, 2024
Understood! Thanks for the explanation!
from sqlite.
KiddoV
commented on June 12, 2024
Thanks for the work on this. I will be testing when u have the PR pushed.
from sqlite.
KiddoV
commented on June 12, 2024
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)
from sqlite.
jepiqueau
commented on June 12, 2024
@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
from sqlite.
KiddoV
commented on June 12, 2024
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?
from sqlite.
KiddoV
commented on June 12, 2024
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?
from sqlite.
jepiqueau
commented on June 12, 2024
@KiddoV i will certaily step back as developers cannot build anymore apps with Angular framework.
from sqlite.
jepiqueau
commented on June 12, 2024
@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
from sqlite.
KiddoV
commented on June 12, 2024
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!
from sqlite.
jepiqueau
commented on June 12, 2024
@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
from sqlite.
KiddoV
commented on June 12, 2024
@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.
from sqlite.
jepiqueau
commented on June 12, 2024
@KiddoV do i have to define myself as an editor
from sqlite.
KiddoV
commented on June 12, 2024
@jepiqueau sorry, I don't understand what do u mean by that?
from sqlite.
jepiqueau
commented on June 12, 2024
@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.tscontainingexport {};`` -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
> [email protected] 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
> [email protected] 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
from sqlite.
jepiqueau
commented on June 12, 2024
@KiddoV if you look at it Do you see my modifications?
from sqlite.
jepiqueau
commented on June 12, 2024
@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);
});from sqlite.
jepiqueau
commented on June 12, 2024
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 ....
from sqlite.
jepiqueau
commented on June 12, 2024
from sqlite.
KiddoV
commented on June 12, 2024
@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?
from sqlite.
KiddoV
commented on June 12, 2024
Thanks for clarifying. I actually did read the docs. It's just too many steps to process at once.
from sqlite.
KiddoV
commented on June 12, 2024
@jepiqueau good to me! Again, thank you for your time on this!
from sqlite.
Related Issues (20)
- Bug(Android): Calling `run` method with `BEGIN` statement throws StringOutOfBoundIndexException HOT 16
- WAL mode with typeorm stopped working from version 0.3.18 HOT 9
- Is this plugin still maintained in 2024? HOT 2
- "CapacitorSQLite" plugin is not implemented on ios HOT 4
- ionic7-angular-sqlite-app tutorial ionic.config.json type angular-standalone doesn't exist HOT 1
- ionic7-angular-sqlite-app tutorial fails on v5.7.1 HOT 6
- GetFromHTTPRequest not working HOT 3
- Multi Row Statement does not escape characters HOT 4
- Multiple Row Statement Values case insensitive HOT 3
- Multirow statements inserts wrong semicolon HOT 2
- foreign key ON DELETE CASCADE HOT 1
- Create CapacitorJS v6 package compatibility HOT 41
- ExecuteTransaction with values throws exception for statements performing no actual changes HOT 5
- Memory leak using .saveToStore() HOT 18
- Migration not working on iOS HOT 3
- executeSet not inserting rows HOT 8
- Multi Value Statement error with spaces inbetween ? HOT 1
- Multi row statement should fail if no values are replaced HOT 5
- Can I use this plugin inside the background runner capacitor plugin? HOT 2
- Update Capacitor dependencies to version >6 HOT 2
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from sqlite.