Custom Field Structure in the Database

[twiclo]

I have inherited legacy software that connects to the NB database directly. I need to update it to work with the latest netbox version and I'm having a hard time wrapping my head around the new structure of custom fields. I've surmised that custom fields are stored in extras_customfields but the values (and fields?) for them are stored in extras_cachedvalue. When looking at cached values that have the object_id for a site I see the custom fields for that site but also custom fields from other objects. It's not until I filter with object_id = 3 and object_type_id = 25 that I get information only pertaining to site 3.

My question is what is the object_type_id? What table holds more info about the object_types? How can there be custom fields on object 3 that aren't related to site 3?

For reference here is the 2.* query that I need to update

query := `SELECT cf.name, cf.type, cfv.serialized_value
	FROM extras_customfield cf
	JOIN extras_customfieldvalue cfv ON cf.id = cfv.field_id
	JOIN django_content_type     dct ON cfv.obj_type_id = dct.id
	WHERE cfv.obj_id = $1 AND dct.model = $2`

Ultimately I need to be able to get all custom fields and their values for a site when given the ID of that site. Any help is appreciated. Thanks.

[kkthxbye-code]

extras_cachedvalue has nothing to do with custom fields. That's the index for the global search.

Custom field values are stored on each row, in a JSONB column named custom_field_data.

[candlerb]

Ultimately I need to be able to get all custom fields and their values for a site when given the ID of that site

It's much simpler in modern versions of Netbox: all you need is

select id,custom_field_data from dcim_site where id=$1`

My question is what is the object_type_id? What table holds more info about the object_types?

FWIW, that's in table django_content_type. You might need to use it when dealing with generic foreign keys, with a pair of (assigned_object_type_id, assigned_object_id)

[twiclo]

Can you tell me more about the django table? What is it used for? Will custom_field_data on dcim_site get what's in that table or do I still need to do a join for that as well if I'm hoping to have the same output from before?

[candlerb]

The table django_content_type lists all the valid object type ids, together with their human names (app and model).

The column custom_field_data has no relationship to that table. It's now purely a JSON value, like

{"custom_field_1": "value1", "custom_field_2", "value2"}

There are still tables which define the custom fields (what they are called, how they are displayed, which models they apply to, how they are validated etc), but the actual data for each custom field now goes directly into the custom_field_data column in whichever model table it relates to, in the row of whichever object it relates to.

In other words: the separate table extras_customfieldvalue, which used to hold detached custom field values (linked to a parent object in a different table via obj_type_id and obj_id), is now gone.

[twiclo]

What are the names of those tables that define the custom fields? I need to display custom fields on a site even if that site doesn't have a custom field set. I can look the extras_customfield table but that doesn't seem to have any object IDs so I'm not sure how to know which customfields belong to which object types

[candlerb]

I need to display custom fields on a site even if that site doesn't have a custom field set

All custom fields are displayed in the GUI, including empty ones (unless UI visibility has been set to "Hidden (if unset)"

I can look the extras_customfield table

That defines the custom fields: i.e. what each custom field is called, what values it's allowed to have etc.

but that doesn't seem to have any object IDs

No it won't. The values for custom fields are stored directly on every object, in an extra JSON column "custom_field_data". In the case of sites, you'd do this: select id,custom_field_data from dcim_site