# SQLx Migration Management Documentation

## Installing SQLx CLI

To manage database migrations, you only need the SQLx CLI:

```
cargo install sqlx-cli
```

## Setting Up the Database Connection

1. Set the database URL as an environment variable:
   ```
   export DATABASE_URL=postgres://username:password@database_server/your_database_name
   ```

2. Alternatively, create a `.env` file in your project root:
   ```
   DATABASE_URL=postgres://username:password@database_server/your_database_name
   ```

3. Automate building DATABASE_URL environment variable using password-store
  ```
  pass edit dev/postgresql/db
  PASSWORDVALUE
  username:sqluser
  hostname:192.168.1.x
  port:32768
  ```
  source setup_env.sh to set the DATABASE_URL using password-store

## Creating the Database

If your database doesn't exist yet, you can create it using the SQLx CLI:
   ```
   sqlx database create
   ```

## Migration Script Usage
### Usage

Create both an up and down migration script
   ```
   sqlx migrate add -r <migration_name>
   ```

## Writing Migrations
### Up Migrations

The up.sql file should contain SQL commands to apply your desired changes to the database schema.

Example for creating a users table:

```sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Add any additional tables or alterations here
```

### Down Migrations

The down.sql file should contain SQL commands to revert the changes made in the corresponding `up.sql` file.

Example for reverting the users table creation:

```sql
DROP TABLE users;

-- Revert any additional changes made in the up migration
```

## Best Practices

1. **Atomicity**: Each migration should be self-contained and represent a single, atomic change to the database schema.
2. **Idempotency**: Migrations should be idempotent, meaning they can be run multiple times without causing errors or unintended side effects.
3. **Data Preservation**: Be cautious when writing `down` migrations that involve data loss. Consider adding data backup steps if necessary.
4. **Testing**: Always test both `up` and `down` migrations to ensure they work as expected.
5. **Comments**: Add comments to your SQL files to explain complex changes or reasoning behind certain decisions.

## Running Migrations

To apply migrations:
```
sqlx migrate run
```

To revert the last migration:
```
sqlx migrate revert
```

Remember, SQLx keeps track of applied migrations in a `_sqlx_migrations` table in your database, ensuring each migration is only applied once.

## Troubleshooting

- If the script fails to create files, ensure you have write permissions in the `migrations` directory.
- If SQLx fails to find your migrations, make sure you're running the `sqlx` commands from the root of your project where the `migrations` folder is located.
- If you encounter connection issues, double-check your `DATABASE_URL` and ensure your PostgreSQL server is running.

For more advanced usage and configuration options, refer to the SQLx documentation.