-
-
Notifications
You must be signed in to change notification settings - Fork 495
Migrate database schema does not work on empty database #5548
Replies: 1 comment · 2 replies
-
This is executed automatically when you do
You first create the schema and then call this method. What the method does is that it creates the migration based on metadata, sees you actually have the schema already in place, and logs the migration as executed because of that. Also if you drop the database, you need to remove the migrations snapshot file, that could cause the latter issue - I guess that's something we should do automatically.
No idea what
That method returns boolean, not array.
Why not latest?
You are not supposed to modify the file anyhow, why are you asking? |
Beta Was this translation helpful? Give feedback.
All reactions
-
Also generally speaking I would discourage this mix of using schema generator and migrations. You should use one or the other, not both at the same time. The migrations respect the snapshot file while schema generator ignores it, so you can end up with similar weird things happening. |
Beta Was this translation helpful? Give feedback.
All reactions
-
Thanks for your response! 🙏
Well, yeah, however, if I don’t want the non-admin DB user to create databases, I need to create the database as the admin DB user with the non-admin DB user as the DB owner (which is what I do). I actually don’t use
Okay, therefore, I should commit the migration files to Git, however, the snapshot files should be put to
It is a top-level property of a snapshot file. I was just curious whether it is a PostgreSQL schema (not a DB schema/structure), as I use different names for PostgreSQL schemas (I never use This is related to the snapshot JSON schema / TS types.
My bad, sorry. It returned
I am struggling with this for some time. I didn’t want to update the deps until I figure this out. No other reason.
Okay. Then, I’d use migrations. Now, I’d like to do something like this:
Notes:
Below is a flowchat of what I want to achieve. Note: In the flowchart, I used a hexagon node instead of a diamond/rhombus, as the rhombus takes too much space, as I make too many decisions in the charts. This made the chart a bit smaller. Migration flowchartflowchart TD
S[Start]
-->|Outside\nNode script| CreateAdminUser[(Create admin user)]
-->|Inside\nNode script| OrmRegular1{{Create regular user\nORM instance}}
-->|success| GetMigrationNumber[Migrate DB schema]
OrmRegular1
-->|failure| OrmAdmin{{Create\nORM instance\nadmin user}}
-->|success| MakeSureUserExists{{Make sure\nthe regular user exists}}
-->|success| CheckIfDbExists{{Check if the DB\nexists}}
-->|success| MakeSureTheDbOwner{{Create the DB or\nchange its owner\nif it exists}}
-->|success| CreateExt{{Create `btree_gist`\nif it does not exist}}
-->|success| OrmRegular2{{Re-create regular user\nORM instance}}
-->|success| GetMigrationNumber{{Get the total\nnumber of migrations}}
-->|0| CreateInitialMigration{{Create initial migration}}
-->|success| OrmAdminClose[Close admin ORM instance]
GetMigrationNumber
-->|>0| MigrateDbSchema{{Migrate DB schema}}
-->|success| OrmAdminClose
--> OrmRegularClose[Close regular ORM instance]
--> End[End]
OrmAdmin -->|failure| Error[Throw an error]
MakeSureUserExists -->|failure| Error
CheckIfDbExists -->|failure| Error
MakeSureTheDbOwner -->|failure| Error
OrmRegular2 -->|failure| Error
CreateExt -->|failure| Error
CreateInitialMigration -->|failure| Error
GetMigrationNumber -->|failure| Error
MigrateDbSchema -->|failure| Error
Error --> OrmAdminClose
Here’s the code I have created. In comparison with the flowchart, I have added some extra steps which were omitted from the chart to make it more readable and smaller; however, the main algorithm is the same. The only thing I miss is a way to get if there are any migrations already created. I could list all files in the migrations folder, however, I believe the migrator has already read them, thus the question is whether we can get the total number of migrations: can I get the total number of migrations using Migration codeimport {Config, getEnvConfig} from '@controlserver/common'
import {ALL_ENTITIES, ORM_CONFIG} from '@controlserver/db'
import {Migrator} from '@mikro-orm/migrations'
import {IMigrator, MikroORM} from '@mikro-orm/postgresql'
export async function initDb(): Promise<void> {
let config: Config
let doesDatabaseExist: number[]
let exitCode = 0
let isOrmCreated = false
let migrator: IMigrator
let orm: AsyncReturnType<typeof MikroORM.init>
let ormAdmin: AsyncReturnType<typeof MikroORM.init>
const logger = console
try {
await [
// Get environment configuration
async (): Promise<void> => {
config = await getEnvConfig()
},
async (): Promise<void> =>
logger.info('Starting database initialisation ...'),
// Check if `config.db.user.name` and `config.db.user.pass` are defined and are non-empty strings
async (): Promise<void> => {
if (config.db.user.name.length < 1) {
logger.error(
`Invalid regular database username: '${config.db.user.name}'`
)
exitCode = 2
}
if (config.db.user.pass.length < 1) {
logger.error(
`Invalid regular database user pass: '${config.db.user.pass}'`
)
exitCode = 3
}
},
// Create an ORM instance for the regular database user
async (): Promise<void> => {
try {
orm = await MikroORM.init({
...ORM_CONFIG,
entities: ALL_ENTITIES,
extensions: [Migrator],
logger: logger.debug.bind(logger),
password: config.db.user.pass,
user: config.db.user.name
})
migrator = orm.getMigrator()
isOrmCreated = true
} catch (e) {
logger.debug(
'Failed to connect to the database using regular DB user credentials:',
e
)
isOrmCreated = false
}
},
// Create an ORM instance for admin database user if the one for regular user failed to be created.
// Note: Using admin database user is optional. However, if we don't get admin user credentials,
// the some of the following commands will fail (like database or user creation),
// unless the regular user has proper privileges assigned.
async (): Promise<void> => {
if (!isOrmCreated) {
try {
ormAdmin = await MikroORM.init({
...ORM_CONFIG,
dbName: 'postgres',
entities: ALL_ENTITIES,
extensions: [Migrator],
logger: logger.debug.bind(logger),
password: config.db.admin.pass,
user: config.db.admin.name
})
} catch (e) {
logger.error(
`Failed to connect to the database using admin DB user credentials:`,
e)
exitCode = 4
}
}
},
// Make sure the regular user exists
// Note: The user is created only if the database does not exist or if the database is owned
// by a different user than `config.db.user.name`.
async (): Promise<void> => {
if (!isOrmCreated) {
try {
await ormAdmin.em.getConnection().execute(`
do $$ begin if not exists (select * from pg_user where usename = '${config.db.user.name}')
then create user ${config.db.user.name} with password '${config.db.user.pass}'; end if; end $$;
`)
logger.info(
`Regular database user created successfully or it has already existed`
)
} catch (e) {
logger.error(
`Failed to make sure the regular database user exists:`,
e
)
exitCode = 5
}
}
},
// Check if the database already exists
async (): Promise<void> => {
if (!isOrmCreated) {
try {
doesDatabaseExist = await ormAdmin.em
.getConnection()
.execute(
`select 1 from pg_database where datname = '${config.db.name}'`
)
console.log({doesDatabaseExist, type: typeof doesDatabaseExist})
logger.info(`Database existence checked successfully`)
} catch (e) {
logger.error(
`Failed to check if the database exists`,
e
)
exitCode = 6
}
}
},
// Create the database or change its owner if it exists
async (): Promise<void> => {
if (!isOrmCreated) {
try {
if (doesDatabaseExist.length === 0) {
// The database does not exist yet
await ormAdmin.em
.getConnection()
.execute(
`create database ${config.db.name} with owner = ${config.db.user.name}`
)
logger.info(`Database created successfully`)
} else {
// The database already exists
await ormAdmin.em
.getConnection()
.execute(
`alter database ${config.db.name} owner to ${config.db.user.name}`
)
logger.info(`Database ownership altered successfully`)
}
} catch (e) {
logger.error(
`Failed to create the database or alter its ownership`,
e
)
exitCode = 7
}
}
},
// Create `btree_gist` if it does not exist
async (): Promise<void> => {
if (!isOrmCreated) {
try {
await ormAdmin.em
.getConnection()
.execute('create extension if not exists btree_gist')
logger.info(
`'btree_gist' extension created successfully or it already exists`
)
} catch (e) {
logger.error(
`Failed to create the 'btree_gist' extension:`,
e
)
exitCode = 8
}
}
},
// Re-create regular user ORM instance if it failed earlier
// Note: This could happen if the user did not exit yet.
async (): Promise<void> => {
if (!isOrmCreated) {
try {
orm = await MikroORM.init({
...ORM_CONFIG,
entities: ALL_ENTITIES,
extensions: [Migrator],
logger: logger.debug.bind(logger),
password: config.db.user.pass,
user: config.db.user.name
})
migrator = orm.getMigrator()
logger.info(
`Connected to the database using regular DB user credentials successfully`
)
} catch (e) {
logger.error(
'Failed to connect to the database using regular DB user credentials',
e
)
exitCode = 9
}
}
},
// Get the total number of migrations
async (): Promise<void> => {
try {
const migrationNumber = {
executed: (await migrator.getExecutedMigrations()).length,
pending: (await migrator.getPendingMigrations()).length
}
logger.debug(
`Number of executed migrations: ${migrationNumber.executed}`
)
logger.debug(
`Number of pending migrations: ${migrationNumber.pending}`
)
if (migrationNumber.executed + migrationNumber.pending === 0) {
// Create initial migration
await migrator.createInitialMigration()
migrationNumber = {
executed: (await migrator.getExecutedMigrations()).length,
pending: (await migrator.getPendingMigrations()).length
}
logger.debug(
`Number of executed migrations after creating the initial migration: ${migrationNumber.executed}`
)
logger.debug(
`Number of pending migrations after creating the initial migration: ${migrationNumber.pending}`
)
}
// Run pending migrations
await migrator.up()
} catch (e) {
logger.error(
'Failed to migrate the database',
e
)
exitCode = 10
}
},
async (): Promise<void> =>
logger.info('Database initialisation completed successfully')
].reduce(
(a: Promise<void>, c: () => Promise<void>): Promise<void> =>
a.then(c).catch(() => {
exitCode = 1
}),
Promise.resolve()
)
} finally {
// @ts-ignore-next-line TS2454 // Variable orm is used before being assigned
if (await orm?.isConnected()) {
try {
// @ts-ignore-next-line TS2454 // Variable orm is used before being assigned
await orm.close()
logger.info(`Database connection using regular database user closed successfully`)
} catch (e) {
logger.error(
`Failed to close database connection using regular database user:`,
e
)
}
}
// @ts-ignore-next-line TS2454 // Variable orm is used before being assigned
if (await ormAdmin?.isConnected()) {
try {
// @ts-ignore-next-line TS2454 // Variable orm is used before being assigned
await ormAdmin.close()
logger.info(`Database connection using admin database user closed successfully`)
} catch (e) {
logger.error(
`Failed to close database connection using admin database user:`,
e
)
}
}
if (exitCode > 0) {
logger.debug(`Exiting with code number ${exitCode}`)
process.exit(exitCode)
}
}
}
/** Get the type of async function */
// eslint-disable-next-line @typescript-eslint/no-explicit-any
type AsyncReturnType<T extends (..._args: any) => Promise<any>> = Awaited<
ReturnType<T>
> Now, here are some issues with my code (always tested after dropping the database with no change to the entities, as well as removing the snapshot file):
What would cause this? How to fix this? 🤔 I Presume it is related to All I want to do is:
|
Beta Was this translation helpful? Give feedback.
-
Why are the migrations not executed/found when I execute them programmatically?
I run the following commands (note that the
config
is always the same apart from the username and its password):Then, I drop the database and run the following commands:
If needed, I can create a repo with reproduction.
Note that while IWhy are the migrations not executed/found when I execute them programmatically?
I run the following commands (note that the
config
is always the same apart from the username and its password):Then, I drop the database and run the following commands:
If needed, I can create a repo with reproduction.
Note that while I use Nest.js, I run this particular script outside it (before boostrapping the app).
Thanks in advance! 🙏
OT: When can I find the JSON schema or TS types for snapshot files? 🤔
Versions:
@mikro-orm/[email protected]
;@mikro-orm/[email protected]
;@mikro-orm/[email protected]
;@mikro-orm/[email protected]
;@mikro-orm/[email protected]
;@mikro-orm/[email protected]
;@mikro-orm/[email protected]
.Beta Was this translation helpful? Give feedback.
All reactions