1. 更新数据库连接字符串

假设你已经有一个名为 postgres 的用户，并且密码为 123456，更新数据库连接字符串如下：

DATABASE_URL=postgresql://postgres:123456@localhost/workflow-visualization

将 .env 文件中的连接字符串更新为：

DATABASE_URL=postgresql://your_user:your_password@localhost/workflow-visualization

2. 创建新的数据库用户

如果你需要创建一个新的数据库用户，执行以下步骤：

1. 登录 PostgreSQL：

sudo -u postgres psql

2. 创建新的用户并设置密码：

CREATE USER your_user WITH PASSWORD 'your_password';

3. 创建数据库并授予用户权限：

CREATE DATABASE "workflow-visualization";
GRANT ALL PRIVILEGES ON DATABASE "workflow-visualization" TO your_user;

4. 确认更改 .env 文件中的连接字符串：

DATABASE_URL=postgresql://your_user:your_password@localhost/workflow-visualization

3. 确认 Alembic 配置

确保在 alembic.ini 文件中也更新了数据库连接字符串：

sqlalchemy.url = postgresql://your_user:your_password@localhost/workflow-visualization

4. 配置 SQLAlchemy

确保 app/core/config.py 文件正确读取环境变量：

from pydantic_settings import BaseSettings

class Settings(BaseSettings):
    DATABASE_URL: str

    class Config:
        env_file = ".env"

settings = Settings()

确保 app/db/database.py 文件正确配置数据库引擎：

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from app.core.config import settings

engine = create_engine(settings.DATABASE_URL)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()

def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

5. Alembic 配置

配置 Alembic 来进行数据库迁移。以下是如何使用 Alembic 创建和应用迁移的具体步骤。

初始化 Alembic

首先，确保你已安装 Alembic：

pip install alembic

在项目根目录初始化 Alembic：

alembic init alembic

这将在你的项目中创建一个名为 alembic 的目录，包含 Alembic 的配置文件 alembic.ini 和目录结构。

配置 Alembic

编辑 alembic.ini 文件，配置数据库连接字符串：

# alembic.ini
[alembic]
# ...

sqlalchemy.url = postgresql://your_user:your_password@localhost/workflow-visualization

修改 Alembic 环境配置

编辑 alembic/env.py 文件，导入你的模型并配置目标元数据：

from logging.config import fileConfig
from sqlalchemy import engine_from_config, pool
from alembic import context

# Import your models here
from app.db.models import Base

# this is the Alembic Config object, which provides
# access to the values within the .ini file in use.
config = context.config

# Interpret the config file for Python logging.
# This line sets up loggers basically.
fileConfig(config.config_file_name)

# add your model's MetaData object here
# for 'autogenerate' support
target_metadata = Base.metadata

def run_migrations_offline():
    """Run migrations in 'offline' mode.
    This configures the context with just a URL
    and not an Engine, though an Engine is also acceptable
    here.  By skipping the Engine creation we don't even need
    a DBAPI to be available.
    Calls to context.execute() here emit the given string to the
    script output.
    """
    url = config.get_main_option("sqlalchemy.url")
    context.configure(
        url=url,
        target_metadata=target_metadata,
        literal_binds=True,
        dialect_opts={"paramstyle": "named"},
    )

    with context.begin_transaction():
        context.run_migrations()

def run_migrations_online():
    """Run migrations in 'online' mode.
    In this scenario we need to create an Engine
    and associate a connection with the context.
    """
    connectable = engine_from_config(
        config.get_section(config.config_ini_section),
        prefix="sqlalchemy.",
        poolclass=pool.NullPool,
    )

    with connectable.connect() as connection:
        context.configure(
            connection=connection, target_metadata=target_metadata
        )

        with context.begin_transaction():
            context.run_migrations()

if context.is_offline_mode():
    run_migrations_offline()
else:
    run_migrations_online()

生成初始迁移

生成初始迁移脚本：

alembic revision --autogenerate -m "Initial migration"

这将在 alembic/versions/ 目录下生成一个新的迁移脚本文件。

应用迁移

运行迁移将变更应用到数据库：

alembic upgrade head

这样可以解决密码认证失败的问题，确保你使用正确的用户名和密码连接到 PostgreSQL 数据库。