> ## Documentation Index
> Fetch the complete documentation index at: https://docs-staging-docs-ia-custom-database-connections.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
# Create User Database Action Script and Templates
> The Create script runs when a user signs up through Universal Login or when you create a user through the Auth0 Dashboard or Management API.
The Create script implements the defined function when a user is created. We recommend naming this function `create`.
This script is optional. If it's enabled, when a user signs up through Universal Login or is created through the Auth0 Dashboard or Auth0 Management API, Auth0 will run the script to create a corresponding user record in the external database.
When a user is created in Auth0, Auth0 calls a series of scripts:
1. [Get User](/docs/authenticate/database-connections/custom-db/templates/get-user): Verifies that the user does not already exist in Auth0 or the external database.
2. Create: Creates the user in the external database.
3. [Login](/docs/authenticate/database-connections/custom-db/templates/login): Verifies that the user was created successfully.
## Create function
The `create` function should:
* Send the user's profile data to the external database's API.
* Return an error if the user creation operation failed.
### Definition
The `create` function accept two parameters and returns a `callback` function:
```js theme={null}
create(user, callback): function
```
| Parameter | Description |
| ---------- | ---------------------------------------------------------------------------------------------------- |
| `user` | Object. Contains [user profile data](#user-object-parameter) sourced from the user creation process. |
| `callback` | Function. Used to pass error data through the pipeline. |
This is a pseudo-JavaScript example of how you could implement the `create` function:
```javascript lines expandable theme={null}
function create(user, callback) {
// Send user profile data to external database API
let hashedPassword = hash(user.password);
let options = {
url: "https://example.com/api/create",
body: {
email: user.email,
username: user.username,
password: hashedPassword
}
};
send(options, err => {
// Return error in callback if user already exists
if (err && err.id === "USER_ALREADY_EXISTS") {
return callback(new ValidationError("user_exists", "My custom error message."));
} else if (err) {
// Return error in callback if error occurred
return callback(new Error("My custom error message."));
}
// Return `null` value in callback if user creation operation succeeded
return callback(null);
});
}
```
### Encryption
Encrypt the password value using a cryptographic hash encryption library such as `bcrypt` to prevent any potential data leak. Avoid logging, storing, or transporting the password credential anywhere in its unencrypted form.
For example:
```js theme={null}
bcrypt.hash(password, 10, function (err, hash) {
if (err) {
return callback(err);
} else {
// Return hashed password
}
});
```
## Callback function
The `callback` function accepts one parameter and returns a function.
### Definition
```js theme={null}
callback(error): function
```
| Parameter | Type | Required | Description |
| --------- | ------ | -------- | -------------------- |
| `error` | Object | Required | Contains error data. |
### ValidationError type object
The `ValidationError` custom error type object allows you to pass data that will be displayed in your [Tenant Logs](/docs/deploy-monitor/logs).
The `ValidationError` constructor accepts up to two parameters:
```js theme={null}
new ValidationError(errorCode[, message]): ValidationError
```
| Parameter | Description |
| ----------- | -------------------------------------------------------- |
| `errorCode` | (Required) String. Specifies the type of error. |
| `message` | (Optional) String. Contains information about the error. |
### Return a success
If the user creation operation succeeded, return the `callback` function, and pass a `null` value as the `error` parameter:
```js theme={null}
return callback(null);
```
### Return an error
If an error occurs, return the callback function, and pass relevant error information to the the `error` parameter.
For example, you can return an error with the value of `user_exists` for the `errorCode` parameter:
```js wrap lines theme={null}
return callback(new ValidationError("user_exists", "My custom error message."));
```
If you do, Auth0 records an `fs` tenant log event.
| Tenant Log Event Field | Value |
| ---------------------- | -------------------------- |
| **Code** | `fs` |
| **Event** | Failed Signup |
| **Description** | `My custom error message.` |
## User object parameter
The `user` object parameter contains a predefined set of properties sourced from the user creation process:
| Property | Description |
| --------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `client_id` | The Auth0 application's client ID if the user signed up through Universal Login, or the API key if the user was created through the Auth0 Dashboard or Management API. |
| `tenant` | The Auth0 tenant name. |
| `email` | The user's email address. |
| `password` | The user's password in plain text. |
| `username` | The user's username. Required only if the custom database connection has the [**Requires Username**](/docs/authenticate/database-connections/require-username) setting enabled. |
| `connection` | The Auth0 connection name. |
| `user_metadata` | Contains the properties of the `user_metadata` object on the user's Auth0 profile, if the object exists. |
| `app_metadata` | Contains the properties of the `app_metadata` object on the user's Auth0 profile, if the object exists. |
### Username property
If your custom database connection has the [Requires Username](/docs/authenticate/database-connections/require-username) setting enabled, then the [Login](/docs/authenticate/database-connections/custom-db/templates/login) and [Get User](/docs/authenticate/database-connections/custom-db/templates/get-user) scripts must support the `username` property, so you should store it in your external database.
### User and app metadata
The `user_metadata` and `app_metadata` properties do not need to be stored in your external database. Auth0 automatically stores these values as part of the user profile record created internally.
### Custom sign up fields
If you [create and use custom fields](/docs/libraries/custom-signup#using-the-api) during the sign up process, they will be included in the `user` object. For example:
```json theme={null}
{
client_id: "8tkMo6n1QkKOazqPcSQd8wU7LzXYibgK",
tenant: "{yourAuth0Tenant}",
email: "username@domain.com",
password: "mySuperSecretPassword123",
username: "username456",
user_metadata: {
"language": "en"
},
app_metadata: {
"plan": "full"
}
}
```
## Language-specific script examples
Auth0 provides sample scripts for use with the following languages/technologies:
```javascript JavaScript lines expandable theme={null}
function create(user, callback) {
// This script should create a user entry in your existing database. It will
// be executed when a user attempts to sign up, or when a user is created
// through the Auth0 Dashboard or Management API.
// When this script has finished executing, the Login script will be
// executed immediately afterwards, to verify that the user was created
// successfully.
//
// The user object will always contain the following properties:
// * email: the user's email
// * password: the password entered by the user, in plain text
// * tenant: the name of this Auth0 account
// * client_id: the client ID of the application where the user signed up, or
// API key if created through the Management API or Auth0 Dashboard
// * connection: the name of this database connection
//
// There are three ways this script can finish:
// 1. A user was successfully created
// callback(null);
// 2. This user already exists in your database
// callback(new ValidationError("user_exists", "my error message"));
// 3. Something went wrong while trying to reach your database
// callback(new Error("my error message"));
const msg = 'Please implement the Create script for this database connection ' +
'at https://manage.auth0.com/#/connections/database';
return callback(new Error(msg));
}
```
```javascript ASP.Net MVC3 lines expandable theme={null}
// For ASP.NET Membership Provider (MVC3 - Universal Providers)
function create(user, callback) {
const crypto = require('crypto');
const sqlserver = require('tedious@1.11.0');
const Connection = sqlserver.Connection;
const Request = sqlserver.Request;
const TYPES = sqlserver.TYPES;
const connection = new Connection({
userName: 'the username',
password: 'the password',
server: 'the server',
options: {
database: 'the db name',
encrypt: true,
// Required to retrieve userId needed for Membership entity creation
rowCollectionOnRequestCompletion: true
}
});
const applicationId = 'your-application-id-goes-here';
/**
* hashPassword
*
* This function creates a hashed version of the password to store in the database.
*
* @password {[string]} the password entered by the user
* @return {[string]} the hashed password
*/
function hashPassword(password, salt) {
// the default implementation uses HMACSHA256 and since Key length is 64
// and default salt is 16 bytes, Membership will fill the buffer repeating the salt
const key = Buffer.concat([salt, salt, salt, salt]);
const hmac = crypto.createHmac('sha256', key);
hmac.update(Buffer.from(password, 'ucs2'));
return hmac.digest('base64');
}
connection.on('debug', function(text) {
// if you have connection issues, uncomment this to get more detailed info
// console.log(text);
}).on('errorMessage', function(text) {
// this will show any errors when connecting to the SQL database or with the SQL statements
console.log(JSON.stringify(text));
});
connection.on('connect', function(err) {
if (err) {
return callback(err);
}
createMembershipUser(user, function(err, user) {
if (err) return callback(err); // this will return a 500
if (!user) return callback(); // this will return a 401
callback(null, user);
});
});
function createMembershipUser(user, callback) {
const userData = {
UserName: user.email,
ApplicationId: applicationId
};
const createUser =
'INSERT INTO Users (UserName, LastActivityDate, ApplicationId, UserId, IsAnonymous) ' +
'OUTPUT Inserted.UserId ' +
'VALUES (@UserName, GETDATE(), @ApplicationId, NEWID(), \'false\')';
const createUserQuery = new Request(createUser, function(err, rowCount, rows) {
if (err) return callback(err);
// No records added
if (rowCount === 0) return callback(null);
const userId = rows[0][0].value;
const salt = crypto.randomBytes(16);
const membershipData = {
ApplicationId: applicationId,
Email: user.email,
Password: hashPassword(user.password, salt),
PasswordSalt: salt.toString('base64'),
UserId: userId
};
const createMembership =
'INSERT INTO Memberships (ApplicationId, UserId, Password, PasswordFormat, ' +
'PasswordSalt, Email, isApproved, isLockedOut, CreateDate, LastLoginDate, ' +
'LastPasswordChangedDate, LastLockoutDate, FailedPasswordAttemptCount, ' +
'FailedPasswordAttemptWindowStart, FailedPasswordAnswerAttemptCount, ' +
'FailedPasswordAnswerAttemptWindowsStart) ' +
'VALUES ' +
'(@ApplicationId, @UserId, @Password, 1, @PasswordSalt, ' +
'@Email, \'false\', \'false\', GETDATE(), GETDATE(), GETDATE(), GETDATE(), 0, 0, 0, 0)';
const createMembershipQuery = new Request(createMembership, function(err, rowCount) {
if (err) return callback(err);
if (rowCount === 0) return callback(null);
callback(null, rowCount > 0);
});
createMembershipQuery.addParameter('ApplicationId', TYPES.VarChar, membershipData.ApplicationId);
createMembershipQuery.addParameter('Email', TYPES.VarChar, membershipData.Email);
createMembershipQuery.addParameter('Password', TYPES.VarChar, membershipData.Password);
createMembershipQuery.addParameter('PasswordSalt', TYPES.VarChar, membershipData.PasswordSalt);
createMembershipQuery.addParameter('UserId', TYPES.VarChar, membershipData.UserId);
connection.execSql(createMembershipQuery);
});
createUserQuery.addParameter('UserName', TYPES.VarChar, userData.UserName);
createUserQuery.addParameter('ApplicationId', TYPES.VarChar, userData.ApplicationId);
connection.execSql(createUserQuery);
}
}
```
```javascript ASP.Net MVC4 lines expandable theme={null}
// For ASP.NET Membership Provider (MVC4 - Simple Membership)
function create(user, callback) {
const crypto = require('crypto');
const sqlserver = require('tedious@1.11.0');
const Connection = sqlserver.Connection;
const Request = sqlserver.Request;
const TYPES = sqlserver.TYPES;
const connection = new Connection({
userName: 'the username',
password: 'the password',
server: 'the server',
options: {
database: 'the db name',
encrypt: true,
// Required to retrieve userId needed for Membership entity creation
rowCollectionOnRequestCompletion: true
}
});
/**
* hashPassword
*
* This function hashes a password using HMAC SHA256 algorithm.
*
* @password {[string]} password to be hased
* @salt {[string]} salt to be used in the hashing process
* @callback {[function]} callback to be called after hashing the password
*/
function hashPassword(password, salt, callback) {
const iterations = 1000;
const passwordHashLength = 32;
crypto.pbkdf2(password, salt, iterations, passwordHashLength, 'sha1', function (err, hashed) {
if (err) return callback(err);
const result = Buffer.concat([Buffer.from([0], 1), salt, Buffer.from(hashed, 'binary')]);
const resultBase64 = result.toString('base64');
callback(null, resultBase64);
});
}
connection.on('debug', function (text) {
// if you have connection issues, uncomment this to get more detailed info
// console.log(text);
}).on('errorMessage', function (text) {
// this will show any errors when connecting to the SQL database or with the SQL statements
console.log(JSON.stringify(text));
});
connection.on('connect', function (err) {
if (err) return callback(err);
const createUser =
'INSERT INTO UserProfile (UserName) ' +
'OUTPUT Inserted.UserId ' +
'VALUES (@UserName)';
const createUserQuery = new Request(createUser, function (err, rowCount, rows) {
if (err || rowCount === 0) return callback(err);
const userId = rows[0][0].value;
const salt = crypto.randomBytes(16);
const createMembership =
'INSERT INTO webpages_Membership ' +
'(UserId, CreateDate, IsConfirmed, PasswordFailuresSinceLastSuccess, Password, PasswordSalt) ' +
'VALUES ' +
'(@UserId, GETDATE(), \'false\', 0, @Password, \'\')';
const createMembershipQuery = new Request(createMembership, function (err, rowCount) {
if (err || rowCount === 0) return callback(err);
callback(null, rowCount > 0);
});
hashPassword(user.password, salt, function (err, hashedPassword) {
if (err) return callback(err);
createMembershipQuery.addParameter('Password', TYPES.VarChar, hashedPassword);
createMembershipQuery.addParameter('PasswordSalt', TYPES.VarChar, salt.toString('base64'));
createMembershipQuery.addParameter('UserId', TYPES.VarChar, userId);
connection.execSql(createMembershipQuery);
});
});
createUserQuery.addParameter('UserName', TYPES.VarChar, user.email);
connection.execSql(createUserQuery);
});
}
```
```javascript MongoDB lines expandable theme={null}
function create(user, callback) {
const bcrypt = require('bcrypt');
const MongoClient = require('mongodb@3.1.4').MongoClient;
const client = new MongoClient('mongodb://user:pass@mymongoserver.com');
client.connect(function (err) {
if (err) return callback(err);
const db = client.db('db-name');
const users = db.collection('users');
users.findOne({ email: user.email }, function (err, withSameMail) {
if (err || withSameMail) {
client.close();
return callback(err || new Error('the user already exists'));
}
bcrypt.hash(user.password, 10, function (err, hash) {
if (err) {
client.close();
return callback(err);
}
user.password = hash;
users.insert(user, function (err, inserted) {
client.close();
if (err) return callback(err);
callback(null);
});
});
});
});
}
```
```javascript MySQL lines expandable theme={null}
function create(user, callback) {
const mysql = require('mysql');
const bcrypt = require('bcrypt');
const connection = mysql({
host: 'localhost',
user: 'me',
password: 'secret',
database: 'mydb'
});
connection.connect();
const query = 'INSERT INTO users SET ?';
bcrypt.hash(user.password, 10, function(err, hash) {
if (err) return callback(err);
const insert = {
password: hash,
email: user.email
};
connection.query(query, insert, function(err, results) {
if (err) return callback(err);
if (results.length === 0) return callback();
callback(null);
});
});
}
```
```javascript PostgreSQL lines expandable theme={null}
function create(user, callback) {
//this example uses the "pg" library
//more info here: https://github.com/brianc/node-postgres
const bcrypt = require('bcrypt');
const postgres = require('pg');
const conString = 'postgres://user:pass@localhost/mydb';
postgres.connect(conString, function (err, client, done) {
if (err) return callback(err);
bcrypt.hash(user.password, 10, function (err, hashedPassword) {
if (err) return callback(err);
const query = 'INSERT INTO users(email, password) VALUES ($1, $2)';
client.query(query, [user.email, hashedPassword], function (err, result) {
// NOTE: always call `done()` here to close
// the connection to the database
done();
return callback(err);
});
});
});
}
```
```javascript SQL Sever lines expandable theme={null}
function create(user, callback) {
//this example uses the "tedious" library
//more info here: http://pekim.github.io/tedious/index.html
const bcrypt = require('bcrypt');
const sqlserver = require('tedious@1.11.0');
const Connection = sqlserver.Connection;
const Request = sqlserver.Request;
const TYPES = sqlserver.TYPES;
const connection = new Connection({
userName: 'test',
password: 'test',
server: 'localhost',
options: {
database: 'mydb'
}
});
const query = 'INSERT INTO dbo.Users SET Email = @Email, Password = @Password';
connection.on('debug', function(text) {
console.log(text);
}).on('errorMessage', function(text) {
console.log(JSON.stringify(text, null, 2));
}).on('infoMessage', function(text) {
console.log(JSON.stringify(text, null, 2));
});
connection.on('connect', function (err) {
if (err) return callback(err);
const request = new Request(query, function (err, rows) {
if (err) return callback(err);
callback(null);
});
bcrypt.hash(user.password, 10, function(err, hash) {
if (err) return callback(err);
request.addParameter('Email', TYPES.VarChar, user.email);
request.addParameter('Password', TYPES.VarChar, hash);
connection.execSql(request);
});
});
}
```
```javascript Azure SQL Database lines expandable theme={null}
function create (user, callback) {
//this example uses the "tedious" library
//more info here: http://pekim.github.io/tedious/index.html
var Connection = require('tedious@1.11.0').Connection;
var Request = require('tedious@1.11.0').Request;
var TYPES = require('tedious@1.11.0').TYPES;
var bcrypt = require('bcrypt');
var connection = new Connection({
userName: 'your-user@your-server-id.database.windows.net',
password: 'the-password',
server: 'your-server-id.database.windows.net',
options: {
database: 'mydb',
encrypt: true
}
});
var query = "INSERT INTO users (Email, Password) VALUES (@Email, @Password)";
connection.on('debug', function(text) {
// Uncomment next line in order to enable debugging messages
// console.log(text);
}).on('errorMessage', function(text) {
console.log(JSON.stringify(text, null, 2));
}).on('infoMessage', function(text) {
// Uncomment next line in order to enable information messages
// console.log(JSON.stringify(text, null, 2));
});
connection.on('connect', function (err) {
if (err) { return callback(err); }
var request = new Request(query, function (err, rows) {
if (err) { return callback(err); }
console.log('rows: ' + rows);
callback(null);
});
bcrypt.hash(user.password, 10, function (err, hashedPassword) {
if (err) { return callback(err); }
request.addParameter('Email', TYPES.VarChar, user.email);
request.addParameter('Password', TYPES.VarChar, hashedPassword);
connection.execSql(request);
});
});
}
```
```javascript Basic Auth request lines theme={null}
function create(user, callback) {
const request = require('request');
request.post({
url: 'https://myserviceurl.com/users',
json: user
//for more options check:
//https://github.com/mikeal/request#requestoptions-callback
}, function(err, response, body) {
if (err) return callback(err);
callback(null);
});
}
```
## Troubleshoot
If you are unable to create a user in either your external user store or Auth0:
1. Check the `console.log()` statements with [Actions Real-Time Logs](/docs/customize/actions/actions-real-time-logs).
2. Find the user in your legacy database and delete accordingly. If the partial user state is in Auth0, use the Management API's [Delete a User](https://auth0.com/docs/api/management/v2#!/Users/delete_users_by_id) endpoint or [Delete a Connection User](https://auth0.com/docs/api/management/v2#!/Connections/delete_users_by_email) endpoint.
3. Make sure **Import Mode** is disabled, then configure the create script.