Store RTF in SQLite TEXT

by

Posted on by

RTF(Rich Text Format) can be stored as SQLite TEXT data.

1. SQLite TEXT Type Overview

SQLite TEXT has no length limits (disk-bound) and is lightweight for character storage.

Embedded database (no server required) with extremely simple syntax.

2. Pure SQL Implementation Steps

Step 1: Create Table with TEXT Field (sqlite3 command line)

CREATE TABLE RTF_DOCS (
    DOC_ID INTEGER PRIMARY KEY AUTOINCREMENT,
    DOC_NAME TEXT NOT NULL,
    RTF_CONTENT TEXT NOT NULL,
    CREATE_TIME DATETIME DEFAULT CURRENT_TIMESTAMP
);

Step 2: Insert RTF Text into TEXT Field

-- Method 1: Insert short RTF text directly
INSERT INTO RTF_DOCS (DOC_NAME, RTF_CONTENT)
VALUES (
    'test.rtf',
    '{\\rtf1\\ansi\\deff0 {\\fonttbl {\\f0 Calibri;}} \\pard\\f0\\fs22 SQLite RTF Test\\par}'
);

-- Method 2: Import external RTF file (sqlite3 command line)
-- 1. Log in to sqlite3: sqlite3 your_database.db
-- 2. Read file and insert with the following command
.read /path/to/insert_rtf.sql -- insert_rtf.sql content: INSERT INTO RTF_DOCS (DOC_NAME, RTF_CONTENT) VALUES ('imported.rtf', '$(cat /path/to/test.rtf)');

Step 3: Query/Edit RTF Content

-- Query full RTF content
SELECT DOC_NAME, RTF_CONTENT FROM RTF_DOCS WHERE DOC_ID = 1;

-- Truncate RTF preview (first 200 characters)
SELECT DOC_NAME, SUBSTR(RTF_CONTENT, 1, 200) AS RTF_PREVIEW FROM RTF_DOCS;

-- Update RTF content
UPDATE RTF_DOCS 
SET RTF_CONTENT = '{\\rtf1\\ansi\\deff0 \\pard\\f0\\fs32 Updated SQLite RTF\\par}'
WHERE DOC_ID = 1;

Step 4: Delete RTF Record

DELETE FROM RTF_DOCS WHERE DOC_ID = 1;

3. Simplify SQLite TEXT RTF Management with DBBlobEditor

DBBlobEditor streamlines RTF management for SQLite TEXT fields (no command-line required):

4. Notes

  • SQLite TEXT fields use UTF-8 by default (no extra configuration needed)
  • Import external files via the .read command (execute SQL scripts containing file content); no direct LOAD_FILE function
  • Ideal for embedded/lightweight scenarios; keep single database files under 2GB to avoid performance degradation

Related Guides