Method ListDatabases

backend/store/database.go:124–257

ListDatabases lists all databases.

(ctx context.Context, find *FindDatabaseMessage)

// ListDatabases lists all databases.
func (s *Store) ListDatabases(ctx context.Context, find *FindDatabaseMessage) ([]*DatabaseMessage, error) {
	from := qb.Q().Space("db")
	where := qb.Q().Space("TRUE")

	if filterQ := find.FilterQ; filterQ != nil {
		where.And("?", filterQ)
		// Check if the filter requires the db_schema table for table filtering
		sql, _, err := filterQ.ToSQL()
		if err == nil && strings.Contains(sql, "ds.metadata") {
			from.Space("INNER JOIN db_schema ds ON db.instance = ds.instance AND db.name = ds.db_name")
		}
	}

	from.Space("LEFT JOIN instance ON db.instance = instance.resource_id")

	if find.Workspace != "" {
		where.And("instance.workspace = ?", find.Workspace)
	}
	if v := find.ProjectID; v != nil {
		where.And("db.project = ?", *v)
	}
	if v := find.EffectiveEnvironmentID; v != nil {
		where.And(`COALESCE(
			db.environment,
			instance.environment
		) = ?`, *v)
	}
	if v := find.InstanceID; v != nil {
		where.And("db.instance = ?", *v)
	}
	if v := find.DatabaseName; v != nil {
		where.And("db.name = ?", *v)
	}
	if len(find.DatabaseNames) > 0 {
		where.And("db.name = ANY(?)", find.DatabaseNames)
	}
	if v := find.Engine; v != nil {
		where.And("instance.metadata->>'engine' = ?", v.String())
	}
	if !find.ShowDeleted {
		where.And("instance.deleted = ?", false)
		where.And("db.deleted = ?", false)
	}

	q := qb.Q().Space(`
		SELECT
			db.project,
			COALESCE(
				db.environment,
				instance.environment
			),
			db.environment,
			db.instance,
			db.name,
			db.deleted,
			db.metadata,
			instance.metadata->>'engine'
		FROM ?