locked
[Node.js] Migration between Azure Mobile Services and Azure Mobile Apps RRS feed

  • Question

  • Hello,

    I have an app in production running on Windows 8.1, Windows Phone 8.1, Windows 10 and Windows 10 Mobile. It currently use Azure Mobile Services.

    Because Azure Mobile Services is going to be stopped at the end of this year, I'm in the process of migrating to Azure Mobile Apps. If I understand correctly without action from me, Microsoft will migrate my Azure Mobile Services app to App Service before the end of this year.

    To prevent production during the automatic migration...

    I would like to handle this migration myself, and I would like to setup a new Azure Mobile Apps that uses the existing tables of the Azure SQL database that is holding the data in production. Then I would push an update to my app to use the new NuGet libraries.

    My question is: how to setup a new Azure Mobile Apps to use an existing database (with a Node.js backend) ?

    So far, what I did:

    • create a new mobile app
    • deploy a backup of the production DB to a new Azure SQL database
    • setup connection from Azure Mobile App to the SQL db

    AMS uses the [serviceName] as root schema instad of [dbo]. In order to overcome this limitation, I created a view:

    create view tableName AS   select id, __createdAt as createdAt, __updatedAt as updatedAt, __version as version, userId as userId, 'name' as 'name', 'order' as 'order', rules from serviceName.tableName

    I'm now seeing the data in easy table, but I cannot access them. When trying to do so, I get a 500 internal server error.

    Backend logs gives me:

    2016-08-04T11:54:24.701Z - error: Error occurred during table initialization RequestError: Cannot alter 'dbo.tableName' because it is not a table.

    The association .js file contains:

    var table = module.exports = require('azure-mobile-apps').table();
    
    table.columns = {
      "id": "string",
      "createdAt": "datetime",
      "updatedAt": "datetime",
      "version": "datetime",
      "userId": "string",
      "name": "string",
      "rules": "string",
      "order": "string"
    };

    I don't understand why Azure Mobile App is trying to update the schema...

    Thanks for your help,

    Jeremy



    • Edited by Jalpf Thursday, August 4, 2016 12:09 PM
    • Edited by Gary Liu - MSFT Friday, August 5, 2016 1:20 AM edit title
    Thursday, August 4, 2016 12:08 PM

Answers

  • Hi Jalpf,

    Dynamic schema works by handling missing column or table errors coming from SQL Server. If you try to insert or update a record and columns are missing, it will try to update the schema. This means that there are properties on the item you are trying to insert that do not have corresponding columns in the database table (or view in your case).

    I can see from the definition that the deleted column is missing (this is required) and that the version column is specified as datetime - this is not valid. You should omit createdAt, updatedAt, deleted and version from the list of table.columns - these will be created automatically, but they require corresponding columns in the database.

    You can also use the SQL script at https://github.com/Azure/azure-mobile-apps-node-compatibility/blob/master/static/createViews.sql to automatically create corresponding views.

    • Marked as answer by Jalpf Thursday, August 4, 2016 7:01 PM
    Thursday, August 4, 2016 5:26 PM

All replies

  • Hi Jalpf,

    Dynamic schema works by handling missing column or table errors coming from SQL Server. If you try to insert or update a record and columns are missing, it will try to update the schema. This means that there are properties on the item you are trying to insert that do not have corresponding columns in the database table (or view in your case).

    I can see from the definition that the deleted column is missing (this is required) and that the version column is specified as datetime - this is not valid. You should omit createdAt, updatedAt, deleted and version from the list of table.columns - these will be created automatically, but they require corresponding columns in the database.

    You can also use the SQL script at https://github.com/Azure/azure-mobile-apps-node-compatibility/blob/master/static/createViews.sql to automatically create corresponding views.

    • Marked as answer by Jalpf Thursday, August 4, 2016 7:01 PM
    Thursday, August 4, 2016 5:26 PM
  • Hi Dale,

    Thanks for the quick feedback. Based on your reply, I made the following changes:

    • I removed the createdAt, updatedAt and deleted from the definition in the .js file
    • I added the __deleted column to the original schema - my tables was created before AMS had support for soft delete and did not have the column
    • I used the SQL script you mentioned to automate the creation of the appropriate views

    So far it seems to be working as I'm able to fetch data using the client library on the Windows 10 app.

    Having said that, when the server starts, I'm still seeing that kind of logs:

    2016-08-04T18:59:19.084Z - info: Updating schema for table xxx
    2016-08-04T18:59:19.146Z - info: Updating schema for table yyy
    2016-08-04T18:59:19.256Z - info: Updating schema for table zzz

    Is that normal ?

    And last question, I also have "old" tables created in AMS where the Id field is of type bigint while more recent tables are of type nvarchar. Should I be worried about that ? It looks like AMA handles this correctly.

    Thank you very much for your help,

    Thursday, August 4, 2016 7:11 PM
  • If you set the log level to silly by running the server with a ---logging.level silly switch like:

        node --debug app.js ---logging.level silly

    or if you are running hosted, you can set the log level for streaming logs to verbose.

    This will show you the SQL statements being executed and the parameters and will show you what schema changes are being made.

    Integer primary key columns are supported - in this case, you want to set the autoIncrement property of the table definition to true. You can see the complete set of options for tables at http://azure.github.io/azure-mobile-apps-node/global.html#tableDefinition.

    Thursday, August 4, 2016 8:03 PM
  • The SQL logs I'm seeing is the following:

    SELECT COLUMN_NAME AS name, DATA_TYPE AS type FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table AND TABLE_SCHEMA = @schema

    So it looks like the AMA is checking if it needs to update the schema rather than updating the actual schema :-)

    Thanks again for the great support !


    Friday, August 5, 2016 8:21 AM