- Struct to column mapping via
db
tags - Database schema management via migrations
go get -u github.com/crisatosal/orm
View go doc documentation here
https://pkg.go.dev/github.com/cristosal/orm
First let's create a struct that represents our table.
type User struct {
ID int64
Name string
Username string
Password string
Active bool
}
By default, the snake_cased name of the struct will be used as the table name. In this case our User
struct will map to the user
table in our database.
Likewise, all fields in the struct will map to a snake_cased column names.
Let's say we want this struct to map instead to a users
table instead of a user
table. To do this we define the TableName
method on the struct.
func (User) TableName() string {
return "users"
}
Now open up an sql.DB
. For our example we are using the pgx
driver, but you can use whatever driver you want.
db, err := sql.Open("pgx", os.Getenv("CONNECTION_STRING"))
Now we can pass this db
around to our orm functions.
To insert our user into the database we call the Add
function. This function will automatically set the ID of our user to the value generated by the database.
u := User{
Name: "John Doe",
Username: "[email protected]",
Password: "changeme",
Active: true,
}
err := orm.Add(db, &u)
// TODO: handle error
fmt.Printf("added user with id=%d", u.ID)
Now that we have added our user, let's retrieve it from the database. First declare the type that will be scanned to.
var u User
Now we get the user from the database. Note that whatever is contained in the SQL string is placed after the SELECT
statement.
err := orm.Get(db, &u, "WHERE id = $1", 1)
This executes the following SQL query:
SELECT id, name, username, password FROM users WHERE id = $1
Lets take a look at all our active users in our database.
Like Get
we must pass a pointer to scan to, but this time we will pass a slice so that we can read all results from the result set.
var users []User
err := orm.List(db, &users, "WHERE active = TRUE")
If we wanted to list all users without needing any additional SQL, we could just pass an empty string or use the
orm.All
function
Let's assume John Doe wants to change their name. To do this we use the Update
function. Like the Get
function the SQL string allows you to customize the query. Here we are updating by id.
// Change the name
u.Name = "Bob Smith"
err := orm.Update(db, &u, "WHERE id = $1", u.ID)
Since this is a common SQL query we can also use the UpdateByID
variant.
err := orm.UpdateByID(db, &u)
Our user decided they want to delete their account. Let's remove them from the database. The function is similar to update
err := orm.Remove(db, &u, "WHERE id = $1", u.ID)
Remove also has a ByID
variant
err := orm.RemoveByID(db, &u)
In order to change your database schema over time you can use the migration features built in to orm
.
Initialize migration tables
err := orm.CreateMigrationTable(db)
You can change the name of the table or the schema that is used for migrations if you wish.
orm.SetMigrationTable("my_migrations") // defaults to _migrations
orm.SetSchema("my_schema") // defaults to public
The core functionality is encompassed in the following methods
// AddMigration adds a migration to the database and executes it.
// No error is returned if migration was already executed
func AddMigration(db.DB, migration *Migration) error
// RemoveMigration executes the down migration removes the migration from db
func RemoveMigration(db.DB) error
Example of adding/ a migration
orm.AddMigration(db, &orm.Migration{
Name: "Create Users Table",
Description: "Add Users Table with username and password fields",
Up: `CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(255) NOT NULL UNIQUE,
password TEXT NOT NULL
)`,
Down: "DROP TABLE users"
})
The most recent migration can then be reversed by calling the Remove method
orm.RemoveMigration(db)