Help to get migrations to run on Waterline standalone

[r2g]

Node version: (18.0.0)
DB adapter & version (sails-postgresql@4.0.0):

---

I kindly require assistance to get migrations to run on waterline standalone version 0.15.0

My config is as follows:

module.exports = {
	adapters: {
		pg: require('sails-postgresql'),
		mysql: require('sails-mysql'),
	},
	datastores: {
		default: {
			adapter: 'pg',
			host: 'localhost',
			port: 5432,
			user: 'rcp',
			password: 'rcp',
			database: 'db_service',
			isVersion12OrNewer: true,
		},
		mysql: {
			adapter: 'mysql',
			host: 'localhost',
			port: 3306,
			user: 'db',
			password: 'db',
			database: 'db_service',
		},
	},
};

My models:

module.exports = {
	identity: 'pet',
	datastore: 'default',
	primaryKey: 'id',
	migrate: 'alter',
	attributes: {
		id: {
			type: 'number',
			autoMigrations: { autoIncrement: true },
		},
		breed: { type: 'string' },
		type: { type: 'string' },
		name: { type: 'string' },

		// Add a reference to User
		owner: {
			model: 'user',
		},
	},
};

module.exports = {
	identity: 'user',
	datastore: 'default',
	primaryKey: 'id',
	migrate: 'alter',
	attributes: {
		id: {
			type: 'number',
			autoMigrations: { autoIncrement: true },
		},
		firstName: { type: 'string' },
		lastName: { type: 'string' },

		// Add a reference to Pets
		pets: {
			collection: 'pet',
			via: 'owner',
		},
	},
};

The bootstrap file:

const Waterline = require('waterline');
const config = require('./config');

const userModel = require('./models/user');
const petModel = require('./models/pet');

const userCollection = Waterline.Collection.extend(userModel);
const petCollection = Waterline.Collection.extend(petModel);

const waterline = new Waterline();

waterline.registerModel(userCollection);
waterline.registerModel(petCollection);

waterline.initialize(config, function (err, ontology) {
	if (err) {
		console.error(err.message);
		return;
	}

	// Tease out fully initialized models.
	let User = ontology.collections.user;
	let Pet = ontology.collections.pet;

	// Since we're using `await`, we'll scope our selves an async IIFE:
	(async () => {
		// First we create a user
		const user = await User.create({
			firstName: 'Neil',
			lastName: 'Armstrong',
		});

		// Then we create the pet
		const pet = await Pet.create({
			breed: 'beagle',
			type: 'dog',
			name: 'Astro',
			owner: user.id,
		});

		// Then we grab all users and their pets
		const users = await User.find().populate('pets');
		console.log(users);
	})()
		.then(() => {
			// All done.
		})
		.catch((err) => {
			console.error(err.message);
		}); //_∏_
});

My package.json:

{
	"name": "db-service",
	"version": "1.0.0",
	"main": "index.js",
	"license": "MIT",
	"scripts": {
		"dev": "nodemon index.js"
	},
	"dependencies": {
		"sails-mysql": "^2.0.0",
		"sails-postgresql": "^4.0.0",
		"waterline": "^0.15.0"
	},
	"devDependencies": {
		"nodemon": "^2.0.18"
	}
}

The issue I am facing is that when I run the bootstrap file, I get the error below:
Unexpected error from database adapter: relation "public.user" does not exist

Any assistance on this is highly appreciated.

Error Stack:

