v2 to v3 upgrade, difference in behavior in specifying sqlite database file location

[sw34]

Description and expected behavior
I am upgrading a zenstack v2 project to v3, using sqlite as the database. The behavior on how the database file location is determined has changed and that was not expected.

In my v2 app, in development:

• my sqllite database was in the /prisma folder
• I have a .env file with DATABASE_URL="file:./database.db".
• my schema.zmodel is in the root folder, and specifies datasource db { provider = "sqlite" url = env("DATABASE_URL") }
• My PrismaClient setup is located in /src/server/db.ts and did not specify a URL const createPrismaClient = () => new PrismaClient();

In v3, i moved schema.zmodel to the "/zenstack" folder. When I run zen db push, the database.db file is created in the /zenstack folder.

I my updated /src/server/db.ts if I don't specify a location for a sqlite file, database: new SQLite(), the table that should be there is not found [cause]: SqliteError: no such table: User

If I specify database: new SQLite(process.env.DATABASE_URL) I get a different error, this time SqliteError: unable to open database file

To make it work I need to specify the exact location of the database file database: new SQLite("file:../../zenstack/database.db")

The problem comes when I deploy this using docker, I have a docker volume that gets mapped to /data and I update my environment variable with that location, but my code now has a hardcoded location for the database.

Environment (please complete the following information):

• ZenStack version: 3.4.5
• Database type: sqlite
• Node.js/Bun version: 22.19.15
• Package manager: npm

[sanny-io]

I don't see how it could be possible for there to be a difference between using an environment variable and a raw string.
I don't see how it could be possible for new SQLite("file:../../zenstack/database.db") to work either.

None of the test cases or samples in ZenStack specify the file: protocol when using the SQLite constructor, and when I add the protocol, I get the unable to open database file error.

Works:

const client = new ZenStackClient(schema, {
    dialect: new SqliteDialect({ database: new SQLite('./test.db') }),
});

Does not work: unable to open database file

const client = new ZenStackClient(schema, {
    dialect: new SqliteDialect({ database: new SQLite('file:./test.db') }),
});

You should include the file: protocol in schema.zmodel, but not in the SQLite constructor. If you are seeing a difference between passing an env variable and a raw string, your env variable is probably either wrong or not being loaded correctly.

[sw34]

new SQLite("file:../../zenstack/database.db") is working in my project.

But that is besides the point the behavior I want/need is to not specify it here, I want it only specified in schema.zmodel like I had under Prisma and zenstack v2

[sanny-io]

There are many differences in behavior between v2 and v3. It is a major rewrite, and it is now expected for the caller to configure the adapter when constructing the ORM client as documented in the link below because the ORM does not use the datasource

https://zenstack.dev/docs/reference/zmodel/datasource

You had mentioned the issue is that you had to hardcode the location, but that should not be necessary. You can use environment variables both in the datasource and in your adapter constructor.

An environment variable means you never have to touch either of those things again.

[sw34]

I see that comment now in the docs.

Thanks for the help.

[sanny-io]

You're very welcome 🙂

Here is also the v2 migration document if you have not seen it. I don't think it mentions this change, but I will try to add a note about it.

https://zenstack.dev/docs/migrate-v2

[sw34]

This is a bit wonky.

using non relative URL I cannot pass file: so DB_CLIENT_URL="file:/data/database.db" will result in database not found errors wheras DB_CLIENT_URL="/data/database.db" works.

If you use relative paths i.e. in my dev, I have to pass file: or it wont find the database. DB_CLIENT_URL="file:../../zenstack/database.db" works, DB_CLIENT_URL="../../zenstack/database.db" does not.

This works, but not a great user experience! I also really hate having to specify the url twice (once for zen generate/push, once for runtime).

[ymc9]

This is by design for now for decoupling migrations generation and the ORM runtime. Maybe we will introduce some sort of config file to unify things in the future.