Skip to main content

​
Data Types

Master MatsushibaDB data types to design efficient database schemas and optimize data storage and retrieval.

​
Numeric Data Types

​
Integer Types

-- Integer storage classes
CREATE TABLE integer_examples (
    id INTEGER PRIMARY KEY,           -- 64-bit signed integer
    small_int SMALLINT,               -- 16-bit signed integer
    medium_int MEDIUMINT,             -- 24-bit signed integer
    big_int BIGINT,                   -- 64-bit signed integer
    tiny_int TINYINT                  -- 8-bit signed integer
);

-- Integer constraints
CREATE TABLE integer_constraints (
    id INTEGER PRIMARY KEY,
    positive_number INTEGER CHECK (positive_number > 0),
    range_number INTEGER CHECK (range_number BETWEEN 1 AND 100),
    not_null_number INTEGER NOT NULL,
    unique_number INTEGER UNIQUE
);

-- Auto-incrementing integers
CREATE TABLE auto_increment_example (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

​
Floating-Point Types

-- Real and floating-point types
CREATE TABLE float_examples (
    id INTEGER PRIMARY KEY,
    real_value REAL,                 -- 64-bit floating point
    float_value FLOAT,               -- 32-bit floating point
    double_value DOUBLE,             -- 64-bit floating point
    decimal_value DECIMAL(10,2),     -- Fixed-point decimal
    numeric_value NUMERIC(8,4)       -- Fixed-point numeric
);

-- Precision and scale examples
CREATE TABLE precision_examples (
    id INTEGER PRIMARY KEY,
    price DECIMAL(10,2),             -- Up to 8 digits before decimal, 2 after
    percentage DECIMAL(5,2),         -- Up to 3 digits before decimal, 2 after
    scientific REAL,                 -- Scientific notation support
    currency DECIMAL(15,2)           -- Large currency values
);

​
Numeric Operations and Functions

// Numeric operations
function numericOperations() {
    // Insert numeric data
    db.run(`
        INSERT INTO numeric_examples (real_value, float_value, decimal_value)
        VALUES (?, ?, ?)
    `, [3.14159, 2.71828, 99.99]);
    
    // Mathematical operations
    const result = db.get(`
        SELECT 
            real_value,
            float_value,
            decimal_value,
            real_value + float_value as sum,
            real_value * decimal_value as product,
            ROUND(decimal_value, 1) as rounded,
            ABS(real_value) as absolute,
            CEIL(real_value) as ceiling,
            FLOOR(real_value) as floor
        FROM numeric_examples 
        WHERE id = ?
    `, [1]);
    
    console.log('Numeric operations:', result);
    
    // Aggregate functions
    const stats = db.get(`
        SELECT 
            COUNT(*) as count,
            AVG(decimal_value) as average,
            MIN(decimal_value) as minimum,
            MAX(decimal_value) as maximum,
            SUM(decimal_value) as total
        FROM numeric_examples
    `);
    
    console.log('Numeric statistics:', stats);
}

// Numeric validation
function validateNumericData(data) {
    const errors = [];
    
    if (data.price && (isNaN(data.price) || data.price < 0)) {
        errors.push('Price must be a positive number');
    }
    
    if (data.quantity && (!Number.isInteger(data.quantity) || data.quantity < 0)) {
        errors.push('Quantity must be a positive integer');
    }
    
    if (data.percentage && (data.percentage < 0 || data.percentage > 100)) {
        errors.push('Percentage must be between 0 and 100');
    }
    
    return errors;
}

​
Text Data Types

​
String Types

-- Text storage classes
CREATE TABLE text_examples (
    id INTEGER PRIMARY KEY,
    varchar_field VARCHAR(255),       -- Variable-length string with limit
    char_field CHAR(10),             -- Fixed-length string
    text_field TEXT,                 -- Variable-length string without limit
    clob_field CLOB,                 -- Character Large Object
    nvarchar_field NVARCHAR(100),    -- Unicode variable-length string
    nchar_field NCHAR(20)            -- Unicode fixed-length string
);

-- Text constraints
CREATE TABLE text_constraints (
    id INTEGER PRIMARY KEY,
    email TEXT UNIQUE NOT NULL,
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash TEXT NOT NULL,
    bio TEXT CHECK (LENGTH(bio) <= 500),
    status TEXT CHECK (status IN ('active', 'inactive', 'suspended'))
);

​
Text Operations and Functions

// Text operations
function textOperations() {
    // Insert text data
    db.run(`
        INSERT INTO text_examples (varchar_field, char_field, text_field)
        VALUES (?, ?, ?)
    `, ['Hello World', 'Fixed', 'This is a long text field']);
    
    // String functions
    const result = db.get(`
        SELECT 
            varchar_field,
            char_field,
            text_field,
            LENGTH(varchar_field) as length,
            UPPER(varchar_field) as uppercase,
            LOWER(varchar_field) as lowercase,
            SUBSTR(varchar_field, 1, 5) as substring,
            REPLACE(varchar_field, 'World', 'Universe') as replaced,
            TRIM(char_field) as trimmed
        FROM text_examples 
        WHERE id = ?
    `, [1]);
    
    console.log('Text operations:', result);
    
    // Pattern matching
    const patternResults = db.all(`
        SELECT varchar_field
        FROM text_examples
        WHERE varchar_field LIKE 'Hello%'
        OR varchar_field GLOB 'H*'
        OR varchar_field REGEXP '^[A-Z]'
    `);
    
    console.log('Pattern matching results:', patternResults);
}

// Text validation
function validateTextData(data) {
    const errors = [];
    
    if (data.email && !/^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(data.email)) {
        errors.push('Invalid email format');
    }
    
    if (data.username && (data.username.length < 3 || data.username.length > 50)) {
        errors.push('Username must be between 3 and 50 characters');
    }
    
    if (data.password && data.password.length < 8) {
        errors.push('Password must be at least 8 characters');
    }
    
    return errors;
}

​
Date and Time Types

​
DateTime Types

-- Date and time storage
CREATE TABLE datetime_examples (
    id INTEGER PRIMARY KEY,
    date_field DATE,                 -- Date only (YYYY-MM-DD)
    time_field TIME,                 -- Time only (HH:MM:SS)
    datetime_field DATETIME,         -- Date and time (YYYY-MM-DD HH:MM:SS)
    timestamp_field TIMESTAMP,       -- Unix timestamp
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Date constraints
CREATE TABLE date_constraints (
    id INTEGER PRIMARY KEY,
    birth_date DATE CHECK (birth_date < date('now')),
    event_date DATE CHECK (event_date >= date('now')),
    start_time TIME CHECK (start_time >= '09:00:00'),
    end_time TIME CHECK (end_time <= '18:00:00')
);

​
DateTime Operations

// DateTime operations
function datetimeOperations() {
    // Insert datetime data
    db.run(`
        INSERT INTO datetime_examples (date_field, time_field, datetime_field)
        VALUES (?, ?, ?)
    `, ['2024-01-15', '14:30:00', '2024-01-15 14:30:00']);
    
    // DateTime functions
    const result = db.get(`
        SELECT 
            date_field,
            time_field,
            datetime_field,
            date('now') as current_date,
            time('now') as current_time,
            datetime('now') as current_datetime,
            strftime('%Y-%m-%d', datetime_field) as formatted_date,
            strftime('%H:%M:%S', datetime_field) as formatted_time,
            julianday('now') - julianday(datetime_field) as days_ago
        FROM datetime_examples 
        WHERE id = ?
    `, [1]);
    
    console.log('DateTime operations:', result);
    
    // Date arithmetic
    const dateArithmetic = db.all(`
        SELECT 
            datetime_field,
            datetime(datetime_field, '+1 day') as next_day,
            datetime(datetime_field, '+1 month') as next_month,
            datetime(datetime_field, '+1 year') as next_year,
            datetime(datetime_field, '+7 days', '+2 hours') as week_and_hours_later
        FROM datetime_examples
    `);
    
    console.log('Date arithmetic:', dateArithmetic);
}

// DateTime validation
function validateDateTimeData(data) {
    const errors = [];
    
    if (data.birth_date && new Date(data.birth_date) > new Date()) {
        errors.push('Birth date cannot be in the future');
    }
    
    if (data.event_date && new Date(data.event_date) < new Date()) {
        errors.push('Event date cannot be in the past');
    }
    
    if (data.start_time && data.end_time) {
        const start = new Date(`2000-01-01 ${data.start_time}`);
        const end = new Date(`2000-01-01 ${data.end_time}`);
        if (start >= end) {
            errors.push('Start time must be before end time');
        }
    }
    
    return errors;
}

​
Binary Data Types

​
BLOB Types

-- Binary data storage
CREATE TABLE binary_examples (
    id INTEGER PRIMARY KEY,
    binary_field BINARY(16),         -- Fixed-length binary data
    blob_field BLOB,                 -- Variable-length binary data
    image_data BLOB,                 -- Image storage
    file_data BLOB,                  -- File storage
    encrypted_data BLOB              -- Encrypted data storage
);

-- Binary constraints
CREATE TABLE binary_constraints (
    id INTEGER PRIMARY KEY,
    file_hash BLOB UNIQUE,           -- Unique file hash
    image_data BLOB CHECK (LENGTH(image_data) <= 10485760), -- Max 10MB
    encrypted_data BLOB NOT NULL      -- Required encrypted data
);

​
Binary Operations

const crypto = require('crypto');

// Binary operations
function binaryOperations() {
    // Generate binary data
    const randomData = crypto.randomBytes(16);
    const hash = crypto.createHash('sha256').update('test data').digest();
    
    // Insert binary data
    db.run(`
        INSERT INTO binary_examples (binary_field, blob_field)
        VALUES (?, ?)
    `, [randomData, hash]);
    
    // Retrieve and process binary data
    const result = db.get(`
        SELECT 
            binary_field,
            blob_field,
            LENGTH(binary_field) as binary_length,
            LENGTH(blob_field) as blob_length,
            hex(binary_field) as binary_hex,
            hex(blob_field) as blob_hex
        FROM binary_examples 
        WHERE id = ?
    `, [1]);
    
    console.log('Binary operations:', result);
    
    // Convert hex back to buffer
    const binaryBuffer = Buffer.from(result.binary_hex, 'hex');
    const blobBuffer = Buffer.from(result.blob_hex, 'hex');
    
    console.log('Converted buffers:', { binaryBuffer, blobBuffer });
}

// File storage operations
function storeFile(filename, fileData) {
    try {
        const hash = crypto.createHash('sha256').update(fileData).digest();
        
        db.run(`
            INSERT INTO binary_examples (file_data, blob_field)
            VALUES (?, ?)
        `, [fileData, hash]);
        
        return { success: true, hash: hash.toString('hex') };
    } catch (error) {
        return { success: false, error: error.message };
    }
}

// Retrieve file
function retrieveFile(fileId) {
    try {
        const result = db.get(`
            SELECT file_data, blob_field
            FROM binary_examples
            WHERE id = ?
        `, [fileId]);
        
        if (result) {
            return {
                success: true,
                data: result.file_data,
                hash: result.blob_field.toString('hex')
            };
        } else {
            return { success: false, error: 'File not found' };
        }
    } catch (error) {
        return { success: false, error: error.message };
    }
}

​
JSON Data Types

​
JSON Storage and Operations

-- JSON data storage
CREATE TABLE json_examples (
    id INTEGER PRIMARY KEY,
    json_data JSON,                  -- JSON data
    jsonb_data JSONB,                -- Binary JSON (if supported)
    metadata JSON,                   -- Metadata storage
    settings JSON,                   -- User settings
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- JSON constraints
CREATE TABLE json_constraints (
    id INTEGER PRIMARY KEY,
    user_profile JSON CHECK (json_valid(user_profile)),
    config JSON CHECK (json_extract(config, '$.version') IS NOT NULL),
    data JSON NOT NULL
);

​
JSON Operations

// JSON operations
function jsonOperations() {
    // Insert JSON data
    const userProfile = {
        name: 'John Doe',
        email: '[email protected]',
        preferences: {
            theme: 'dark',
            notifications: true,
            language: 'en'
        },
        tags: ['developer', 'javascript', 'nodejs']
    };
    
    db.run(`
        INSERT INTO json_examples (json_data, metadata)
        VALUES (?, ?)
    `, [JSON.stringify(userProfile), JSON.stringify({ source: 'api', version: '1.0' })]);
    
    // JSON functions
    const result = db.get(`
        SELECT 
            json_data,
            json_extract(json_data, '$.name') as name,
            json_extract(json_data, '$.email') as email,
            json_extract(json_data, '$.preferences.theme') as theme,
            json_extract(json_data, '$.tags[0]') as first_tag,
            json_array_length(json_extract(json_data, '$.tags')) as tag_count,
            json_valid(json_data) as is_valid
        FROM json_examples 
        WHERE id = ?
    `, [1]);
    
    console.log('JSON operations:', result);
    
    // Update JSON data
    db.run(`
        UPDATE json_examples 
        SET json_data = json_set(json_data, '$.preferences.theme', ?)
        WHERE id = ?
    `, ['light', 1]);
    
    // Query JSON arrays
    const usersWithTag = db.all(`
        SELECT json_data
        FROM json_examples
        WHERE json_extract(json_data, '$.tags') LIKE '%developer%'
    `);
    
    console.log('Users with developer tag:', usersWithTag);
}

// JSON validation
function validateJsonData(data) {
    const errors = [];
    
    try {
        const parsed = typeof data === 'string' ? JSON.parse(data) : data;
        
        if (parsed.name && typeof parsed.name !== 'string') {
            errors.push('Name must be a string');
        }
        
        if (parsed.email && !/^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(parsed.email)) {
            errors.push('Invalid email format');
        }
        
        if (parsed.preferences && typeof parsed.preferences !== 'object') {
            errors.push('Preferences must be an object');
        }
        
        if (parsed.tags && !Array.isArray(parsed.tags)) {
            errors.push('Tags must be an array');
        }
        
    } catch (error) {
        errors.push('Invalid JSON format');
    }
    
    return errors;
}

​
Data Type Best Practices

​
Choosing the Right Data Type

// Data type selection guidelines
function selectDataType(fieldName, value, requirements) {
    const recommendations = [];
    
    // Numeric types
    if (typeof value === 'number') {
        if (Number.isInteger(value)) {
            if (value >= -128 && value <= 127) {
                recommendations.push('TINYINT');
            } else if (value >= -32768 && value <= 32767) {
                recommendations.push('SMALLINT');
            } else if (value >= -2147483648 && value <= 2147483647) {
                recommendations.push('INTEGER');
            } else {
                recommendations.push('BIGINT');
            }
        } else {
            recommendations.push('REAL or DECIMAL');
        }
    }
    
    // Text types
    if (typeof value === 'string') {
        if (value.length <= 255) {
            recommendations.push('VARCHAR(255)');
        } else {
            recommendations.push('TEXT');
        }
    }
    
    // Date types
    if (value instanceof Date) {
        recommendations.push('DATETIME');
    }
    
    // Boolean types
    if (typeof value === 'boolean') {
        recommendations.push('BOOLEAN or INTEGER');
    }
    
    return recommendations;
}

// Schema optimization
function optimizeSchema(tableName) {
    const analysis = db.all(`
        SELECT 
            name,
            type,
            pk,
            notnull,
            dflt_value
        FROM pragma_table_info(?)
    `, [tableName]);
    
    const recommendations = [];
    
    analysis.forEach(column => {
        if (column.type === 'TEXT' && column.name.includes('id')) {
            recommendations.push({
                column: column.name,
                suggestion: 'Consider using INTEGER for ID columns'
            });
        }
        
        if (column.type === 'TEXT' && column.name.includes('email')) {
            recommendations.push({
                column: column.name,
                suggestion: 'Add UNIQUE constraint for email columns'
            });
        }
        
        if (column.type === 'TEXT' && column.name.includes('status')) {
            recommendations.push({
                column: column.name,
                suggestion: 'Consider using CHECK constraint for status values'
            });
        }
    });
    
    return recommendations;
}

​
Storage Optimization

-- Optimize storage with appropriate data types
CREATE TABLE optimized_storage (
    -- Use smallest appropriate integer type
    id SMALLINT PRIMARY KEY,                    -- Instead of INTEGER for small datasets
    status TINYINT DEFAULT 0,                   -- Instead of INTEGER for status flags
    
    -- Use appropriate text types
    email VARCHAR(255) UNIQUE NOT NULL,         -- Instead of TEXT for fixed-length data
    username VARCHAR(50) UNIQUE NOT NULL,      -- Limit length for usernames
    bio TEXT,                                   -- Use TEXT for variable-length content
    
    -- Use appropriate numeric types
    price DECIMAL(10,2) NOT NULL,              -- Fixed precision for currency
    percentage DECIMAL(5,2) DEFAULT 0,         -- Limited precision for percentages
    
    -- Use appropriate date types
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_login DATETIME,
    
    -- Use appropriate binary types
    avatar BLOB,                               -- For small binary data
    file_hash BINARY(32) UNIQUE               -- Fixed-length hash
);

-- Create indexes on frequently queried columns
CREATE INDEX idx_optimized_email ON optimized_storage(email);
CREATE INDEX idx_optimized_status ON optimized_storage(status);
CREATE INDEX idx_optimized_created ON optimized_storage(created_at);

​
Best Practices

1

Choose Appropriate Types

Select the smallest data type that can accommodate your data to optimize storage and performance.
2

Use Constraints

Apply appropriate constraints (NOT NULL, UNIQUE, CHECK) to ensure data integrity.
3

Normalize Data

Use proper normalization techniques to avoid data redundancy and maintain consistency.
4

Index Strategically

Create indexes on frequently queried columns, but avoid over-indexing.
5

Validate Input

Always validate data before inserting it into the database.
6

Use Prepared Statements

Use prepared statements for better performance and security.
7

Consider Future Growth

Plan for future data growth when choosing data types and constraints.
8

Document Schema

Document your database schema and data type choices for future reference.
Choosing the right data types is crucial for database performance and data integrity. Always consider your data requirements, storage constraints, and query patterns when designing your database schema.