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.3.3 to 0.4.0

dialects/mysql-client.js
dialects/mysql.js
test/test-cli.js
test/test-mysql.js
test/test-postgres.js

82

dbdiff.js

@@ -27,2 +27,6 @@ var _ = require('underscore')

_quote (name) {
return this._quotation + name + this._quotation
}
_compareTables (table1, table2) {

@@ -38,3 +42,3 @@ var tableName = this._fullName(table1)

diff1.forEach((columnName) => {
this._drop(`ALTER TABLE ${tableName} DROP COLUMN "${columnName}";`)
this._drop(`ALTER TABLE ${tableName} DROP COLUMN ${this._quote(columnName)};`)
})

@@ -44,3 +48,3 @@

var col = table2.columns.find((column) => column.name === columnName)
this._safe(`ALTER TABLE ${tableName} ADD COLUMN "${columnName}" ${this._columnDescription(col)};`)
this._safe(`ALTER TABLE ${tableName} ADD COLUMN ${this._quote(columnName)} ${this._columnDescription(col)};`)
})

@@ -53,6 +57,13 @@

if (this._dialect === 'mysql' && !_.isEqual(col1, col2)) {
var func = (col1.type !== col2.type || (col1.nullable !== col2.nullable && !col2.nullable)) ? this._warn : this._safe
var extra = col2.extra ? ' ' + col2.extra : ''
var comment = col1.type !== col2.type ? `-- Previous data type was ${col1.type}\n` : ''
func.bind(this)(`${comment}ALTER TABLE ${tableName} MODIFY ${this._quote(columnName)} ${this._columnDescription(col2)}${extra};`)
return
}
if (col1.type !== col2.type) {
this._warn(dedent`
-- Previous data type was ${col1.type}
ALTER TABLE ${tableName} ALTER COLUMN "${columnName}" SET DATA TYPE ${col2.type};
ALTER TABLE ${tableName} ALTER COLUMN ${this._quote(columnName)} SET DATA TYPE ${col2.type};
`)

@@ -62,5 +73,5 @@ }

if (col2.nullable) {
this._safe(`ALTER TABLE ${tableName} ALTER COLUMN "${columnName}" DROP NOT NULL;`)
this._safe(`ALTER TABLE ${tableName} ALTER COLUMN ${this._quote(columnName)} DROP NOT NULL;`)
} else {
this._warn(`ALTER TABLE ${tableName} ALTER COLUMN "${columnName}" SET NOT NULL;`)
this._warn(`ALTER TABLE ${tableName} ALTER COLUMN ${this._quote(columnName)} SET NOT NULL;`)
}

@@ -73,8 +84,17 @@ }

var tableName = this._fullName(table)
var keys = index.keys.map((key) => `"${key}"`) .join(',')
this._safe(`CREATE INDEX "${index.name}" ON ${tableName} USING ${index.type} (${keys});`)
var keys = index.columns.map((key) => `${this._quote(key)}`).join(',')
if (this._dialect === 'postgres') {
this._safe(`CREATE INDEX ${this._quote(index.name)} ON ${tableName} USING ${index.type} (${keys});`)
} else {
// mysql
this._safe(`CREATE INDEX ${this._quote(index.name)} USING ${index.type} ON ${tableName} (${keys});`)
}
}
_dropIndex (index) {
this._safe(`DROP INDEX "${index.schema}"."${index.name}";`)
_dropIndex (table, index) {
if (this._dialect === 'postgres') {
this._safe(`DROP INDEX ${this._fullName(index)};`)
} else {
this._safe(`DROP INDEX ${this._fullName(index)} ON ${this._fullName(table)};`)
}
}

@@ -92,3 +112,3 @@

var index = table1.indexes.find((index) => index.name === indexName)
this._dropIndex(index)
this._dropIndex(table1, index)
})

@@ -108,8 +128,8 @@ }

