Ability to use different schema without prefixing

[captainjapeng]

If you're having a generation problem please answer these questions before submitting your issue. Thanks!

What version of SQLBoiler are you using (sqlboiler --version)?

SQLBoiler v4.5.0

What is your database and version (eg. Postgresql 10)

Postgresql 12.6

If this happened at generation time what was the full SQLBoiler command you used to generate your models? (if not applicable leave blank)

output = "pkg/storage/psql/models"
no-tests = true
[psql]
    dbname      = "test-database"
    schema      = "test-database"
    host        = "localhost"
    port        = 5433
    user        = "user"
    pass        = "pass"
    sslmode     = "disable"
    blacklist = ["migrations", "gorp_migrations"]

[[types]]
    [types.match]
        db_type = "uuid"
    [types.replace]
        type = "uuid.UUID"
    [types.imports]
        third_party = ['"github.com/gofrs/uuid"']

If this happened at runtime what code produced the issue? (if not applicable leave blank)

What is the output of the command above with the -d flag added to it? (Provided you are comfortable sharing this, it contains a blueprint of your schema)

Please provide a relevant database schema so we can replicate your issue (Provided you are comfortable sharing this)

Using CrunchData's Postgresql operator, the database is provisioned under a different schema (based on the user's name)

 table_catalog | table_schema |     table_name     
---------------+--------------+--------------------
 test-database  | public       | pg_stat_statements
 test-database  | user          | table1
 test-database  | user          | table2
......

Further information. What did you do, what did you expect?

Running sqlboiler psql command generates commands with the schema named prefixed, I think there should be a way to disable this behavior as there could be a schema name difference between environments (local, staging, production).

Upon checking the implementation for prefixing, would it be okay if we expose this field via the config?

[aarondl]

So the proposal here is to allow users to override that value, and then use the connection string to set a default schema for the queries instead of have them in the sql created by sqlboiler itself?

[captainjapeng]

Sorry for not being able to reply immediately, but yes, CrunchyData's PSQL Operator is using the search_path's variable to the default schema to use on non-prefixed commands.

[lighttiger2505]

I had the same problem and avoided the problem by workarounds. I don't know if it will match your case, but here it is.

It seems that the psql generator in sqlboiler judges the schema specification based on whether the schema is public or not. If you specify public, it will create a go file with the schema excluded.

https://github.com/volatiletech/sqlboiler/blob/d6e9344de51013ac39233fae5e6df4b6212c3571/drivers/sqlboiler-psql/driver/psql.go#L92

However, it is not desirable to create a table in the public schema in order to create a schema-excluded file. Therefore, it is better to add an option to the generator such as --no-with-schema.

[stephenafamo]

A PR to add an option for --no-schema would be appreciated

[morganhein]

~Is this still a possible enhancement? Using public is a decent answer, but not great.~

I'm in the middle of a PR for this. I'll push within the day and hopefully can facilitate this.