About the lack of enums in SQLite: there's no need for them to ensure valid strings

[fernandocanizo]

Hi, I was reading the decisions document on SQLite and I came across this part:

SQLite does not support enums [...] The main drawback to this is when it comes to the typings for the client which doesn't allow you to ensure all values of a column are only within a set of specific possible values for the string. However, with Prisma client extensions, handling this kind of enforcement at the client (and typing) level should be possible.

(cuttings and highlights are my own)

I also read issue #29 , where a Prisma example is given.

I didn't want to reopen the issue, but I wanted to add that you don't need no Prisma thingy. To ensure all values of a column are only within a set of specific possible values just use a table and foreign keys.

For example, if you wanted to have an enum like:

enum Direction {
  up = "up",
  down = "down",
  left = "left",
  right = "right",
}

You can just create a direction table like this:

pragma foreign_keys = on;

create table direction (
  name char(5) primary key
);

insert into direction (name) values (
  "up",
  "down",
  "left",
  "right"
);

And then use it like this:

create table example (
  whatever varchar not null,
  direction char(5) not null,
  foreign key (direction) references direction(name)
);

And with that you ensure you cannot insert into example any other value than the ones hold by direction table, for example:

insert into example (whatever, direction) values ('aThing', 'down');
-- next will error
-- insert into example (whatever, direction) values ('this will error', 'random string');
-- Runtime error: FOREIGN KEY constraint failed (19)

[kiliman]

Great tip. Thanks!