Preview deployments on tenants?

Hi, I have being working for a while on migrating our current database into a database per tenant architecture on Vercel and I'm almost done. For preview deployment, we use the Vercel integration but what happend for tenant databases? I have a script for migrations very similar to this one: https://github.com/neondatabase/db-per-tenant/blob/main/migrate.ts The only difference is that when there is the env variable VERCEL_BRANCH_URL, then I want to create a branch with that name and apply the migrations to that branch. If there is not, then the migrations are applied to the main branch. The only issue is that the current neon api (v2) doesn't allow to specify a branch name or a branch id, so now I'm kinda lost and I don't know how to proceed.

 const branch = await neonApiClient.POST("/projects/{project_id}/branches", {
  params: {
    path: {
      project_id: neonDatabaseId,
    },
    // Not possible :(
    query: {
      branch_name: process.env.VERCEL_BRANCH_URL,
    }
  },
})

 const branch = await neonApiClient.POST("/projects/{project_id}/branches", {
  params: {
    path: {
      project_id: neonDatabaseId,
    },
    // Not possible :(
    query: {
      branch_name: process.env.VERCEL_BRANCH_URL,
    }
  },
})

GitHub

db-per-tenant/migrate.ts at main · neondatabase/db-per-tenant

Example chat-with-pdf app showing how to provision a dedicated database instance for each user. In this app, every database uses pgvector for similarity search. Powered by Neon - neondatabase/db-pe...

10 Replies

exotic-emeraldOP•12mo ago

Any ideas so far for this?

afraid-scarlet•12mo ago

I'm confused, it looks like you're quering the branches but I thought you wanted to create a branch. Creating a branch takes a name, it looks like this:

curl --request POST \
     --url https://console.neon.tech/api/v2/projects/project_id/branches \
     --header 'accept: application/json' \
     --header 'content-type: application/json' \
     --data '
{
  "branch": {
    "name": process.env.VERCEL_BRANCH_URL
  }
}

curl --request POST \
     --url https://console.neon.tech/api/v2/projects/project_id/branches \
     --header 'accept: application/json' \
     --header 'content-type: application/json' \
     --data '
{
  "branch": {
    "name": process.env.VERCEL_BRANCH_URL
  }
}

exotic-emeraldOP•12mo ago

I'm try to do both. I need to create a branch for each tenant when a new preview deployment comes in. And then I need to read from that branch This is how I did the first part

const getProjectConnectionString = async (projectId: string) => {
  if (process.env.VERCEL_ENV === "production") {
    const project = await neonApiClient.GET("/projects/{project_id}/connection_uri", {
      params: {
        path: {
          project_id: projectId,
        },
        query: {
          database_name: "neondb",
          role_name: "neondb_owner",
        },
      },
    })

    return project.data?.uri
  }

  const branch = await neonApiClient.POST(`/projects/{project_id}/branches`, {
    params: {
      path: {
        project_id: projectId,
      },
    },
    body: {
      branch: {
        name: process.env.VERCEL_BRANCH_URL,
      },
    },
  })

  return branch.data?.connection_uris?.at(0)?.connection_uri
}

const main = async () => {
  try {
    const databases = await mainDatabase.query.unioBusinessesTable.findMany({})
    log("Databases to migrate:", databases.length)

    for (const database of databases) {
      const { neonDatabaseId } = database

      try {
        const connectionString = await getProjectConnectionString(neonDatabaseId)
        if (!connectionString) {
          throw new Error(`Connection string not found for database ${neonDatabaseId}`)
        }

        const client = postgres(connectionString, { max: 1 })
        const drizzleDb = drizzle(client)

        await migrate(drizzleDb, {
          migrationsFolder: "src/databases/business/migrations",
        })

        await client.end()

        log(`Successfully migrated database ${neonDatabaseId}`)
      } catch (error) {
        console.error(`Error migrating database ${neonDatabaseId}:`, error)
      }
    }

    log("Migration process completed")
  } catch (error) {
    console.error("Error in main process:", error)
  } finally {
    process.exit(0)
  }
}

