A Next.js-based authentication project using the Better-Auth plugin for organizations, adapted to work with the Nile database.
While we can use the Better-Auth organization plugin with Nile without modifications, doing so loses the tenant isolation features that Nile offers.
For the Better-Auth organization plugin to work with Nile, we need to achieve the following 4 things:
- To store organizations, we must use the integrated
tenants
table instead oforganizations
, because we need a reference to this table (tenant_id
) to isolate tenants. - To store organization members, we must use the
tenant_users
table instead of themembers
table, as this allows us to check if a user has access to a tenant using the Nile SDK. Example:nile.getInstance({ tenantId: tenantId, userId: userId, api: { token: token, }, });
To achieve this, we use Better-Auth's optional schema definition to utilize the required tables see commit 792fa22.
- To store user information, optionally, we can use the integrated
users
table from Nile instead of theuser
table used by Better-Auth. While this does not affect Nile's operation, using theusers
table prevents having two tables performing the same function.
To use these tables, we simply define a custom schema for the plugin in the auth.ts
file. See commit.
The organization plugin has multiple foreign keys that reference different tables. To integrate it with Nile, we must sacrifice some of them because Nile has limitations on using foreign keys between shared and isolated tables. See: tenant-sharing. It also has a limitation when using unique columns in the tenants
table.
-
Use of unique for organization slugs: We replace:
"slug" text not null unique
with:
"slug" text not null
Although multiple rows with the same slug can now exist in the table, Better-Auth performs a check before creating a new organization, so this modification does not affect functionality but does affect data integrity.
-
Foreign keys: To adapt foreign keys to Nile while considering the limitations of shared tables, the user reference is moved to a table that is isolated. See images for details.
Better-Auth Plugin Table Relationships
Custom Relationships: The
users
table remains shared, so it will have no foreign keys. -
Make the organization plugin work with UUID instead of nanoid: Although Better-Auth provides a function to generate custom IDs, it currently does not work with the plugins. To fix this, we modify the function used to generate the ID.
-
Modify some CRUD operations to adapt to using composite primary keys: To do this, we need to add the
tenant_id
reference to some CRUD operations. For example, we change from:where: [ { field: "id", value: memberId, }, ],
to:
where: [ { field: "id", value: memberId, }, { field: "organizationId", value: organizationId, }, ],
-
Modify the role type to be compatible with Nile: In the Better-Auth plugin, organization member roles are stored as strings, whereas Nile stores them as an array. To use the
roles
column integrated in Nile, we modify how Better-Auth stores roles, for example, changing from"admin"
to['admin']
. For more details, see commit.