if (_.difference(index1.keys, index2.keys).length > 0 ||
if (_.difference(index1.columns, index2.columns).length > 0 ||
index1.primary !== index2.primary ||
index1.unique !== index2.unique) {
var index = index2
this._comment(`-- Index "${index.schema}"."${index.name}" needs to be changed`)
this._dropIndex(index)
this._comment(`-- Index ${this._fullName(index)} needs to be changed`)
this._dropIndex(table1, index)
this._createIndex(table1, index)

@@ -157,15 +177,21 @@ }

if (_.isEqual(constraint1, constraint2)) return
this._safe(`ALTER TABLE ${tableName} DROP CONSTRAINT "${constraint2.name}";`)
if (this._dialect === 'postgres') {
this._safe(`ALTER TABLE ${tableName} DROP CONSTRAINT ${this._quote(constraint2.name)};`)
} else {
this._safe(`ALTER TABLE ${tableName} DROP INDEX ${this._quote(constraint2.name)};`)
}
constraint1 = null
}
if (!constraint1) {
var keys = constraint2.keys.map((s) => `"${s}"`).join(', ')
var keys = constraint2.columns.map((s) => `${this._quote(s)}`).join(', ')
var func = (table1 ? this._warn : this._safe).bind(this)
var fullName = this._quote(constraint2.name)
if (constraint2.type === 'primary') {
func(`ALTER TABLE ${tableName} ADD CONSTRAINT "${constraint2.name}" PRIMARY KEY (${keys});`)
if (this._dialect === 'mysql') fullName = 'foo'
func(`ALTER TABLE ${tableName} ADD CONSTRAINT ${fullName} PRIMARY KEY (${keys});`)
} else if (constraint2.type === 'unique') {
func(`ALTER TABLE ${tableName} ADD CONSTRAINT "${constraint2.name}" UNIQUE (${keys});`)
func(`ALTER TABLE ${tableName} ADD CONSTRAINT ${fullName} UNIQUE (${keys});`)
} else if (constraint2.type === 'foreign') {
var foreignKeys = constraint2.foreign_keys.map((s) => `"${s}"`).join(', ')
func(`ALTER TABLE ${tableName} ADD CONSTRAINT "${constraint2.name}" FOREIGN KEY (${keys}) REFERENCES "${constraint2.foreign_table}" (${foreignKeys});`)
var foreignKeys = constraint2.referenced_columns.map((s) => `${this._quote(s)}`).join(', ')
func(`ALTER TABLE ${tableName} ADD CONSTRAINT ${fullName} FOREIGN KEY (${keys}) REFERENCES ${this._quote(constraint2.referenced_table)} (${foreignKeys});`)
}

@@ -191,3 +217,9 @@ }

var columns = table.columns.map((col) => {
return `\n "${col.name}" ${this._columnDescription(col)}`
var extra = ''
if (col.extra === 'auto_increment') {
extra = ' PRIMARY KEY AUTO_INCREMENT'
var constraint = table.constraints.find((constraints) => constraints.type === 'primary')
table.constraints.splice(table.constraints.indexOf(constraint), 1)
}
return `\n ${this._quote(col.name)} ${this._columnDescription(col)}${extra}`
})

@@ -199,3 +231,3 @@ this._safe(`CREATE TABLE ${tableName} (${columns.join(',')}\n);`)

var index = table.indexes.find((index) => index.name === indexName)
this._safe(`CREATE INDEX "${index.name}" ON ${tableName} USING ${index.type} (${index.keys.join(', ')});`)
this._createIndex(table, index)
})

@@ -223,2 +255,7 @@ } else {

var db2 = results[1]
this._dialect = db1.dialect
this._quotation = {
mysql: '`',
postgres: '"'
}[this._dialect]
this.compareSchemas(db1, db2)

@@ -269,3 +306,4 @@ })

_fullName (obj) {
return `"${obj.schema}"."${obj.name}"`
if (obj.schema) return `${this._quote(obj.schema)}.${this._quote(obj.name)}`
return this._quote(obj.name)
}

@@ -272,0 +310,0 @@

1

dialects/index.js

@@ -34,1 +34,2 @@ var url = require('url')

require('./postgres')
require('./mysql')

22

