Database assertions after HTTP requests

hitspec run tests/ --allow-db

### Create a user and verify in database
# @name createUser
# @db sqlite://./test.db

POST {{baseUrl}}/api/users
Content-Type: application/json

{
  "name": "Jane Doe",
  "email": "jane@example.com"
}

>>>
expect status 201
<<<

>>>db
query SELECT COUNT(*) as count FROM users WHERE email = 'jane@example.com'
expect count == 1
<<<

### PostgreSQL example
# @db postgres://testuser:testpass@localhost:5432/testdb

### MySQL example
# @db mysql://root:password@localhost:3306/myapp

### SQLite example
# @db sqlite://./test.db

>>>db
query SELECT name, email, active FROM users WHERE id = 1
expect name == "Jane Doe"
expect email == "jane@example.com"
expect active == true
<<<

### Create order
# @name createOrder
# @db postgres://{{dbUser}}:{{dbPass}}@localhost:5432/{{dbName}}

POST {{baseUrl}}/api/orders
Content-Type: application/json

{
  "product_id": 42,
  "quantity": 3
}

>>>
expect status 201
expect body.id exists
<<<

>>>capture
orderId from body.id
<<<

>>>db
query SELECT product_id, quantity, status FROM orders WHERE id = {{createOrder.orderId}}
expect product_id == 42
expect quantity == 3
expect status == "pending"
<<<

### Delete user
# @name deleteUser
# @depends createUser
# @db sqlite://./test.db

DELETE {{baseUrl}}/api/users/{{createUser.userId}}

>>>
expect status 204
<<<

>>>db
query SELECT COUNT(*) as count FROM users WHERE id = {{createUser.userId}}
expect count == 0
<<<

### Bulk import
# @name bulkImport
# @db postgres://{{dbUser}}:{{dbPass}}@localhost:5432/{{dbName}}

POST {{baseUrl}}/api/import
Content-Type: application/json

{
  "file": "data.csv"
}

>>>
expect status 200
<<<

>>>db
query SELECT COUNT(*) as total FROM imports WHERE batch_id IS NOT NULL
expect total > 0
<<<