OperationalError [AdapterError]: Unexpected error from database adapter: relation "public.user" does not exist
    at callback (/Users/.../Downloads/musings/db-service/index.js:20:27)
    ... 20 lines matching cause stack trace ...
    at Connection.emit (node:events:539:35) {
  cause: Error [AdapterError]: Unexpected error from database adapter: relation "public.user" does not exist
      at callback (/Users/.../Downloads/musings/db-service/index.js:20:27)
      at /Users/.../Downloads/musings/db-service/node_modules/waterline/lib/waterline.js:731:14
      at /Users/.../Downloads/musings/db-service/node_modules/async/dist/async.js:952:25
      at iteratorCallback (/Users/.../Downloads/musings/db-service/node_modules/async/dist/async.js:997:17)
      at /Users/.../Downloads/musings/db-service/node_modules/async/dist/async.js:847:20
      at /Users/.../Downloads/musings/db-service/node_modules/waterline/lib/waterline/utils/system/validate-datastore-connectivity.js:42:14
      at /Users/.../Downloads/musings/db-service/node_modules/machine/lib/private/help-build-machine.js:954:24
      at handlerCbs.success (/Users/.../Downloads/musings/db-service/node_modules/machine/lib/private/help-build-machine.js:814:26)
      at Object.releaseConnection (/Users/.../Downloads/musings/db-service/node_modules/machinepack-postgresql/machines/release-connection.js:79:18)
      at wrapper (/Users/.../Downloads/musings/db-service/node_modules/@sailshq/lodash/lib/index.js:3282:19)
      at parley.retry (/Users/.../Downloads/musings/db-service/node_modules/machine/lib/private/help-build-machine.js:1076:19)
      at parley (/Users/.../Downloads/musings/db-service/node_modules/parley/lib/parley.js:140:5)
      at Object.runFn [as releaseConnection] (/Users/.../Downloads/musings/db-service/node_modules/machine/lib/private/help-build-machine.js:461:23)
      at /Users/.../Downloads/musings/db-service/node_modules/waterline/lib/waterline/utils/system/validate-datastore-connectivity.js:35:27
      at /Users/.../Downloads/musings/db-service/node_modules/machine/lib/private/help-build-machine.js:954:24
      at handlerCbs.success (/Users/.../Downloads/musings/db-service/node_modules/machine/lib/private/help-build-machine.js:814:26)
      at PendingItem.cb [as callback] (/Users/.../Downloads/musings/db-service/node_modules/machinepack-postgresql/machines/get-connection.js:87:20)
      at BoundPool._acquireClient (/Users/.../Downloads/musings/db-service/node_modules/pg-pool/index.js:298:21)
      at /Users/.../Downloads/musings/db-service/node_modules/pg-pool/index.js:270:21
      at Connection.<anonymous> (/Users/.../Downloads/musings/db-service/node_modules/pg/lib/client.js:253:7)
      at Object.onceWrapper (node:events:642:26)
      at Connection.emit (node:events:539:35) {
    adapterMethodName: 'create',
    modelIdentity: 'user',
    raw: error: relation "public.user" does not exist
        at Parser.parseErrorMessage (/Users/.../Downloads/musings/db-service/node_modules/pg-protocol/dist/parser.js:287:98)
        at Parser.handlePacket (/Users/.../Downloads/musings/db-service/node_modules/pg-protocol/dist/parser.js:126:29)
        at Parser.parse (/Users/.../Downloads/musings/db-service/node_modules/pg-protocol/dist/parser.js:39:38)
        at Socket.<anonymous> (/Users/.../Downloads/musings/db-service/node_modules/pg-protocol/dist/index.js:11:42)
        at Socket.emit (node:events:527:28)
        at addChunk (node:internal/streams/readable:324:12)
        at readableAddChunk (node:internal/streams/readable:297:9)
        at Readable.push (node:internal/streams/readable:234:10)
        at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
      length: 110,
      severity: 'ERROR',
      code: '42P01',
      detail: undefined,
      hint: undefined,
      position: '13',
      internalPosition: undefined,
      internalQuery: undefined,
      where: undefined,
      schema: undefined,
      table: undefined,
      column: undefined,
      dataType: undefined,
      constraint: undefined,
      file: 'parse_relation.c',
      line: '1363',
      routine: 'parserOpenTable'
    }
  },
  isOperational: true,
  adapterMethodName: 'create',
  modelIdentity: 'user',
  raw: error: relation "public.user" does not exist
      at Parser.parseErrorMessage (/Users/.../Downloads/musings/db-service/node_modules/pg-protocol/dist/parser.js:287:98)
      at Parser.handlePacket (/Users/.../Downloads/musings/db-service/node_modules/pg-protocol/dist/parser.js:126:29)
      at Parser.parse (/Users/.../Downloads/musings/db-service/node_modules/pg-protocol/dist/parser.js:39:38)
      at Socket.<anonymous> (/Users/.../Downloads/musings/db-service/node_modules/pg-protocol/dist/index.js:11:42)
      at Socket.emit (node:events:527:28)
      at addChunk (node:internal/streams/readable:324:12)
      at readableAddChunk (node:internal/streams/readable:297:9)
      at Readable.push (node:internal/streams/readable:234:10)
      at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
    length: 110,
    severity: 'ERROR',
    code: '42P01',
    detail: undefined,
    hint: undefined,
    position: '13',
    internalPosition: undefined,
    internalQuery: undefined,
    where: undefined,
    schema: undefined,
    table: undefined,
    column: undefined,
    dataType: undefined,
    constraint: undefined,
    file: 'parse_relation.c',
    line: '1363',
    routine: 'parserOpenTable'
  }
}

