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

dwsio · 2025-09-01T07:25:45Z

dwsio
Sep 1, 2025

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.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

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

Uh oh!

{{title}}

Uh oh!

Replies: 0 comments

Select a reply

Uh oh!

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

Uh oh!

dwsio Sep 1, 2025

Approach 1: Using PostgreSQL Arrays (Recommended)

PostgreSQL Table

SQLx Model in Rust

Example Operations

Approach 2: Using JSONB

PostgreSQL Table

SQLx Model

Example Operations

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

PostgreSQL Tables

SQLx Models

Comparison and Recommendations

Replies: 0 comments

dwsio
Sep 1, 2025