Using capacitor-sqlite with TypeORM and Ionic Vue

[nirajhinge]

I tried following (this guide)[https://github.com/capacitor-community/sqlite/blob/master/docs/Ionic-Vue-Usage.md#vue-sqlite-hook-use-in-components] to use the @capacitor-community/sqlite@next along with (this guide)[https://github.com/capacitor-community/sqlite/blob/master/docs/TypeORM-Usage.md] for setting it up with TypeORM so that I can use both and haven't managed to get it setup as the guides seem to assume a few things and would be helpful if someone could add some pointers. Specifically I am not understanding the following:

1. On the Ionic/Vue, it has the following import which I presume we will have to create our own as I managed to do that: import { useState } from '@/composables/state';
2. When using the TypeORM guide, it imports the createConnection from TypeORM. In the Ionic/Vue guide, it uses it from the useSQLite hook. So are we supposed to simply replace the connection from the hook to use the TypeORM createConnection
3. In the context of Ionic/Vue with TypeORM, do we still have to provide the name of the database and where will the config be setup? I presume main.js?
4. On the Ionic Vue guide, there are example components that seem to use a few utilities functions (import { createTablesNoEncryption, importTwoUsers, dropTablesTablesNoEncryption } from '@/utils/utils-db-no-encryption'). I presume we will have to create it on our own or is it already included in the package?

Thanks.

[jepiqueau]

@nirajhinge Seems you are trying to do something which have not been yet done. Now you should use @capacitor-community/sqlite@latest and vue-sqlite-hook@latest not @next anymore the doc will have to be updated.

1. useState you are not forced to use it but if you do take the one which is given in the vue-sqlite-app-starter ie

import { readonly, ref } from 'vue';

// eslint-disable-next-line @typescript-eslint/explicit-module-boundary-types
export function useState(initialState: any): any {
  const state = ref(initialState);
  const setState = (newState: any) => {
    state.value = newState;
  };

  return [readonly(state), setState];
} 

2. I do not know it has to be tested may be it will work easier without using the vue-sqlite-hook
3. It could be in main.ts if the connection is shared in many Vue component but again it has to be tested
4. All the utilities functions are used and specific to the App. You may create your own if required

@chriswep Did you test the typeOrm driver in the context of Ionic/Vue?

[chriswep]

i haven't tested vue, however i see no reason why it should make any difference. neither typeorm nor capacitor-sqlite depend on anything framework-specific. it doesn't matter what method is used to create the typeorm connection as long as the correct SqliteConnection object is passed.

[jepiqueau]

@chriswep Thanks for your answer, as @nirajhinge want to use both typeorm and the standard way i have to provide in the vue-sqlite-hook a getSqliteConnection object. Chris, it would be nice if you can you provide me a link to a simple typeorm example so i can try to implement it in a Ionic/Vue app.

[nirajhinge]

Thanks @jepiqueau @chriswep for the responses. I am trying to create Codesandbox link so that I can show what I am trying to do but finding it difficult to get see an example setup with TypeORM and Vue in Ionic/Vue context (all examples are Ionic/Angular). I also am not sure if I can use Sql.js with this plugin so that it works with something like Codesandbox as otherwise it has to be previewed in a real device/emulator.

Currently I have managed to get capacitor-community/sqlite working with Capacitor 3 in Ionic without using Custom Hooks using something like this:

// App.vue
import { SQLiteConnection } from "@capacitor-community/sqlite";
import { CapacitorSQLite } from '@capacitor-community/sqlite';

const database = ref<any>(null);
const databaseInitialized = ref(false);

  const databaseStartup = async () => {
    const sqlite = new SQLiteConnection(CapacitorSQLite);
    const db = await sqlite.createConnection(
      "ionic-vue-db",
      false,
      "no-encryption",
      1
    );

    if (!db) throw new Error("No such database");
    await db?.open();
    database.value = db;

    await initDbTable();
    databaseInitialized.value = true;
  };

onBeforeMount(async () => {
      try {
        await databaseStartup();
      } catch (e) {
        // Error
      }
    });

What I am struggling is to integrate TypeORM with or without using the hooks.

[jepiqueau]

@nirajhinge i have tried to implement sql.js inside the @capacitor-community/sqlite without success for the time being. If you could give me a simple initDbTable and corresponding entities in typeOrm it could give me a quick start to test

[jepiqueau]

@nirajhinge if you do it without the hook if you do

import { SQLiteConnection } from "@capacitor-community/sqlite";
import { CapacitorSQLite } from '@capacitor-community/sqlite';
import { createConnection } from 'typeorm';

const database = ref<any>(null);
const databaseInitialized = ref(false);

  const databaseStartup = async () => {
    const sqlite = new SQLiteConnection(CapacitorSQLite);
    const db = await sqlite.createConnection(
      "ionic-vue-db",
      false,
      "no-encryption",
      1
    );
   // create the TypeORM connection
   const typeOrmConnection = await createConnection({
      type: 'capacitor',
      driver: sqlite
      database: 'ionic-vue-db' 
  });

...

It should work

[nirajhinge]

Thanks @jepiqueau I will give that a go and let you know how it goes!

Here is the service I am using for interacting with the database. I haven't yet started creating entities / migrations using TypeORM which I am hoping to do once I get TypeORM setup.

import { ref } from "vue";
// PLUGIN IMPORTS
import "@capacitor-community/sqlite";

import { SQLiteConnection } from "@capacitor-community/sqlite";
import { CapacitorSQLite } from '@capacitor-community/sqlite';

const database = ref<any>(null);
const users = ref<any>(null);

const sqliteService = () => {
  const databaseInitialized = ref(false);
  // INIT DATABASE

  const initDbTable = async () => {
    const CREATE_TABLE =
      "CREATE TABLE IF NOT EXISTS users (" +
      "id INTEGER PRIMARY KEY NOT NULL," +
      "first_name TEXT NOT NULL," +
      "last_name TEXT NOT NULL," +
      "email TEXT NOT NULL UNIQUE );";
    const resp = await database.value?.query(CREATE_TABLE);

    if (resp.message) {
      throw new Error(resp?.message);
    }

    return true;
  };

  const addUser = async () => {
    const resp = await database.value?.run(
      "INSERT INTO users (first_name, last_name, email) " +
        "VALUES(?, ?, ?);",
      ["Allen", "Saunders", "allen@mail.com"]
    );
    if (resp.message) {
      throw new Error(resp?.message);
    }

    return true;
  };

  const loaduserData = async () => {
    try {
      const resp = await (database as any).value?.query(
        "SELECT * FROM users;"
      );
      users.value = resp.values;
      return true;
    } catch (e) {
      alert("Error Loading users");
    }
  };

  /**
   *
   * @param userId
   */
  const deleteuserById = async (userId: string) => {
    const response = await database.value?.run(
      "DELETE FROM users WHERE id=?",
      [userId + ""]
    );
    await loaduserData();
    return response;
  };

  /**
   *
   */
  const getuserById = async (userId: string) => {
    const result = await database.value?.query(
      "SELECT * FROM users WHERE id = ?;",
      [userId]
    );
    return result.values[0];
  };

  /**
   *
   * @param firstName
   * @param lastName
   * @param email
   */
  const createuser = async (
    firstName: string,
    lastName: string,
    email: string
  ) => {
    const resp = await (database as any).value?.query(
      "INSERT INTO users (first_name, last_name, email) " + "VALUES(?,?,?)",
      [firstName, lastName, email]
    );
    await loaduserData();
    return resp;
  };

  /**
   *
   * @param firstName
   * @param lastName
   * @param email
   * @param userId
   */
  const updateuser = async (
    firstName: string,
    lastName: string,
    email: string,
    userId: string
  ) => {
    const resp = await (database as any).value?.query(
      "UPDATE users SET first_name=?, last_name=?, email=? " + "WHERE id=?",
      [firstName, lastName, email, userId]
    );

    await loaduserData();
    return resp;
  };
  /**
   * Create the database in the application
   */
  const databaseStartup = async () => {
    const sqlite = new SQLiteConnection(CapacitorSQLite);
    const db = await sqlite.createConnection(
      "ionic-vue-db",
      false,
      "no-encryption",
      1
    );

    if (!db) throw new Error("No such database");
    await db?.open();
    database.value = db;

    await initDbTable();

    // Run once
    await addUser();

    databaseInitialized.value = true;

    await loaduserData();
  };

  return {
    // FUNCTIONS
    databaseStartup,
    loaduserData,
    deleteuserById,
    getuserById,
    createuser,
    updateuser,

    // PROPS
    databaseInitialized,
    users,
    database,
  };
};

export default sqliteService;

[jepiqueau]

@chriswep @nirajhinge i have created a vue app https://github.com/jepiqueau/vue-typeorm-app . Chris can you have a look at it as it is bugging in the typeOrm createConnection. Look at what i did as i may be wrong, there is first a creation of the db using the standard sqliteConnection through a service as it is a way that @nirajhinge want to use it (without using the vue-sqlite-hook) then i close the connection and create a connection with typeorm using what you recommend in the doc. Hope you will find the reason. Thanks for your time

[nirajhinge]

@jepiqueau I managed to get the connection working so thank you for that. I do have an issue which I am not sure is related to this plugin or the way I have created the connection.

I can connect to the sqlite database following your example but for some reason the migrations does not seem to be getting registered at all and always comes out as an empty array with the following code:

// App.vue

import {createConnection} from "typeorm";
import {CapacitorSQLite, SQLiteConnection} from "@capacitor-community/sqlite";

onBeforeMount(async () => {
    const sqlite = new SQLiteConnection(CapacitorSQLite);
    createConnection({
        type: 'capacitor',
        driver: sqlite,
        database: 'mydrivetime',
        synchronize: false,
        migrationsRun: false,
        logging: true,
        entities: [
            "User"
        ],
        migrations: [
            "1626771964722-UserCreate"
        ],
    }).then(async connection => {

        console.log('migrations', connection.migrations);
        console.log('isConnected', connection.isConnected);

        connection.runMigrations()
          .then(() => {
            console.log("migrations successful");
          }).catch(error => console.log(JSON.stringify(error)));

    });
});

I get the following output for this even though I have the migration setup on the same path:

⚡️  [log] - migrations []
⚡️  [log] - isConnected true
⚡️  [log] - migrations successful

This is the migration file which I have in the same path as App.vue. I also tried putting it in a separate folder and trying to glob it but no luck.

import {MigrationInterface, QueryRunner} from "typeorm";

export class UserCreate1626771964722 implements MigrationInterface {

    public async up(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.query(`
            CREATE TABLE "user" (
                "id" integer PRIMARY KEY AUTOINCREMENT NOT NULL,
                "firstName" varchar NOT NULL,
                "age" integer NOT NULL
            )
        `);
    }

    public async down(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.query(`
            DROP TABLE "user"
        `);
    }

}

I don't have any other settings anywhere else and I am not sure why the migration (or entity) are not getting registered. Here is the full console.log of the connection object:

{
    "migrations": [],
    "subscribers": [],
    "entityMetadatas": [],
    "name": "default",
    "options": {
        "type": "capacitor",
        "driver": {
            "sqlite": {},
            "_connectionDict": {}
        },
        "database": "mydrivetime",
        "synchronize": false,
        "migrationsRun": false,
        "logging": true,
        "entities": [
            "User"
        ],
        "migrations": [
            "1626771964722-UserCreate"
        ]
    },
    "logger": {
        "options": true
    },
    "driver": {
        "isReplicated": false,
        "treeSupport": true,
        "supportedDataTypes": [
            "int",
            "integer",
            "tinyint",
            "smallint",
            "mediumint",
            "bigint",
            "unsigned big int",
            "int2",
            "int8",
            "integer",
            "character",
            "varchar",
            "varying character",
            "nchar",
            "native character",
            "nvarchar",
            "text",
            "clob",
            "text",
            "blob",
            "real",
            "double",
            "double precision",
            "float",
            "real",
            "numeric",
            "decimal",
            "boolean",
            "date",
            "time",
            "datetime"
        ],
        "withLengthColumnTypes": [
            "character",
            "varchar",
            "varying character",
            "nchar",
            "native character",
            "nvarchar",
            "text",
            "blob",
            "clob"
        ],
        "spatialTypes": [],
        "withPrecisionColumnTypes": [
            "real",
            "double",
            "double precision",
            "float",
            "real",
            "numeric",
            "decimal",
            "date",
            "time",
            "datetime"
        ],
        "withScaleColumnTypes": [
            "real",
            "double",
            "double precision",
            "float",
            "real",
            "numeric",
            "decimal"
        ],
        "mappedDataTypes": {
            "createDate": "datetime",
            "createDateDefault": "datetime('now')",
            "updateDate": "datetime",
            "updateDateDefault": "datetime('now')",
            "deleteDate": "datetime",
            "deleteDateNullable": true,
            "version": "integer",
            "treeLevel": "integer",
            "migrationId": "integer",
            "migrationName": "varchar",
            "migrationTimestamp": "bigint",
            "cacheId": "int",
            "cacheIdentifier": "varchar",
            "cacheTime": "bigint",
            "cacheDuration": "int",
            "cacheQuery": "text",
            "cacheResult": "text",
            "metadataType": "varchar",
            "metadataDatabase": "varchar",
            "metadataSchema": "varchar",
            "metadataTable": "varchar",
            "metadataName": "varchar",
            "metadataValue": "text"
        },
        "connection": "...",
        "options": "...",
        "database": "mydrivetime",
        "driver": "...",
        "sqlite": "...",
        "databaseConnection": {}
    },
    "manager": {
        "repositories": [],
        "plainObjectToEntityTransformer": {},
        "connection": "..."
    },
    "namingStrategy": {
        "nestedSetColumnNames": {
            "left": "nsleft",
            "right": "nsright"
        },
        "materializedPathColumnName": "mpath"
    },
    "relationLoader": {
        "connection": "..."
    },
    "relationIdLoader": {
        "connection": "..."
    },
    "isConnected": true
}

[jepiqueau]

@nirajhinge Can you explain me how you have make the connection working ?

[nirajhinge]

@jepiqueau I didn't see the package you created but I will try to make it work on that package and make a pull request in the next hour. Thanks.

[nirajhinge]

@jepiqueau I decided against the pull request since I think this would be easier. If you add this on SQLiteServiceTest.vue, the connection should work in terms of creating the connection as you can see from the console.log I have added. It still fails the test as there seems to be an issue when saving the data (probably because there is no migration/table):

export default defineComponent({
  name: 'SQLiteServiceTest',
  setup() {
    const [log, setLog] = useState("");
    const [users, setUsers] = useState([]);
    let errMess = "";

    onBeforeMount(async () => {
      console.log('Trying TypeORM connection');

      try {
        const sqlite = new SQLiteConnection(CapacitorSQLite);
        const connection = await createConnection({
          type: 'capacitor',
          driver: sqlite,
          database: 'sample',
          synchronize: false,
          migrationsRun: false,
          logging: true,
          entities: [
            "src/entity/**/*.ts"
          ],
          migrations: [
            "src/migration/**/*.ts"
          ],
          subscribers: [
            "src/subscriber/**/*.ts"
          ],
          cli: {
            entitiesDir: "src/entity",
            migrationsDir: "src/migration",
            subscribersDir: "src/subscriber"
          }
        });

        console.log('isConnected', connection.isConnected);
        console.log('migrations', connection.migrations);

        await connection.runMigrations()
          .then(() => {
            console.log("Migrations Success");
          }).catch(error => console.log("Migrations Failed: " + JSON.stringify(error)));

        // *******************************************
        // TypeOrm Connection definition
        const typeOrmConn: Connection = connection;
        console.log({
          typeOrmConn
        });

        // define a new user with typeOrmConn
        const user = new User();
        // eslint-disable-next-line @typescript-eslint/camelcase
        user.first_name = "Williams";
        // eslint-disable-next-line @typescript-eslint/camelcase
        user.last_name = "Clinton";
        user.email = "bill.clinton@example.com";
        console.log(`>>> User : ${JSON.stringify(user)}`);
        await typeOrmConn.manager.save(user);
        console.log(`>>> Users: ${JSON.stringify(users)}`);

        // get users from typeOrmConn
        setUsers(await typeOrmConn.manager.find(User));
        // ************************************************
        setLog(log.value
          .concat("\n* The set of tests was successfull *\n"));
      } catch (err) {
        errMess = err;
        setLog(log.value
          .concat("\n* The set of tests failed *\n"));
      }
    });
    return {
      log,
      errMess,
      users
    };
  },
});

```json
⚡️  [log] - isConnected true
⚡️  [log] - migrations []
⚡️  To Native ->  CapacitorSQLite query 70858634
⚡️  [info] - query:  SELECT * FROM "sqlite_master" WHERE "type" = 'table' AND "name" = 'migrations'
⚡️  TO JS {"values":[]}
⚡️  [info] - query:  CREATE TABLE "migrations" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "timestamp" bigint NOT NULL, "name" varchar NOT NULL)
⚡️  To Native ->  CapacitorSQLite execute 70858635
⚡️  TO JS {"changes":{"changes":0}}
⚡️  To Native ->  CapacitorSQLite query 70858636
⚡️  [info] - query:  SELECT * FROM "migrations" "migrations" ORDER BY "id" DESC
⚡️  TO JS {"values":[]}
⚡️  [log] - Migrations Success
⚡️  [log] - {
  "typeOrmConn": {
    "migrations": [],
    "subscribers": [],
    "entityMetadatas": [],
    "name": "default",
    "options": {
      "type": "capacitor",
      "driver": {
        "sqlite": {},
        "_connectionDict": {}
      },
      "database": "sample",
      "synchronize": false,
      "migrationsRun": false,
      "logging": true,
      "entities": [
        "src/entity/**/*.ts"
      ],
      "migrations": [
        "src/migration/**/*.ts"
      ],
      "subscribers": [
        "src/subscriber/**/*.ts"
      ],
      "cli": {
        "entitiesDir": "src/entity",
        "migrationsDir": "src/migration",
        "subscribersDir": "src/subscriber"
      }
    },
    "logger": {
      "options": true
    },
    "driver": {
      "isReplicated": false,
      "treeSupport": true,
      "supportedDataTypes": [
        "int",
        "integer",
        "tinyint",
        "smallint",
        "mediumint",
        "bigint",
        "unsigned big int",
        "int2",
        "int8",
        "integer",
        "character",
        "varchar",
        "varying character",
        "nchar",
        "native character",
        "nvarchar",
        "text",
        "clob",
        "text",
        "blob",
        "real",
        "double",
        "double precision",
        "float",
        "real",
        "numeric",
        "decimal",
        "boolean",
        "date",
        "time",
        "datetime"
      ],
      "withLengthColumnTypes": [
        "character",
        "varchar",
        "varying character",
        "nchar",
        "native character",
        "nvarchar",
        "text",
        "blob",
        "clob"
      ],
      "spatialTypes": [],
      "withPrecisionColumnTypes": [
        "real",
        "double",
        "double precision",
        "float",
        "real",
        "numeric",
        "decimal",
        "date",
        "time",
        "datetime"
      ],
      "withScaleColumnTypes": [
        "real",
        "double",
        "double precision",
        "float",
        "real",
        "numeric",
        "decimal"
      ],
      "mappedDataTypes": {
        "createDate": "datetime",
        "createDateDefault": "datetime('now')",
        "updateDate": "datetime",
        "updateDateDefault": "datetime('now')",
        "deleteDate": "datetime",
        "deleteDateNullable": true,
        "version": "integer",
        "treeLevel": "integer",
        "migrationId": "integer",
        "migrationName": "varchar",
        "migrationTimestamp": "bigint",
        "cacheId": "int",
        "cacheIdentifier": "varchar",
        "cacheTime": "bigint",
        "cacheDuration": "int",
        "cacheQuery": "text",
        "cacheResult": "text",
        "metadataType": "varchar",
        "metadataDatabase": "varchar",
        "metadataSchema": "varchar",
        "metadataTable": "varchar",
        "metadataName": "varchar",
        "metadataValue": "text"
      },
      "connection": "...",
      "options": "...",
      "database": "sample",
      "driver": "...",
      "sqlite": "...",
      "databaseConnection": {},
      "queryRunner": {
        "isReleased": false,
        "isTransactionActive": false,
        "data": {},
        "loadedTables": [],
        "loadedViews": [],
        "sqlMemoryMode": false,
        "sqlInMemory": {
          "upQueries": [],
          "downQueries": []
        },
        "driver": "...",
        "connection": "...",
        "broadcaster": {
          "queryRunner": "..."
        },
        "manager": {
          "repositories": [],
          "plainObjectToEntityTransformer": {},
          "connection": "...",
          "queryRunner": "..."
        }
      }
    },
    "manager": {
      "repositories": [],
      "plainObjectToEntityTransformer": {},
      "connection": "..."
    },
    "namingStrategy": {
      "nestedSetColumnNames": {
        "left": "nsleft",
        "right": "nsright"
      },
      "materializedPathColumnName": "mpath"
    },
    "relationLoader": {
      "connection": "..."
    },
    "relationIdLoader": {
      "connection": "..."
    },
    "isConnected": true
  }
}
⚡️  [log] - >>> User : {
  "first_name": "Williams",
  "last_name": "Clinton",
  "email": "bill.clinton@example.com"
}

[jepiqueau]

@nirajhinge @chriswep i did this

    createTypeOrmConnection = async(): Promise<Connection> => {
      console.log("$$$ in createTypeOrmConnection ... $$$")
      try {
        const res = await this.sqlite.isConnection('ionic-vue-db'); 
        if( res.result) {
          await this.sqlite.closeConnection('ionic-vue-db');
          console.log("$$$ in createTypeOrmConnection close connection... $$$")
        }
        const typeOrmConnection = await createConnection({
          type: 'capacitor',
          driver: this.sqlite, 
          database: 'ionic-vue-db',
          entities: [User],
          logging: true,
          synchronize: true
        });
        console.log(`$$$ typeOrmConnection: ${JSON.stringify(typeOrmConnection)}`);
        return Promise.resolve(typeOrmConnection);
      } catch (err) {
        return Promise.reject(`Error: createTypeOrmConnection ${err}`);
      }
    }

the database is created and i got the following

  To Native ->  CapacitorSQLite open 90677579
⚡️  WebView loaded
⚡️  TO JS undefined
⚡️  To Native ->  CapacitorSQLite execute 90677580
⚡️  TO JS {"changes":{"changes":0}}
⚡️  To Native ->  CapacitorSQLite execute 90677581
⚡️  [info] - query:  BEGIN TRANSACTION
⚡️  TO JS {"changes":{"changes":0}}
⚡️  [info] - query:  SELECT * FROM "sqlite_master" WHERE "type" = 'table' AND "name" IN ('s')
⚡️  To Native ->  CapacitorSQLite query 90677582
⚡️  TO JS {"values":[]}
⚡️  To Native ->  CapacitorSQLite query 90677583
⚡️  [info] - query:  SELECT * FROM "sqlite_master" WHERE "type" = 'index' AND "tbl_name" IN ('s')
⚡️  TO JS {"values":[]}
⚡️  To Native ->  CapacitorSQLite query 90677584
⚡️  [info] - query:  SELECT * FROM "sqlite_master" WHERE "type" = 'table' AND "name" = 'typeorm_metadata'
⚡️  TO JS {"values":[]}
⚡️  To Native ->  App addListener 90677585
⚡️  To Native ->  CapacitorSQLite execute 90677586
⚡️  [info] - query:  CREATE TABLE "s" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "first_name" varchar NOT NULL, "last_name" varchar NOT NULL, "email" varchar NOT NULL)
⚡️  TO JS {"changes":{"changes":0}}
⚡️  To Native ->  CapacitorSQLite query 90677587
⚡️  [info] - query:  COMMIT
⚡️  TO JS {"values":[]}

the table should be "Users" and it is "s" . i think @chriswep should look at this and tell us what we are doing wrong

[nirajhinge]

@jepiqueau I see. You are using synchronize:true but I have both the synchronize and runMigrations set to false as I want to have full control over the migrations on production like this (I read that both the synchronize and runMigrations re-creates the table but maybe I am wrong). Either way, it would be great to have some input from @chriswep.

[chriswep]

i find it difficult to single out a problem/question, since there is a lot going on in this issue. A few general things:

• the cap-driver is running createConnection, passing the return of new SQLiteConnection(CapacitorSQLite) to the typeorm connection-options is all that an application should need to do. If need be, one can get direct access to the capacitor-sqlite connection via the typeORM connection. at least as long as something is not working i'd suggest doing db-acces with typeorm exclusively first and work from there.
• briefly looking over the example repo, i would suspect the issue to be with the correct usage of TypeORM, not anything related to Vue / Ionic / capcitor-sqlite or even the capacitor-driver. if typeorm creates a table "s" it looks like it does not have an entity name. Try passing it in the entity decorator.
• there are two PRs for the cap-driver already merged in TypeORM, however there isn't a new release yet. if the issue remains unclear, i suggest updating by forking typeORM, npm run package and import locally from the build folder.
• similar to the last point, the fix for https://github.com/ionic-team/capacitor/issues/4798 should be applied to rule it out as a potential cause of error

[jepiqueau]

@chriswep thanks for your answer i had found that i had to put the table name in the entity name like you suggest and use only the typeorm driver to access the db i do this

        createConnection({
          type: 'capacitor',
          driver: this.sqlite, 
          database: 'ionic-vue-db',
          entities: [User],
          logging: true,
          synchronize: true
        }).then( async (connection) => {
            console.log(`$$$ typeOrmConnection: ${JSON.stringify(connection)}`);
            const user = new User();
            // eslint-disable-next-line @typescript-eslint/camelcase
            user.first_name = "Williams";
            // eslint-disable-next-line @typescript-eslint/camelcase
            user.last_name = "Clinton";
            user.email = "bill.clinton@example.com";
            console.log(`>>> User : ${user}`);
            const userRepository = getRepository('User') as Repository<User>;
            await userRepository.save(user);
            const saveUser  = await userRepository.findOne(user.id); 
            console.log(`>>> saveUser ${saveUser}`);         

with the entity User as

import {Entity, PrimaryGeneratedColumn, Column} from "typeorm"; 

@Entity("users") 
export class User {   

  @PrimaryGeneratedColumn()
  id?: number; 

  @Column()
  first_name?: string; 

  @Column()
  last_name?: string;

  @Column()
  email?: string; 
}

the output is

⚡️  [log] - $$$ in createTypeOrmConnection ... $$$
⚡️  [log] - isConnection false
⚡️  To Native ->  CapacitorSQLite createConnection 81686608
databaseName: ionic-vue-dbSQLite.db 
database path /Users/queaujeanpierre/Library/Developer/CoreSimulator/Devices/F38CDAFB-6BDF-45F9-B3EC-49BD8063AA89/data/Containers/Data/Application/E4A7613D-125D-4041-8CA5-6461AE7D4472/Documents/ionic-vue-dbSQLite.db
⚡️  TO JS undefined
⚡️  To Native ->  CapacitorSQLite open 81686609
⚡️  TO JS undefined
⚡️  To Native ->  CapacitorSQLite execute 81686610
⚡️  TO JS {"changes":{"changes":0}}
⚡️  WebView loaded
⚡️  To Native ->  CapacitorSQLite execute 81686611
⚡️  [info] - query:  BEGIN TRANSACTION
⚡️  TO JS {"changes":{"changes":0}}
⚡️  [info] - query:  SELECT * FROM "sqlite_master" WHERE "type" = 'table' AND "name" IN ('users')
⚡️  To Native ->  CapacitorSQLite query 81686612
⚡️  TO JS {"values":[]}
⚡️  To Native ->  App addListener 81686613
⚡️  To Native ->  CapacitorSQLite query 81686614
⚡️  [info] - query:  SELECT * FROM "sqlite_master" WHERE "type" = 'index' AND "tbl_name" IN ('users')
⚡️  TO JS {"values":[]}
⚡️  To Native ->  CapacitorSQLite query 81686615
⚡️  [info] - query:  SELECT * FROM "sqlite_master" WHERE "type" = 'table' AND "name" = 'typeorm_metadata'
⚡️  TO JS {"values":[]}
⚡️  To Native ->  CapacitorSQLite execute 81686616
⚡️  [info] - query:  CREATE TABLE "users" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "first_name" varchar NOT NULL, "last_name" varchar NOT NULL, "email" varchar NOT NULL)
⚡️  TO JS {"changes":{"changes":0}}
⚡️  To Native ->  CapacitorSQLite query 81686617
⚡️  [info] - query:  COMMIT
⚡️  TO JS {"values":[]}
⚡️  [log] - Error:  {}

the database is created with a right schema but it seems that the connection out with an error

[jepiqueau]

@chriswep it would be nice if you could give us an example that you used to test the driver. I do not know enough typeorm to understand what is going on and i spent two full days trying to find a solution. So please help

[nirajhinge]

@jepiqueau I went ahead and tried a few different things and as you say, it looks like the connection doesn't get created or doesn't seem to persist. Here is the setup.

// main.ts
...
import "reflect-metadata";
import {CapacitorSQLite, SQLiteConnection} from "@capacitor-community/sqlite";
import {createConnection} from "typeorm";
import {User} from "@/entity/user";

const app = createApp(App)
  .use(IonicVue)
  .use(router);

const sqlite = new SQLiteConnection(CapacitorSQLite);
console.log('creating connection');

createConnection({
    type: 'capacitor',
    driver: sqlite,
    database: 'testdb',
    entities: [User],
    logging: true,
    synchronize: true
});

router.isReady().then(() => {
  app.mount('#app');
});

This is the User entity:

import {Entity, PrimaryGeneratedColumn, Column} from "typeorm";

@Entity('users')
export class User {

    @PrimaryGeneratedColumn()
    id?: number;

    @Column()
    firstName?: string;

    @Column()
    lastName?: string;
}

And this is the SQLiteServiceTest.vue

<template>
    ...
</template>

<script lang="ts">
import {defineComponent, onMounted} from 'vue';
import {useState} from '@/composables/state';
import {User} from "@/entity/user";
import {getConnection} from "typeorm";
import {isPlatform} from "@ionic/vue";

export default defineComponent({
    name: 'SQLiteServiceTest',
    setup() {
        const [log] = useState("");
        const [users] = useState([]);

        onMounted(async () => {

            const connection = getConnection();

            console.log("Inserting a new user into the database...");
            const user = new User();
            user.firstName = "Timber";
            user.lastName = "Saw";

            console.log(connection.isConnected);

            if (!isPlatform('capacitor')) return Promise.resolve();

            await connection.manager.save(user);
            console.log("Saved a new user with id: " + user.id);

            console.log("Loading users from the database...");
            const users = await connection.manager.find(User);
            console.log("Loaded users: ", users);
        });
        return {log, users};
    },
});
</script>

This creates the database with the users table and then tries to insert the row but fails with the following output. In the example you sent, there seems to be an issue with the console.log trying to stringify the output. I have highlighted the two errors - one is the connection.isConnected is coming out as false and then another one which seems TypeORM specific looking at similar issues here . Not sure why that would happen as we are providing the entity directly rather than globbing a path.

⚡️ [log] - creating connection databaseName: testdbSQLite.db database path /Users/nirajhinge/Library/Developer/CoreSimulator/Devices/C2E05CA1-F439-4B07-B511-6215BA2ECF25/data/Containers/Data/Application/3756D765-8C06-4A36-ACD5-67F3DB2CC78F/Documents/testdbSQLite.db ⚡️ TO JS undefined ⚡️ [log] - Inserting a new user into the database... ⚡️ [log] - connection.isConnected false ⚡️ [error] - {"name":"EntityMetadataNotFound","message":"No metadata for \"E\" was found.","line":1,"column":6286,"sourceURL":"capacitor://localhost/js/chunk-vendors.fcc9c668.js"} ⚡️ To Native -> CapacitorSQLite open 84807750 ⚡️ WebView loaded ⚡️ TO JS undefined ⚡️ To Native -> CapacitorSQLite execute 84807751 ⚡️ TO JS {"changes":{"changes":0}} ⚡️ To Native -> App addListener 84807752 ⚡️ To Native -> CapacitorSQLite execute 84807753 ⚡️ [info] - query: BEGIN TRANSACTION ⚡️ TO JS {"changes":{"changes":0}} ⚡️ [info] - query: SELECT FROM "sqlite_master" WHERE "type" = 'table' AND "name" IN ('users') ⚡️ To Native -> CapacitorSQLite query 84807754 ⚡️ TO JS {"values":[]} ⚡️ To Native -> CapacitorSQLite query 84807755 ⚡️ [info] - query: SELECT FROM "sqlite_master" WHERE "type" = 'index' AND "tbl_name" IN ('users') ⚡️ TO JS {"values":[]} ⚡️ [info] - query: SELECT * FROM "sqlite_master" WHERE "type" = 'table' AND "name" = 'typeorm_metadata' ⚡️ To Native -> CapacitorSQLite query 84807756 ⚡️ TO JS {"values":[]} ⚡️ [info] - query: CREATE TABLE "users" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "firstName" varchar NOT NULL, "lastName" varchar NOT NULL) ⚡️ To Native -> CapacitorSQLite execute 84807757 ⚡️ TO JS {"changes":{"changes":0}} ⚡️ To Native -> CapacitorSQLite query 84807758 ⚡️ [info] - query: COMMIT ⚡️ TO JS {"values":[]}

I think I will just use the plugin without TypeORM for now but thank you both for your time. I will keep an eye on the issue to see if we can fix it as that would be great.

[chriswep]

@nirajhinge the error looks like an issue with code minification. TypeORM does not work with minified code, i.e. the entity classes need to be excluded from minification. Did you have TypeORM working without capacitor-sqlite? I think that would be a start since i still feel that this issue need to be narrowed down to effectively talk / do something about it.

@jepiqueau i have this running in a complex app. basically just replaced the cordova driver with capacitor in the typeorm connection options (and renamed the dbs). in a working typeorm app there shouldn't be anything else needed than passing the capacitor-sqlite object. Maybe it would be worth making it work on the cordova-driver first and then switch the driver. Then you could focus on making TypeORM work first and then look at potential issues with capacitor. As @nirajhinge pointed out already, it looks like your code errors out at the console.log. I suggest removing the stringify since the object likely contains circular references.

[jepiqueau]

@nirajhinge i got it working in pure typeOrm see https://github.com/jepiqueau/vue-typeorm-app it give you a start to play with @chriswep thanks for your non-help on this

[nirajhinge]

@chriswep That is interesting. I will look into preserving the Entity class as is when it compiled to the app with Ionic. I did manage to get it to mostly work - can't seem to get the migrations to work by replacing the drive to cordova from capacitor.

@jepiqueau Thank you for that update. I have managed to make it work with the capacitor driver as you have done in the example repo. If you have some time could you please have a look at the pull request I have made for trying to use migrations instead of sync option as that seems to not work - same issue if I use cordova driver as well.

Thanks for your time once again.

[jepiqueau]

@nirajhinge @chriswep i play with your PR i update the app repo but it still not work. i am sorry but i will stop working on that. The migration table is created but empty so the item table is not created. We can see few things when we firstConn.close() the driver must close the capacitor sqlite connection , the migration doesn't work and how this will work with the addUpgradeStatement of the capacitor sqlite which is made for upgrading version. So can the typeOrm driver can take that into account i do not now. i will keep this issue open as i would like to be aware if you find the solution.

[chriswep]

TypeORM is taking care of opening and closing connections. It also has its own migration system: https://github.com/typeorm/typeorm/blob/master/docs/migrations.md. (For completion: TypeORM also takes care of transactions internally, thats why we added the flag to disable automatic transactions in capacitor-sqlite some time ago)

The driver does only use the following methods of capacitor-sqlite:

• createConnection

and on the connection object:

• close
• execute
• executeSet
• open
• query
• run

[nirajhinge]

@jepiqueau Thanks for your time. I will let you know if I manage to get anywhere.

@chriswep Thanks for the response and sorry for my ignorance as all of this is very new to me but I think I followed that guide when creating the migrations. Do you mean we have to run additional method like run or something else for the migrations to run?

[chriswep]

I create the dbs that are preloaded with a node script via TypeORM with synchronize: true. For the dbs that are created on device, I generate the necessary migration files via TypeORM CLI. See the TypeORM doc for details.

@nirajhinge if you pass the migration files in the TypeORM connection options, they will be applied automatically. You can however set migrationsRun: false and then run runMigrations() manually on the TypeORM connection object.

[nirajhinge]

@chriswep I think that is exactly what I did here: https://github.com/nirajhinge/vue-typeorm-app/blob/migrations/src/main.ts#L45

But that never seems to create the migration(s) except the migration table. Tried with both with cordova and capacitor driver. I generated those migration files using the CLI. I will try one last thing based on what you suggested which will be something like this otherwise there is probably some other issue somewhere. Thanks for your time.

async runMigrations(){
   await this.connection.close();
   let migration = await createConnection({
     type: 'capacitor',
    driver: sqliteConnection,
    database: 'ionic-vue-db-items',
    entities: [User, Item],
    migrations: [AddItemTable1626863626662],
    logging: ['error', 'query', 'schema'],
    synchronize: false,
    migrationsRun: false
   }).then(async connection => {

     // run all migrations
     await connection.runMigrations();
     await connection.close();
   });
 });

[chriswep]

just in case: if the database file exists and you have run the code already, it won't run the migration again.

[nirajhinge]

@chriswep I have been creating new database every single time I am testing. So I think I have figured out what the issue might be. It seems like a minification issue again as I see this error on the code below even though my migration is named 1626863626662-AddItemTable.ts

try {
  await connection.runMigrations();
  } catch (e) {
     console.log(e.message);
}

// output
[log] - O migration name is wrong. Migration class name should have a JavaScript timestamp appended.

Now I need to figure out how to create the custom webpack file for ionic vue and see if I can use the migration files that is unminifed.

[nirajhinge]

@chriswep @jepiqueau So all that time I think what was happening was the migrations were not working because they were getting minified and there was no error until I caught it with a try catch. Luckily, someone has a fix that seems to work as I managed to run the migration when I added this config file in the root

[jepiqueau]

@nirajhinge Can you make a PR including that modification in the shared app.

[nirajhinge]

@jepiqueau I have made the PR now in the repo

[jepiqueau]

@nirajhinge Many thanks, finally it is working. To avoid other developers to go through that painful exercise, do you mind if i use that repo and include it in the documentation. I think by using migrations, it is quite safe but risky now to mix it with normal use of capacitor-community/sqlite. then developers should stick to one of the way, what do you think about this?

[nirajhinge]

@jepiqueau You can use it however you want :). Not sure what you mean by risky mixing capacitor-community/sqlite and TypeORM but for my use case I plan to use TypeORM as much as possible and only fall back to the plugin methods if I need to.

My use case is running migrations on the Ionic based apps on iOS/Android which I will use instead of synchronize since the documentation says it's better to use migration. I then plan to use the getConnection and other TypeORM methods within the app.

The only annoying thing right now is this won't work on a web browser so might have to look into integrating sql.js for web and use sqlite plugin in capacitor enabled devices if that makes sense.

Thanks.

[jepiqueau]

@nirajhinge Risky means that they could be some errors as the typeOrm driver as not implemented all the functionalities of capacitor-community/sqlite. The database versioning is working differently than the migration in typeOrm as when schemas are updated you cannot come back to previous version. So i think it is good that you want to use TypeOrm as much as you can. For the browser, i did try to implement sql.js without success right now but this happens when i was doing the migration to Capacitor so i may come back on this. Now if you are using typeOrm only they have a driver to sql.js and i saw in you PR that you have already test the plaform for capacitor so if it is browser you can do something like this and it should work tell me if it is the case

        await createConnection({
          type: 'sqljs',
          autoSave: true,
          location: 'browser',
          logging: ['error', 'query', 'schema'],
          synchronize: false,
          entities: [Item],
         migrations: [AddItemTable1626863626662]
        });

So for the time being it make sense Thanks, i was pleased that you come with this issue, that force me to start looking to TypeORM and understand why developers where asking for that driver and i will thanks again @chriswep for the effort he made even if some time i was quite rude with him. I am pleased that we could use this example to enhance the documentation. I will add you as a contributor for @capacitor-community/sqlite on the next release Good luck for your app

[jepiqueau]

@nirajhinge i have updated the app for multiple connections

[nirajhinge]

@jepiqueau Thanks for that. Don't need multiple connections for me but should be useful for someone. Going to try sql.js and will let you know how it goes.

[jepiqueau]

@nirajhinge I am starting to look on how to implement sql.js in @capacitor-community/sqlite. I do not know yet how long it will take me. I will keep you updated

[jepiqueau]

@nirajhinge in trying to implement sql.js i am struggling in a Ionic/Angular app during the build with

./node_modules/sql.js/dist/sql-wasm.js:570:18-31 - Error: Module not found: Error: Can't resolve 'fs' in '/Users/queaujeanpierre/Development/next/angular-sqlite-app-starter/node_modules/sql.js/dist'

Any idea on how to solve this

[nirajhinge]

@jepiqueau I haven't used angular for a while but looks like some node related issue. This might be helpful:

config.node = {
  fs: 'empty',
}

[jepiqueau]

@nirajhinge thanks i saw this but i do not know in which file i have to put this Any idea?

[nirajhinge]

@jepiqueau I think guess that would go on a webpack config file or something to override it like the vue.config.json where we added the config to not to uglify the TypeORM class names. I think there are similar ways to add custom webpack config file for Angular as well.

[jepiqueau]

@nirajhinge i already add one webpack-config.js with

var webpack = require('webpack');

const CopyPlugin = require('copy-webpack-plugin');

var useDefaultConfig = require('@ionic/app-scripts/config/webpack.config.js');

useDefaultConfig.dev.plugins.push(
  new webpack.ProvidePlugin({
    'window.SQL': 'sql.js/dist/sql-wasm-debug.js'
  }),
  new CopyPlugin([ { from: 'node_modules/sql.js/dist/sql-wasm-debug.wasm', to: "../sql-wasm-debug.wasm" } ]),
);

useDefaultConfig.prod.plugins.push(
  new webpack.ProvidePlugin({
    'window.SQL': 'sql.js/dist/sql-wasm.js'
  }),
  new CopyPlugin([ { from: 'node_modules/sql.js/dist/sql-wasm.wasm', to: "../sql-wasm.wasm" } ]),
);

module.exports = function () {
  return useDefaultConfig;
};

[jepiqueau]

@nirajhinge and var useDefaultConfig = require('@ionic/app-scripts/config/webpack.config.js'); and if i look into that file i got :

  node: {
    fs: 'empty',
    net: 'empty',
    tls: 'empty'
  }

so it seems there

[jepiqueau]

@nirajhinge i was using something for Ionic 4, For Ionic5 and Angular 12 we should use a custom.webpack.config.js like this

const webpack = require('webpack');
const CopyPlugin = require('copy-webpack-plugin');

console.log('The custom config is used');
module.exports = {
    plugins: [
        new webpack.ProvidePlugin({
            'window.SQL': 'sql.js/dist/sql-wasm.js'
        }),
        new CopyPlugin({
            patterns: [
              { from: "node_modules/sql.js/dist/sql-wasm.wasm", to: "sql-wasm.wasm" }
            ],
          }),
    ],
    resolve: {
        fallback: {
            fs: false,
            path: false,
            crypto: false,
            stream: false
        }
    },

    optimization: {
        minimize: false
    }
};

modify the tsconfig.app.json and add "types": ["node"] in the compilerOptions and then modify the angular.json to accept a custom.webpack.config file

      "architect": {
        "build": {
          "builder": "@angular-builders/custom-webpack:browser",
          "options": {
            "customWebpackConfig": {
              "path": "config/custom.webpack.config.js"
            },

...

        "serve": {
          "builder": "@angular-builders/custom-webpack:dev-server",

and npm install --save @angular-builders/custom-webpack and @angular-builders/dev-server

having done all this it builds correctly. The problem is when you do a ionic serve of the app it cannot load the sql-wasm.wasm file. So i do not know what to do from there. i may stop my investigation at least this way

[jepiqueau]

@nirajhinge @chriswep The plugin will come soon with a web plugin part based on sql.js and localforage through the use of a stencil component jeep-sqlite If this component is defined in the DOM of the app the web plugin will interact with it otherwise the web plugin will return 'not implemented' this is to limit the impact on the build sizes and startup time for mobile devices. So i will close the issue. Thanks again both of you for your help.