Define Tables

Create DataJoint table classes with proper definitions.

Create a Schema

import datajoint as dj

schema = dj.Schema('my_schema')  # Creates schema in database if it doesn't exist

The Schema object connects to the database and creates the schema (database) if it doesn't already exist.

Basic Table Structure

@schema
class MyTable(dj.Manual):
    definition = """
    # Table comment (optional)
    primary_attr : type      # attribute comment
    ---
    secondary_attr : type    # attribute comment
    optional_attr = null : type
    """

Table Types

Type	Base Class	Purpose
Manual	dj.Manual	Data inserted directly from outside the pipeline (forms, instruments, ingestion scripts)
Lookup	dj.Lookup	Reference data defined in the schema via contents
Imported	dj.Imported	Populated by make() from an external source
Computed	dj.Computed	Populated by make() from other tables
Part	dj.Part	Child of master table

Primary Key (Above ---)

definition = """
subject_id : varchar(16)      # Subject identifier
session_idx : int32          # Session number
---
...
"""

Primary key attributes:

• Cannot be NULL
• Must be unique together
• Cannot be changed after insertion

Secondary Attributes (Below ---)

definition = """
...
---
session_date : date           # Required attribute
notes = '' : varchar(1000)    # Optional with default
score = null : float32        # Nullable attribute
"""

Default Values and Nullable Attributes

Default values are specified with = value before the type:

definition = """
subject_id : varchar(16)
---
weight = null : float32           # Nullable (default is NULL)
notes = '' : varchar(1000)        # Default empty string
is_active = 1 : bool              # Default true
created = CURRENT_TIMESTAMP : timestamp
"""

Key rules:

• The only way to make an attribute nullable is = null
• Attributes without defaults are required (NOT NULL)
• Primary key attributes cannot be nullable
• Primary key attributes cannot have static defaults

Timestamp defaults:

Primary keys can use time-dependent defaults like CURRENT_TIMESTAMP:

definition = """
created_at = CURRENT_TIMESTAMP : timestamp(6)   # Microsecond precision
---
data : <blob>
"""

Timestamp precision options:

• timestamp or datetime — Second precision
• timestamp(3) or datetime(3) — Millisecond precision
• timestamp(6) or datetime(6) — Microsecond precision

Auto-Increment (Not Recommended)

DataJoint core types do not support AUTO_INCREMENT. This is intentional—explicit key values enforce entity integrity and prevent silent creation of duplicate records.

Use uuid or natural keys instead:

definition = """
recording_id : uuid              # Globally unique, client-generated
---
...
"""

If you must use auto-increment, native MySQL types allow it (with a warning):

definition = """
record_id : int unsigned auto_increment    # Native type
---
...
"""

See Design Primary Keys for detailed guidance on key selection and why DataJoint avoids auto-increment.

Core DataJoint Types

Type	Description
bool	Boolean (true/false)
int8, int16, int32, int64	Signed integers
float32, float64	Floating point
decimal(m,n)	Fixed precision decimal
varchar(n)	Variable-length string
char(n)	Fixed-length string
date	Date (YYYY-MM-DD)
datetime	Date and time
datetime(3)	With millisecond precision
datetime(6)	With microsecond precision
uuid	UUID type
enum('a', 'b', 'c')	Enumerated values
json	JSON data
bytes	Raw binary data

Built-in Codecs

Codecs serialize Python objects to database storage. Use angle brackets for codec types:

Codec	Description
<blob>	Serialized Python objects (NumPy arrays, etc.) stored in database
<blob@store>	Serialized objects in object storage
<attach>	File attachments in database
<attach@store>	File attachments in object storage
<object@store>	Files/folders via ObjectRef (schema-addressed, supports Zarr/HDF5)

Example:

definition = """
recording_id : uuid
---
neural_data : <blob@raw>      # NumPy array in 'raw' store
config_file : <attach>        # Attached file in database
parameters : json             # JSON data (core type, no brackets)
"""

Native Database Types

You can also use native database types directly when needed:

definition = """
record_id : int unsigned      # Native MySQL type
data : mediumblob             # For larger binary data
description : text            # Unlimited text
"""

Native types are flagged with a warning at declaration time but are allowed. Core DataJoint types (like int32, float64) are portable and recommended for most use cases. Native database types provide access to database-specific features when needed.

