GORM with managed PostgreSQL and MySQL

GORM is an ORM library for Go. You declare models as structs, create tables with AutoMigrate, and query through a chainable API, while GORM manages a database/sql connection pool underneath.

How does the integration work?

Appwrite native PostgreSQL and MySQL databases are standard engines that Appwrite provisions in your project's region, each with its own hostname, credentials, and TLS. GORM connects to them through its regular drivers, gorm.io/driver/postgres and gorm.io/driver/mysql, so models, migrations, and queries work with no Appwrite-specific configuration.

A long-running Go server connects to the engine directly and caps its own pool. Many instances or serverless runtimes can share the database's connection pooler instead.

How to implement

To connect GORM to an Appwrite native database, there are several steps you must complete:

Step 1: Create a native database

Create an account on Appwrite Cloud if you haven't already. In your project, go to Databases, click Create database, and select PostgreSQL or MySQL from the Native databases group. Pick a specification and click Create database. The database becomes ready within a few minutes.

For PostgreSQL, open the database and click Credentials to copy the host, password, and database name from the Details tab. For MySQL, read them from the database object with mysql.get() and an API key that has the databases.read scope. The MySQL username is admin.<hash> and the database name is always default.

Step 2: Build the DSN and open a connection

Read the credentials from the environment and open GORM against the direct engine port:

dsn := fmt.Sprintf(
    "host=%s user=admin password=%s dbname=%s port=5432 sslmode=require",
    os.Getenv("DB_HOST"), os.Getenv("DB_PASSWORD"), os.Getenv("DB_NAME"),
)

db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{})

cfg := mysqlcfg.Config{
    User:      os.Getenv("DB_USER"), // admin.<hash>
    Passwd:    os.Getenv("DB_PASSWORD"),
    Net:       "tcp",
    Addr:      net.JoinHostPort(os.Getenv("DB_HOST"), "3306"),
    DBName:    "default",
    ParseTime: true,
    TLSConfig: "true",
}

db, err := gorm.Open(gormmysql.Open(cfg.FormatDSN()), &gorm.Config{})

Step 3: Migrate the schema

Create tables with AutoMigrate during development:

type User struct {
    ID        uint   `gorm:"primaryKey"`
    Email     string `gorm:"size:255;uniqueIndex"`
    CreatedAt time.Time
}

if err := db.AutoMigrate(&User{}); err != nil {
    panic(err)
}

AutoMigrate adds missing tables, columns, and indexes, but never drops columns or changes types. For production, run versioned migrations with golang-migrate. Either way, run schema changes on the direct port (5432 or 3306) as the primary user.

Step 4: Size the connection pool

Cap the underlying database/sql pool so the sum across all instances stays under your specification's connection limit:

sqlDB, err := db.DB()
if err != nil {
    panic(err)
}

sqlDB.SetMaxOpenConns(25)
sqlDB.SetMaxIdleConns(25)
sqlDB.SetConnMaxLifetime(time.Hour)

Step 5: Use the pooler for high fan-out traffic

For many instances or serverless runtimes, connect through the pooler and keep each pool small. On PostgreSQL, use port 6432 and open the driver with postgres.New(postgres.Config{DSN: dsn, PreferSimpleProtocol: true}) so it stops issuing prepared statements, which transaction mode cannot keep. On MySQL, use port 6033 and set InterpolateParams: true so placeholder values are sent as one text query.

To test migrations in CI, run them against a PostgreSQL or MySQL branch, then delete the branch when the job finishes.

Read more about GORM and Appwrite Databases

If you would like to learn more about GORM and Appwrite Databases, we have some resources that you should visit: