Using UUIDs in SQLite: Storage and Querying

SQLite UUID: Managing Unique Identifiers

A UUID (Universally Unique Identifier) is a 128-bit number used to uniquely identify information in a database. SQLite does not have built-in support for UUIDs, but you can implement them using extensions or by generating them in your application layer. This guide explains how to use UUIDs in SQLite, including generation, storage, and querying, with examples and explanations.

What is a UUID?

A UUID consists of 32 hexadecimal characters divided into five groups separated by hyphens:

Example: 550e8400-e29b-41d4-a716-446655440000.

• Universally Unique: Ensures no two identifiers are the same across systems.
• Common Use Cases: Useful for primary keys and distributed systems.

Storing UUIDs in SQLite

SQLite can store UUIDs as text or binary data:

1. Text Format: Easier to read and query but larger in size.

2. Binary Format: Compact and efficient for storage.

Syntax for UUID in SQLite

Text Format :

CREATE TABLE users (
    id TEXT PRIMARY KEY, -- UUID stored as text
    name TEXT NOT NULL
);

Binary Format :

CREATE TABLE users (
    id BLOB PRIMARY KEY, -- UUID stored as binary
    name TEXT NOT NULL
);

Generating UUIDs

Option 1: Use SQLite Extensions

SQLite extensions like uuid.c provide built-in UUID generation.

Example:

Code:

SELECT uuid();
-- Generates a new UUID.

Option 2: Generate UUIDs in Application Code

Languages like Python or JavaScript can generate UUIDs and insert them into SQLite.

Examples:

Example 1: Insert UUID in SQLite using Python

Code:

import sqlite3
import uuid

# Connect to SQLite database
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Create a table with UUID
cursor.execute('''
CREATE TABLE users (
    id TEXT PRIMARY KEY, -- Storing UUID as text
    name TEXT NOT NULL
)
''')

# Generate a UUID and insert it
user_id = str(uuid.uuid4())  # Generate UUID as a string
cursor.execute('INSERT INTO users (id, name) VALUES (?, ?)', (user_id, 'Alice'))

# Commit changes and close connection
conn.commit()
conn.close()

print(f"Inserted user with UUID: {user_id}")

• uuid.uuid4(): Generates a random UUID.
• ?: Placeholder for parameters in SQLite queries.

Example 2: Querying by UUID

Code:

# Connect to SQLite database
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Query user by UUID
search_id = '550e8400-e29b-41d4-a716-446655440000'
cursor.execute('SELECT * FROM users WHERE id = ?', (search_id,))
user = cursor.fetchone()

print(f"Found user: {user}")

conn.close()

• Query by UUID: Matches exact UUID value in the database.

Using UUID with Binary Format

Store UUIDs in binary for efficient storage and faster indexing.

Example:

Code:

import sqlite3
import uuid

# Connect to SQLite database
conn = sqlite3.connect('example_binary.db')
cursor = conn.cursor()

# Create a table with binary UUID
cursor.execute('''
CREATE TABLE users (
    id BLOB PRIMARY KEY, -- Storing UUID as binary
    name TEXT NOT NULL
)
''')

# Generate UUID and convert to binary
user_id = uuid.uuid4().bytes  # Generate UUID as bytes
cursor.execute('INSERT INTO users (id, name) VALUES (?, ?)', (user_id, 'Bob'))

conn.commit()
conn.close()

print(f"Inserted user with binary UUID.")

• Binary UUID: Compact and efficient for database storage.

Explanation

1. Why Use UUIDs?

• Avoids collision in distributed systems.
• Universally recognizable and portable.

2. Text vs Binary

• Use text for readability and ease of use.
• Use binary for storage efficiency.

3. Indexing

Ensure primary keys are indexed for fast retrieval. UUIDs in text format can be indexed, but binary keys are more efficient.

Practical Guides to SQLite Snippets and Examples.