Foreign Keys

@schema
class Session(dj.Manual):
    definition = """
    -> Subject                # References Subject table
    session_idx : int32
    ---
    session_date : date
    """

The -> inherits primary key attributes from the referenced table.

Foreign Key Modifiers

Use modifiers in brackets to change foreign key behavior:

Modifier	Effect
[nullable]	Makes FK attributes nullable (optional relationship)
[unique]	Creates UNIQUE INDEX on FK attributes (one-to-one)
[nullable, unique]	Both: optional one-to-one relationship

@schema
class SessionAnnotation(dj.Manual):
    definition = """
    -> Session
    ---
    -> [nullable] Experimenter     # Optional: experimenter may be unknown
    -> [unique] Protocol           # Unique: each protocol used at most once
    -> [nullable, unique] Reviewer # Optional & unique
    """

Placement rules:

• [nullable] only allowed in secondary position (below ---)
• [unique] allowed in both primary and secondary positions
• Primary key FKs cannot be nullable

Nullable unique behavior:

Multiple rows can have NULL in a [nullable, unique] FK because SQL's UNIQUE constraint does not consider NULLs equal. This enables optional one-to-one relationships.

Lookup Tables with Contents

@schema
class TaskType(dj.Lookup):
    definition = """
    task_type : varchar(32)
    ---
    description : varchar(200)
    """
    contents = [
        {'task_type': 'detection', 'description': 'Detect target stimulus'},
        {'task_type': 'discrimination', 'description': 'Distinguish between stimuli'},
    ]

Manual or Lookup?

Both tiers hold rows that are entered rather than computed, so the question is where a row comes from:

• Use dj.Lookup when the rows are part of the schema's design and belong in the code — parameter sets, method definitions, controlled vocabularies, enumerations. They are declared in contents, versioned with the table, and identical in every deployment until the code changes. Updating a Lookup means editing contents and redeploying — the change flows through your normal CI/CD process, not a runtime insert.
• Use dj.Manual when the rows are entered at runtime and are specific to a project or experiment — subjects, sessions, samples, or anything typed into a form, ingested from a file, or read from an instrument.

If you find yourself populating a "Lookup" table at runtime (for example, from a dashboard form) rather than from its committed contents, make it dj.Manual instead — its rows are runtime data, not part of the schema definition.

Part Tables

@schema
class Session(dj.Manual):
    definition = """
    -> Subject
    session_idx : int32
    ---
    session_date : date
    """

    class Trial(dj.Part):
        definition = """
        -> master
        trial_idx : int32
        ---
        outcome : enum('hit', 'miss')
        reaction_time : float32
        """

Computed Tables

@schema
class SessionStats(dj.Computed):
    definition = """
    -> Session
    ---
    n_trials : int64
    hit_rate : float32
    """

    def make(self, key):
        trials = (Session.Trial & key).to_dicts()
        self.insert1({
            **key,
            'n_trials': len(trials),
            'hit_rate': sum(t['outcome'] == 'hit' for t in trials) / len(trials)
        })

Indexes

Declare indexes at the end of the definition, after all attributes:

definition = """
subject_id : varchar(16)
session_idx : int32
---
session_date : date
experimenter : varchar(50)
index (session_date)              # Index for faster queries
index (experimenter)              # Another index
unique index (external_id)        # Unique constraint
"""

Declaring Tables

Tables are declared in the database when the @schema decorator applies to the class:

@schema  # Table is declared here
class Session(dj.Manual):
    definition = """
    session_id : int32
    ---
    session_date : date
    """

The decorator reads the definition string, parses it, and creates the corresponding table in the database if it doesn't exist.

Dropping Tables and Schemas

During prototyping (before data are populated), you can drop and recreate tables:

# Drop a single table
Session.drop()

# Drop entire schema (all tables)
schema.drop()

Warning: These operations permanently delete data. Use only during development.

View Table Definition

# Show SQL definition
print(Session().describe())

# Show heading
print(Session().heading)

See Also

• Master-Part Tables — Working with part tables
• Model Relationships — Foreign key patterns
• Design Primary Keys — Key selection strategies
• Insert Data — Adding data to tables