What's the difference between these two Gorm way of query things?

Question

I'm using Go and Gorm for Postgresql.

I want to understand what the difference is between

1. this:

   var name = "myName"
   var user User
   
   db.Where("user like ?", name).Find(&user)
   

2. and this:

   var user User
   
   db.Where("user like " + name).Find(&user)
   

The SQL query is the same.

I mean, why do we use ORMs?

1. Can the #1 become a prepared statement?

2. Is the #1 "more optimized" than the #2?

3. What does it mean "more optimized"?

Answer 1

To answer specifically to your questions:

Can the #1 become a prepared statement?

Usually ORMs will build prepared statements, so that (as explained by Flimzy) you can avoid sql injection and DB engine does not need to recalculate query plans.

Gorm seems to have an specific configuration for caching prepared statements: https://gorm.io/docs/v2_release_note.html#Prepared-Statement-Mode

Is the #1 "more optimized" than the #2?

You can see this part from database perspective and language perspective.

• Database: Because #1 uses prepared statements if you execute the same query again and again the db engine doesn't need to recalculate the query (re use prepared statements)
• Language: Golang is going to create a string for "user like", then another string for the concatenation "user like" + name. If this code is executed multiple times (in a loop for example) you will see an increase of execution time, just because each string means a new memory address assigned.

What does it mean "more optimized"?

More optimized means faster. As explained above:

• Prepared statements help to calculate a query plan only once, which means next time the same query is executed this plan doesn't need to be calculated by DB engine saving time. (You usually see this differences in complex queries)
• Language string concatenation can be resource consuming