main()

const getProjectConnectionString = async (projectId: string) => {
  if (process.env.VERCEL_ENV === "production") {
    const project = await neonApiClient.GET("/projects/{project_id}/connection_uri", {
      params: {
        path: {
          project_id: projectId,
        },
        query: {
          database_name: "neondb",
          role_name: "neondb_owner",
        },
      },
    })

    return project.data?.uri
  }

  const branch = await neonApiClient.POST(`/projects/{project_id}/branches`, {
    params: {
      path: {
        project_id: projectId,
      },
    },
    body: {
      branch: {
        name: process.env.VERCEL_BRANCH_URL,
      },
    },
  })

  return branch.data?.connection_uris?.at(0)?.connection_uri
}

const main = async () => {
  try {
    const databases = await mainDatabase.query.unioBusinessesTable.findMany({})
    log("Databases to migrate:", databases.length)

    for (const database of databases) {
      const { neonDatabaseId } = database

      try {
        const connectionString = await getProjectConnectionString(neonDatabaseId)
        if (!connectionString) {
          throw new Error(`Connection string not found for database ${neonDatabaseId}`)
        }

        const client = postgres(connectionString, { max: 1 })
        const drizzleDb = drizzle(client)

        await migrate(drizzleDb, {
          migrationsFolder: "src/databases/business/migrations",
        })

        await client.end()

        log(`Successfully migrated database ${neonDatabaseId}`)
      } catch (error) {
        console.error(`Error migrating database ${neonDatabaseId}:`, error)
      }
    }

    log("Migration process completed")
  } catch (error) {
    console.error("Error in main process:", error)
  } finally {
    process.exit(0)
  }
}

main()

and for reading the connection string

const getProjectConnectionString = async (projectId: string) => {
  if (process.env.VERCEL_ENV === "production") {
    const project = await neonApiClient.GET("/projects/{project_id}/connection_uri", {
      params: {
        path: {
          project_id: projectId,
        },
        query: {
          database_name: "neondb",
          role_name: "neondb_owner",
          pooled: true,
        },
      },
    })

    return project.data?.uri
  }

  const branches = await neonApiClient.GET(`/projects/{project_id}/branches`, {
    params: {
      path: {
        project_id: projectId,
      },
    },
  })

  const branch = branches.data?.branches.find((x) => x.name === process.env.VERCEL_BRANCH_URL)
  if (!branch) {
    throw new Error(`Branch ${process.env.VERCEL_BRANCH_URL} not found`)
  }

  const project = await neonApiClient.GET("/projects/{project_id}/connection_uri", {
    params: {
      path: {
        project_id: projectId,
      },
      query: {
        database_name: "neondb",
        role_name: "neondb_owner",
        pooled: true,
        branch_id: branch.id,
      },
    },
  })

  return project.data?.uri
}

const getProjectConnectionString = async (projectId: string) => {
  if (process.env.VERCEL_ENV === "production") {
    const project = await neonApiClient.GET("/projects/{project_id}/connection_uri", {
      params: {
        path: {
          project_id: projectId,
        },
        query: {
          database_name: "neondb",
          role_name: "neondb_owner",
          pooled: true,
        },
      },
    })

    return project.data?.uri
  }

  const branches = await neonApiClient.GET(`/projects/{project_id}/branches`, {
    params: {
      path: {
        project_id: projectId,
      },
    },
  })

  const branch = branches.data?.branches.find((x) => x.name === process.env.VERCEL_BRANCH_URL)
  if (!branch) {
    throw new Error(`Branch ${process.env.VERCEL_BRANCH_URL} not found`)
  }

  const project = await neonApiClient.GET("/projects/{project_id}/connection_uri", {
    params: {
      path: {
        project_id: projectId,
      },
      query: {
        database_name: "neondb",
        role_name: "neondb_owner",
        pooled: true,
        branch_id: branch.id,
      },
    },
  })

  return project.data?.uri
}

