Querying the user list from the database

Question

There is a need from a different team in our organization to provide a periodic export of users, roles, and users-roles relationship data in a certain Aveksa-specific format.

Where (which table(s)) are the list of Drupal 8 users and roles stored in Drupal database? I've looked at all the tables listed in the schema and examined their records, and could not find anything that resembled user/roles data.

I figure querying the database is the way to go rather than looking at the /admin/people section of Drupal and scraping the data from there.

Answer 1

If you're set on scraping this out of the database, you could try looking at the user__roles table that Kevin mentioned in his comment above:

mysql> SELECT * FROM user__roles;
+--------+---------+-----------+-------------+----------+-------+-----------------+
| bundle | deleted | entity_id | revision_id | langcode | delta | roles_target_id |
+--------+---------+-----------+-------------+----------+-------+-----------------+
| user   |       0 |         1 |           1 | en       |     0 | administrator   |
+--------+---------+-----------+-------------+----------+-------+-----------------+
1 row in set (0.00 sec)

But since this is Drupal, you have other methods at your disposal.

For example, you could create a View of users and roles, and then use Views Data Export to export.

This module also exposes a drush command that can execute the view (interactively, or via scheduled jobs from the system crontab) and save its results to a file.

drush views-data-export [view-name] [display-id] [output-file]