# Database Operation

This document will describe how to access database of central service and make db migrations.

## Prepare

Before everything, make sure that you have switched to the appropiate cluster (context) and namespace for Kubectl.

For example, for developers, you may:

```shell
# Switch to use the `dev-base` cluster.
# You can also execute `kubectl ctx` to select a cluster (context) interactively.
kubectl ctx `kubectl config get-contexts --output=name | grep dev-base`
# >  Switched to context "arn:aws:eks:us-west-2:...:cluster/dev-base-us-west-2".

# Switch to your namespace
kubectl ns ${PROJECT}
# >  Context "arn:aws:eks:us-west-2:...:cluster/dev-base-us-west-2" modified.
# >  Active namespace is "...".
```

Make sure you've setup pulumi config as described in [Development Environment](development-env.md).

## Access Database

If you are using a shared database, you can access it from inside your project namespace.

```shell
cd deploy/aws

dbuser=$(pulumi config get sharedMetadbUser)
dbhost=$(pulumi config get sharedMetadbAddress)
dbpassword=$(pulumi config get sharedMetadbPassword)

# connect it in pod
kubectl -n "$PROJECT" run --rm mysql-client -it --image=imega/mysql-client -- mysql -u "$dbuser" -h "$dbhost" -P 4000 -p"$dbpassword"
# or connect it in local
mysql -u "$dbuser" -h "$dbhost" -P 4000 -p"$dbpassword"
```

If you deployed a TiDB Cluster in the namespace of your project, you can view it with:

```shell
kubectl get po | grep db
```

Sample output:

```text
db-discovery-5777d57db7-h47cf       1/1     Running     0          64m
db-pd-0                             1/1     Running     0          64m
db-tidb-0                           2/2     Running     0          61m
db-tidb-initializer-47qs4           0/1     Completed   0          64m
db-tikv-0                           1/1     Running     0          62m
```

The SQL root user is protected by a randomly generated password. To retrieve the password:

```shell
kubectl get `kubectl get secrets --output=name | grep tidb-secret` -o jsonpath="{['data']['root']}" | base64 -d
```

Then you can login the tidb service via mysql-client:

```shell
kubectl port-forward svc/db-tidb 4020:4000
mysql -u root -h 127.0.0.1 -P 4020 -p
```

> If you find port-forward is unstable (due to network conditions, etc), you may try with [Kube Forwarder](https://kube-forwarder.pixelpoint.io/) which supports auto-reconnection.

**Note:** For MySQL 8.0 clients, you may need to use the following command instead:

```shell
mysql -u root -h 127.0.0.1 -P 4020 -p --default-auth=mysql_native_password --default-character-set=utf8
```

## DB Migration

```
mysql> show databases;
+----------------------------+
| Database                   |
+----------------------------+
| INFORMATION_SCHEMA         |
| METRICS_SCHEMA             |
| PERFORMANCE_SCHEMA         |
| $PROJECT_central           |
| $PROJECT_billing           |
+----------------------------+
```

In central service, basic data such as users and clusters are stored in the `dbaas` database. If you are using a shared database, the database name is `$PROJECT_central`.

All models are defined in [entity.go](https://github.com/tidbcloud/dbaas/blob/master/central/db/entity/entity.go) file. And we use [go-queryset](https://github.com/jirfag/go-queryset) pkg to generate queryset for CRUD.
For example, you want to add a User model. You need to add following User struct to [entity.go](https://github.com/tidbcloud/dbaas/blob/master/central/db/entity/entity.go) file. Take a look at line `// gen:qs`. It's a necessary line to enable querysets for this struct.

```go
// gen:qs
type User struct {
	System

	FirstName string
	LastName  string
	// This comes from Auth0
	Email string `gorm:"unique_index;not null" valid:"email"`

	Status     central.UserStatus `gorm:"not null" valid:"required"`
	InviteTime *time.Time
	Deleted    *UserChange `gorm:"foreignkey:DeletedID"`
	DeletedID  *uint32     `valid:"required"`
	IsRoot     bool        `gorm:"default:false"`
}
```

Then execute the following command to update [autogenerated_entity.go](https://github.com/tidbcloud/dbaas/blob/master/central/db/entity/autogenerated_entity.go) file:

```shell
$ cd central
$ make db/entity/autogenerated_entity.go
make: `db/entity/autogenerated_entity.go' is up to date.
```

In this autogenerated file you will find a lot of autogenerated queryset for CRUD. Learn more in the README of [go-queryset](https://github.com/jirfag/go-queryset).

For db migration, you need to add `User` model to the list of `Models` in [entity.go](https://github.com/tidbcloud/dbaas/blob/master/central/db/entity/entity.go) file. Then write a database migration file in [central_migrate_record](https://github.com/tidbcloud/dbaas/tree/master/central/migrate/central_migrate_record) folder. If you want to modify database schema, you need to update model definition in [entity.go](https://github.com/tidbcloud/dbaas/blob/master/central/db/entity/entity.go) file and write database migration file as well. When using `pulumi up` to update the central service, central service will auto migrate the changes.

## Troubleshooting

- Enable database query debug log

  ```
  pulumi config set enableDBQueryLog true
  ```

## Caution

- If another table is preloaded when querying a table, update the field of the able will also update the contents of the associated table.
  For example, `cluster.Update(hasDB.DB(), entity.ClusterDBSchema.OperatorDeletedAt)` will update `reserved_addresses` table if cluster `PreloadMysqlAddress()`. See details in [Check db Update and Preload logic](https://github.com/tidbcloud/dbaas/issues/2513) issue.