[User Scenario] Create APIs with existing database

[dhmlau]

User Scenario

I'd like to create REST APIs with an existing database.

Current Behavior

Steps involved:

1. Scaffold the app using lb4 app command
2. Discover schema
   In src/application.ts file, use DataSource.discoverSchemas to discover the mode definitions
3. Create an empty model with Entity as baseClass, using lb4 model command.
   Copy and paste the json from step 2 in the generated model.ts located in src/models folder.

See details in:
strongloop/v4.loopback.io#50 (comment)
strongloop/v4.loopback.io#50 (comment)

To-dos

• Document this model discovery path possibly in the "How tos" section in the docs

Potential Enhancement

• @marioestradarosa suggested to discover the model through the lb4 model command such that:

lb4 model --table mysqlTable --datasource myDS

• Proposed by @bajtos, a lb4 discover command would be helpful - see CLI for model discovery in LB4 #1949

See his comment for more details.

See also #1949

See Reporting Issues for more tips on writing good issues

[marvinirwin]

This is my favorite part of loopback3, being able to create and configure every model from a data source.

Using the typescript compiler API I've hacked together something which mimics the behavior of the old loopback which produces the following typescript.

import { model as model, property as property, Entity as Entity } from "@loopback/repository";
@model({ settings: { postgresql: { schema: "public", table: "v_nested_sets_graph" } } })
export class VNestedSetsGraph extends Entity {
    @property({ type: "Number", postgresql: { columnName: "source_id", dataType: "bigint", nullable: "YES" } })
    sourceId: Number;
    @property({ type: "Number", postgresql: { columnName: "node_id", dataType: "bigint", nullable: "YES" } })
    nodeId: Number;
    @property({ type: "Number", postgresql: { columnName: "lft", dataType: "bigint", nullable: "YES" } })
    lft: Number;
    @property({ type: "Number", postgresql: { columnName: "rgt", dataType: "bigint", nullable: "YES" } })
    rgt: Number;
    @property({ type: "Number", postgresql: { columnName: "id", dataType: "bigint", nullable: "YES" }, id: true })
    id: Number;
    @property({ type: "String", postgresql: { columnName: "last_modified", dataType: "timestamp with time zone", nullable: "YES" } })
    lastModified: String;
    @property({ type: "String", postgresql: { columnName: "text", dataType: "text", nullable: "YES" } })
    text: String;
    @property({ type: "Number", postgresql: { columnName: "user_id", dataType: "bigint", nullable: "YES" } })
    userId: Number;
    @property({ type: "Boolean", postgresql: { columnName: "visible", dataType: "boolean", nullable: "YES" } })
    visible: Boolean;
    constructor(data?: Partial<VNestedSetsGraph>) {
        super(data);
    }
}

The source code is here it's messy, but the code is produces works. I want to contribute to loopback, is it worth fixing this up and submitting a cleaner version for a model discovery generator, or an example of model discovery?

[bajtos]

@marvinirwin this is awesome ❤️

I want to contribute to loopback, is it worth fixing this up and submitting a cleaner version for a model discovery generator, or an example of model discovery?

Yes please!

I think we need to make a decision about the user-facing interface first - should we have a new command like lb4 discover or extend the existing lb4 model command? Maybe both, where lb4 discover can be used to create multiple models at the same time, offering a list of tables available in the target database?

Using the typescript compiler API I've hacked together something which mimics the behavior of the old loopback which produces the following typescript.

Uh oh, is there any particular reason for using TypeScript Compiler API instead of EJS templates we are currently using in @loopback/cli? Personally, I find the code generation based on TSC API as difficult to understand and reason about.

@raymondfeng do you have any opinions about the high-level design of model discovery in LB4? Any constraints or requirement to keep in mind while working on this feature?

/cc @strongloop/loopback-maintainers

[jpegram2]

I think we need to make a decision about the user-facing interface first - should we have a new command like lb4 discover or extend the existing lb4 model command? Maybe both, where lb4 discover can be used to create multiple models at the same time, offering a list of tables available in the target database?

As an end user/developer I like the idea of the following:
lb4 model --table mysqlTable --datasource myDS
Generate a single tables model
lb4 discovery
Generate a set of models from all tables in all the defined datasources. Maybe add the --datasource myDS switch to use a specific one or provide a list of datasources vs tables (or both).

I can see where both options would be beneficial but I'd be happy with the first one right now.

[marvinirwin]

Uh oh, is there any particular reason for using TypeScript Compiler API instead of EJS templates we are currently using in @loopback/cli? Personally, I find the code generation based on TSC API as difficult to understand and reason about.

Yeah, I'm not sure it's better with the TSC API either. In my case, in lb3 I sometimes make modifications to the model definition and merged the changes with the discovered model (this was really easy with JSON, and is possible with the TSC api). However I doubt merging is safe enough for the average user.

I think the advantages for TSC are that it's slightly safer because the generation is typed so it's more difficult to generate invalid code (but not impossible). It's potentially mergeable like I said above, and It's not as scary once you get the hang of it.

I think you're right though, I'm going to try making the lb4 discover command and use EJS templates.

[marvinirwin]

This is my attempt to write the discovery command

https://github.com/marvinirwin/loopback-next/tree/discovery

git clone https://github.com/marvinirwin/loopback-next
cd loopback-next
git checkout discovery
cd packages/cli
npm link
echo "{}" >> generators/datasource/connectors.json
cd /SOME_LOOPBACK_4_PROJECT_WITH_A_DISCOVERABLE_DATASOURCE_AND_DIST_FOLDER
lb4 discover --schema "database/schema"

1. If option dataSourcePath is not set then discover all datasource files and select one to load.
2. Get all models for our datasource and ask the user which model's they'd like to generate
3. Discover their definitions
4. In scaffold, proceeed to iterate through all the models and write it using the model.ts.ejs template.

In my case I get all the datasources from ./dist/src/datasources/*.datasource.js and then I require them to run the discovery functions. Is this the ideal way to run it? It's the simplest way I could think of things.

Changes/overhauls/criticism are welcome, I've written never anything with yeoman before.

[marvinirwin]

I added a bunch more to my discover feature.

• --dataSource, --schema, --table options to model discoverer so it can use a datasource
• Integration tests for the discover feature. Along the lines of the repository/datasource tests. I use a
• --schemaDefinitions option to point it towards a JSON file with model definition JSON in it. I can avoid needing a database for the integration test.
• Small refactors around the cli package and its tests.

Is it useful for me to develop this further, or write some documentation for it so that it can be used? I don't mind redoing it all, most of the time was spent figuring out the structure/conventions of the project.