How to store enum values in database
Storing enums in a database table typically involves mapping each enum value to a corresponding database column. Different database systems handle enums in slightly different ways. Here’s a general approach using SQL and then specifically with examples for MySQL and PostgreSQL:
General Approach
-
Define Enum in Application: Define your enum in your application code. For example, in JavaScript or TypeScript:
const UserRole = { ADMIN: 'admin', USER: 'user', MODERATOR: 'moderator' };
-
Map Enum to Database Table:
- Option 1: Integer Mapping: Map each enum value to an integer in the database table.
- Option 2: String Mapping: Map each enum value directly to a string column in the database.
Example with MySQL
In MySQL, you can use ENUM
to define a column that accepts one of a predefined set of string values:
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(255) NOT NULL,
role ENUM('admin', 'user', 'moderator') NOT NULL
);
In this example:
- The
role
column is defined as anENUM
that accepts values 'admin', 'user', or 'moderator'. - MySQL internally stores
ENUM
values as integers, corresponding to the index of each value in the list ('admin' = 1, 'user' = 2, 'moderator' = 3).
Example with PostgreSQL
In PostgreSQL, you can use ENUM
types or a check constraint to achieve similar functionality:
Using ENUM
type:
CREATE TYPE user_role AS ENUM ('admin', 'user', 'moderator');
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(255) NOT NULL,
role user_role NOT NULL
);
Using check constraint:
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(255) NOT NULL,
role VARCHAR(20) NOT NULL,
CONSTRAINT role_check CHECK (role IN ('admin', 'user', 'moderator'))
);
Benefits of Using ENUM
- Data Integrity: Enums ensure that only predefined values are stored in the database, maintaining data integrity.
- Readability: Enums improve readability of the database schema, as they clearly define the possible values for a column.
- Performance: Enums can be more efficient than storing arbitrary strings, especially when indexed.
Published on: Jul 10, 2024, 03:10 AM