dialects/postgres-client.js
var pg = require('pg')
var querystring = require('querystring')
class PostgresClient {
constructor (conString) {
constructor (options) {
var conString
if (typeof options === 'string') {
conString = options
} else {
var dialectOptions = Object.assign({}, options.dialectOptions)
Object.keys(dialectOptions).forEach((key) => {
var value = dialectOptions[key]
if (typeof value === 'boolean') {
dialectOptions[key] = value ? 'true' : 'false'
}
})
var query = querystring.stringify(dialectOptions)
if (query.length > 0) query = '?' + query
conString = `postgres://${options.username}:${options.password}@${options.host}:${options.port || 5432}/${options.database}${query}`
}
this.conString = conString
}
dropTables () {
return this.query('drop schema public cascade; create schema public;')
}
connect () {

@@ -9,0 +29,0 @@ return new Promise((resolve, reject) => {

33

dialects/postgres.js
var dialects = require('./')
var querystring = require('querystring')
var pync = require('pync')

@@ -7,3 +6,3 @@ var PostgresClient = require('./postgres-client')

class PostgresDialect {
_unescape (str) {
_unquote (str) {
if (str.substring(0, 1) === '"' && str.substring(str.length - 1) === '"') {

@@ -16,20 +15,4 @@ return str.substring(1, str.length - 1)

describeDatabase (options) {
var conString
if (typeof options === 'string') {
conString = options
} else {
var dialectOptions = Object.assign({}, options.dialectOptions)
Object.keys(dialectOptions).forEach((key) => {
var value = dialectOptions[key]
if (typeof value === 'boolean') {
dialectOptions[key] = value ? 'true' : 'false'
}
})
var query = querystring.stringify(dialectOptions)
if (query.length > 0) query = '?' + query
conString = `postgres://${options.username}:${options.password}@${options.host}:${options.port || 5432}/${options.database}${query}`
}
var schema = {}
var client = new PostgresClient(conString)
var schema = { dialect: 'postgres' }
var client = new PostgresClient(options)
return client.find('SELECT * FROM pg_tables WHERE schemaname NOT IN ($1, $2, $3)', ['temp', 'pg_catalog', 'information_schema'])

@@ -107,3 +90,3 @@ .then((tables) => (

type: index.indam,
keys: index.indkey_names
columns: index.indkey_names
})

@@ -126,3 +109,3 @@ })

constraints.forEach((constraint) => {
var tableFrom = this._unescape(constraint.table_from)
var tableFrom = this._unquote(constraint.table_from)
var table = schema.tables.find((table) => table.name === tableFrom && table.schema === constraint.nspname)

@@ -137,3 +120,3 @@ var { description } = constraint

type: types[constraint.contype],
keys: description.substring(i + 1, n).split(',').map((s) => s.trim())
columns: description.substring(i + 1, n).split(',').map((s) => s.trim())
}

@@ -145,4 +128,4 @@ table.constraints.push(info)

n = substr.indexOf(')')
info.foreign_table = substr.substring(0, i).trim()
info.foreign_keys = substr.substring(i + 1, n).split(',').map((s) => s.trim())
info.referenced_table = substr.substring(0, i).trim()
info.referenced_columns = substr.substring(i + 1, n).split(',').map((s) => s.trim())
}

@@ -149,0 +132,0 @@ })

5

package.json
{
"name": "dbdiff",
"version": "0.3.3",
"description": "Compares two postgresql databases and prints SQL commands to modify the first one in order to match the second one",
"version": "0.4.0",
"description": "Compares two databases and prints SQL commands to modify the first one in order to match the second one",
"main": "index.js",

@@ -20,2 +20,3 @@ "scripts": {

"dedent": "^0.6.0",
"mysql": "^2.10.2",
"pg": "^4.5.5",

@@ -22,0 +23,0 @@ "pync": "^1.0.1",

127

README.md
# dbdiff
Compares two postgresql databases and prints SQL commands to modify the first one in order to match the second one.
Compares two databases and prints SQL commands to modify the first one in order to match the second one.
**It does NOT execute the statements**. It only prints the statements to the standard output.
**It does NOT execute the statements**. It only prints the statements.
It supports PostgreSQL and MySQL.
# Installing

@@ -20,6 +22,8 @@

-l safe
postgres://user:pass@host[:port]/dbname1 \
postgres://user:pass@host[:port]/dbname2
dialect://user:pass@host[:port]/dbname1 \
dialect://user:pass@host[:port]/dbname2
```
Where `dialect` can be either `postgres` or `mysql`.
The flag `-l` or `--level` indicates the safety of the SQL. Allowed values are `safe`, `warn` and `drop`

@@ -81,3 +85,3 @@

dbdiff.describeDatabase({
dialect: 'postgres',
dialect: 'postgres', // use `mysql` for mysql
username: 'user',

@@ -95,1 +99,114 @@ password: 'pass',

```
# Example of `.describeDatabase()` output
```json
{
"tables": [
{
"name": "users",
"schema": "public",
"indexes": [],
"constraints": [
{
"name": "email_unique",
"schema": "public",
"type": "unique",
"keys": [
"email"
]
},
{
"name": "users_pk",
"schema": "public",
"type": "primary",
"keys": [
"id"
]
}
],
"columns": [
{
"name": "id",
"nullable": false,
"defaultValue": "nextval('users_id_seq'::regclass)",
"type": "integer"
},
{
"name": "email",
"nullable": true,
"defaultValue": null,
"type": "character varying(255)"
}
]
},
{
"name": "items",
"schema": "public",
"indexes": [],
"constraints": [
{
"name": "items_fk",
"schema": "public",
"type": "foreign",
"keys": [
"user_id"
],
"referenced_table": "users",
"referenced_columns": [
"id"
]
}
],
"columns": [
{
"name": "id",
"nullable": false,
"defaultValue": "nextval('items_id_seq'::regclass)",
"type": "integer"
},
{
"name": "name",
"nullable": true,
"defaultValue": null,
"type": "character varying(255)"
},
{
"name": "user_id",
"nullable": true,
"defaultValue": null,
"type": "bigint"
}
]
}
],
"sequences": [
{
"data_type": "bigint",
"numeric_precision": 64,
"numeric_precision_radix": 2,
"numeric_scale": 0,
"start_value": "1",
"minimum_value": "1",
"maximum_value": "9223372036854775807",
"increment": "1",
"schema": "public",
"name": "users_id_seq",
"cycle": false
},
{
"data_type": "bigint",
"numeric_precision": 64,
"numeric_precision_radix": 2,
"numeric_scale": 0,
"start_value": "1",
"minimum_value": "1",
"maximum_value": "9223372036854775807",
"increment": "1",
"schema": "public",
"name": "items_id_seq",
"cycle": false
}
]
}
```

85

test/utils.js

@@ -1,2 +0,1 @@

var Client = require('../dialects/postgres-client')
var DbDiff = require('../dbdiff')

@@ -6,51 +5,49 @@ var assert = require('assert')

var conString1 = 'postgres://postgres:postgres@localhost/db1'
var conString2 = 'postgres://postgres:postgres@localhost/db2'
class Utils {
constructor (dialect, conn1, conn2) {
var Client = require(`../dialects/${dialect}-client`)
this.dialect = dialect
this.conn1 = conn1
this.conn2 = conn2
this.client1 = new Client(conn1)
this.client2 = new Client(conn2)
}
var conSettings2 = {
dialect: 'postgres',
username: 'postgres',
password: 'postgres',
database: 'db2',
host: 'localhost',
dialectOptions: {
ssl: false
resetDatabases () {
return Promise.all([
this.client1.dropTables(),
this.client2.dropTables()
])
}
}
var client1 = new Client(conString1)
var client2 = new Client(conString2)
runCommands (commands1, commands2) {
return this.resetDatabases()
.then(() => Promise.all([
pync.series(commands1, (command) => this.client1.query(command)),
pync.series(commands2, (command) => this.client2.query(command))
]))
}
exports.resetDatabases = () => {
return Promise.all([
client1.query('drop schema public cascade; create schema public;'),
client2.query('drop schema public cascade; create schema public;')
])
runAndCompare (commands1, commands2, expected, levels = ['drop', 'warn', 'safe']) {
var dbdiff = new DbDiff()
return pync.series(levels, (level) => {
return this.runCommands(commands1, commands2)
.then(() => dbdiff.compare(this.conn1, this.conn2))
.then(() => assert.equal(dbdiff.commands(level), expected))
.then(() => this.client1.query(dbdiff.commands(level)))
.then(() => dbdiff.compare(this.conn1, this.conn2))
.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}`)
}
})
})
})
}
}
exports.runCommands = (commands1, commands2) => {
return exports.resetDatabases()
.then(() => Promise.all([
pync.series(commands1, (command) => client1.query(command)),
pync.series(commands2, (command) => client2.query(command))
]))
module.exports = (dialect, conn1, conn2) => {
return new Utils(dialect, conn1, conn2)
}
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, conSettings2))
.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}`)
}
})
})
})
}
test/test.js
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