I am a new MySQL learner and like to use it within a NodeJS application. From my previous knowledge with MongoDB all the tables we need will be created by MongoDB automatically. We only needed to connect to the MongoDB and with create or save commands it could find the table and put something inside or create the table if it was not exist.

But from MySQL tutorials I have seen so far they create databases manually by executing SQL codes directly inside a MySQL shell/bash or by using applications like MySQL Workbench.

So I want to make sure which way is correct? Should I create my tables before running my Node app or there is also a way, and it is a good way to create tables dynamically within the code when it's needed regarding the app usage by the user? If the second way is a better way, how can I do that? Is there a code sample for creating databases from inside a Node app?

Moreover the same question for databases. Can we or is it a good way creating them from inside our Node app?

CodePudding user response：

Both are possible.
You can create a piece of code that will check the schema and update it if it is not (there are lots of tools and for all languages).
In my experience, in most cases we tend to create the tables, indexes, views, etc. by hand first and then run the application. For larger companies / projects, they often use tools. I personally have seen a lot of flyway in the Java world.
In any case, the most important thing is that your creation script is idempotent. For this, you can use the if not exists statements : https://dev.mysql.com/doc/refman/8.0/en/replication-features-create-if-not-exists.html

CodePudding user response：

In sql-based applications standard tables are created when you install the application giving it a fix structure. Table structure is only changed during program updates as structure changes can be a very expensive operation. The initial table creation, subsequent changes can be done via sql scripts or by application code - both are acceptable practices. But the main point stands: permanent tables are not created on the fly.

Some complex scripts may require temporary tables that are created and destroyed on the fly, but most of these will be done implicitly (e.g. database engine may materialise the resultset of a subquery as a temporary table), rather than explicitly (application code executing create temporary table statement).

Rationale: RDBMSs have a rigid, inflexible data structure that is optimised towards data storage and retrieval speed. Any solution to introduce flexibility is at odds with relational theory and will come with its drawbacks (see Bill Karwin's excellent answer on this subject, particularly the last two pragraphs).

NoSQL solutions, however, more focus on flexibility, than data storage. Mongodb for example, does not even have tables with rows and columns. It has collections that are made up of documents and every document in a collection may have different properties. This is why you can create these collections and documents on the fly.