Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Migrate to ORM #17

Open
2 tasks
snosratiershad opened this issue Dec 28, 2024 · 4 comments
Open
2 tasks

Migrate to ORM #17

snosratiershad opened this issue Dec 28, 2024 · 4 comments

Comments

@snosratiershad
Copy link
Contributor

I'm going to take these TODOs:

  • Implement database migrations system
  • Add database connection pooling configuration

This issue is for tracking and discussions about it.

@snosratiershad
Copy link
Contributor Author

My approach is to use GORM orm and handle migration and database connections using the orm. It will also enable us to use scalable databases over sqlite.

@skariel
Copy link
Owner

skariel commented Dec 28, 2024

Thank you for your interest in contributing to KeyPub! I really appreciate you taking the initiative to help improve the project's database infrastructure.

After thinking about your proposal, I wanted to share some thoughts about the direction I'm envisioning for the project:

  1. Regarding the ORM choice: The project currently uses go-jet, and I'm actually planning to migrate to sqlc in the near future rather than GORM. This decision is based on wanting full type safety at compile time and optimal performance as the database grows. While GORM is popular and feature-rich, its runtime reflection approach can lead to performance overhead and potential runtime errors that we'd prefer to catch during compilation.

  2. On database migrations: The project is still in its early stages with around 100 active users, and SQLite is serving us well. Based on similar projects' scaling patterns, it should comfortably handle up to a million users. When we do need to scale beyond SQLite, it would likely involve adapting our schema to a new database system rather than traditional schema migrations.

That said, I see you're interested in database infrastructure improvements! There are several other valuable areas where contributions would be extremely helpful:

  • Helping with the planned migration from go-jet to sqlc
  • Adding more database-related tests
  • Improving query performance
  • Adding database metrics and monitoring

Would any of these areas interest you? I'm happy to provide more details about any of these tasks or discuss other ideas you might have for improving the project.

Thanks again for your enthusiasm to contribute!

@wizardishungry
Copy link
Contributor

I've had good luck with sqlc + golang-migrate. I embed the migrations in the binary so the database file is created on first use.

package store

import (
	"context"
	"database/sql"
	"embed"
	_ "embed"

	"github.com/golang-migrate/migrate/v4"
	_ "github.com/golang-migrate/migrate/v4/database/sqlite"
	"github.com/golang-migrate/migrate/v4/source/iofs"
	_ "modernc.org/sqlite"
)

//go:embed schema/*.sql
var migrations embed.FS

func InitDB(path string) (*sql.DB, error) {
	d, err := iofs.New(migrations, "schema")
	if err != nil {
		return nil, err
	}

	dsn := "sqlite://" + path
	m, err := migrate.NewWithSourceInstance("iofs", d, dsn)
	if err != nil {
		return nil, err
	}

	if err := m.Up(); err != nil && err != migrate.ErrNoChange {
		return nil, err
	}

	db, err := sql.Open("sqlite", path)
	if err != nil {
		return nil, err
	}

	return db, nil
}

The migrations directory has a series of files that may contain any sql. They are processed in lexicographic order:

0001_schema.up.sql
0002_images.up.sql
  • Performing an (up) migration is as easy as restarting with the new binary.
  • It's relatively easy to write unit tests around migrations.

100% agree about GORM. sqlc is a good choice, but I wasn't unhappy with Jet (first time using it).

@snosratiershad
Copy link
Contributor Author

Thanks @wizardishungry @skariel, I'll send a PR based on your decisions

@snosratiershad snosratiershad mentioned this issue Jan 15, 2025
5 tasks
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

3 participants