Tutorial: how to design sqlx model and postgresql to store the Vec<String> type data

[dwsio]

I'll help you design a SQLx model and PostgreSQL table that can store Vec<String>. There are several approaches, each with different trade-offs.

Approach 1: Using PostgreSQL Arrays (Recommended)

PostgreSQL natively supports arrays, which maps well to Vec<String>.

PostgreSQL Table

CREATE TABLE items (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    tags TEXT[] NOT NULL DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

SQLx Model in Rust

use sqlx::FromRow;
use chrono::{DateTime, Utc};

#[derive(Debug, FromRow)]
pub struct Item {
    pub id: i32,
    pub name: String,
    pub tags: Vec<String>,  // PostgreSQL TEXT[] maps directly to Vec<String>
    pub created_at: DateTime<Utc>,
}

Example Operations

// Insert
sqlx::query!(
    "INSERT INTO items (name, tags) VALUES ($1, $2)",
    name,
    &tags as &[String]  // Cast Vec<String> to slice for insertion
)
.execute(&pool)
.await?;

// Select
let item = sqlx::query_as!(
    Item,
    "SELECT * FROM items WHERE id = $1",
    id
)
.fetch_one(&pool)
.await?;

// Update with array operations
sqlx::query!(
    "UPDATE items SET tags = array_append(tags, $1) WHERE id = $2",
    new_tag,
    id
)
.execute(&pool)
.await?;

Approach 2: Using JSONB

JSONB provides flexibility and good indexing support.

PostgreSQL Table

CREATE TABLE items (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    tags JSONB NOT NULL DEFAULT '[]',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Optional: Add GIN index for better query performance
CREATE INDEX idx_items_tags ON items USING GIN (tags);

SQLx Model

use sqlx::FromRow;
use serde::{Deserialize, Serialize};
use sqlx::types::Json;

#[derive(Debug, FromRow)]
pub struct Item {
    pub id: i32,
    pub name: String,
    pub tags: Json<Vec<String>>,  // Using sqlx::types::Json wrapper
    pub created_at: DateTime<Utc>,
}

Example Operations

// Insert
let tags = Json(vec!["rust".to_string(), "database".to_string()]);
sqlx::query!(
    "INSERT INTO items (name, tags) VALUES ($1, $2)",
    name,
    tags as _
)
.execute(&pool)
.await?;

// Query using JSONB operators
let items = sqlx::query_as!(
    Item,
    "SELECT * FROM items WHERE tags @> $1",
    Json(vec!["rust".to_string()]) as _
)
.fetch_all(&pool)
.await?;

Approach 3: Normalized Design (Many-to-Many)

For better normalization and when you need to query tags efficiently.

PostgreSQL Tables

CREATE TABLE items (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE tags (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE item_tags (
    item_id INTEGER REFERENCES items(id) ON DELETE CASCADE,
    tag_id INTEGER REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (item_id, tag_id)
);

-- Indexes for performance
CREATE INDEX idx_item_tags_item_id ON item_tags(item_id);
CREATE INDEX idx_item_tags_tag_id ON item_tags(tag_id);

SQLx Models

#[derive(Debug, FromRow)]
pub struct Item {
    pub id: i32,
    pub name: String,
    pub created_at: DateTime<Utc>,
}

#[derive(Debug, FromRow)]
pub struct Tag {
    pub id: i32,
    pub name: String,
}

// Helper struct for loading items with tags
#[derive(Debug)]
pub struct ItemWithTags {
    pub item: Item,
    pub tags: Vec<String>,
}

impl ItemWithTags {
    pub async fn load(pool: &PgPool, item_id: i32) -> Result<Self, sqlx::Error> {
        let item = sqlx::query_as!(Item, "SELECT * FROM items WHERE id = $1", item_id)
            .fetch_one(pool)
            .await?;
        
        let tags = sqlx::query!(
            "SELECT t.name FROM tags t 
             JOIN item_tags it ON t.id = it.tag_id 
             WHERE it.item_id = $1",
            item_id
        )
        .fetch_all(pool)
        .await?
        .into_iter()
        .map(|r| r.name)
        .collect();
        
        Ok(Self { item, tags })
    }
}

Comparison and Recommendations

Use PostgreSQL Arrays (Approach 1) when:

• You have simple string lists that don't need complex querying
• Performance for basic operations is important
• You want the simplest implementation

Use JSONB (Approach 2) when:

• You might need to store more complex structures in the future
• You need powerful query operators
• You want flexibility without schema changes

Use Normalized Tables (Approach 3) when:

• Tags are shared across many items
• You need to query/analyze tags independently
• You want to avoid data duplication
• You need referential integrity for tags

For most cases where you just need to store a simple Vec<String>, I'd recommend Approach 1 (PostgreSQL Arrays) as it's the most straightforward and has excellent SQLx support with direct type mapping.