[sailsbot]

@r2g Thanks for posting! We'll take a look as soon as possible.

In the mean time, there are a few ways you can help speed things along:

• look for a workaround. (Even if it's just temporary, sharing your solution can save someone else a lot of time and effort.)
• tell us why this issue is important to you and your team. What are you trying to accomplish? (Submissions with a little bit of human context tend to be easier to understand and faster to resolve.)
• make sure you've provided clear instructions on how to reproduce the bug from a clean install.
• double-check that you've provided all of the requested version and dependency information. (Some of this info might seem irrelevant at first, like which database adapter you're using, but we ask that you include it anyway. Oftentimes an issue is caused by a confluence of unexpected factors, and it can save everybody a ton of time to know all the details up front.)
• read the code of conduct.
• if appropriate, ask your business to sponsor your issue. (Open source is our passion, and our core maintainers volunteer many of their nights and weekends working on Sails. But you only get so many nights and weekends in life, and stuff gets done a lot faster when you can work on it during normal daylight hours.)
• let us know if you are using a 3rd party plugin; whether that's a database adapter, a non-standard view engine, or any other dependency maintained by someone other than our core team. (Besides the name of the 3rd party package, it helps to include the exact version you're using. If you're unsure, check out this list of all the core packages we maintain.)

---

Please remember: never post in a public forum if you believe you've found a genuine security vulnerability. Instead, disclose it responsibly.

For help with questions about Sails, click here.

[ehainer]

Gonna +1 this question cause I too am very confused. Although it's a very cool MVC framework, using the full Sails package is way overkill for what I need to do (no need for controllers, views, etc. Just in need a solid ORM, which it appears waterline is)

Everything seems fairly straightforward except: how do you sync db schema? Or run migrations/seeds when using the standalone waterline?

Sequelize, TypeORM, Prisma (the 3 I've messed around with) all seem to expose a CLI command or at least the ability to run migrations/sync schema programmatically, but those abilities seem very obfuscated within... sails? waterline? waterline-postgresql?

Not meaning to sound critical because I do appreciate the work the Sails team does very much, just kind of in a black hole of "what do I do now?" if I can't even get the db schema populated. Googling yields next to nil questions on the subject, and fewer answers. I'd even be happy to explore the idea of a PR that would add the needed functions/commands, but could use some help getting pointed in the direction of where all that magic happens now (the functionality clearly exists within Sails since it does what it should during sails lift, so it should be doable). I couldn't even begin to guess where Sails does all the schema generation and syncing now, if it's even in that package. Just feels like I'm trying to drive a car with no ignition at the moment.

Or... if I just suck at googling and this is something super obvious like await Waterline.syncSchema(), apologies for the question, just need to doc reference link.

Thanks kindly 😄

[eashaw]

Hi @r2g & @ehainer,
Auto-migrations are not a part of Waterline, they are handled by sails-hook-orm.
When using standalone Waterline, you need to manage your schema yourself (with a tool like PgAdmin or Sequel Pro).