It would be nice if the /projects/{project_id}/connection_uri endpoint also accepts a branch_name. Like branch_id_or_name to make only 1 request

afraid-scarlet•12mo ago

oh i see, that's a bit of extra work. i'll see if it's possible to query for the branch like you've asked. Appreciate all the extra context!

exotic-emeraldOP•12mo ago

The main idea is to use Vercel Preview Deployments on the tenant databases (the main is configure by the Vercel Integration so it's omit) When the Vercel preview deployment starts, the following script is run

const getProjectConnectionString = async (projectId: string) => {
  if (!process.env.VERCEL_ENV || process.env.VERCEL_ENV === "production") {
    const project = await neonApiClient.GET(
      "/projects/{project_id}/connection_uri",
      {
        params: {
          path: {
            project_id: projectId,
          },
          query: {
            database_name: "neondb",
            role_name: "neondb_owner",
          },
        },
      }
    );

    return project.data?.uri;
  }

  const branch = await neonApiClient.POST(`/projects/{project_id}/branches`, {
    params: {
      path: {
        project_id: projectId,
      },
    },
    body: {
      branch: {
        name: process.env.VERCEL_BRANCH_URL,
      },
    },
  });

  return branch.data?.connection_uris?.at(0)?.connection_uri;
};

const main = async () => {
  try {
    const { mainDatabase } = getMainDatabase();
    const databases = await mainDatabase
      .select({ neonDatabaseId: tenantsTable.neonDatabaseId })
      .from(tenantsTable);

    for (const database of databases) {
      const { neonDatabaseId } = database;

      try {
        const connectionString = await getProjectConnectionString(
          neonDatabaseId
        );
        if (!connectionString) {
          throw new Error(
            `Connection string not found for database ${neonDatabaseId}`
          );
        }

        const client = postgres(connectionString, { max: 1 });
        const drizzleDb = drizzle(client);

        await migrate(drizzleDb, {
          migrationsFolder: "src/databases/tenants/migrations",
        });

        await client.end();

        log(`Successfully migrated database ${neonDatabaseId}`);
      } catch (error) {
        console.error(`Error migrating database ${neonDatabaseId}:`, error);
      }
    }

    log("Migration process completed");
  } catch (error) {
    console.error("Error in main process:", error);
  } finally {
    process.exit(0);
  }
};

main();

const getProjectConnectionString = async (projectId: string) => {
  if (!process.env.VERCEL_ENV || process.env.VERCEL_ENV === "production") {
    const project = await neonApiClient.GET(
      "/projects/{project_id}/connection_uri",
      {
        params: {
          path: {
            project_id: projectId,
          },
          query: {
            database_name: "neondb",
            role_name: "neondb_owner",
          },
        },
      }
    );

    return project.data?.uri;
  }

  const branch = await neonApiClient.POST(`/projects/{project_id}/branches`, {
    params: {
      path: {
        project_id: projectId,
      },
    },
    body: {
      branch: {
        name: process.env.VERCEL_BRANCH_URL,
      },
    },
  });

  return branch.data?.connection_uris?.at(0)?.connection_uri;
};

const main = async () => {
  try {
    const { mainDatabase } = getMainDatabase();
    const databases = await mainDatabase
      .select({ neonDatabaseId: tenantsTable.neonDatabaseId })
      .from(tenantsTable);

    for (const database of databases) {
      const { neonDatabaseId } = database;

      try {
        const connectionString = await getProjectConnectionString(
          neonDatabaseId
        );
        if (!connectionString) {
          throw new Error(
            `Connection string not found for database ${neonDatabaseId}`
          );
        }

        const client = postgres(connectionString, { max: 1 });
        const drizzleDb = drizzle(client);

        await migrate(drizzleDb, {
          migrationsFolder: "src/databases/tenants/migrations",
        });

        await client.end();

        log(`Successfully migrated database ${neonDatabaseId}`);
      } catch (error) {
        console.error(`Error migrating database ${neonDatabaseId}:`, error);
      }
    }

    log("Migration process completed");
  } catch (error) {
    console.error("Error in main process:", error);
  } finally {
    process.exit(0);
  }
};

main();

So it can create a branch for all the tenants

afraid-scarlet•12mo ago

curl --request GET \ --url 'https://console.neon.tech/api/v2/projects/billowing-block-74888911/branches?search=BRANCH_NAME_OR_ID' \ --header 'accept: application/json' \ --header 'authorization: Bearer XXX' the branches API allows for a search term to be passed

afraid-scarlet•12mo ago

https://api-docs.neon.tech/reference/listprojectbranches

Neon

Get a list of branches

Retrieves a list of branches for the specified project. You can obtain a project_id by listing the projects for your Neon account. Each Neon project has a root branch named main . A branch_id value has a br- prefix. A project may contain child branches that were branched from main or from another br...

exotic-emeraldOP•12mo ago

Lastly, in the code, we switch branches using the following code

const getProjectConnectionString = async (projectId: string) => {
  if (!process.env.VERCEL_ENV || process.env.VERCEL_ENV === "production") {
    const project = await neonApiClient.GET("/projects/{project_id}/connection_uri", {
      params: {
        path: {
          project_id: projectId,
        },
        query: {
          database_name: "neondb",
          role_name: "neondb_owner",
          pooled: true,
        },
      },
    })

    return project.data?.uri
  }

  const branches = await neonApiClient.GET(`/projects/{project_id}/branches`, {
    params: {
      path: {
        project_id: projectId,
      },
    },
  })

  const branch = branches.data?.branches.find((x) => x.name === process.env.VERCEL_BRANCH_URL)
  if (!branch) {
    throw new Error(`Branch ${process.env.VERCEL_BRANCH_URL} not found`)
  }

  const project = await neonApiClient.GET("/projects/{project_id}/connection_uri", {
    params: {
      path: {
        project_id: projectId,
      },
      query: {
        database_name: "neondb",
        role_name: "neondb_owner",
        pooled: true,
        branch_id: branch.id,
      },
    },
  })

  return project.data?.uri
}

const _getTenantConnectionString = async (tenantId: TenantId) => {
  const { mainDatabase } = getMainDatabase()
  const [tenant] = await mainDatabase
    .select({ neonDatabaseId: tenantsTable.neonDatabaseId })
    .from(tenantsTable)
    .where(eq(tenantsTable.id, tenantId))

  if (!tenant?.neonDatabaseId) {
    throw new Error(`Tenant ${tenantId} not found`)
  }

  const connectionString = await getProjectConnectionString(tenant.neonDatabaseId)
  if (!connectionString) {
    throw new Error(`Connection string not found for tenant ${tenantId}`)
  }

  return connectionString
}

export const getTenantDatabase = cache(async (tenantId: TenantId) => {
  const tenantDatabase = drizzleInteractive(pool, {
    schema: schema,
  })

  return {
    tenantDatabase,
  }
})

const getProjectConnectionString = async (projectId: string) => {
  if (!process.env.VERCEL_ENV || process.env.VERCEL_ENV === "production") {
    const project = await neonApiClient.GET("/projects/{project_id}/connection_uri", {
      params: {
        path: {
          project_id: projectId,
        },
        query: {
          database_name: "neondb",
          role_name: "neondb_owner",
          pooled: true,
        },
      },
    })

    return project.data?.uri
  }

  const branches = await neonApiClient.GET(`/projects/{project_id}/branches`, {
    params: {
      path: {
        project_id: projectId,
      },
    },
  })

  const branch = branches.data?.branches.find((x) => x.name === process.env.VERCEL_BRANCH_URL)
  if (!branch) {
    throw new Error(`Branch ${process.env.VERCEL_BRANCH_URL} not found`)
  }

  const project = await neonApiClient.GET("/projects/{project_id}/connection_uri", {
    params: {
      path: {
        project_id: projectId,
      },
      query: {
        database_name: "neondb",
        role_name: "neondb_owner",
        pooled: true,
        branch_id: branch.id,
      },
    },
  })

  return project.data?.uri
}

const _getTenantConnectionString = async (tenantId: TenantId) => {
  const { mainDatabase } = getMainDatabase()
  const [tenant] = await mainDatabase
    .select({ neonDatabaseId: tenantsTable.neonDatabaseId })
    .from(tenantsTable)
    .where(eq(tenantsTable.id, tenantId))

  if (!tenant?.neonDatabaseId) {
    throw new Error(`Tenant ${tenantId} not found`)
  }

  const connectionString = await getProjectConnectionString(tenant.neonDatabaseId)
  if (!connectionString) {
    throw new Error(`Connection string not found for tenant ${tenantId}`)
  }

  return connectionString
}

export const getTenantDatabase = cache(async (tenantId: TenantId) => {
  const tenantDatabase = drizzleInteractive(pool, {
    schema: schema,
  })

  return {
    tenantDatabase,
  }
})

That's all the context I think This is almost the same, you will still need 2 requests to get the connection string My idea was the following

const getProjectConnectionString = async (projectId: string) => {
  if (!process.env.VERCEL_ENV || process.env.VERCEL_ENV === "production") {
    const project = await neonApiClient.GET("/projects/{project_id}/connection_uri", {
      params: {
        path: {
          project_id: projectId,
        },
        query: {
          database_name: "neondb",
          role_name: "neondb_owner",
          pooled: true,
        },
      },
    })

    return project.data?.uri
  }

  const project = await neonApiClient.GET("/projects/{project_id}/connection_uri", {
    params: {
      path: {
        project_id: projectId,
      },
      query: {
        database_name: "neondb",
        role_name: "neondb_owner",
        pooled: true,
        // Something like this 
        branch_id_or_name: process.env.VERCEL_BRANCH_URL,
      },
    },
  })

  return project.data?.uri
}

const getProjectConnectionString = async (projectId: string) => {
  if (!process.env.VERCEL_ENV || process.env.VERCEL_ENV === "production") {
    const project = await neonApiClient.GET("/projects/{project_id}/connection_uri", {
      params: {
        path: {
          project_id: projectId,
        },
        query: {
          database_name: "neondb",
          role_name: "neondb_owner",
          pooled: true,
        },
      },
    })

    return project.data?.uri
  }

  const project = await neonApiClient.GET("/projects/{project_id}/connection_uri", {
    params: {
      path: {
        project_id: projectId,
      },
      query: {
        database_name: "neondb",
        role_name: "neondb_owner",
        pooled: true,
        // Something like this 
        branch_id_or_name: process.env.VERCEL_BRANCH_URL,
      },
    },
  })

  return project.data?.uri
}

Currently the code works without issues, it's just a request/feedback 🙂

afraid-scarlet•12mo ago

Yes, with query.search = VERCEL_BRANCH_URL you'll reduce the number of returns. I'll put in the request for querying the connection strings! thx again

exotic-emeraldOP•12mo ago

I know, the issue is that the endpoint /projects/{project_id}/branches doesn't return the connection string, so you need to hit /projects/{project_id}/branches just to get the branch id and the hit /projects/{project_id}/connection_uri to get the connection string Would be easier that the endpoint /projects/{project_id}/connection_uri allows also a branch_name so the first request is avoided at all This is more of a whim, there's actually nothing wrong with the current code

Gaming

Programming

Preview deployments on tenants?

Did you find this page helpful?