But isn't that insecure,
There is no absolute scale of "secure", indeed it is not even a vector or scalar property. (IT) Security is about ensuring that your technology does not do things it is not intended to do - while continuing to do the things it should. This is a very long winded way of saying you should have told us what the threat model is.
Leaving aside the many questions about how your application persists the pasword and whether it needs a password at all and the fact that your method is dependant on the root user not having a password....
Your current method will expose the password (briefly) to other users via 'ps' on Unix/Linux (and probably something similar on MSWindows - you didn't tell us what OS this sits on, but the 'bash' tag kind of implies Unix/Linux). And if it was run directly at the command line, will be present in audit logs and your shell history - although you said this was in a script. There are potential path and alias exploits a third party could use (if they have access) to subvert the behaviour - although is starting to get a little esoteric.
So considering only the issue described in isolation, and constrained by the only tools available being bash, then:
echo "grant all on database.* to 'user'@'localhost' identified by '$PW';" | mysql
is slightly better (by default,
echo is a bash builtin and hence does not start a new process with a command line).