New Case Study:See how Anthropic automated 95% of dependency reviews with Socket.Learn More →
Socket
Sign inDemoInstall
Socket
Sign inDemoInstall

dbdiff

Package Overview
Dependencies
Maintainers
1
Versions
15
Alerts
File Explorer

Advanced tools

License
Socket logo

Install Socket

Detect and block malicious and high-risk dependencies

Install

dbdiff - npm Package Compare versions

Comparing version 0.2.0 to 0.3.0

dbdiff.js
dialects/index.js
dialects/postgres-client.js
dialects/postgres.js

365

index.js
#!/usr/bin/env node
var txain = require('txain')
var multiline = require('multiline')
var _ = require('underscore')
var pg = require('pg')
var util = require('util')
var DbDiff = exports.DbDiff = require('./dbdiff')
var dbdiff = module.exports = {}
exports.describeDatabase = require('./dialects').describeDatabase
dbdiff.log = function() {
var msg = util.format.apply(null, Array.prototype.slice.call(arguments))
dbdiff.logger(msg)
}
dbdiff.describeDatabase = function(conString, callback) {
var client = new pg.Client(conString)
var schema = {
tables: {},
}
txain(function(callback) {
client.connect(callback)
})
.then(function(client, done, callback) {
client.query('SELECT * FROM pg_tables WHERE schemaname NOT IN ($1, $2, $3)', ['temp', 'pg_catalog', 'information_schema'], callback)
})
.then(function(result, callback) {
callback(null, result.rows)
})
.map(function(table, callback) {
var query = multiline(function() {;/*
SELECT
table_name,
table_schema,
column_name,
data_type,
udt_name,
character_maximum_length,
is_nullable,
column_default
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
table_name=$1 AND table_schema=$2;
*/})
client.query(query, [table.tablename, table.schemaname], callback)
})
.then(function(descriptions, callback) {
var tables = schema.tables = {}
descriptions.forEach(function(desc) {
desc.rows.forEach(function(row) {
var tableName = util.format('"%s"."%s"', row.table_schema, row.table_name)
var table = tables[tableName]
if (!table) {
tables[tableName] = []
table = tables[tableName]
}
delete row.table_schema
delete row.table_name
table.push(row)
})
})
var query = multiline(function() {;/*
SELECT
i.relname as indname,
i.relowner as indowner,
idx.indrelid::regclass,
am.amname as indam,
idx.indkey,
ARRAY(
SELECT pg_get_indexdef(idx.indexrelid, k + 1, true)
FROM generate_subscripts(idx.indkey, 1) as k
ORDER BY k
) AS indkey_names,
idx.indexprs IS NOT NULL as indexprs,
idx.indpred IS NOT NULL as indpred,
ns.nspname
FROM
pg_index as idx
JOIN pg_class as i
ON i.oid = idx.indexrelid
JOIN pg_am as am
ON i.relam = am.oid
JOIN pg_namespace as ns
ON ns.oid = i.relnamespace
AND ns.nspname NOT IN ('pg_catalog', 'pg_toast');
*/})
client.query(query, callback)
})
.then(function(result, callback) {
schema.indexes = result.rows
client.query('SELECT * FROM information_schema.sequences', callback)
}).then(function(result, callback) {
schema.sequences = result.rows
schema.sequences.forEach(function(sequence) {
sequence.name = util.format('"%s"."%s"', sequence.sequence_schema, sequence.sequence_name)
})
client.query('SELECT current_schema()', callback)
})
.end(function(err, result) {
client.end()
if (err) return callback(err)
schema.public_schema = result.rows[0].current_schema
callback(null, schema)
})
}
function dataType(info) {
var type
if (info.data_type === 'ARRAY') {
type = info.udt_name
if (type.substring(0, 1) === '_') {
type = type.substring(1)
}
type += '[]'
} else if (info.data_type === 'USER-DEFINED') {
type = info.udt_name // hstore for example
} else {
type = info.data_type
}
if (info.character_maximum_length) {
type = type+'('+info.character_maximum_length+')'
}
return type
}
function columnNames(columns) {
return columns.map(function(col) {
return col.column_name
}).sort()
}
function columnDescription(col) {
var desc = dataType(col)
if (col.column_default) {
desc += ' DEFAULT '+col.column_default
}
desc += col.is_nullable === 'NO' ? ' NOT NULL' : ' NULL'
return desc
}
function compareTables(tableName, db1, db2) {
var table1 = db1.tables[tableName]
var table2 = db2.tables[tableName]
var columNames1 = columnNames(table1)
var columNames2 = columnNames(table2)
var diff1 = _.difference(columNames1, columNames2)
var diff2 = _.difference(columNames2, columNames1)
diff1.forEach(function(columnName) {
dbdiff.log('ALTER TABLE %s DROP COLUMN "%s";', tableName, columnName)
dbdiff.log()
})
diff2.forEach(function(columnName) {
var col = _.findWhere(table2, { column_name: columnName })
var type = dataType(col)
dbdiff.log('ALTER TABLE %s ADD COLUMN "%s" %s;', tableName, columnName, columnDescription(col))
dbdiff.log()
})
var common = _.intersection(columNames1, columNames2)
common.forEach(function(columnName) {
var col1 = _.findWhere(table1, { column_name: columnName })
var col2 = _.findWhere(table2, { column_name: columnName })
if (col1.data_type !== col2.data_type
|| col1.udt_name !== col2.udt_name
|| col1.character_maximum_length !== col2.character_maximum_length) {
dbdiff.log('-- Previous data type was %s', dataType(col1))
dbdiff.log('ALTER TABLE %s ALTER COLUMN "%s" SET DATA TYPE %s;', tableName, columnName, dataType(col2))
dbdiff.log()
}
if (col1.is_nullable !== col2.is_nullable) {
if (col2.is_nullable === 'YES') {
dbdiff.log('ALTER TABLE %s ALTER COLUMN "%s" DROP NOT NULL;', tableName, columnName)
} else {
dbdiff.log('ALTER TABLE %s ALTER COLUMN "%s" SET NOT NULL;', tableName, columnName)
}
dbdiff.log()
}
})
}
function indexNames(tableName, indexes) {
return _.filter(indexes, function(index) {
return util.format('"%s"."%s"', index.nspname, index.indrelid) === tableName
}).map(function(index) {
return index.indname
}).sort()
}
function compareIndexes(tableName, db1, db2) {
var indexes1 = db1.indexes
var indexes2 = db2.indexes
var indexNames1 = indexNames(tableName, indexes1)
var indexNames2 = indexNames(tableName, indexes2)
var diff1 = _.difference(indexNames1, indexNames2)
var diff2 = _.difference(indexNames2, indexNames1)
if (diff1.length > 0) {
diff1.forEach(function(indexName) {
var index = _.findWhere(indexes1, { indname: indexName })
dbdiff.log('DROP INDEX "%s"."%s";', index.nspname, indexName)
})
}
if (diff2.length > 0) {
diff2.forEach(function(indexName) {
var index = _.findWhere(indexes2, { indname: indexName })
dbdiff.log('CREATE INDEX "%s" ON %s USING %s (%s);', indexName, index.indrelid, index.indam, index.indkey_names.join(','))
})
}
var inter = _.intersection(indexNames1, indexNames2)
inter.forEach(function(indexName) {
var index1 = _.findWhere(indexes1, { indname: indexName })
var index2 = _.findWhere(indexes2, { indname: indexName })
if (_.difference(index1.indkey_names, index2.indkey_names).length > 0) {
var index = index2
dbdiff.log('-- Index "%s"."%s" needs to be changed', index.nspname, index.indname)
dbdiff.log('DROP INDEX "%s"."%s";', index.nspname, index.indname)
dbdiff.log('CREATE INDEX "%s" ON %s USING %s (%s);', index.indname, index.indrelid, index.indam, index.indkey_names.join(','))
dbdiff.log()
}
})
}
function isNumber(n) {
return +n == n
}
function sequenceDescription(sequence) {
return util.format('CREATE SEQUENCE %s INCREMENT %s %s %s %s %s CYCLE;',
sequence.name,
sequence.increment,
isNumber(sequence.minimum_value) ? 'MINVALUE '+sequence.minimum_value : 'NO MINVALUE',
isNumber(sequence.maximum_value) ? 'MAXVALUE '+sequence.maximum_value : 'NO MAXVALUE',
isNumber(sequence.start_value) ? 'START '+sequence.start_value : '',
sequence.cycle_option === 'NO' ? 'NO' : ''
)
}
function sequenceNames(db) {
return db.sequences.map(function(sequence) {
return sequence.name
}).sort()
}
function compareSequences(db1, db2) {
var sequenceNames1 = sequenceNames(db1)
var sequenceNames2 = sequenceNames(db2)
var diff1 = _.difference(sequenceNames1, sequenceNames2)
var diff2 = _.difference(sequenceNames2, sequenceNames1)
diff1.forEach(function(sequenceName) {
dbdiff.log('DROP SEQUENCE %s;', sequenceName)
dbdiff.log()
})
diff2.forEach(function(sequenceName) {
var sequence = _.findWhere(db2.sequences, { name: sequenceName })
dbdiff.log(sequenceDescription(sequence))
dbdiff.log()
})
var inter = _.intersection(sequenceNames1, sequenceNames2)
inter.forEach(function(sequenceName) {
var sequence1 = _.findWhere(db1.sequences, { name: sequenceName })
var sequence2 = _.findWhere(db2.sequences, { name: sequenceName })
var desc1 = sequenceDescription(sequence1)
var desc2 = sequenceDescription(sequence2)
if (desc2 !== desc1) {
dbdiff.log('DROP SEQUENCE %s;', sequenceName)
dbdiff.log(desc2)
dbdiff.log()
}
})
}
dbdiff.compareSchemas = function(db1, db2) {
compareSequences(db1, db2)
var tableNames1 = _.keys(db1.tables).sort()
var tableNames2 = _.keys(db2.tables).sort()
var diff1 = _.difference(tableNames1, tableNames2)
var diff2 = _.difference(tableNames2, tableNames1)
diff1.forEach(function(tableName) {
dbdiff.log('DROP TABLE %s;', tableName)
dbdiff.log()
})
diff2.forEach(function(tableName) {
var columns = db2.tables[tableName].map(function(col) {
var type = dataType(col)
return '\n "'+col.column_name+'" '+columnDescription(col)
})
dbdiff.log('CREATE TABLE %s (%s', tableName, columns.join(','))
dbdiff.log(');')
dbdiff.log()
var indexNames2 = indexNames(tableName, db2.indexes)
indexNames2.forEach(function(indexName) {
var index = _.findWhere(db2.indexes, { indname: indexName })
dbdiff.log('CREATE INDEX "%s" ON %s USING %s (%s);', index.indname, index.indrelid, index.indam, index.indkey_names.join(','))
dbdiff.log()
})
})
var inter = _.intersection(tableNames1, tableNames2)
inter.forEach(function(tableName) {
compareTables(tableName, db1, db2)
compareIndexes(tableName, db1, db2)
})
}
dbdiff.compareDatabases = function(conn1, conn2, callback) {
var db1, db2
txain(function(callback) {
dbdiff.describeDatabase(conn1, callback)
})
.then(function(db, callback) {
db1 = db
dbdiff.describeDatabase(conn2, callback)
})
.then(function(db, callback) {
db2 = db
dbdiff.compareSchemas(db1, db2)
callback()
})
.end(callback)
}
if (module.id === require.main.id) {

@@ -356,2 +17,7 @@ var yargs = require('yargs')

.alias('h', 'help')
.option('level', {
alias: 'l',
describe: 'chooses the safety of the sql',
choices: ['safe', 'warn', 'drop']
})
.argv

@@ -361,11 +27,12 @@

var conn2 = argv._[1]
dbdiff.logger = function(msg) {
console.log(msg)
}
dbdiff.compareDatabases(conn1, conn2, function(err) {
if (err) {
console.error(String(err))
var dbdiff = new DbDiff()
dbdiff.compare(conn1, conn2)
.then(() => {
console.log(dbdiff.commands(argv.level))
process.exit(0)
})
.catch((err) => {
console.error(err.stack)
process.exit(1)
}
})
})
}

