SQLite3 Database Type Support Status and Missing Implementations

[kazu-2020]

Summary

ActiveRecord ADBC Adapter currently supports 11 out of 13 SQLite3 database types defined in Rails' NATIVE_DATABASE_TYPES. This issue tracks the current implementation status and identifies the remaining types that need support.

Current Implementation Status

✅ Supported Types (11/13)

ActiveRecord Type	Arrow Type	SQLite3 Storage	Status	Test Coverage
string	StringArray	TEXT	✅ Working	✅ Tested
text	StringArray	TEXT	✅ Working	✅ Tested
integer	Int32Array	INTEGER	✅ Working	✅ Tested
bigint	Int64Array	INTEGER	✅ Working	✅ Tested
float	DoubleArray	REAL	✅ Working	✅ Tested
binary	BinaryArray	BLOB	✅ Working	✅ Tested
boolean	BooleanArray	INTEGER/TEXT	✅ Working	✅ Tested
date	Date32Array	TEXT	✅ Working	✅ Tested
datetime	TimestampArray	TEXT	✅ Working	✅ Tested
timestamp	TimestampArray	TEXT	✅ Working	✅ Tested
time	N/A	TEXT	⚠️ Explicitly Skipped	❌ Skipped

❌ Missing Types (2/13)

ActiveRecord Type	Expected Arrow Type	SQLite3 Storage	Status	Priority
decimal	Decimal128Array	TEXT/NUMERIC	❌ Not Implemented	🔴 High
json	StringArray	TEXT/JSON	❌ Not Implemented	🔴 High

• https://github.com/rails/rails/blob/077c3ad60db4c43cccb8f4637b53b8d8eb7a3c19/activerecord/lib/active_record/connection_adapters/abstract/schema_definitions.rb#L331-L335
• https://github.com/rails/rails/blob/077c3ad60db4c43cccb8f4637b53b8d8eb7a3c19/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb#L122-L136

Technical Background

SQLite3 Dynamic Typing System

SQLite3 uses a dynamic typing system (manifest typing) where:

• Storage Classes: Only 5 actual types (NULL, INTEGER, REAL, TEXT, BLOB)
• Type Affinity: Column declarations provide "hints" for type conversion
• Value-based Types: Actual storage type depends on the inserted value

Example:

CREATE TABLE test (time_col TIME);  -- "TIME" maps to NUMERIC affinity
INSERT INTO test VALUES ('14:30:00');  -- Stored as TEXT
INSERT INTO test VALUES (52200);        -- Stored as INTEGER

ADBC Adapter Architecture

The adapter performs 3-layer type conversion:

Ruby Objects ↔ Apache Arrow Arrays ↔ SQLite3 Storage

Detailed Analysis

⚠️ Time Type - Intentionally Skipped

Current Status: Explicitly skipped in tests

# test/test_type.rb
def test_time_active_record
  omit("SQLite ADBC adapter doesn't support time for now") if sqlite?
end

Technical Challenge:

• SQLite3 has no native TIME type
• Rails SQLite3 adapter maps time → "time" (stored as TEXT)
• ADBC adapter needs to handle: Ruby Time ↔ Arrow Time32/Time64 ↔ SQLite3 TEXT

Complexity: Medium - requires careful string parsing and timezone handling

❌ Decimal Type - Not Implemented

Current Status: Missing from type conversion logic

Expected Implementation:

# In result.rb resolve_type method
when Arrow::Decimal128DataType
  ActiveRecord::Type::Decimal.new

Technical Challenge:

• Precision and scale handling
• SQLite3 stores as TEXT or NUMERIC affinity
• Arrow decimal arrays require explicit precision/scale

Complexity: Medium-High - requires precision/scale metadata preservation

❌ JSON Type - Not Implemented

Current Status: Missing from type conversion logic

Rails SQLite3 Adapter Behavior:

# Rails NATIVE_DATABASE_TYPES
json: { name: "json" }

Expected Implementation:

# JSON is stored as TEXT in SQLite3, parsed by ActiveRecord
when Arrow::StringDataType
  # Need to detect if column is JSON type
  ActiveRecord::Type::Json.new

Technical Challenge:

• JSON detection from schema metadata
• Proper parsing/serialization
• Large JSON document handling

Complexity: High - requires schema introspection and JSON validation

Proposed Implementation Plan

Phase 1: High Priority Types (Target: Next Release)

1. Decimal Type Support

   • Add Arrow::Decimal128DataType handling in result.rb
   • Implement precision/scale detection from SQLite3 schema
   • Add comprehensive test coverage
   • Benchmark performance vs standard adapter

2. JSON Type Support

   • Add JSON column detection logic
   • Implement proper JSON parsing/serialization
   • Add test coverage for various JSON structures
   • Handle malformed JSON gracefully

Phase 2: Enhanced Time Support (Target: Future Release)

3. Time Type Support
   • Research optimal Arrow time type mapping
   • Implement string ↔ time conversion logic
   • Handle timezone considerations
   • Add comprehensive time format tests

Testing Requirements

Each new type implementation must include:

• Round-trip tests: Ruby → Arrow → SQLite3 → Arrow → Ruby
• Edge case handling: NULL values, invalid data
• Performance benchmarks: vs standard SQLite3 adapter
• Data integrity: Precision preservation, no data loss

Expected Benefits

After implementation completion:

• 100% type compatibility with Rails SQLite3 adapter
• Seamless migration from standard to ADBC adapter
• Complete bulk data processing support for all Rails types
• Enhanced data pipeline capabilities

Related Documentation

• SQLite3 dynamic typing documentation: https://sqlite.org/datatype3.html
• Apache Arrow type system: https://arrow.apache.org/docs/format/Schema.html

---

Priority: High
Complexity: Medium to High
Impact: Enables complete SQLite3 type compatibility for bulk data operations

This issue represents the final step toward full SQLite3 type coverage in the ADBC adapter, enabling seamless adoption for applications requiring high-performance bulk data processing.