| 545 | |
| 546 | |
| 547 | def _connect_sql(urlstr: str) -> psycopg.Connection | None: |
| 548 | if urlstr.startswith("foundationdb:"): |
| 549 | return None |
| 550 | |
| 551 | hint = """Have you correctly configured CockroachDB or PostgreSQL? |
| 552 | |
| 553 | For CockroachDB: |
| 554 | Follow the instructions in doc/developer/guide.md#CockroachDB |
| 555 | |
| 556 | For PostgreSQL: |
| 557 | 1. Install PostgreSQL |
| 558 | 2. Create a database: `createdb materialize` |
| 559 | 3. Set the MZDEV_POSTGRES environment variable accordingly: `export MZDEV_POSTGRES=postgres://$(whoami)@localhost/materialize`""" |
| 560 | try: |
| 561 | dbconn = psycopg.connect(urlstr) |
| 562 | dbconn.autocommit = True |
| 563 | except psycopg.DatabaseError as e: |
| 564 | raise UIError( |
| 565 | f"unable to connect to metadata database: {e}", |
| 566 | hint=hint, |
| 567 | ) |
| 568 | except psycopg.InterfaceError as e: |
| 569 | raise UIError( |
| 570 | f"unable to connect to metadata database: {e}", |
| 571 | hint=hint, |
| 572 | ) |
| 573 | |
| 574 | # For CockroachDB, after connecting, we can ensure the database exists. For |
| 575 | # PostgreSQL, the database must exist for us to connect to it at all--we |
| 576 | # declare it to be the user's problem to create this database. |
| 577 | url = urlparse(urlstr) |
| 578 | database = url.path.removeprefix("/") |
| 579 | with dbconn.cursor() as cur: |
| 580 | try: |
| 581 | cur.execute("SHOW crdb_version") |
| 582 | if not database: |
| 583 | raise UIError( |
| 584 | f"database name is missing in the postgres URL: {urlstr}", |
| 585 | hint="When connecting to CockroachDB, the database name is required.", |
| 586 | ) |
| 587 | except psycopg.errors.UndefinedObject: |
| 588 | return dbconn |
| 589 | |
| 590 | _run_sql(dbconn, f"CREATE DATABASE IF NOT EXISTS {database}") |
| 591 | return dbconn |
| 592 | |
| 593 | |
| 594 | def _run_sql(conn: psycopg.Connection, sql: str) -> None: |