16

package.json
{
"name": "dbdiff",
"version": "0.2.0",
"version": "0.3.0",
"description": "Compares two postgresql databases and prints SQL commands to modify the first one in order to match the second one",
"main": "index.js",
"scripts": {
"test": "nyc --reporter=lcov ./node_modules/.bin/_mocha"
"test": "mocha",
"coverage": "nyc --reporter=lcov npm test && nyc report"
},

@@ -18,5 +19,5 @@ "keywords": [

"dependencies": {
"multiline": "^1.0.2",
"pg": "^4.4.3",
"txain": "^0.4.1",
"dedent": "^0.6.0",
"pg": "^4.5.5",
"pync": "^1.0.1",
"underscore": "^1.8.3",

@@ -26,4 +27,5 @@ "yargs": "^3.29.0"

"devDependencies": {
"mocha": "^2.3.3",
"nyc": "^3.2.2"
"mocha": "^2.4.5",
"nyc": "^3.2.2",
"standard": "^7.0.1"
},

@@ -30,0 +32,0 @@ "bin": {

46

README.md

@@ -15,6 +15,7 @@ # dbdiff

# Usage
# CLI Usage
```
dbdiff \
-l safe
postgres://user:pass@host[:port]/dbname1 \

@@ -24,13 +25,17 @@ postgres://user:pass@host[:port]/dbname2

# Caveats
The flag `-l` or `--level` indicates the safety of the SQL. Allowed values are `safe`, `warn` and `drop`
Some statements may fail or may produce data loss depending on the data stored in the target database. For example:
# Safety level
## Dropping tables and columns
Some statements may fail or may produce data loss depending on the data stored in the target database.
`dbdiff` will generate `DROP TABLE` and `DROP COLUMN` statements. Make sure you want to drop those tables / columns.
- When the `safe` level is specified, only SQL statements that are guaranteed to preserve existing data will be printed. Any other command will be commented out.
- When the `warn` level is specified also SQL statements that *may* fail because of existing data will be printed. These commands are for example: changes in data types or dropping a `NOT NULL` constraint.
- When the `drop` level is specified all SQL statements are printed and this may contain `DROP COLUMN` or `DROP TABLE` statements.
Dropping a sequence or dropping an index is considered safe.
## Changing the data type of existing columns
Postgresql is not able to change the existing data to the new data type. In that case you will get an error similar to this:
Sometimes Postgresql won't be able to change the existing data to the new data type. In that case you will get an error similar to this:

@@ -42,3 +47,3 @@ ```

So you will need to specify a `USING` expression to perform de conversion. For example to convert text to integers:
You can manually specify a `USING` expression to perform de conversion. For example to convert text to integers:

@@ -50,11 +55,24 @@ ```

## NOT NULL violations
# Usage as a library
If an existing column needs to be changed from nullable to not nullable the statement may fail if there are existing rows with a `NULL` value in that column.
In that case you will get an error like:
You can use `dbdiff` as a library:
```javascript
var dbdiff = require('dbdiff')
dbdiff.describeDatabase(connString)
.then((schema) => {
// schema is a JSON-serializable object representing the database structure
})
var diff = new dbdiff.DbDiff()
// Compare two databases passing the connection strings
diff.compare(conn1, conn2)
.then(() => {
console.log(diff.commands('drop'))
})
// Compare two schemas
diff.compareSchemas(schema1, schema2)
console.log(diff.commands('drop'))
```
ERROR: column "column_name" contains null values.
```
You should fill the existing rows with not null values before making the column not nullable.

268

test/test.js

@@ -1,131 +0,155 @@

var dbdiff = require('../index')
/* globals describe it */
var utils = require('./utils')
var assert = require('assert')
var exec = require('child_process').exec
var txain = require('txain')
var childProcess = require('child_process')
var dedent = require('dedent')
describe('dbdiff.compareDatabases', function() {
beforeEach(function(done) {
utils.resetDatabases(done)
const exec = (cmd) => {
return new Promise((resolve, reject) => {
childProcess.exec(cmd, (err, stdout, stderr) => {
err && !stderr ? reject(err) : resolve({ stdout, stderr })
})
})
}
it('should create a table', function(done) {
describe('dbdiff.compareDatabases', () => {
it('should create a table', () => {
var commands1 = []
var commands2 = ['CREATE TABLE users (email VARCHAR(255), tags varchar(255)[])']
var expected = [
'CREATE TABLE "public"."users" (\n "email" character varying(255) NULL,\n \"tags\" varchar[] NULL',
');',
]
utils.runAndCompare(commands1, commands2, expected, done)
var expected = dedent`
CREATE TABLE "public"."users" (
"email" character varying(255) NULL,
"tags" varchar[] NULL
);
`
return utils.runAndCompare(commands1, commands2, expected)
})
it('should drop a table', function(done) {
it('should drop a table', () => {
var commands1 = ['CREATE TABLE users (email VARCHAR(255))']
var commands2 = []
var expected = [
'DROP TABLE "public"."users";',
]
utils.runAndCompare(commands1, commands2, expected, done)
return Promise.resolve()
.then(() => {
var expected = 'DROP TABLE "public"."users";'
return utils.runAndCompare(commands1, commands2, expected, ['drop'])
})
.then(() => {
var expected = '-- DROP TABLE "public"."users";'
return utils.runAndCompare(commands1, commands2, expected, ['safe', 'warn'])
})
})
it('should create a table wih an index', function(done) {
it('should create a table wih an index', () => {
var commands1 = []
var commands2 = ['CREATE TABLE users (id serial)']
var expected = [
'CREATE SEQUENCE "public"."users_id_seq" INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 NO CYCLE;',
'CREATE TABLE "public"."users" (\n "id" integer DEFAULT nextval(\'users_id_seq\'::regclass) NOT NULL',
');',
]
utils.runAndCompare(commands1, commands2, expected, done)
var expected = dedent`
CREATE SEQUENCE "public"."users_id_seq" INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 NO CYCLE;
CREATE TABLE "public"."users" (
"id" integer DEFAULT nextval('users_id_seq'::regclass) NOT NULL
);
`
return utils.runAndCompare(commands1, commands2, expected)
})
it('should add a column to a table', function(done) {
it('should add a column to a table', () => {
var commands1 = ['CREATE TABLE users (email VARCHAR(255))']
var commands2 = [
'CREATE TABLE users (email VARCHAR(255))',
'ALTER TABLE users ADD COLUMN first_name VARCHAR(255)',
'ALTER TABLE users ADD COLUMN first_name VARCHAR(255)'
]
var expected = [
'ALTER TABLE "public"."users" ADD COLUMN "first_name" character varying(255) NULL;',
]
utils.runAndCompare(commands1, commands2, expected, done)
var expected = 'ALTER TABLE "public"."users" ADD COLUMN "first_name" character varying(255) NULL;'
return utils.runAndCompare(commands1, commands2, expected)
})
it('should drop a column from a table', function(done) {
it('should drop a column from a table', () => {
var commands1 = [
'CREATE TABLE users (email VARCHAR(255))',
'ALTER TABLE users ADD COLUMN first_name VARCHAR(255)',
'ALTER TABLE users ADD COLUMN first_name VARCHAR(255)'
]
var commands2 = [
'CREATE TABLE users (email VARCHAR(255))',
'CREATE TABLE users (email VARCHAR(255))'
]
var expected = [
'ALTER TABLE "public"."users" DROP COLUMN "first_name";',
]
utils.runAndCompare(commands1, commands2, expected, done)
return Promise.resolve()
.then(() => {
var expected = 'ALTER TABLE "public"."users" DROP COLUMN "first_name";'
return utils.runAndCompare(commands1, commands2, expected, ['drop'])
})
.then(() => {
var expected = '-- ALTER TABLE "public"."users" DROP COLUMN "first_name";'
return utils.runAndCompare(commands1, commands2, expected, ['safe', 'warn'])
})
})
it('should change the type of a column', function(done) {
it('should change the type of a column', () => {
var commands1 = [
'CREATE TABLE users (email VARCHAR(255))',
'ALTER TABLE users ADD COLUMN first_name VARCHAR(200)',
'ALTER TABLE users ADD COLUMN first_name VARCHAR(200)'
]
var commands2 = [
'CREATE TABLE users (email VARCHAR(255))',
'ALTER TABLE users ADD COLUMN first_name VARCHAR(255)',
'ALTER TABLE users ADD COLUMN first_name VARCHAR(255)'
]
var expected = [
'-- Previous data type was character varying(200)',
'ALTER TABLE "public"."users" ALTER COLUMN "first_name" SET DATA TYPE character varying(255);',
]
utils.runAndCompare(commands1, commands2, expected, done)
return Promise.resolve()
.then(() => {
var expected = dedent`
-- Previous data type was character varying(200)
ALTER TABLE "public"."users" ALTER COLUMN "first_name" SET DATA TYPE character varying(255);
`
return utils.runAndCompare(commands1, commands2, expected, ['drop', 'warn'])
})
.then(() => {
var expected = dedent`
-- Previous data type was character varying(200)
-- ALTER TABLE "public"."users" ALTER COLUMN "first_name" SET DATA TYPE character varying(255);
`
return utils.runAndCompare(commands1, commands2, expected, ['safe'])
})
})
it('should change a column to not nullable', function(done) {
it('should change a column to not nullable', () => {
var commands1 = [
'CREATE TABLE users (email VARCHAR(255))',
'ALTER TABLE users ADD COLUMN first_name VARCHAR(255)',
'ALTER TABLE users ADD COLUMN first_name VARCHAR(255)'
]
var commands2 = [
'CREATE TABLE users (email VARCHAR(255))',
'ALTER TABLE users ADD COLUMN first_name VARCHAR(255) NOT NULL',
'ALTER TABLE users ADD COLUMN first_name VARCHAR(255) NOT NULL'
]
var expected = [
'ALTER TABLE "public"."users" ALTER COLUMN "first_name" SET NOT NULL;',
]
utils.runAndCompare(commands1, commands2, expected, done)
return Promise.resolve()
.then(() => {
var expected = 'ALTER TABLE "public"."users" ALTER COLUMN "first_name" SET NOT NULL;'
return utils.runAndCompare(commands1, commands2, expected, ['drop', 'warn'])
})
.then(() => {
var expected = '-- ALTER TABLE "public"."users" ALTER COLUMN "first_name" SET NOT NULL;'
return utils.runAndCompare(commands1, commands2, expected, ['safe'])
})
})
it('should change a column to nullable', function(done) {
it('should change a column to nullable', () => {
var commands1 = [
'CREATE TABLE users (email VARCHAR(255))',
'ALTER TABLE users ADD COLUMN first_name VARCHAR(255) NOT NULL',
'ALTER TABLE users ADD COLUMN first_name VARCHAR(255) NOT NULL'
]
var commands2 = [
'CREATE TABLE users (email VARCHAR(255))',
'ALTER TABLE users ADD COLUMN first_name VARCHAR(255)',
'ALTER TABLE users ADD COLUMN first_name VARCHAR(255)'
]
var expected = [
'ALTER TABLE "public"."users" ALTER COLUMN "first_name" DROP NOT NULL;',
]
utils.runAndCompare(commands1, commands2, expected, done)
var expected = 'ALTER TABLE "public"."users" ALTER COLUMN "first_name" DROP NOT NULL;'
return utils.runAndCompare(commands1, commands2, expected)
})
it('should create a sequence', function(done) {
it('should create a sequence', () => {
var commands1 = []
var commands2 = ['CREATE SEQUENCE seq_name']
var expected = [
'CREATE SEQUENCE "public"."seq_name" INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 NO CYCLE;',
]
utils.runAndCompare(commands1, commands2, expected, done)
var expected = 'CREATE SEQUENCE "public"."seq_name" INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 NO CYCLE;'
return utils.runAndCompare(commands1, commands2, expected)
})
it('should drop a sequence', function(done) {
it('should drop a sequence', () => {
var commands1 = ['CREATE SEQUENCE seq_name']
var commands2 = []
var expected = [
'DROP SEQUENCE "public"."seq_name";',
]
utils.runAndCompare(commands1, commands2, expected, done)
var expected = 'DROP SEQUENCE "public"."seq_name";'
return utils.runAndCompare(commands1, commands2, expected)
})

@@ -135,6 +159,6 @@

it('should create an index', function(done) {
it('should create an index', () => {
var commands1 = [
'CREATE TABLE users (email VARCHAR(255))',
'ALTER TABLE users ADD COLUMN first_name VARCHAR(255)',
'ALTER TABLE users ADD COLUMN first_name VARCHAR(255)'
]

@@ -144,27 +168,23 @@ var commands2 = [

'ALTER TABLE users ADD COLUMN first_name VARCHAR(255)',
'CREATE INDEX users_email ON users (email)',
'CREATE INDEX users_email ON "users" (email)'
]
var expected = [
'CREATE INDEX "users_email" ON users USING btree (email);',
]
utils.runAndCompare(commands1, commands2, expected, done)
var expected = 'CREATE INDEX "users_email" ON "public"."users" USING btree (email);'
return utils.runAndCompare(commands1, commands2, expected)
})
it('should drop an index', function(done) {
it('should drop an index', () => {
var commands1 = [
'CREATE TABLE users (email VARCHAR(255))',
'ALTER TABLE users ADD COLUMN first_name VARCHAR(255)',
'CREATE INDEX users_email ON users (email)',
'CREATE INDEX users_email ON users (email)'
]
var commands2 = [
'CREATE TABLE users (email VARCHAR(255))',
'ALTER TABLE users ADD COLUMN first_name VARCHAR(255)',
'ALTER TABLE users ADD COLUMN first_name VARCHAR(255)'
]
var expected = [
'DROP INDEX "public"."users_email";',
]
utils.runAndCompare(commands1, commands2, expected, done)
var expected = 'DROP INDEX "public"."users_email";'
return utils.runAndCompare(commands1, commands2, expected)
})
it('should recreate an index', function(done) {
it('should recreate an index', () => {
var commands1 = [

@@ -174,3 +194,3 @@ 'CREATE TABLE users (email VARCHAR(255))',

'ALTER TABLE users ADD COLUMN last_name VARCHAR(255)',
'CREATE INDEX some_index ON users (first_name)',
'CREATE INDEX some_index ON "users" (first_name)'
]

@@ -181,54 +201,64 @@ var commands2 = [

'ALTER TABLE users ADD COLUMN last_name VARCHAR(255)',
'CREATE INDEX some_index ON users (last_name)',
'CREATE INDEX some_index ON "users" (last_name)'
]
var expected = [
'-- Index "public"."some_index" needs to be changed',
'DROP INDEX "public"."some_index";',
'CREATE INDEX "some_index" ON users USING btree (last_name);',
]
utils.runAndCompare(commands1, commands2, expected, done)
var expected = dedent`
-- Index "public"."some_index" needs to be changed
DROP INDEX "public"."some_index";
CREATE INDEX "some_index" ON "public"."users" USING btree (last_name);
`
return utils.runAndCompare(commands1, commands2, expected)
})
it('should create a table with an index', function(done) {
it('should create a table with an index', () => {
var commands1 = []
var commands2 = [
'CREATE TABLE users (email VARCHAR(255))',
'CREATE INDEX users_email ON users (email)',
'CREATE INDEX users_email ON users (email)'
]
var expected = [
'CREATE TABLE "public"."users" (\n "email" character varying(255) NULL',
');',
'CREATE INDEX "users_email" ON users USING btree (email);',
]
utils.runAndCompare(commands1, commands2, expected, done)
var expected = dedent`
CREATE TABLE "public"."users" (
"email" character varying(255) NULL
);
CREATE INDEX "users_email" ON "public"."users" USING btree (email);
`
return utils.runAndCompare(commands1, commands2, expected)
})
it('should run as a cli application', function(done) {
it('should run as a cli application', () => {
var conString1 = 'postgres://postgres:postgres@localhost/db1'
var conString2 = 'postgres://postgres:postgres@localhost/db2'
txain(function(callback) {
utils.runCommands(['CREATE SEQUENCE seq_name'], [], callback)
})
.then(function(arg, callback) {
exec('node index.js '+conString1+' '+conString2, function(err, stdout, stderr) {
assert.ifError(err)
assert.equal(stdout, 'DROP SEQUENCE "public"."seq_name";\n\n')
done()
return utils.runCommands(['CREATE SEQUENCE seq_name'], [])
.then(() => exec(`node index.js ${conString1} ${conString2}`))
.then((result) => {
var { stdout } = result
assert.equal(stdout, 'DROP SEQUENCE "public"."seq_name";\n')
})
})
.end(done)
})
it('should fail with an erorr', function(done) {
it('should run as a cli application with level argument', () => {
var conString1 = 'postgres://postgres:postgres@localhost/db1'
var conString2 = 'postgres://postgres:postgres@localhost/db2'
return utils.runCommands(['CREATE TABLE users (email VARCHAR(255))'], [])
.then(() => exec(`node index.js -l safe ${conString1} ${conString2}`))
.then((result) => {
var { stdout } = result
assert.equal(stdout, '-- DROP TABLE "public"."users";\n')
})
})
it('should fail with an erorr', () => {
var conString1 = 'postgres://postgres:postgres@localhost/db1'
var conString2 = 'postgres://postgres:postgres@localhost/none'
require('child_process').exec('node index.js '+conString1+' '+conString2, function(err, stdout, stderr) {
assert.ok(err)
assert.ok(stderr.indexOf('error: database "none" does not exist') >= 0)
done()
})
return exec(`node index.js ${conString1} ${conString2}`)
.then((result) => {
var { stderr } = result
assert.ok(stderr.indexOf('error: database "none" does not exist') >= 0)
})
})
})

103

test/utils.js

@@ -1,5 +0,5 @@

var pg = require('pg')
var txain = require('txain')
var dbdiff = require('../')
var Client = require('../dialects/postgres-client')
var DbDiff = require('../dbdiff')
var assert = require('assert')
var pync = require('pync')

@@ -9,76 +9,37 @@ var conString1 = 'postgres://postgres:postgres@localhost/db1'

var client1, client2
var client1 = new Client(conString1)
var client2 = new Client(conString2)
exports.connect = function(callback) {
if (client1) return callback()
client1 = new pg.Client(conString1)
client2 = new pg.Client(conString2)
var arr = [client1, client2]
txain(arr)
.each(function(client, callback) {
client.connect(callback)
})
.end(callback)
exports.resetDatabases = () => {
return Promise.all([
client1.query('drop schema public cascade; create schema public;'),
client2.query('drop schema public cascade; create schema public;')
])
}
exports.resetDatabases = function(callback) {
txain(function(callback) {
exports.connect(callback)
})
.then(function(callback) {
callback(null, [client1, client2])
})
.each(function(client, callback) {
client.query('drop schema public cascade; create schema public;', callback)
})
.end(callback)
exports.runCommands = (commands1, commands2) => {
return exports.resetDatabases()
.then(() => Promise.all([
pync.series(commands1, (command) => client1.query(command)),
pync.series(commands2, (command) => client2.query(command))
]))
}
exports.runCommands = function(commands1, commands2, callback) {
txain(function(callback) {
callback(null, commands1)
exports.runAndCompare = (commands1, commands2, expected, levels = ['drop', 'warn', 'safe']) => {
var dbdiff = new DbDiff()
return pync.series(levels, (level) => {
return exports.runCommands(commands1, commands2)
.then(() => dbdiff.compare(conString1, conString2))
.then(() => assert.equal(dbdiff.commands(level), expected))
.then(() => client1.query(dbdiff.commands(level)))
.then(() => dbdiff.compare(conString1, conString2))
.then(() => {
var lines = dbdiff.commands(level).split('\n')
lines.forEach((line) => {
if (line.length > 0 && line.substring(0, 2) !== '--') {
assert.fail(`After running commands there is a change not executed: ${line}`)
}
})
})
})
.each(function(command, callback) {
client1.query(command, callback)
})
.then(function(callback) {
callback(null, commands2)
}).each(function(command, callback) {
client2.query(command, callback)
})
.end(callback)
}
exports.runAndCompare = function(commands1, commands2, expected, callback) {
var arr = []
dbdiff.logger = function(msg) {
if (msg) {
arr.push(msg)
}
}
txain(function(callback) {
exports.runCommands(commands1, commands2, callback)
})
.then(function(callback) {
dbdiff.compareDatabases(conString1, conString2, callback)
})
.then(function(callback) {
// run the expected commands
client1.query(arr.join('\n'), callback)
})
.then(function(callback) {
assert.deepEqual(arr, expected)
// compare again the dbs
arr.splice(0)
dbdiff.compareDatabases(conString1, conString2, callback)
})
.then(function(callback) {
assert.deepEqual(arr, [])
callback(null, arr)
})
.end(callback)
}
.istanbul.yml
SocketSocket SOC 2 Logo

Product

  • Package Alerts
  • Integrations
  • Docs
  • Pricing
  • FAQ
  • Roadmap
  • Changelog

About

Packages

npm

Go

Maven

PyPI

Rubygems

Stay in touch

Get open source security insights delivered straight into your inbox.

  • Terms
  • Privacy
  • Security

Made with ⚡️ by Socket Inc