sqlc

[SunChero]

Hello,
first of all thanks for the work .. i really appreciate it
i saw at somepoint you moved away from sqlc .. can i know why you decided such a move

Thank you

[nicolasparada]

You are welcome and thank you for the interest.

I find sqlc great to kickstart a project since generates all the structs and methods. But I like to take over it after initial work to have full control.
Otherwise you spend time fighting the tool and looking up how to do things instead of actually implementing the features.

For example both posts and comments have a user, but you cannot reuse the same user model.

Or post and timeline item have the same fields but they generate a different model.

Etc.

[SunChero]

Thank you very much :)

[thomastthai]

Handling Shared Models in Go

I'm just started learning how to use sqlc. I may not fully understand the issue and the following could be gibberish.

In Go with sqlc, if Post and Comment models both have a user, but you want to reuse the same User model to avoid code duplication, couldn't you can achieve this by structuring your models in a way that the User model is defined separately and can be referenced by both Post and Comment? A possible solution on how to do it:

1. Define the User Model Separately

Define the User model as a distinct type, and reference it in both the Post and Comment models. This keeps the code DRY and ensures you are reusing the same model.

Example:

// user.go
type User struct {
    ID        int    `json:"id"`
    Username  string `json:"username"`
    FirstName string `json:"first_name"`
    LastName  string `json:"last_name"`
    Email     string `json:"email"`
}

2. Define the Post and Comment Models Using User

Both the Post and Comment models can include the User model as a field. You can use the UserID as a foreign key reference or embed the User struct directly, depending on your use case.

Post Model:

// post.go
type Post struct {
    ID        int    `json:"id"`
    Title     string `json:"title"`
    Content   string `json:"content"`
    CreatedAt string `json:"created_at"`
    UpdatedAt string `json:"updated_at"`
    User      User   `json:"user"` // Reference to User model
}

Comment Model:

// comment.go
type Comment struct {
    ID        int    `json:"id"`
    Content   string `json:"content"`
    PostID    int    `json:"post_id"`
    CreatedAt string `json:"created_at"`
    UpdatedAt string `json:"updated_at"`
    User      User   `json:"user"` // Reference to User model
}

In this example, the User model is reused directly in both Post and Comment as a field. User will be populated from your database query result along with other fields like Post or Comment.

3. Using SQL Queries with sqlc

When you define your queries in SQL, ensure that User is included in the result set. For example:

Post Query:

-- file: queries/posts.sql
SELECT
    p.id,
    p.title,
    p.content,
    p.created_at,
    p.updated_at,
    u.id AS user_id,
    u.username,
    u.first_name,
    u.last_name,
    u.email
FROM posts p
JOIN users u ON p.user_id = u.id
WHERE p.id = $1;

Comment Query:

-- file: queries/comments.sql
SELECT
    c.id,
    c.content,
    c.post_id,
    c.created_at,
    c.updated_at,
    u.id AS user_id,
    u.username,
    u.first_name,
    u.last_name,
    u.email
FROM comments c
JOIN users u ON c.user_id = u.id
WHERE c.id = $1;

4. sqlc Output

When you run sqlc to generate Go code, sqlc will generate the appropriate Go models and query functions. You will get a result similar to the following for Post and Comment:

Generated Post Model (post.go):

type Post struct {
    ID        int    `json:"id"`
    Title     string `json:"title"`
    Content   string `json:"content"`
    CreatedAt string `json:"created_at"`
    UpdatedAt string `json:"updated_at"`
    User      User   `json:"user"` // Embedded User struct
}

Generated Comment Model (comment.go):

type Comment struct {
    ID        int    `json:"id"`
    Content   string `json:"content"`
    PostID    int    `json:"post_id"`
    CreatedAt string `json:"created_at"`
    UpdatedAt string `json:"updated_at"`
    User      User   `json:"user"` // Embedded User struct
}

Generated User Model (user.go):

type User struct {
    ID        int    `json:"id"`
    Username  string `json:"username"`
    FirstName string `json:"first_name"`
    LastName  string `json:"last_name"`
    Email     string `json:"email"`
}

5. How to Handle SQLC Output and Queries

After generating the models with sqlc, you can query for posts and comments, and both models will have the User data embedded. For example, if you query for posts:

// Example to fetch post with user
func getPostByID(db *sql.DB, id int) (*Post, error) {
    query := `SELECT p.id, p.title, p.content, p.created_at, p.updated_at, u.id AS user_id, u.username, u.first_name, u.last_name, u.email
              FROM posts p
              JOIN users u ON p.user_id = u.id
              WHERE p.id = $1`
    row := db.QueryRow(query, id)

    var p Post
    if err := row.Scan(&p.ID, &p.Title, &p.Content, &p.CreatedAt, &p.UpdatedAt, 
                       &p.User.ID, &p.User.Username, &p.User.FirstName, &p.User.LastName, &p.User.Email); err != nil {
        return nil, err
    }
    return &p, nil
}

This way, you only need to define the User model once and reuse it in multiple places, keeping your code DRY and clean.

Conclusion:

By defining the User model separately and embedding it in both Post and Comment, you can easily reuse the same model across different structures in your application. Using sqlc to handle queries ensures that the User data is populated alongside the other fields from your queries.

[nicolasparada]

You can embed types now in sqlc ;)

At the time of this initial question, that was not possible I think.