Skip to main content

Database

We use PostgreSQL as our primary database and we use two separate PostgreSQL databases:

  • ardb - the database used for packages related data
  • identitydb - the database used for user related data

We use **ClickHouse as the data store for the events comming from various sources, such as browser extension, VS Code extension, GIT providers, and more.

Migrations​

We have a separate service that is responsible for running migrations: /go/cmd/ardb-migrations. Pending migrations are automatically run on server startup in a single transaction. They are embedded into the server binary. In the local environment you need to run the service each time you add any migrations to apply them to the local database.

Adding Migration to ARDB Database​

To add a new migration to the ardb database:

  1. Add a new file with the next number in the sequence to go/ardb/migrations.
  2. Restart your server, the migration should automatically run.

Adding Migration to IdentityDB Database​

To add a new migration to the identitydb database:

  1. Add a new file with the next number in the sequence to go/identitydb/migrations.
  2. Restart your server, the migration should automatically run.

Adding Migration to Analytics Database​

To add a new migration to the analyticsdb database:

  1. Add a new file with the next number in the sequence to go/analyticsdb/migrations.
  2. Restart your server, the migration should automatically run.

Queries​

Depending on the database we use different tool to run queries.

PostgreSQL​

For ardb and identitydb we use a tool called sqlc to automatically generate typed Go code to access our database. This approach puts SQL first, avoids typical impedance mismatch and generates efficient Go code. To use this, you’ll need to familiarize yourself with sqlc, add a query to the proper file (or create a new one):

Once that is done run just gen in the repository root and the Go code for the queries will be generated.

ClickHouse​

deprecated

For running the queries to ClickHouse we use GORM. The files with the queries are located in events/queries. You can find examples on how to run the queries in the /go/events/client_clickhouse.go.

There is also a possibility of running raw requests to ClickHouse. You can find the examples on how to do that the /go/events/clickhouse_digest_scope.go.

Reset Database​

During development it may be necessary to reset your database. The easiest way to do this for the PostgreSQL databases is:

dropdb archer; drop identity; drop analytics; createdb archer; createdb identity; createdb analytics;

If you need to reset the ClickHouse database the easiest way is to just completely delete the ClickHouse Docker container and its disks and launch it once again.

Keep in mind that you need to run migrations service to re-create the tables in correct order.